Matthew W Ford
Making Effective Graphs (Updated 09/05/2012 01:36 PM)
Levels and Trends
Let's say that you're studying Dell Computer's operational effectiveness as it relates to the days of sales the company keeps in inventory. You have a bunch of historical data (called a 'series) and you'd like to present the data in a manner that would allow the reader to understand levels (what's the average?) and trends (is the series going up or down?) over time.
Your tool of choice is a usually a graph (as opposed to a table) because it will allow you to accomplish your goals quickly and visually (a picture's worth a thousand words...).
The primary ground rule when making a graph is your time data should go on the x (or horizontal) axis and your series data should go on the y (or vertical) axis. You may have more than one series to plot, as we'll see below.
Formatting: Remove Chartjunk
Good graphs are all about formatting. And the key rule of thumb is: less is better. This comes from pioneering work of Edward Tufte who found that ability to convey a graphical message was inversely related to the amount of extraneous ink expended to make the graph. Tufte affectionately termed this extraneous, non-value added ink chartjunk.
So the basic idea is that you want to eliminate the chartjunk--all ink (or pixels) in your graph that don't directly add to your message.
Perhaps the most common type of graph is called a bar graph. Bar graphs come in handy when your series is, say, 10 data points or less, or when you want to emphasize the magnitude of your data (as expressed by the height of the bars).
To make one in Excel, click the Chart wizard and select either Column for a graphs with vertical bars or Bar for horizontal bars. The easiest thing to do next is to highlight the cells that contain your data can then click through to the Finish button on the graph.
Figure 1 shows what you'll get by default. It shows, er, heck we don't what it shows, do we? There is a non-descriptive title and no labels on the axes. This, of course, is a primary rule when making good graphs: All graphs need descriptive titles and axis labels.
I get a lot of graphs from novice student analysts (and actually lots of practicing managers and professors) that look a lot like Figure 1. You can do better...
Figure 2 shows a better version. Right away, of course, the title and axis labels clue us in on exactly what we're looking at. Some other things are apparent as well. Perhaps the most obvious is that we've eliminated the gray background and the top and right hand border (can be done by double clicking the background area in Excel and selecting 'none' for both background and borders). We've also removed all those horizontal gridlines (click the chart then Chart>Options>Gridlines then turn off major gridlines. What we're doing, you see, is getting rid of the chartjunk. I've also removed the legend to the right (just click on it and press delete). Why have a legend when there is only one series?
One other difference between Figs 1 and 2 is that the actual years of time appear on the horizontal axis in Figure 2. I did this when initially creating the graph by clicking the Series tab in step 2 of 4 when using the Chart wizard. This is hard to explain here but suffice to say that you're usually better off controlling the entry of your series in step 2 of Chart wizard production rather than highlighting the Datarange by default and 'hoping' that Excel figures out what you're trying to do. Spend a little time on step 2 of 4 of the Chart Wizard and you should be able to figure it out. If not, please ask me and I should be able to talk you home.
You can have more than one series on a bar graph. Figure 3 compares Dell and Hewlett Packard's days of sales in inventory over a 10 year period. Note that here I elected to maintain the gridlines to help make sense of the levels of the bars. Overall, though, there is a concerted effort to minimize the chartjunk.
Another popular type of graph is called a line graph. Instead of bars, each value in the series is represented by a data point. Usually, these data points are connected unless there are spaces caused by missing data. The utility of line graphs grows with series that have lots of data.
To make a line graph in Excel, click the Chart wizard and select the Line category of graph. Note that the sub-types of line graphs include those with lines only and no points. I don't like these. I almost always select line graph types that permit me to show individual data points.
Figure 4 shows, well, you should be catching on by now. We can't tell, can we? By default, line graphs lack titles while possessing some chart junk. Don't let this graph be yours...
Figure 5 shows the labeled, cleaned up version. Like the bar graph, I've removed the background gray, the borders, and the gridlines. The result is a clean, professional look where nearly all the ink is 'value added' and focused on the data. We can quickly discern levels and trends from this arrangement--which is just what we want.
Can you also see that producing a bar graph here might start looking kind of clunky? We'd have about 40 bars and the effect may not be as pronounced as the one reflected by data points on our line graph.
We can put more than one series on a line graph. Figure 6 shows that Dell versus Hewlett Packard comparison in line graph form. Compare this to the bar graph format in Figure 3 above. In some ways, line graphs of more than series are superior in that you can easily judge which series is higher or lower than the other. The advantages of line graphs for plotting multiple series becomes more apparent when the number of data points increases.
Wrapping It Up
So what are the key rules of thumb? Titles and labels: yes. Chartjunk: no. With a couple of clicks in Microsoft Excel you can take your graphs to a level that will make many managers envious. Indeed, you're chart-making prowess will likely be in demand.
Site maintained 1999-2016 by Matthew W. Ford.