Lab 9: Excel Charts and Graphs

Objectives

• Create a column chart in Microsoft Excel
• Add extra data to a chart & modify your chart
• Create a line chart & format the appearance of your chart

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.

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. Go to Insert->Chart.... A window, called the Chart Wizard, will show up.
5. In the Chart Wizard window,
   • under Chart Type, select Column.
   • under Chart Sub-Type, select the leftmost, top-most chart. It's called the Clustered Column chart.
   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.

6. Now you are in Step 2 of the Chart Wizard. The default values should be correct.
7. Click Next.
8. 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.
9. • Change the chart title to Alice's Zwidget Production.
   • Change Category (X) axis to Days of the Week.
   • Change the Value (Y) axis title to Number of Zwidgets.
10. 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.
11. 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.
12. Click finish.
13. Congratulations, you've just created a chart in Excel! Be sure to save your work.

14. 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.
15. You can move your chart around on the sheet by clicking on white area and dragging the chart around.
16. 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:

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. 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.)
3. Go to Chart->Add Data... (If you can't see "Add Data...", expand the Chart menu).

4. 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.
5. Click Ok
6. Your chart should now have Bob's data included.
7. 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.
8. Click on the Titles tab and change the title of your chart to something more appropriate, such as Zwidget Production By Employee.
9. Click on the Legends tab, and select Show Legend.
10. 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.
   
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.
   • Click on the chart to select it, and then go to Chart->Chart Options.
   • Go to the Data Labels tab, under Label Contains check only the Value checkbox.
   • Click Ok.
   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...)
• Inside the "Format Data Labels" window, click on the Font and Alignment tabs to change font style and positioning of the labels next to the value dots. The Patterns tab lets you create a little box that will surround the value label number, and you can change the background color behind it to make it easier to see. Try changing one thing on each tab, and see what happens. If you don't like it, you can always undo.
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:
   • To move individual data labels around
   • Click once on a number (data label) to select all the data labels for that series
   • Click and drag one number to move it around.
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.

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.