Lab 5: Excel Charts and Graphs

Objectives

• Create a column chart in Microsoft Excel
• Learn how to add extra data to your chart, and to modify your chart
• Create a line chart & format the appearance of your chart

Preparation

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

Creating a Column Chart

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

Alice just started working at a zwidget factory, where she makes zwidgets all day. 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 lab5.xls on the Desktop of your computer.
2. Enter the above data for Alice's zwidget production.
3. Now, select all the data you just inputted.
4. Screenshot:
5. Go to Insert->Chart.... A window, called the Chart Wizard, will show up.
6. On the left side of the Chart Wizard window, under Chart Type, select Column. Then, on the right side 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.
7. Screenshot:
8. Now you are in Step 2 of the Chart Wizard. The default values should be correct. Click Next.
9. 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.
10. 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.
11. Click on the Legend tab. Uncheck the Show Legend box. We only have one series of data in our chart, it's not particularly complicated enough for us to need a legend on our chart. Click next.
12. In Step 4, Chart Location, we get to select whether we want the chart to show up on the worksheet along with your data, or on a brand new sheet by itself. We're going to keep the default value, which is to place the chart on the same sheet. If it's not already selected, select As object in and the current sheet, which should be Sheet1. Click finish.
13. Congratulations, you've just created a chart in Excel! Be sure to save your work.
14. Screenshot:
15. Click on your chart. Notice how there are colored lines surrounding the various parts of your data in the spreadsheet, to show which cells are associated with the chart. Try to 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.

Adding new data to your chart & modifying your chart

Now, let's say 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 Number of Zwidgets to be 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:
3. Now we need to change our chart to match our data. Click on the chart so that it is selected.
4. Go to Chart->Add Data....
5. We'll now have to tell the chart where the new values to be added are. Making sure the Range textbox is highlighted, click, hold, and drag over 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. Now let go. The reference for range of the cells should be added to the range textbox.
6. Screenshot:
7. Click Ok
8. Your chart should now have Bob's data included.
9. Now you need a new title for your chart because it's no longer just Alice's data, 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.

Let's say you've decided now that you'd rather have a line chart instead of a bar chart. Let's go change the chart type of your chart, and then make some other modifications to it.

1. Click on your chart to select it.
2. Go to Chart->Chart Type.... Select Line for Chart Type on the left. Select the second from the top, leftmost chart for Chart Subtype. Click Ok.
3. Screenshot:
4. 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 Value and click Ok. You should now have data label numbers on each of the dots.
5. To change the way those data labels look, double click on one of them, on one of the numbers. The Format Data Labels window should pop up.
6. 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. 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. If the labels are hiding each other, you can click, hold and drag them to move them 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's an example of what your final chart might look like. Of course, feel free to 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 lab5chart.jpg as the filename, select JPEG under Save as type:, and save the file on the desktop.
7. Create a lab5 directory in your CSS305 directory on Dante. Copy your Excel file as well as your lab5chart.jpg to the directory. You can view your files by going to the usual http://students.washington.edu/yourUWNetID/CSS305/lab5, and then click on the lab5chart.jpg to see your rchart.

Checklist

• I can create line and column charts in Excel.
• I can add modify data in my chart.
• I know how to change simple formatting in my Excel charts.