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
Charley Kyd of ExcelUser.com says to use magazine formatting:
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:
- Old Business Week Excel Dashboard Report
- Excel Dashboard, Forbes Magazine Style
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.
| Axis | ||
| Jan | 1/15/07 | 0 |
| Apr | 4/15/07 | 0 |
| Jul | 7/15/07 | 0 |
| Oct | 10/15/07 | 0 |
| Dec | 12/15/07 | 0 |
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.
Sandi Mays says
Very nice blog. Clear instructions! Love it.
Jair VieraQ says
Soy un seguidor de tus enseñanzas, las cuales me han ayudado mucho en mi desempeño laboral; ahora tengo grandes ambiciones para crear una empresa dedicada al desarrollo de soluciones en excel, ya que en mi región hay poca cultura con la optimización de esta herramienta….
Pues bien esta pocas letras es para agradecer tu apote en el conocimiento que me he formado.
Jon Peltier says
From
“I am a follower of your teachings, which have helped me a lot in my labor performance; now I have large ambitions to create a business dedicated to the development of solutions in excel, since in my region there is little culture with the optimization of this tool….
Therefore well these few words are to thank your apote in the knowledge that I have formed. ”
Jair: Contento ayudar (Glad to help).
Tony Rose says
Nice job Jon. Text boxes are very powerful.
I don’t like the x-axis labels in the original chart because i’m not sure where the months fall. I may have gone with the J F M A M J J A S O N D to make it easier to follow. No knock against your version because you just recreated the original in Excel.
Jon Peltier says
Tony –
I think I agree.
The only difference is that I used a longer range for the labels, with the 15th of every month, and one-letter labels, using a simple formula to convert the date in (for example) cell F32 to a one-character label:
=LEFT(TEXT(F32,”mmm”),1)
Colin Banfield says
With each month identified, there’s probably less of a necessity to go the extra step to add special labels. The original category labels land on the tick marks (identifying the start of each month), so it’s clear what the intervals between ticks mean. This isn’t the case when you have gaps between the labels.
Jon Peltier says
Colin –
I initially had month abbreviations on the tick marks at the start of each month, but I didn’t care for this. I’d rather the month label span the month not half of the prior month.
I found the Excel axis in my finished chart slightly less confusing than the Economist one, because there was a subtle difference in tick size between the major (quarterly) and minor (monthly ticks). You can’t control the tick mark sizes in Excel, unfortunately, other than by changing the tick label font size.
In Excel I often use short error bars on a dummy series to provide tick marks of a desired size. This works well enough on tick marks on the inside of the axis, though you have to experiment with the error bar size, since it correlates with the axis scale, not with the pixel or point dimensions of the chart. This is less effective with ticks on the outside of the axis, but it can be done. If any series in the chart uses markers, there is a narrow margin (half the width of a marker, I presume) around the plot area that will show chart elements, including markers located on the edge of the plot area, and also including short error bars.
Marty says
Jon,
Thanks. I got it but now I cant seem to put the recession years. I am working on hard on it though.
Marty
Jon Peltier says
Marty –
I have a post in the works that should help. It will be published by Monday.
Marty says
Oh Thank you. It is driving me nuts. Boy, I tell you, what would life be without the internet? :)
I looked at your instructions but I just could not get the red one pt line to go from 1970 to the top (which is one series) and then all the recession years from 1960 till 2007 (as series three).
Other then that I got it. I fine tuned it and your latest post gave some more pointers as to how the Economist charts work so that was good.
Again thank you.
Marty
P.S. Good one on finding the data for that chart. That impressed me a little more then the graph. :):)
Colin Banfield says
Jon, in general, what technique or techniques do you use to update the custom labels automatically as a chart’s data change (e.g. categories contract or expand)? Although I’ve seen enough solutions that use a custom labeler, I don’t recall any articles that describe how one would update these labels as data changes (without manual user intervention).
Bacause virtually 100% of the charts I create are shared by multiple people, if I have to use any special axis labels, I embed a labeler macro within the solution. As the source data is refreshed (mostly in a querytable or pivottable), an event is triggered, which updates the label range (unless it’s a case where the range is controlled by formulas) and the chart labels. For this reason, unless the solution cannot at least be approximated using standard Excel techniques (this isn’t the case here), I avoid taking the extra steps because of the diminishing returns. Of course, if the charts are one-time events (my work doesn’t involve these), then the static labeling isn’t an issue.
Jon Peltier says
Colin –
If I have a situation like this, I use a range that always is plotted (including labels), and this range is linked to the data which is filtered or otherwise processed. The construction of this plot source range is custom: every time there are different formulas or criteria that retrieve the appropriate information.
Jay says
Jon
Nice explanations. Very helpful. A big thanks!
You seem to have missed out the red small rectangle in the top left in the magazine. :-))) Can u explain how to do that in excel graph?
Secondly, While on the subject of copying economist’s nice graphs, Can you please advice how to do that “half-donut” graph which economist is doing so well. Do you know what graph I mean? Do you wish me to search their site and give a link.
Thanks
Jay
Jon Peltier says
Jay –
I didn’t forget the little red shape, I simply chose to ignore it. If you add a shape to a chart, you can’t position it right in the corner; there’s a small margin, a couple pixels or so, that are inaccessible. (This margin is smaller or nonexistent in Excel 2007, I haven’t tested enough to be sure.)
If I wanted to achieve it, I would make a white rectangle the size of the entire background, add the red rectangle, save it as an image fiel, then use it as a custom chart area fill.
I’m not familiar with the half donut pies that The Economist presents. If you reply with a link, I can decide whether to discuss it.
Kevin says
The Economist uses Officina fonts in their charts, so if one wants to make them have that “look”, either use Officina or a free knockoff.
Jay says
Jon,
Thanks for your detailed explanation on this “small red rectangle”. I had not considered it important and casually enquired with a smiley. Your serious reply demonstrates your attention to detail and professionalism.
If you need specimen of the “half-donut ” charts I referred, Please check the below URL.
http://www.economist.com/world/na/displaystory.cfm?story_id=11376204&CFID=8103987&CFTOKEN=17239760
and also
http://www.economist.com/world/na/displaystory.cfm?story_id=11376262
Thanks
Jay
Jon Peltier says
Jay –
While the half-donut charts used by The Economist do not live down to the full incapabilities of pie and donut charts in general, they are too close for comfort to their default-formatted cousins. I feel I’d be endorsing these charts if I described how to make them. In any case, while the two examples you pointed out are not horrible, the Obama-Clinton chart falsely implies a total vote of 100%, and the Mississippi election results chart implies a much greater quantity of some sort in 2008 over 2006 merely by putting the annulus for 2008 outside that for 2006. I don’t think these curvy charts have performed any better than vertical or horizontal bar charts would have done, in fact, I just described how each does worse. I will continue to advise against using pies, donuts, and other unfavorable chart types.
Marty says
Way to go Jon. I read a book on the lie with a chart idea. It is done far too often.
Marty
Jay says
Jon,
Thanks for your explanations. I am aware of your views on the negatives of pie-chart. I am also aware that this is a consensus generally on the data visualization preferences.
Nonetheless, without taking up any issue with anyone, I wish to provide my take on these graphs as follows.
/// I don’t think these curvy charts have performed any better than vertical or horizontal bar charts would have done ///
Under this specific circumstance, the graph depicts only two values, which are slices of a whole 100%. Hence, I believe that this brings out – in a simple visual way – the extent of “share” enjoyed by each unit on the whole.
/// the Obama-Clinton chart falsely implies a total vote of 100% ///
All pie-charts do. Even if I were to convert this into a stacked-column or a simple bar chart, this negative will remain. The problem is with the data and not with the graph.
Thanks
Jay
Jon Peltier says
Jay –
The Obama-Clinton chart I thought was better than the one in the McCain article, since it only had one arc. To acknowledge the missing few percent, a third wedge should be inserted, in a neutral color, probably between the other two rather than at one end.
A stacked column or bar chart also risks the false assumption of summing to a whole. A clustered column or bar does not, and also provides an easier comparison between the two values. In almost all cases, a clustered column or bar chart is preferred over the stacked version.
The problem is not with the data but with the illustrator’s choice to omit some of the data. The arc chart is at fault for implying all 100% is there when it is not.
Rob says
Great post – and excellent example of what you can do with excel charts.
A side issue that people might find useful is how you get such nicely designed ‘magazine quality’ charts out of excel in sufficiently high resolution (300dpi+) for magazine printing. I’m producing a big set of charts for print at the moment and have been struggling with this. All export options in excel appear to only allow 72dpi.
As a solution, I’ve gone for printing to pdf with Acrobat Professional, which has a specialist printing mode PDF/A-1b:2005 (CMYK), which also gets round the RGB to CMYK (printing colours) problem. It would appear that most publishing packages (we’re using Quark) can import a pdf as a picture.
I’d be interested if anyone else has alternatives.
best wishes
Rob
Jon Peltier says
Rob –
I made charts for a client who was putting them into glossy four color brochures. I was skeptical that the resolution of an Excel chart would be adequate without major work in something like Illustrator. I tried printing the workbook as a PDF file, and the PDF driver converted the charts into EPS objects which had 300+ dpi resolution, and the output was much nicer than I could have imagined.
Excel 2003 only provides limited options for such things as line thickness, but presumably Excel 2007 would improve on this.
I was not using any fancy PDF software. I used PrimoPDF and PDF Creator, both of which are capable freeware programs.
Matt H says
Yes, printing to PDF has become my preferred way to get high-resolution output from Excel — I use GhostScript myself. For viewing and for converting to bitmaps I use the free Acrobat Viewer from Adobe — recent versions have a “Snapshot tool” that allows selecting a rectangular region copy to the clipboard as a bitmap at the currently displayed resolution. By adjusting the zoom setting before capturing the image, I can easily get a bitmap at whatever resolution I want.
Also, when pasting directly from Excel into PowerPoint or similar programs, under Paste special select “Picture (Enhanced metafile)” and you get a scalable vector image without all the overhead of an embedded Excel object.
I frequently combined Excel, PowerPoint, Ghostscript, and Acrobat Viewer in this manner: make the charts in Excel, copy and paste into PowerPoint as Enhanced metafile pictures, further tweak in PowerPoint, print to PDF using the Ghostscript driver, then finally convert to bitmaps at the needed resolution with Acrobat Viewer. All tools except Excel and PPT for these steps are free.
mike says
Dumb question, sorry, but how do you get the Y labels from the left-hand side to the right-hand?
Jon Peltier says
Mike –
None of my readers ask dumb questions. What you have to do is format the X axis scale, and check the “Value (Y) axis crosses at maximum” box.
Paul says
I see how you’ve created the custom data markers, I have a line graph i have one custom marker on the end, when i add data to it, the line extends but the marker doesnt move to the new end point, can you advise if this is possible?
Jon Peltier says
Paul –
There are two ways to handle this. One is to keep the series with lines as is, without the marker on the last point, and a one-point XY series that always indexes to the last X and Y values in the data, and this series has the custom marker. The other is to sort the data in reverse order, so the data is listed newest to oldest in the worksheet. This way the first point of the series is always the latest, and if you add the marker to this point, it will always be on the last point.
chrisham says
Wow! Thanks Jon…. Your tutorials are a fantastic read.
The chart looks great and from another tutorial you have pointed me to great utility in selecting colors : http://colorbrewer2.org/
The right selection of Color and font make a great impact in making a good dashboard or reports as you would well know.
My question is, are anyways to recognize a particular font I may see on a chart (normally something I would view online through some JPG or GIF files).
Jon Peltier says
Chrisham – To identify a font, I think you have to already have a good idea what it is. At least narrow it down by style.
Steven Matthews says
Are you sure The Economist uses Excel to make its charts? I am a graphic production artist and have worked at a number of financial institutions and publications, and we’ve always used Adobe Illustrator for final output. These institutions have unwavering standards regarding their output — font size/leading, placement, colors, etc.
Jon Peltier says
Steve –
I have no knowledge of what graphics package The Economist uses for its charts, but I am positive they don’t use Excel. I would assume they use Illustrator. This article shows how to make such a chart using Excel.
chrisham says
Jon, this may be trivial, but I am unable to select the formula bar when I select the Chart or the plot areas to add the Text Box in what would be the easiest way you recommend.
Jon Peltier says
Chrisham –
What version of Excel? Are you really selecting the chart area? Do you see “Chart Area” in the “Selected Chart Element” dropdown? It’s either on the Chart toolbar or Name Box in “Classic” Excel, or on the second and third Chart Tools ribbon tabs.
Jay says
Is there a way to make a macro or a one-click solution to paste-special a copied chart from excel into powerpoint as an enhanced Metafile? I would love to make this frequent task of mine a one-click action. I use Office 2007
Jon Peltier says
Jay –
Read my tutorial about Using Excel with Other Office Applications. It is heavily concentrated on moving charts using the image vector format of Picture (which relates to Windows Metafile). The only problem is that in Office 2007, the charts are not copied as faithfully as in 2003.
falsafay says
Jon:
How to create a chart like this:
http://www.bls.gov/emp/ep_chart_001.htm
Thanks.