Troubleshooting Problems in Excel Charts: X-Axis Doesn’t Chart Correctly

Charts

You’re in a little hurry, you knock out a hand-entered table in the worksheet, drag across the data and insert a chart. But, it looks weird, like the chart below. The Y-axis isn’t right. You wanted a quick and simple table to compare months of different years so you typed in the month’s (text) across the top and years (numbers) down the left. What is going wrong?

Excel can misinterpret data when there is a label at the top left in chart data.

This has probably happened to all of us in our first year of creating Excel charts. There are two tips to what is going wrong. Look at the chart. First, the X-axis has a label, Year, in it. That shouldn’t even appear in the chart. Second, the legends don’t show the numeric years, instead it shows Series 1, Series 2, and Series 3.

What is happening is that Excel is not interpreting the data layout correctly. It’s being thrown off by the text label Year at the top left. Excel thinks that there are three series (rows) of numeric data with the first number in each series being the numeric year, e.g., 2014. It also interprets the labels across the top as the labels for each point on the X-axis, so the “Year” text label shows up on the X-axis.

Unless you are charting scattergrams or radar charts and understand how the data will be interpreted, it’s a safe rule to always leave a blank cell at the top left. Here’s what this default chart would look like with a blank cell at the top left.

With a blank cell at the top left Excel correctly interprets the data and the X-axis is correct.

With a blank cell at the top left Excel correctly interprets the data and the X-axis is correct.

 

Share the power...