A visitor to my web site emailed me, asking how to make a nice chart like the one in Postcards from the ledge on The Economist‘s web site. There is no default Excel chart type that comes anywhere close, and the person asking the question didn’t yet understand the breadth of Excel’s flexibility. I reproduce the Economist chart below, along with the Excel counterpart I cobbled together in about ten minutes (it took longer to find the data than to knock off the chart). It’s not a perfect match, but it shows what is possible.
Nice chart from The Economist
Nice chart done in Excel
Graphic artists who work at business magazines know how to create charts and tables that are easy to read. Learn from these people. The easiest way to do so is to look for charts and tables in business magazines, figures that you can adapt to your own reporting needs.
At one level, it seems like a waste of time to worry about ways to format your management reports. It’s difficult enough to make reports accurate; you shouldn’t also need to make them pretty.
However, pretty is not the issue; clear communication is. With millions of readers each week, magazine designers have learned ways to communicate numeric data in clear and interesting ways. Your reports probably will benefit from their knowledge.
Charley provides examples of magazine-quality graphics which nicely complement the chart from The Economist:
The procedure I followed to create this chart in Excel is spelled out below. TED Spread is an indicator of credit risk. I know next to nothing about it: I’m an engineer, not an economist. I was merely using the data to show how to replicate the chart. If you’re playing along at home, you can save yourself some Google time and download the data file Economist_20071219_TED.csv.
Creating the chart
The first step is to create a line chart from the date-TED data. The default in Excel 97-2003 is fairly ugly, and the Excel 2007 version (not shown) is better, but still miles from the target.
The next step is to resize the chart to fit within the allotted space. Do this first, because resizing the chart later will probably require realignment of most of the chart elements.
Next, delete the legend and format the plot area to remove the borders and get rid of the muddy gray background.
Next, format the category (X) axis, the horizontal axis along the bottom of the chart. Make the scale minimum and maximum 1-Jan-2007 and 31-Dec-2007. Make sure the base unit is days, the major unit is 3 months, and the minor unit is 1 month. Check the “Value (Y) axis crosses at maximum” box. Finally, select the outside option for both major and minor tick marks.
Format the gridlines to use a medium gray line color. Format the category (X) axis to use a thicker line of the same color, and remove the tick labels (we’ll add custom labels in a moment). Format the value (Y) axis so it uses no line, and so its major unit is 100. Change the line color of the plotted series, and use a thicker line.
Set up a data range for the custom category (X) axis labels, as shown below. The first column contains the month labels that we want to display, the second contains the date in the middle of the same month, and the third contains zeros so the series we must add is located at the bottom of the chart.
Copy the green part of this data range, select the chart, and use paste special to add the data as a new series, with series in columns, series names in the first row, and category labels in the first column. The new series may be difficult to discern, because the chart was created as a line without markers chart. I’ve added markers to this series for visibility, and I’ll remove them later.
Select this new series and convert it to an XY chart type.
Yikes! Don’t panic (never panic, it’s only Excel). Select the new series, and move it to the primary axis.
Add custom data labels under this new series. You can do it manually by adding any of the default data label options, then one by one editing each label, but doing it yourself gets old after about the third label. Fortunately there are numerous utilities that automate this process, using a selected worksheet range for the labels (maybe one day Excel will do this without the need for programming). Two good (and free) ones are Rob Bovey’s Chart Labeler and John Walkenbach’s Chart Tools. Use the utility of choice, and apply the range of labels in the yellow cells below the new series.
Add the chart title, format it, and drag it into position. You can individually format any portion of a chart title, axis title, or data label by selecting a subset of its characters, and clicking the formatting buttons in the menu or ribbon. If the text element is linked to a worksheet cell, this partial formatting is not possible.
Add the “Basis points” label as a textbox. The easiest way to add a textbox to an Excel chart is to select the chart area or plot area, type the label, and press Enter. The label appears in the middle of the chart. It’s too easy to create labels this way, if you aren’t watching and start typing blindly, so Excel 2007 won’t recognize the keystrokes unless you select the chart area or plot area, then click in the formula bar before typing.
Finally, shrink the height of the plot area to make room for the description at the bottom, and add the description as one or two textboxes. I used two to allow better control over line spacing. Readjust the positions of all of the chart elements as needed, save the file, and you’re done.