Chapter 9. Charts: Graph your data

Who wants to look at numbers all the time?

Very often a nice graphic is a more engaging way to present data. And sometimes you have so much data that you actually can’t see it all without a nice graphic. Excel has extensive charting facilities, and if you just know where to click, you’ll unlock the power to make charts and graphs to display your data with drama and lucidity.

Head First Investments needs charts for its investment report

There is a big presentation coming up for Head First Investment’s board of directors. They have all the data compiled to show their performance over the last year, but they need some charts to make the data easier to read and understand. It’s up to you to crank out some attractive charts.

Load this!

www.headfirstlabs.com/books/hfexcel/hfe_ch09_allocation.xlsx

Sharpen your pencil

Look at each chart type. Which is most appropriate to show portfolio allocation?

Sharpen your pencil Solution

Which chart did you conclude would be the most useful way of representing your company’s portfolio visually?

Create charts using the Insert tab

Let’s try creating one of those pie charts. Making charts in Excel is easy: just start by clicking on the Insert tab of the Ribbon. You’ll find everything you need to get started.

Do this!

1. Make sure your cursor is outside your data range in a blank cell. Then insert a pie chart.

   

2. Next, click the Select Data button. Fill in your data range and axis labels.

   

Use the Design and Layout tabs to rework your chart

Inserting a chart in Excel is only the beginning. Once your chart has been created, you’ll always head to the Design and Layout tabs under Chart Tools to change elements in your chart like the titles and formatting.

You just hit the Select Data button under the Design tab to select your data and labels.

Exercise

Let’s polish up your chart using the Design and Layout tabs.

1. Using the far-right button on the Design tab, move the chart you created to its own sheet. This will clear up the sheet with your data.

   

2. Now head over to the Layout tab. Click the Chart Title button to add a title.

   

3. It’d be nice if your labels were actually next to the pie slices rather than in the legend, so let’s get rid of the legend. Under the Layout tab, make the legend go away.

4. Add data labels next to the pie slices. Once you’ve added them, right-click and select Format Data Labels to make sure they refer to the Category Name rather than the Values.

5. Finally, increase the font size of all text elements in the chart to make them more readable. You can change the font size using the Home tab.

Exercise Solution

You just executed a variety of modifications to your pie chart’s layout using the Design and Layout tabs. How do your results look?

1. Move the chart you created to its own sheet.

2. Add a title.

3. Get rid of the legend.

4. Add data labels next to the pie slices, making sure they refer to the Category Name rather than the Values.

5. Increase the font size of all text elements in the chart.

   

There are no Dumb Questions

Q:	Q: That made sense, but it looks like there are a lot of different options in the chart menus. If I want to change just one thing, how do I know how to find it?
A:	A: That’s a great question, and there’s a really simple trick. You can right-click on any of the components on your chart, so if you want to change an element of your chart, you can always just right-click on it to search for the menu item that will change that one thing.
Q:	Q: So when all else fails, if I want to change something on my chart, I should right-click and explore the menus?
A:	A: That’s exactly right.
Q:	Q: Are the charts that come with Excel pretty well designed? I mean, if I just go with the defaults, will I generally create pretty attractive, readable charts?
A:	A: Yes and no. The graphic quality of Excel’s built-in visualizations is greater than it’s ever been. If you look at the Chart Styles under the Design tab, you can see a neat grid of design variations that you can quickly apply to your data. Excel has never been better.
Q:	Q: I’m sensing there’s a “but” coming.
A:	A: You sense correctly. The “but” is that no software can ever make your design decisions for you, especially when it comes to charts. The fact that your chart came built into Excel is not much help if your visualization is not analytically rigorous or useful.
Q:	Q: Is Excel the best spreadsheet charting tool available?
A:	A: It depends on what you’re trying to accomplish. If you have data and a problem that fits the built-in charts nicely, then Excel is probably the tool for you. If you need to do high-level, hardcore statistical visualizations, you might want to reach for a program like the open source statistical package R.
Q:	Q: What you’re telling me is that I have to learn another piece of software?
A:	A: Not necessarily! Recent versions of Excel are more powerful and versatile than ever, and Excel’s features are more than most people need to manage their data. But it never hurts to be aware of other visualization options, and if you find yourself spending hours and hours trying to force Excel’s charting features to create some chart that it wasn’t designed to make, then you may want to investigate other graphing programs.
Q:	Q: Do people do that—use Excel to make charts that the Excel designers never thought to support?
A:	A: A lot of people use Excel to do things that the original designers of spreadsheets never thought to support. And it’s actually one of the coolest things about how people use Excel: users dream up features, then force Excel to implement them (even in weird ways sometimes), and later Microsoft picks them up and implements them in a user-friendly way.
Q:	Q: So what’s the bottom line?
A:	A: Learn Excel’s features. As many as you can. Come up with creative ways to apply those features to your own problems. And if you find yourself spending a huge amount of time forcing Excel to solve your problems, consider the features of more full-fledged computer or statistical programming platforms like R or Python.
Q:	Q: Let’s get back to charts. How do I know which chart to use with my data?
A:	A: You’re about to find out. Your client is almost certainly going to need you to create more charts for the big presentation....

Let’s see what the boss thinks....

Your pie chart isn’t going over well with the corporate graphic artist

Your pie chart has been passed around, and some guy you’ve never even dealt with is weighing in with a negative opinion.

The artist is correct. This is a common critique of pie charts among people who care a lot about data visualizations, so you should probably try a different chart. But no worries: changing the chart type is a snap.

Let’s take a look at some other chart types.

Who Does What?

Match each Excel chart type to what it does. Which chart do you think would be an improvement over the pie chart?

	Lets you plot two variables with the option of fitting a curve to the data points.
	A way to plot financial instruments, showing high, low, and closing prices.
	Basic comparison, using length and one or more variables.
	A visualization that lets you add a third dimension of area to a scatterplot.
	Charts for plotting data with three related variables.
	Shows changing trends, generally over time.

Who Does What? Solution

Match each Excel chart type to what it does. Which chart do you think would be an improvement over the pie chart?

Exercise

Now that you’ve decided to change your pie chart to a bar chart, go ahead and make that change.

Do the labels update correctly? You might need to adjust the fonts.

Exercise Solution

You just changed your pie chart to a bar chart. How did the conversion go?

Exercise

Create a line chart to compare stock 1 and stock 2. Put your cursor inside your data range, and Excel will try to figure out which columns represent your data.

Which stock did better this year?

_______________________________________________

_______________________________________________

_______________________________________________

_______________________________________________

Load this!

www.headfirstlabs.com/books/hfexcel/hfe_ch09_stocks.xlsx

Exercise Solution

You just created a time chart to compare two stocks. Did Excel create a strong visualization?

Which stock did better this year?

It appears that Excel created the visualization correctly, but there’s a problem. It’s hard to tell which stock performed better, because they have such different values. The starting and ending price of the S&P index stock is way higher than that of the EEM stock. The stocks started from different places, so we might need to do something to the data to get a good comparison.

Sometimes you need to transform the data in order to graph it effectively. Creating effective graphics isn’t just about using the graphical manipulation features of Excel effectively. It’s also about making sure that your data is prepped correctly for a good visualization.

Exercise

Write a formula to show a percentage change from Day 1 for each subsequent day.

Exercise Solution

You just transformed your data to show a percentage change from a zero basis rather than the original value. What did you find?

Let’s copy the formulas and graph the data....

Exercise

Now that you’ve written a formula to show the percentage change for one unit of time, copy and paste the formula for all dates. Then graph your new data.

1. Copy and paste the formula for both stocks. Make sure you add absolute references where necessary.

   

2. Create a new time series line chart.

   

Hint: You might have to go back into the menu that tells Excel which data to select. You want Excel to select only your new data, not all four columns.

Exercise Solution

You just copied and pasted your new data transformation functions, and then you graphed them. How does your chart look now?

You’re starting to get tight on time...

Exercise

Format your chart according to these parameters.

1. Make this the title: S&P versus EEM.

2. Put the legend, which is currently on the right, at the bottom of the chart.

3. The date labels on the y-axis are kind of verbose and also get in the way of the lines. See whether you can fix that issue as well.

Exercise Solution

You just reformatted your new line chart. How did it turn out?

1. Make this the title: S&P versus EEM.

2. Put the legend, which is currently on the right, at the bottom of the chart.

3. The date labels on the y-axis are kind of verbose and also get in the way of the lines. See whether you can fix that issue as well.

Your report was a big success...

The two charts you created were a big hit among the board of directors. You made the data accessible and elegant through your graphical visualizations.