29 Comments

    Leave a Reply

    Your email address will not be published.

    “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).

    Excel spreadsheet showing data and corresponding chart.

    How to Re-Sort Your Bar Charts in Microsoft Excel

    You’ve got two options for re-sorting your bar chart.

    Option A: Re-Sort the Table

    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.

    Here’s how you can sort data tables in Microsoft Excel:

    1. Highlight your table. You can see which rows I highlighted in the screenshot below.
    2. Head to the Data tab.
    3. Click the Sort icon.
    4. 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!

    Excel spreadsheet showing data and corresponding chart where the data is upside down.

    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.

    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: Re-Sort the Bars within the 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.

    Here’s how you can re-sort the bars within your Microsoft Excel charts:

    1. Click on the category labels on the left. You’ll see a rectangular border appear around the outside of the categories.
      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.
    2. Hold your mouse over the lettering, like the word apples.
    3. 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.
    4. 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.

    Your Turn

    Comment and let me know: Which method do you prefer, and why? Do you prefer Option A, in which you anticipate that the table will do a somersault, and you sort the table upside down? Or do you prefer Option B, in which you use the Categories in Reverse Order button to sort the bar chart itself?

    Bonus: Download the Spreadsheet

    Want to practice sorting your table and/or sorting the bar chart? Download the spreadsheet I used in this blog post.


    Download the template

    More about Ann K. Emery
    Ann K. Emery is a sought-after speaker who is determined to get your data out of spreadsheets and into stakeholders’ hands. Each year, she leads more than 100 workshops, webinars, and keynotes for thousands of people around the globe. Her design consultancy also overhauls graphs, publications, and slideshows with the goal of making technical information easier to understand for non-technical audiences.

    29 Comments

      Leave a Reply

      Your email address will not be published.

      You Might Like

      Our complimentary mini course for beginners to dataviz. Takes 45 minutes to complete.

      Enroll

      Need practical how-to tips? In this course, you’ll learn how to make great graphs inside software you already have. Includes beginner, intermediate, and advanced tutorials for making charts from start to finish.

      Enroll

      Subscribe

      Not another fluffy newsletter. Get actionable tips, videos and strategies from Ann in your inbox.