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?
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 You may have forgotten too, so read on.
The Excel Rules
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?
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.
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.
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.