Ask Mr. Excel - Leave Chart Corner Blank in Excel

Back

About MrExcel

Consulting Services

Learn Excel Resources

Challenge of the Month

Past Tip of the Week

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.

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.

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 at the right, 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.

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).

MrExcel.com Consulting can be hired to implement this concept, or many other cool applications, with your data.

MrExcel.com provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures on this web site are provided "as is" and we do not guarantee that they can be used in all situations.

Excel is a registered trademark of the Microsoft® Corporation.