Class #4: Excel

Ola Hurrikan, great god of misery!
Please strike down those wooden puppets who have no respect for these computers!
Throw the malcontents up into the trees!
Please hear the cries of our Boricua students who cannot use the broken computers!

Check out the new tree map from another of my Science lab groups! For extra credit, see if you can include any of the trees in your PowerPoint slides.

Objective

Each of you will (surprise) work in groups of 3-5 and create a bunch of PowerPoint slides that contain Excel graphs generated by data you will download from several Internet sites. First, get yourselves in groups, and figure out which of the following world regions (except Oceania) your group will be investigating. Post a comment to this blog, including the names of everyone in your group, and the region your group will be responsible for. First come, first serve!

Reference Maps

• North America | Caribbean
• South America
• Europe
• Middle East
• Asia
• Oceania
• Africa

Now let's take a look at what each group is responsible for. Open up the Oceania PowerPoint file
and page through the slides to get a sense of what your final product should look like. Answer the following questions:

1. What is the list on the first page?
2. What kinds of graphs are on the slides?
3. What sort of information do the graphs communicate?
   

Each of you will be responsible for producing at least two of these slides. The more slides, the better the group's evaluation will be. Of course, each slide should look good as well, including font and color formatting. These Oceania slides should illustrate exactly what I am looking for in terms of presentation.

So how did I make these graphs? Let's look at the Excel file that I used to make them. But first, remember, don't panic if the file looks strange to you. We will be going through its creation step by step. So open up the

Oceania Excel file

Let's look at how we can move through the file. Each 'page' is a 'spreadsheet,' or a graph that is created from data in another spreadsheet. At the bottom of the page are the 'tabs,' allow you to move between the various pages. You can use the mouse (arrrggg...) to click on a tab, or, hold down the Ctrl button, and hit the 'Page Up' or 'Page Down' button. The highlighted tab means you are looking at that specific spreadsheet page in the Excel book. Page back and forth through the Excel book to get a feel for what is in the Excel file.

So how did I get that data? If you go back to the PowerPoint file, you will see an Internet URL at the bottom right corner of each slide. So first we will download data from these websites, into an Excel file. Here are the steps to create the first graph:

1. Go to the United Nations Population Division website.
2. In the first box, select 'Population'
3. In the second box, scroll down to the bottom of the list and select your group's region
4. Click on the 'Download as a .CSV file' button
5. Click on the 'OK' button to open the file in Excel.
6. (Most important) Save the file as an Excel file with your name, to your flash drive.

Okay, this spreadsheet of data should look exactly like the first tab in my Oceania file. Many types of jobs, like financial analysts, medical researchers, and publishers rip data from the Internet, makes graphs from it, and show the results of their work to clients. You can make $25/hr easy if you learn the process I am now showing you. So keep focused!

Let's look at the spreadsheet. The most important numbers are the numbers under the 'Year' and 'Value' cells. It should be clear that we're looking at how many people have lived, are living, or will live in that region of the world at the given date. But looking at just numbers can be mad boring, so let's punch them up by making a graph with the numbers.

The $25/hr technique

The first step in making an Excel graph is to select the data. You can always use the mouse, but you know how I feel about that. What we want to do is selected the cell range from D1 to E22. How do we know where these cells are in the spreadsheet? The number refers to a specific row, the first row is labelled '1,' the second, '2,' and so on. You can see these letters on the left side of the spreadsheet. The columns are labelled in a similar way, using letters. So 'A' means the first column, 'B' the second column, and so on. To find cell D1, you find column D and row 1, and where the row and column intersect is the cell D1. So find it.

You should notice that the text, "Year" was typed into cell D1, and you can see that text in the spreadsheet at that location, as well as in the 'formula box,' which is located in the box just to the right of the 'fx' symbol. What text is in cell E1?

You should see the text, "Value" in cell E1. We're going to type the word, Population, into that box. So click on that cell with the mouse (ssss.....) or use the arrow keys to move to the E1 cell. Now we really must use the mouse. Click on the 'Value' text in the function box, and delete it. Type the word, Population, into the box, and hit the Enter key. See what happens?

Now we're ready to take the next step, actually making the graph. Before starting, however, we must select the data we want to graph. As you might expect, there are two ways to do this. One is to use the mouse (sob...), click in the upper left most cell, and, holding the left mouse button down, drag to the lower right most cell in the range of data to be graphed. The other way is to use the arrow keys to highlight the upper left cell, and, holding the Ctrl and Shift keys down, hit the down arrow once, and then the right arrow once. Now all of your data is selected!

Okay, so here are the steps to make a graph. Click on the 'Chart Wizard' button on the Standard toolbar, or on the Menu go Insert | Chart. Then follow these steps:

• Step 1: Choose a type

1. Column/Bar
2. Line
3. Pie
4. Scatter
5. Stock
6. Custom Types

• Step 2: Define the Data Range
  

1. Highlight the data, using the button in the Data Range tab, or
2. Type in the ranges using the Series tab

• Step 3: Format the Graph

1. Chart Title and Variable Names
2. Legends
3. Gridlines
4. Labels (Pie Chart)

• Step 4: Place the graph on the spreadsheet

1. Using the mouse, left-click in the upper left corner of the cell the graph is to be placed
2. Holding the mouse button down, drag to the lower right corner of the cell the graph is to be placed
3. Release mouse button
4. Holding the Alt key down, use the mouse to snap the graph into the range of cells.
   

Let's apply these step to making a graph like the second slide in the Oceania PowerPoint slide. Do you know what kind of graph it is, from the list in step 1? You may think it's a line, but you should avoid using line graphs whenever possible. Use a scatter plot instead. So select the 'Scatter Plot' button in Step 1. Then select the box that has no dots and two curves in it. Click on the 'Next' button.

Step 2 we can ignore, because we already selected the data. So we're on to Step 3, where we type in the Titles, select both X and Y gridlines, and turn off the checkmark for a legend. Then click the 'Next' button. For Step 4, let's use a simpler technique and click on the radio button that says 'As a new sheet.' Click on the 'Finish' button and we're done!

Of course, we need to make the graph look a little nicer, so here are some things we can do. Click on the following areas of the chart, and:

• 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.

Now you have a pretty graph, but nowhere to go with it. The last step is to bring it into PowerPoint. To do so,

1. Click in the upper left corner of the chart until you see the handles selected. (This may take some time to get comfortable with)
2. Copy it (Ctrl-c!)
3. Toggle over to your PowerPoint file, and find the slide you want to place the graph on.
4. DO NOT PASTE! You must 'Paste Special,' that is, on the menu, select Edit | Paste Special, and select 'Picture' from the list.

Voila! Your graph is now in a PowerPoint slide, ready for presentation.

Here are the PowerPoint files for each region:


North America
South America
Middle East
Europe
Africa
Asia

A lot of work, I know, but the process described above is always the same, no matter what kind of graph you make. And it's worth $25/hr to anyone who doesn't know the technique. So try to master the skill.

Now I would like each of you to choose a nation in your region (preferably one with a large population) and repeat the entire process. I promise I will help all of you do it.

Next Steps

Let's make a pie graph now.

1. Go to the United Nations Development Programme website
2. Hold down the Ctrl button, and select all of the countries listed on the first slide of your PowerPoint file. Click on the 'Add' button at the bottom of the box.
3. In the Themes box, click on the 'To Lead a Long and Healthy Life,' and in the Themes box, click on the 'Demographic Trends.' Finally, in the Available Indicators box, select 'Population, total (millions)' Click on the 'Add' Button.
4. Click on the 'Show Results' button at the bottom of the page.
5. A new page will come up, with your population data. Click on the 'Export to Excel' button, and open up the Excel file.

You now have a new spreadsheet! Save it to your Excel file with the following technique:

1. Right click on the tab at the bottom of the spreadsheet.
   
2. Select 'Move or Copy.'
   
3. On the 'To Book' drop down menu, select your file.
   
4. Hit the 'OK' button.
   

Delete the 1975 data. Select the name of the countries, and the 2005 data. Make a pie graph using the techniques described above.

Energy Trends

http://www.census.gov/ipc/www/idb/
http://esa.un.org/unpp/index.asp?panel=1