Software optimize

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.

10 comments
emswann506
emswann506

THANK YOU!!! This article has saved my life (well, at the very least my sanity.) I had spent ages trying to get the graph to work and when I made the small change you suggested, it worked perfectly. I can't thank you enough!

Ray Baker
Ray Baker

In the old days when tables and charts (graphs) were done by hand. the upper left "cell" was empty. The title and units for the x-axis data was placed above on table and below on the chart, and the title for the y-axis data was placed to the left of the data with units. The chart title was placed above the whole chart. This convention carried over to Lotus 123 and Excel. Data Title Trial 1 2 3 1 28 26 29 2 22 23 24 Time, ms 3 19 20 19 Volts, mV 4 12 14 13 5 8 9 7 Chart Title 30 | x * . trial 1 . Volts, mV 20 | . x * trial 2 x 10 | . * x trial 3 * 0 |_________________________________ 0 1 2 3 4 5 Time, ms

Julie9009
Julie9009

This clarifies why sometime it works and sometimes it doesn't (mixing strings and numeric values).

djstates
djstates

Another subtle "feature" in Excel, if you are trying to make an X-Y scatter plot (Insert => Scatter => ...), and your data contains even a single non-numeric value, Excel will flip from a scatter plot to plotting your X and Y data as two series against an index on the abcissa. Often comes up when you are importing data because 'NaN' and 'Null' are not numeric values in Excel. Once you crack the code, it is easy enough to find aberrant values by sorting the data, but Excel does not provide any hints as to what is going on.

LedLincoln
LedLincoln

A teeny bit off topic, but this could save some readers some grief. Last week I discovered a bug that results in Excel randomly failing to print certain chart elements, even though they appear in print preview. It was caused by a recent update - look up KB2596596. Just remove the update and Excel prints properly.

ed
ed

Excel is doing what you told it to do--it's just that you didn't mean it. You expected it to use the words in the row across the top for labels for the X-Axis. Excel doesn't speak English--or Spanish or Chinese or Mesopotamian. It doesn't know that "Year" isn't "the same thing" as "January." You want it to use the numbers in the columns below each header word for the data in the chart, and it does. Unfortunately, using these "rules," it can't find labels for the Y-Axis. So it muddles through and calls them Series 1, Series 2, etc. Barring (we hope unlikely) hardware and software bugs, a computer will do exactly what you tell it to do--whether you mean it or not.

alecpjd
alecpjd

The chart works if you enter the years as text (i.e. preceded by an apostrophe!) Great tip, though

d_blair
d_blair

Actually the problem is the numeric character of the year numbers. If you pre-format the column A as "Text" in the example above it charts correctly. Think about it - how is the program supposed to know that a column with valid numeric contents is actually chart legends unless you tell it by making them text. But here is where the real bug exists - once you've typed in the values, formatting the column as text does not do anything more than left justify the years. Charting it does not correctly recognise the values as text unless you then retype the same values in the cells! Seems like they have you either way :-)

Ray Baker
Ray Baker

There is supposed to be a table and chart displayed in my post but all the "extra" spaces were formatted out.

ssharkins
ssharkins

I didn't apply the Text format, but I did enter the years as string values to begin with -- didn't make any difference either way.