Class #5: Mixing It Up

Oh Saint Anthony, please help those students
who were forced to attend practice graduation
instead of today's class
Persevere towards passing this class!

As the title says, after this class, only three classes remain. Two weeks from today, we will have an evaluation that will go something as follows:

I will give each of you three printed pages, a page created from a Word file, and three PowerPoint slides. Two of the slides will have Excel charts on them, and the other slide is a graphics slide created straight in PowerPoint. Sample data in an Excel file, and pastable text will be supplied so that each student can reproduce what they see on the printed page in a Word, Excel, and PowerPoint file that each student will upload to their wiki page for evaluation.

Next week we will look at some of the more advanced bells and whistles in PowerPoint, but the skills you will need for the evaluation we have already investigated in previous classes. We will also prepare for the evaluation next week.

Today, here's what's on tap...

First things first. Each group needs to choose one of the following lists of trees to be researched:

List #1	List #2	List #3	List #4
Norway Maple Honeylocust Ginkgo Horsechestnut Japanese Maple Chinese Elm Tree of Heaven Hawthorn Purpleleaf Plum Willow Oak Mulberry English Oak	Red Maple Callery Pear Japanese Tree Lilac Sugar Maple Sycamore Maple Zelkova Goldenrain Tree Crabapple Serviceberry Sawtooth Oak Schubert Cherry Dawn Redwood	Silver Maple Pin Oak Flowering Dogwood Hedge Maple Black Locust Sweetgum Eastern Redbud Flowering Cherry Hackberry Magnolia Northern Red Oak Eastern White Pine	Green Ash London Planetree Katsura Tree Amur Maple American Elm Kentucky Coffeetree Tuliptree Birch European Hornbeam Poplar Swamp White Oak Baldcypress

Once your group has decided on a list, post a comment to this blog, including the names of everyone in the group, and the list number. Once a list has been chosen, no other group may take it.

Objective

Each group needs to figure out who wants to do some tree research on the Internet, saving what they learn to a Word template, or dig deeper into Excel charts. Whatever you choose, we have already covered the material that will be covered in the evaluation two weeks from now, so whatever you choose will not affect your ability to pass the evaluation. If you choose the tree research, you can be working on it in this class with one eye on the Excel instruction I will be delivering, if you choose Excel, I need both of your eyes, as you will be creating more Excel graphs.

Tree Words

Basically, each group is responsible for filling out a form in a Word template for each tree in the tree list chosen at the beginning of this class. For each tree, use your Internet research skills to find out the following things about the tree:

1. Its scientific (Latin) name
2. Check the correct boxes at the top of the form (is it an conifer or broadleaf, etc.)
3. Give a one sentence description for identifying the leaf, flowers, and tree dimensions.
4. Find three good webpages (not the homepage!) that offer good information about the tree.
5. A medium sized, black and white line drawing of a tree limb. When you are ready to paste a graphic into the form, get my attention, and I will show you how to do it.
   
6. Make sure to include your name for credit.

I have created a sample form for the honeylocust tree for your reference.

And now to the Excel graphs...

Excel Charts, Part II.

Let's try using some detailed data from the United Nations site. In Panel 2, select the first variable, 'Population by five-year age group and sex' and your world region at the bottom of the right-hand list. Click on the 'Download as .CSV file' button.

CSV, or comma separated values, is a simple spreadsheet format. When Excel opens up a .csv file, it begins by placing the first letters or numbers into cell A1, until it reaches a comma. Then whatever is to the right of that comma goes into cell B1, until it reaches the next comma, and so on. A paragraph return means to stop putting characters in row A, and start the process all over again in cell B1. The most important thing about opening a .csv file in Excel is that as soon as it is open, save it as an Excel Workbook.

Saving as a tab in a workbook

Do you have your Excel file open from last week? If you don't open it now. Let's say you have downloaded a bunch of spreadsheets from the Internet and you want to consolidate them all into one book. Let's save the spreadsheet you just downloaded from the Internet to last week's Excel file.

1. Right click on the single table in the UN spreadsheet
   
2. Select 'Move or Copy'
3. In the first dropdown menu, select last week's spreadsheet
4. In the second menu, select (move to end)

Using this technique, you can keep all of your spreadsheets in one file. I would recommend doing this as you can be certain that whatever spreadsheet you may be looking for, you know it has to be in this one Excel file. And don't forget about using the Ctrl-Page Up/Down to move from tab to tab.

Let's look at the data. What we downloaded is a count of all the men and women in specific age group, in the past and into the future. What we want to do is create a graph that "stacks" all of the age group counts for a particular year into one large bar. We will have to use several techniques, sorting, pivot tables, and graph formatting to make this happen.

Sorting Fields

Sorting an Excel spreadsheet allows us to work with a smaller set of data in the table. In this case, we're not interested in the male/female counts, only the total number. Anytime you want to sort a spreadsheet, use the following key strokes:

• Ctrl-Home
• Ctrl-Shift-down arrow
• Ctrl-Shift-right arrow

Now all of your data is selected. Just like when creating a chart, it is a lot easier to sort if you select the data you are interested in first. So now we can sort.

Either click on the button with the blue A, red Z and black down arrow, or select (use the Alt key, not the mouse!) the 'Data' drop down menu, and select the first option, 'Sort'. Make sure the 'My List has' radio button has the 'Header Row' option selected. Header rows are the first row in a spreadsheet, and each cell describes what the remaining data in that column means. In this case, we want to sort to get the 'Total' counts all by themselves, so in the first 'Sort By' dropdown, select 'Sex' and click on the 'Descending' radio button. After hitting the 'OK' button, you should see all of the 'Total' rows as the top of the spreadsheet.

Replacing Text

Now let's get rid of all the data we don't need, that is, all of the 'Male' and 'Female' records. Scroll down (using the arrow keys, or Page Down button) until you get to the first 'Male' record, which should be around row 443. Select the whole row (Ctrl-Shift-right arrow), then the rest of the data (Ctrl-Shift-End). Hit the 'Delete' key. Now the only data we have to work with is the 'Total' counts.

Go back up to the top of the spreadsheet (Ctrl-Home). The next thing we need to do is change some of the age ranges. I'll explain why later. Any cell in the 'Age' Column that has '0-4' must be changed to '00-04'. We could type the new range into each and every cell, but there is a faster way to do it, using the Replace function. Let's see how it works.

1. First, go to the first cell that has the range '0-4' (are you using the keyboard?).
   
2. Copy (Ctrl-c) that cell, which should be F2. The moving dotted line lasso should highlight that cell.
   
3. Now select the entire F column (click on the 'F' cell).
   
4. Use the Ctrl-f hotkey to bring up the Find function. We want to replace, however, so
   
5. use the Ctrl-Tab hotkey to select the Replace tab in the dialog box.
   
6. Now we can swap out all of the '0-4' cells in one fell swoop. In the first box, which says, 'Find what:' double click on the box so that the entire long box is highlighted.
   
7. Paste (Ctrl-v) your search term ('0-4') into the box.
8. Hit the Tab key to go to the next box.
9. Type in ' 00-04' (the numbers, not the quotes) into the 'Replace With:' box. Make sure to include a space before the first zero.
10. Hit the Tab button twice, and hit the 'Replace All' button.
    

Voila! All 14 cells have been changed! And you didn't have to type anything in the cells to make it happen. Like making charts last week, this technique is a little difficult to master, but you'll end up saving a lot of time (and making more money) if you master this skill.

Now swap out all of the ' 5-9' cells with ' 05-09'.

Pivot Tables

Go back to the Oceania PowerPoint file and look at the Age Trends slide. Are you getting a sense how the spreadsheet we have been editing for the last hour has something to do with the stacked bar graph? Look at the legend on the right side of the graph. See the age groups? Look at the X-axis labels. See the dates? We have the data, the only thing that remains to be done is figuring out how to use the Chart Wizard that we used last week to make the actual stacked bar chart.

Easier said than done, however. Do you see the problem? How many columns of data do we need to make this chart? Don't just look at the answer below. Look at the spreadsheet, and think about it.

.
.
.

Which columns of data do we need? Year, Age, and Value, so we need three columns of data. However, two dimensional graphs (X and Y axis) can only handle two columns of data. So what do we do? Remember this rule:

Whenever you have more than two columns of data to graph,
use a pivot table.

The easiest way to understand what a pivot table requires actually making one. So let's do it.

1. Select all the data.
2. Click on the 'Finish' button. Again, if you select the data before sorting, charting, or making a pivot table, your life will be a lot easier.
3. A new blank spreadsheet should open up, and a 'PivotTable Field list drop down docker should be visible.
4. Click and drag the Year field into the 'Drop Row Fields Here' box.
5. Click and drag the Age field into the 'Drop Column Fields Here' box.
6. Click and drag the Values field into the 'Drop Data Items Here' box.

You have made a Pivot Table! Now can you see how we're going to make that stacked bar graph? Each column represents a specific year, and each row represents a specific age range. There is one problem, the age sequence goes from 0-4, to 5-9, and the next one is 100+. This is why we had to swap out those age ranges. This pivot table views the age ranges as letters, not numbers, because of the dash in each cell. So the easiest thing to do is simply get rid of the 100+ values, most of them are zeros anyway. Click on the Age dropdown menu, and uncheck the 100+ box. Now the entire 100+ has disappeared.

Onto the Chart! Click on the Chart Wizard button. We have now created a pivot table, stacked bar chart. We should now format the chart, to make it look nicer. Make sure to do the following things:

1. Delete the ugly plot area.
2. Format the Y axis values as numbers with the 1000s separator on.
3. Change the colors of each age range by right clicking on the first stack (0-4) and select 'Format Data Series.' Under the 'Patterns' tab, select a light fill color. Make the bars thicker by selecting 'Options' tab, and change the gap width to '50.'
4. Right click on the next stack (5-9) and select a little darker fill color, and so on.
5. When you're done, copy and paste the stacked bar chart into your PowerPoint slide.
   

Getting the feel of making graphs? Let's try one more.

Energy Graphs

Global warming is a big issue across the world right now, and one of its major causes is the burning of fossil fuels. So let's see how much oil each region of the world is burning.

Instead of saving a new tab into your Excel workbook, this time we will use the old fashioned "cut and paste" technique. So right click on one of your tabs and select 'Insert...' Double click on the 'Worksheet' icon to create a new blank spreadsheet in your Excel file.

Now open British Petroleum's world energy analysis world energy analysis spreadsheet. Click on the Row 18 'Oil Consumption' link. This tab offers historical data on each country's consumption of oil each year. Follow these step to get the information you need to create an energy graph.

1. Select Row 3. Copy and paste that row into the first row ('A') of your blank spreadsheet.
   
2. Let's say your group is doing North America. Select row 5, the 'US' row by click on the '5' cell. Copy and paste it into your blank spreadsheet on row 'B'. Copy all rows that list a nation in your region into your spreadsheet.
3. Select all of your pasted data, except the last two columns (the % data).
4. Create a stacked bar graph. Make sure to format the chart in the following ways:

• Plot Area – hit the Delete button.
• Title text – make it 16 point.
• Axis text – make it 14 point.
• Y axis – select the Number format from the list, use zero decimal places, and check on the 1000 separator.
  
• X axis –make it 12 point.
• Stacked bars –Right click on the curve, select 'Format Data Series,' and choose some nice color backgrounds. Fatten the bars.

Repeat the process for 'Primary Energy: Consumption Mtoe (from 1965)', Row 51 on the 'Contents' tab.

• Plot Area – hit the Delete button.
• Title text – make it 16 point.
• Axis text – make it 14 point.
• Y axis – select the Number format from the list, use zero decimal places, and check on the 1000 separator.
  
• X axis –Make the minimum 1950 and the maximum 2050.
• Series curve –Right click on the curve, select 'Format Data Series,' and choose the heaviest weight for the curve.