5 Comments

  1. Chris Ganowski says:

    Thanks for posting about this. I too have never understood while Excel reverses the sort order when transposing data from a table to chart. I generally use option B that you outlined above. However, I’ve noticed that if the chart includes a y axis, and you choose this option, Excel moves the axis from the bottom of the plot area to the top. Sometimes this isn’t an issue, like when you don’t use a y axis at all, as in your example. Or sometimes having a y axis at the top of the plot area makes logical and aesthetic sense. But there are cases where keeping the y axis at the bottom of the plot area is preferred, but by reversing the category order, this isn’t possible. I wonder if this whole thing is a strange little blip, or if there some kind of method to Microsoft’s madness…

  2. Ian Watkins says:

    Chris – in the Format Axis pane, as shown above, under horizontal axis crosses, change it to ‘at maximum category’. This will move the y-axis back to the bottom.

  3. Dave Bruns says:

    Hi Ann – with categories in reverse order – Jon Peltier has a nice article on the source of this problem in Excel. The gist: Excel plots low values closest to the origin, and higher values further away. This works well for column charts, but causes bar charts to look upside down.

  4. Dawn Reinhardt-Wood says:

    Thank you! Life saver! Or, at least, hair saver! Been pulling mine out for a half hour.

  5. Kalpana says:

    Thank you so much. Saved my life and time. you are a genius

Leave a Reply to Kalpana Cancel reply

Your email address will not be published. Required fields are marked *

How to Sort Your Bar Charts

Mar 14th, 2017 / Data Visualization / , , , , , ,

“Ann, my bar chart is upside down! Help!”
You read my prior post about using horizontal bar charts for nominal variables like favorite fruits. My toddler is eating two! bananas a day so bananas are the winner in this fictional fruit contest. Our original table is ordered alphabetically and that just won’t do. Our viewers will either want to know about the favorite fruit (a greatest to least ordering) or they’ll want to know about the least favorite fruit (a least to greatest ordering).

You’ve got two options for re-sorting your bar chart.
Option A is to re-sort your table. If you’re not working in spreadsheets all the time, you may not have discovered sorting and filtering features before. Highlight your table (you can see which rows I highlighted in this screenshot), head to the Data tab, and click the Sort icon. You can sort either column. To arrange your bar chart from greatest to least, you sort the # of votes column from largest to smallest. Well, that would be the logical approach. A largest to smallest sorting should produce a largest to smallest chart, right? Right? Right? But the chart does a somersault! I’ve never figured out why spreadsheets do this. If you work for Microsoft, c’mon. Fix this already.

Here’s where option A comes in. You anticipate that your chart is going to do a somersault and sort your table the opposite of what you want to appear in your chart.

Option B, my preferred method, is to leave your table alone. I like my tables to match my charts–largest to smallest tables with largest to smallest graphs. It keeps me organized so that I don’t have to do mental somersaults every time I look at my screen.
To re-sort your chart, click on the category labels on the left. You’ll see a rectangular border appear around the outside of the categories.

Hold your mouse over the lettering, like the word apples. Right-click and select the option on very bottom of the pop-up menu called Format Axis.
Select the option on the very bottom of the pop-up menu, which is called Format Axis.
Then, on the Format Axis window, check the box for Categories in Reverse Order. That’s a jargony name with a straightforward purpose. It just re-sorts your bar chart in the opposite order of your table.
Check the box that says Categories in Reverse Order to, you know, reverse the order of your chart's categories.
 

Download the template
 

5 Comments

  1. Chris Ganowski says:

    Thanks for posting about this. I too have never understood while Excel reverses the sort order when transposing data from a table to chart. I generally use option B that you outlined above. However, I’ve noticed that if the chart includes a y axis, and you choose this option, Excel moves the axis from the bottom of the plot area to the top. Sometimes this isn’t an issue, like when you don’t use a y axis at all, as in your example. Or sometimes having a y axis at the top of the plot area makes logical and aesthetic sense. But there are cases where keeping the y axis at the bottom of the plot area is preferred, but by reversing the category order, this isn’t possible. I wonder if this whole thing is a strange little blip, or if there some kind of method to Microsoft’s madness…

  2. Ian Watkins says:

    Chris – in the Format Axis pane, as shown above, under horizontal axis crosses, change it to ‘at maximum category’. This will move the y-axis back to the bottom.

  3. Dave Bruns says:

    Hi Ann – with categories in reverse order – Jon Peltier has a nice article on the source of this problem in Excel. The gist: Excel plots low values closest to the origin, and higher values further away. This works well for column charts, but causes bar charts to look upside down.

  4. Dawn Reinhardt-Wood says:

    Thank you! Life saver! Or, at least, hair saver! Been pulling mine out for a half hour.

  5. Kalpana says:

    Thank you so much. Saved my life and time. you are a genius

Leave a Reply to Kalpana Cancel reply

Your email address will not be published. Required fields are marked *

Related Courses

Most “professional” reports are too long, dense, and jargony. Transform your reports with these practical tips. You’ll never look at reports the same way again.

Learn More

Data visualization best practices, practical how-tos, tutorials in multiple software platforms, and guest experts. Designed with busy number-crunchers in mind.

Learn More

SPONSORED

#f-post-el-35{display:none !important}

#f-post-el-35{display:none !important}

Learn More