Math 114Q, Section 10
Excel Tutorial
October 18, 2007

The purpose of this tutorial is to show you how to create this graph and others like it:

The instructions are for use with a Mac, so you may have to make some slight adjustments if you are using a PC.  Some helpful information about Excel before we get started:

• When you open a file in Excel, or start a new file, it is called a workbook.  Within the workbook there are worksheets.  You can move to another worksheet by clicking the tabs (Sheet 1, Sheet 2, etc.) located at the bottom of the spreadsheet. 
  
• When you save an Excel file, you need to make sure the suffix .xls is at the end of the file.  This is especially important if you will be opening the file on a PC.  For example, you might call your file QRwork.xls.  Usually you will be prompted to include the .xls when you save - but do make sure that it is included.
• To open a new workbook in Excel, you can go to the File menu and click on "New" or you can put the mouse on the icon showing a piece of paper (all the way to the left on the toolbar at the top of the page) and click on it.
• If you do something using Excel and then change your mind, you can always click on the "undo" button (it's an arrow) located in the middle of your toolbar at the top of the page.  Excel remembers what you've done for several steps back, so if you want to undo something you did 6 steps ago, that may be possible using the undo button.  You an also "redo" something by using the forward button in your toolbar.
• Sometimes you'll open up Excel and you won't see the formula bar and the toolbar.  These are easy to make visible.  Just go to the View menu and make sure "Formula Bar" has a check next to it (click on it if it doesn't).  You can also go to the "Toolbars" drop down menu in the View menu and click on "Standard" and "Formatting" to display those toolbars.
• Save often!  You can save a file to the Documents  folder on the desktop of the computers in our classroom.  This is a short-term solution, as the file may not be there the next day.  It's better to always carry a jump drive (also called memory stick) and save your files on that.  Make sure, however, that you then save them on your home computer so that you have a backup.  You can also send a file to yourself as an e-mail attachment.
• When you print, it's best to use "Print Preview" in the File menu to make sure that you are printing what you think you are printing.  Excel may only be looking at a graph, or it may print over several pages.  Save money and paper by doing a quick check.  Also, try to use white backgrounds for graphs so that you don't waste toner when printing.

• Download the spreadsheet BostonPopulation.xls from the course web page by clicking on the link and opening the file with Excel. If your browser refuses to open the file, try saving it instead - read the next bullet about saving, download and save the file, open it with Excel and skip the next bullet.

• Save the Excel file - on your thumb drive, or someplace on the hard drive (if you are in class, it will automatically save to the Documents folder on your desktop). Choose "Save As" from the File menu, go to the place you want to save the file, and name the saved file BostonPopulation.xls. In the lab you can do that only in a folder for temporary use by students, so it's best to use your flash drive. If you don't have one you can use the student folder and email the file to yourself as an attachment at the end of class.

• Select the cells in the range from A5 to B27.  To do this, position your mouse on cell A5 then click and hold.  Drag the mouse over and down until all of the cells are highlighted.   The rectangle with corners A5, B5. A27 and B27 should be blue.  Do not highlight any extra cells!  It will mess up your graph if you do.

• Create a Chart (Excel's name for a graph) with the Chart Wizard. You start the wizard by clicking on the Chart icon on the toolbar (a batch of multicolored vertical bars) or by selecting Chart from the Insert pulldown menu.

  • You should be looking at Step 1 of 4. Select XY (Scatter) from the menu on the left, and the second choice (smoothly connected dots) from the menu on the right.

    If you wish, Press and Hold to View Sample as suggested.

  • Click Next or press the return key. Now you are at Step 2 of 4. Since we don't want to change anything here we accept what the wizard has done for us. Click Next again.  Note that you can always go back (by clicking on the Back button in this window) if you want to change something.

  • Now we are at Step 3 of 4 - Chart Options. Here we do have some work to do. The numbers are all where we want them and the picture is just fine - but we haven't documented anything. Fill in the Chart title and the X and Y axis values as in the picture above. We'll talk about the other buttons (Axes, etc.) later.  For now, go to Next.

  • For Step 4 of 4 - Chart Location, just accept what the wizard offers and click on Finish.  Excel should put the chart on the same page as your data table.
    

• Save your work, with Save from the File menu. You should always save your work from time to time just in case something goes wrong.

• For practice opening and closing files, quit from Excel now (Choose "Quit" on the File menu, or press Apple-Q). Then open the saved file by double clicking on it, or by opening Excel and then using "Open" on the File menu.

• Play around with the chart you've drawn. If you click on the white part of the chart area you can drag the chart to a different place on the screen. If you move your mouse to a corner or an edge you can change the size and shape of the chart or of the actual graph.

• The data points are big and clumsy looking. In the picture above there are no data points, just the smooth connecting line. To fix yours, click with the right mouse button in the white Chart area (or hold down the Control key and the mouse at the same time; or go to the Chart Menu - you may need to click once somewhere on the Chart to have the Chart menu appear). You should see a menu. Select "Chart Type". You will find yourself back at what the wizard showed you at the second step. You can change your mind now. Select the third option on the right - smoothed lines without markers. Then click OK.

• Next we'll fix the legend, which explains what the data mean. Open the Chart menu again, select "Source Data" from the menu, and then select the Series tab instead of the Data Range tab. Series 1 should be blue. Type in "population" for the Name and click OK.  If you were making a graph with two different series, then you could go back to this window and name the second series.  We'll see examples of this soon. In this case, since there's just one data series and the y axis has been labelled you could delete this field completely. But it's good to know how to edit it since you will have to do that later.

• Now the legend is useful - it tells us what the line on the chart means. But the chart itself is squeezed over to the left. Let's put the legend at the bottom. Right click the white area (again) and select Chart Options. We've seen this window before - the wizard showed it to us and we entered the titles. Now select the Legend tab, and Placement bottom

• Save your work again! To be extra cautious, save the file as BostonPopulation2.xls. That way you can always go back to BostonPopulation if you do something you don't like and can't seem to undo.

• Your Plot Area is gray. The white one in the picture above will print better. So make yours white: double click (or right click) inside the gray area and select Format Plot Area from the menu. Click on "No fill" or white for the color, then click OK.

• The blue lines connecting the points that you see on your chart don't match the ones in my picture. And they may not print nicely. So change them. This time double click (or right click) when your mouse is on the blue part of the graph. The data points should appear (as blocks) along with a menu called Format Data Series.  Select Colors and Lines (it may default to that) Then change the color to black and increase the line weight - to its maximum on a PC, the mac seems to allow much wider lines, so pick one that that looks good.

• Save your work again - perhaps in file BostonPopulation3.xls.

• You can resize and move the Plot Area, the Legend and the Title around on the Chart by selecting them and dragging with the mouse. Do that in order to make room at the bottom for the URL from which we got the data.

• On a Mac you use the "Drawing" toolbar to insert a textbox (this may be called the Auto Shapes toolbar on a PC).  If the Drawing toolbar is not visible, go to the View menu, click on the "Toolbars" menu and then click on Drawing. Now find the box with the [A] in it - it's called a Text Box. Click on it once, then move the mouse over to your chart (you'll see the little A following the mouse) and click at the bottom of the chart window to create a box on your chart.  You will type the data source and any other important information  into this box.  Move and resize the box so that it more or less matches the one above. Add your name and the date below the source information.

• Select the chart one last time, select Print Preview from the File menu, make sure the computer will print what you think it should print.  Then  print your chart.

• Save your work!

• Take your work home with you, either by ejecting your flash drive (on a Mac this means going to the File menu and clicking on "Eject Drive" or dragging the icon of your drive to the Trash)  or by sending it to yourself as an email attachment - or both, just to be safe.

• Open up the BostonPopulation.xls spreadsheet again (if you've closed it). Click on cell C4 and type in "percent change". 
  

• Move the mouse to the next cell (cell C5).  Since this is the first piece of population data that we have, there is nothing earlier to compare it to.  So we skip this cell and move to cell C6. 
  

• In cell C6, you want to type in a formula - in other words, you want to make Excel do the calcution for you.  The easy way to do this is to click on the cells that you will use in the formula.  If you do it this way, then you can copy and paste your formula into the rest of the column and you'll have all of your information quickly and easily.  Here are the steps.

• Every formula must start with an equal sign.  Make sure you have clicked on cell C6.  You should also make sure you can see the formula bar near the top of your window.  That bar shows you what you are typing into the cell.  Here is what we'll type in:  =(B6-B5)/B5     
  

• You actually have a choice here.  You can type in B6 and B5 by hand, or you can click on those cells and Excel will fill them in for you.  Try it and see if it works.  Pay attention to what is in the formula bar.  If you click on any extra cells, Excel will try to put those into your formula too and it won't work out properly. You can alway click on the "undo" button or delete what you have typed.

• Hit return and check that Excel has put 0.36119 into cell C6. 
  

• Now click on cell C6 then move the cursor to the lower right-hand corner of this cell.  When the cursor turns into a bold-faced plus sign, click and hold and drag down.  This will fill in the rest of the column with the formula you put in cell C6, and it will update the formula's reference cells.  To check this, click on cell 12.  If you look at the formula bar, you'll see it reads   =(B12-B11)/B11.   It's always good to double check the calculation and the pasting that you've done, as it is very easy to make a mistake with this.

• The numbers in the cells in column C look pretty messy.  They are written with too many digits and they aren't written as percentages.  Here's how we can make it look better.  Select all of the numbers in column C and click on the % symbol on the toolbar.  Excel automatically changes all of these numbers to percentages (it multiplies them by 100 - you could have told Excel to do that too) and it rounds them to whole numbers.
  

• One last step.  Now make a graph of years and percentage change in population.  This means you have to select two columns that are not adjacent.  To do this on a Mac, first highlight the years in column A that you will graph, then hold down the apple key and highlight the percentages in column C that you will graph.  Be careful that you highlight the correct years.  Each year needs to be paired with a percent change.

• Now construct the graph, labeling it as we suggested earlier.  Print it for use with your homework this week.