Lab 9: Excel Charts and Graphs

Objectives

Preparation

Before starting this lab, you should have completed Lab 8.

In this lab, we'll input some sample data into an Excel worksheet, and create different kinds of visualizations of it. We'll be creating graphs, which Excel calls charts.

Creating a Column Chart

Let's make vertical bar charts. Excel calls these Column Charts.

Alice just started working at a zwidget factory where she makes zwidgets all day. (Never mind what a zwidget is.) This is a table of how many zwidgets she made each day during the first week.

Day of WeekNumber of Zwidgets
Monday2
Tuesday10
Wednesday15
Thursday16
Friday20
  1. Open Microsoft Excel. You should have a new blank workbook open. Save this file as lab9.xls on the Desktop of your computer.
  2. Enter the data above for Alice's zwidget production.
  3. Now, select all the data you just inputted.
  4. Screenshot of data inputted and selected
  5. Go to Insert->Chart.... A window, called the Chart Wizard, will show up.
  6. In the Chart Wizard window, This should be the default chart. You can click the Press and Hold to View Sample button to preview what your chart will look like. Click Next to move on.
  7. Screenshot of Step 1 of Chart Wizard
  8. Now you are in Step 2 of the Chart Wizard. The default values should be correct.
  9. Click Next.
  10. You should be in Step 3 of the Chart Wizard, Chart Options. There should be six tabs, Titles, Axes, Gridlines, Legend, Data Labels, Data Table. Make sure you are in the Titles tab.
  11. Click on the Legend tab. Uncheck the Show Legend box. A legend tells people how to interpret the data on the chart. We only have one series of data in our chart, we don't need a legend for something this simple. Click next.
  12. In Step 4 of 4, Chart Location, we're going to place the chart on the same sheet. If it's not already selected, select As object in and the current sheet (Sheet1). The other option would place the chart on a separate Excel worksheet.
  13. Click finish.
  14. Congratulations, you've just created a chart in Excel! Be sure to save your work.
  15. finished chart
  16. Click on your chart. Notice how there are colored lines surrounding the various parts of your data in the spreadsheet. They show which cells the chart is using. Change one of values, say, Alice produced 25 instead of 20 zwidgets on Friday. The chart will change automatically. Change the value back to 20.
  17. You can move your chart around on the sheet by clicking on white area and dragging the chart around.
  18. You can resize the chart by clicking on (selecting) the chart and dragging on the black squares on the borders around.

Adding new data to your chart & modifying your chart

Now, we want to compare Alice's level of production to Bob's. Bob also just started this week. This is what his production numbers are:

Day of WeekNumber of Zwigets
Monday5
Tuesday7
Wednesday15
Thursday18
Friday30
  1. Change the Excel spreadsheet data that you had inputted before in the following ways. First, change the text:"Number of Zwidgets" to "Alice". Then in the cell to the right of it, add "Bob". Now, in the cells in the column below Bob, add Bob's data using the table given above.
  2. Screenshot of Alice and Bob's data coexisting in a worksheet
  3. Now we need to change our chart to match our data. Click on the chart so that it is selected. (You'll know that the chart has been selected when the borders of the chart have little black boxes on them.)
  4. Go to Chart->Add Data... (If you can't see "Add Data...", expand the Chart menu).
  5. Screenshot of what Add Data window should look like after adding Bob
  6. This is how you tell the chart to add the new values: Making sure the Range textbox is highlighted, (using your mouse) drag-select all the cells in Bob's column, from Bob's name to Bob's values for Friday. Six cells, from C1 to C6 should be selected. The reference for range of the cells should be added to the range textbox.
  7. Click Ok
  8. Your chart should now have Bob's data included.
  9. Now you need a fix the title for your chart and you also need a legend. To change these, make sure the chart is selected as before, and select Chart->Chart Options... from the menu bar.
  10. Click on the Titles tab and change the title of your chart to something more appropriate, such as Zwidget Production By Employee.
  11. Click on the Legends tab, and select Show Legend.
  12. Click Ok, there's your new chart. Save your work.

Line charts and formatting the appearance of your chart.

You've decided now that you'd rather have a line chart instead of a bar chart. Let's change the chart type of your chart and make some other changes.

  1. Click on your chart to select it.
  2. Go to Chart->Chart Type.... Select Line for Chart Type on the left. Select the "Line with markers" chart for Chart Subtype. Click Ok.
    Screenshot of what Chart Type window should look like after changing to Line Chart
  3. Your chart is now a line chart, with dots for each value. But it's hard to tell what value each dot is, so let's add data labels to them. You should now have data label numbers on each of the dots.
  4. To change the way those data labels look, double click on one of the numbers. The Format Data Labels window should pop up. (If you clicked on the wrong thing you might get Format Axis, Format Plot Area, Format Gridlines, etc...)
  5. When you are satisfied with the way the labels look, move on. You need to do this to Alice as well as Bob's lines.
  6. If the labels are hiding each other, you can move them around. Excel doesn't do the best job to let you do this, here are the instructions:
  7. You can change virtually anything about the formatting of your chart by just double clicking on it. A Formatting window for that part of your chart will pop up. Try doing things such as changing the background color of the chart, the color of the lines, the shape of the dots, or the font used in the axes. You can also drag elements of the chart, like the legend, labels, the whole chart itself, around to adjust their position if you like.
  8. When you're satisfied with the way your chart looks, you're done with this chart!

Here are some examples of what your final chart might look like. Play around with the formatting as you wish.

What your final output could look like 1
What your final output could look like 2
What your final output could look like 3

Finishing up

  1. To save just the chart that you created, click on the chart so that the entire chart is selected, but only the chart is selected. Go to Edit->Copy.
  2. Open an image editing program such as Paint (Start->Programs->Accessories->Paint). You will probably have a blank document in front of you.
  3. Paste the image onto the blank document. Edit->Paste.
  4. If there happens to be extra white space on the bottom and right side of the image, you can click on the corner of the white part, and drag it to make it smaller, to fit your chart.
  5. Now, let's save your chart in a web-readable format, such as a jpeg. File->Save As..
  6. Name the file lab9chart.jpg as the filename, select JPEG under Save as type:, and save the file on the desktop.
  7. Create a lab9 directory in your fit100 directory on Dante. Copy your Excel file as well as your lab9chart.jpg to the directory. You can view your files by going to the usual http://students.washington.edu/yourUWNetID/fit100/lab9, and then click on the lab9chart.jpg to see your rchart.

Grading

In section, show your TA your spreadsheet and charts for Lab grading.