Saturday, August 2, 2008

Tips & tricks: Why Excel charts suck and what you can do about it



Excel is probably guilty of more ugly and hard-to-read charts in the workplace than any other piece of software. In this post I will show how you can tame Excel so it makes nice-looking charts that are easy to read... despite Microsoft's best efforts to the contrary.


Let's say we are asked to present revenue and expenses for last year, quarter by quarter. Jim in Accounting gave us this spreadsheet:


As Interaction Workers, we know we should tell our stories with pictures whenever possible. So let's create a default Excel column chart of our numbers.


This is much better than the raw numbers. Now the audience can see that the margin between revenues and costs was small in Q1, costs were cut in Q2 but the margin improved, and that Q4 revenue was up over Q1 with a healthier margin over expenses.

But there is too much junk in the chart that doesn't contribute to the story. Chances are we only get one shot to present this, and we need maximum impact. This leads us to the most important thing to remember about Excel charts.

All default Excel charts suck!

Why? They contain useless non-white pixels that the audience's brains will try to make sense of. Every non-white pixel should make it easier to read the graph. If it doesn't, we should get rid of it. While the default charts suck, fortunately it is fairly easy to fix them.

Let's start removing any non-white pixels that aren't strictly necessary. First off, the cent zeroes in the Y axis labels don't add any value. We remove them by right-clicking the Y axis, selecting Format axis and entering these settings.


While we are formatting the Y axis, we can remove the tick marks next to the labels, as well as the axis itself. Do the same for the X axis.


In our hunt for useless non-white pixels, we notice that the background of the chart is gray and that there are light-gray lines bordering the chart area at the top and the right. We can easily remove them by right-clicking the gray area and selecting Format chart area and making the selections below.


Do the black outlines of the bars add anything? If not, let's remove them by clicking them, selecting Format data series and then selecting no border.


We have removed many non-white pixels, but the chart is still perfectly understandable.


But there are still many pixels of dubious value. There are 28 zeroes, 9 commas, and 6 $ signs on the Y axis. Is every single one absolutely necessary? The horizontal gridlines distract from the data, but give us a hint of the dollar size of each bar. Yet, it takes some effort to tell how many dollars some bars represent, for example Q3 revenues.

Let's start by creating a new spreadsheet where we lop off extra zeroes and list everything in millions of dollars.


Now, let's remove the Y axis and the gridlines! Right-click the graph, select Chart options and get rid of the axis and the lines.



The resulting graph is very clean, but we can't tell dollar amounts from it. Let's add labels to the bars. They are less distracting than our old Y axis and gridlines, while they are more precise. Right-click a bar, select Format data series and make the selections below. You will need to do this once for the revenues data series and once more for expenses.



Does the black border around the legend add any value? Didn't think so. Right-click it, select Format legend, and turn the border off.


We need to tell the audience that the numbers are millions of dollars. The most economical way to do this is to add $MM to the Revenues and Expenses cells of the spreadsheet, which will automatically update the legend.

Finally, we may want to update the colors. I personally think the purple of the expense bars is too heavy. Right now shades of blue seem to be the fashion, so let's change the purple to a light blue. And the numbers above the bars are less intrusive if we make them a little smaller and gray instead of black.


The resulting chart is clean and pleasing to the eye, giving the audience a feel for the relative size of the numbers and how they change over time. The raw numbers can easily be read from the graph, and they are accurate within 1% without cluttering up the presentation.

This is what the default column chart should have looked like in Excel. Fortunately we now know how to make Excel charts readable, understandable, and dare I say beautiful.

Let me know what you think!

2 comments:

  1. I like a title, the point you want to make. Such as "Since Q1 our margin has remained above .4 million." Or, snappier. But, you need some title or label, so better that than something bland. IOW the statement can contain labeling that only a fool could not understand.

    ReplyDelete
  2. incredulous, that is a very good point! Just showing raw data without any caption runs the risk of having people misinterpret it, or not get it. I should have called that out.

    ReplyDelete

I love comments! We're in the Interaction Age, see?