When you’re reporting, you can’t run a cable from your data to your readers’ brains. But the next-best alternative is to use great charts.
By “great charts” I mean charts that are relatively small, easily understood, and have a professional appearance.
Unfortunately, few Excel charts have that professional appearance. To illustrate, here’s a standard Excel chart for some artificial data.
In contrast, consider the charts below. The chart on the left obviously is from The Wall Street Journal…because it says so. The chart on the right is from Excel. And both charts look professionally designed.
Here’s another example. The chart on the left is from Business Week in 2006, and the chart on the right is from Excel.
In this article, I’m going to explain how to create the version suggested by the example from The Wall Street Journal. And along the way, I’ll give you some general ideas about how to make your Excel charts look more professional.
However, you won’t need to copy the exact formats of professionally designed charts to give your Excel charts a professional appearance. To illustrate, the following figure from my Kyd War Room dashboard reports uses similar Excel techniques for its charts. (The lines show actual performance and the area plots show targeted performance.)
How to Create the WSJ Chart in Excel
Here’s the sample data we’ll use to create The Wall Street Journal (“WSJ”) chart.
To create the default column chart in Excel, first select the range A2:B6. Then, in your Ribbon, choose Insert, Charts, Column Chart, Clustered Column.
After you do so, Excel will create a big chart similar to the one shown in the first figure in this article.
Excel’s default chart includes a title. But we’ll use a different method. So select the title in your chart and then press the Delete key on your keyboard.
Also, click and drag a corner of the chart to reduce it to the width of about three columns in your worksheet. After you do so, your chart should look something like this:
To make the blue columns wider, click on any column in the chart and then press Ctrl+1 on your keyboard to display the Format Data Series side panel in Excel. In the Series Options tab, set the Gap Width to 5%.
If you want to use the color of blue that the WSJ used, first make sure that the blue columns are still selected. Then click on the Fill & Line tab of the Format Data Series side panel. In the Fill section, choose Solid Fill. Click on Color, and click on More Colors to launch the Colors dialog. And then set the color to Red=1, Green=144, and Blue=184.
After you do so, your chart should look something like this:
You now need to make room for the title, caption, and unit-of-measure text above the chart. So click and drag your chart several columns to the right for a few minutes.
In cell D1, enter the title, YTD Sales. Assign the font Franklin Gothic Demi Cond. If you don’t have that font available, assign another font you like for the title. Make the font 16 points, and make it bold.
A caption isn’t required. But if you decide to add one, enter it in cell D2, then continue it in cell D3, if necessary. Format the caption as 10-pt Arial Narrow.
To keep the date in the caption updated, I used this formula in the cell shown:
D2: =”Year-to-date retail sales for “&TEXT($B$8,”mmm-yyyy”)&”, ”
You can create a similar formula if you want.
For the unit-of-measure text, enter…
▼ Thousands of Dollars
…in cell D4. The down-pointing black triangle is a Unicode character. In Excel 2013 and after, you can generate it using this formula:
=UNICHAR(9660)
You want the text for the unit of measure to be readable, but easily ignorable. Therefore, set its font to 8 points and give it a medium-dark gray color.
Your chart figure now should look something like this:
You’re almost ready to combine the chart with the text in column D. But first, select your chart, press Ctrl+1 if necessary to display the side panel, and then in the Fill & Line tab, set the chart’s Fill value to No Fill, and its Border value to No Line.
Now, press Ctrl+x to cut the chart. Select cell D4, which contains the unit-of-measure text, and then press Ctrl+v to paste the chart to that cell. After you do so, your chart figure should look something like this:
As you can see, the top of the chart’s Plot Area is slightly overlapping the unit-of-measure text. But that’s not a problem. You just click on the edge of the chart to select it, if necesessary, and then tap the up-arrow of your keyboard one time to select the Plot Area. (You also could click in the white area above one of the columns and between the gridlines.)
With the Plot Area selected, hover above its top-center handle and then click and drag the handle downward until the top vertical axis label seems properly positioned below the unit-of-measure text, like this…
Typically, I enter text in one or two cells below the chart. One cell often contains the source for the data. For internal data, the source often is the number of the close, which in many companies will tell your readers whether the data is preliminary or final. It also could be a source like Orders, Purchases, or whatever. If the source would be obvious to your readers, you could omit it.
The last cell typically contains contact information for the person who created the chart. That way, people know where to go for questions about the chart or its content.
To add the two cells of text, just enter it immediately below the chart, as the following figure illustrates.
Finally, I typically assign a bold font to the labels in the chart axes. To do so, just select one of the axes, press Ctrl+b, then select the other axis and so the same.
When you’re done, and your chart object is selected, your worksheet should look something like this:
And, of course, when your chart object isn’t selected, your chart figure should look something like the figure at the right.
To make the chart figure wider, you could widen a column in your worksheet or insert a column within the chart area.
And to make the chart taller or shorter, you can add or delete worksheet rows within the chart area.
Good luck with your charting!
