Why Does Excel Switch Rows/Columns in My Chart?

Excel can do some crazy things to your Dashboard Templates, Charts and Graphs, but most of the time we just roll with the punches.  But have you ever wondered why Excel does what it does and how to fix it without changing your chart data layout?

In a message board, an Excel user asked why Excel some times puts his data in a chart one way and other times the other way.  Kind of like this:image

I didn’t choose to make my chart with Mark, John and Tom as chart legend entries, I wanted my chart to look like the one on the right.  So what gives?

Well I never really knew the reason why so I did some background investigation.  Turns out I did know, but just forgot Smile  You may have forgotten too, so read on.

The Excel Rules





So lets say that your data is in the following format:image

Now if you highlight the range and create a 2-D Clustered Column chart, it will look like this:image

It may not be the way that you thought Excel would plot your graph.  Why didn’t Excel plot the months on the horizontal axis and instead put them in the legend entries?

Then if you add one more data point for each of the sales people and your chart data table would look like this:image

and if you create a 2D clustered column chart from this data, Excel will create this graph:image



Now look at this, Excel has plotted my data in a different fashion so that the months are now on the Horizontal Axis and the sales people’s names are in the legend entries.  What is going on with this decision?

Here is what Excel is doing when creating your charts and graphs.

RULE: If you have an equal amount of rows or more rows than columns, then Excel will plot the column names as the horizontal axis categories.

RULE: If you have more rows than columns, then Excel will put the row categories as the horizontal axis categories.

That is it.  It all depends on how many rows compared to columns that you are trying to plot in the chart/graph.  If you have more rows than columns, Excel will use these as the horizontal axis.  If you have an equal amount or less rows than columns, then Excel will switch the layout design of your chart.





But don’t worry, you do not have to change the layout of your chart data, you only need to follow this easy step to make your Excel chart look the way you want it to be.

How do you fix this problem?

If you ever create a chart and Excel has chosen to put your data in the graph backwards to how you intended it to be presented, it is an easy fix.  You must first select your chart.  Then a new Excel Ribbon will appear called the “Design Ribbon”.  Select the “Design Ribbon” and then choose the Switch Row/Column button in the Data group.

image

You can toggle this chart setting back and forth and your chart will switch from rows to columns and back again.  image



First your months will appear on the Horizontal Axis and the sales person’s names will appear as legend entries.  Then it will switch the other way around.  Don’t be afraid, you won’t mess up anything and it will help you get your chart the way you want it without changing your chart data layout.

Video Tutorial

See it in action at this URL:

Did you know this rule?  What other Excel rules are you aware of and how they affect charts, graphs and tables in Excel?  Let me know in the comments.

Steve=True









2 COMMENTS

  1. No, I did not know about this rule. If you create a column or line chart, if you have a zero in a cell, then the chart will put a zero in for that label, if the data is blank, then there is no line. It can be confusing when making a chart for the year, and you are in March, and the line chart goes to zero in April. Thank you for maintaining this blog.

LEAVE A REPLY

Please enter your comment!
Please enter your name here