Tips for the 'Perfect Chart'

The telephone rang at MrExcel.com Consulting the other day and Mark had a charting problem. It just happened that I had run into the exact problem a day earlier for another client, and so I was luckily able to "wow" Mark with the answer right off the bat.

Sample Data

First, some background. Excel makes it very easy to get a default chart up and running. Let's say you have some data like that data shown to the right. You can highlight A1:D4, then from the menu, select Insert - Chart, hit Finish and get a nicely formatted default chart like this one.

Missing Series Captions

When Mark called, he was trying to chart population figures for several countries. He was using this trick and Excel would not recognize the series correctly. Instead of lines for 1990 and 2000, he was getting "Series 1" and "Series 2" and the years were being plotted as the first data point. This is frustrating to no end. For the casual chart user, you know that this should work, and there doesn't seem to be any reason why it is not working.

The solution is incredibly simple. I asked Mark if he had a title in the upper left corner of his chart data. Like the example shown, he did. If you can re-arrange your data so that this upper left cell of the chart data is blank, (in the example below, I moved the work "Census" up one cell). With the upper left corner cell blank, you can use Insert - Chart - Finish and get the correct chart.

Sample Data Table
Sample Data Table
Modified Data Table
Modified Data Table

Some notes: To make life more confusing, it is not necessary to leave the top left corner open in all cases. This workaround seems to be most necessary when one of your series has headings which are numeric (such as the years in this example).

For more tips like this page, check out MrExcel's book

Charts and Graphs for Excel 2007 Book