Discussion on:

Squash an odd Excel charting bug that could drive you crazy

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

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

Great tip, though

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.

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.

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.

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

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