Software

Squash an odd Excel charting bug that could drive you crazy

If Excel fails to recognize a series, check the heading rows in the data range before reaching for a hammer.

The easiest way to chart Excel data is to select the data range and press [F11]-instant chart! Unfortunately, the resulting chart can be a bit off and if you're in a hurry, you might not even notice the problem: sometimes Excel fails to interpret a series correctly. For example, the following chart fails to recognize the year series in column A correctly.

But why? Everything seems to be in order, and it doesn't matter how you generate the chart. Whether you select A1:M4 and press [F11] or click the appropriate chart type from the Insert tab, Excel fails to chart the series in the left-most column correctly. Even the legend defaults to Series 1, Series 2, and so on, instead of using the year values in column A.

This problem is frustrating, and you could spend a lot of time trying to fix the resulting chart with no success. You might try to recreate the chart. You might even try a different chart type, but it just won't matter. Unfortunately, most of the changes you make won't correct the chart, and you could waste a lot of time trying.

The problem is the string, Year, in A1. If there's a value in the upper-left corner of the data set (A1 in this case), Excel fails to chart the data correctly. The fix is simple: remove the string in cell A1 and generate the chart again. As you can see in the chart below, that's the only change—the string Year is gone.

Don't spend time trying to figure this one out. The key is to recognize what's happening when you see it in action. Just apply  the workaround and continue on.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks