How to Sort Bar Chart in Excel Without Sorting Data

An Excel bar chart is most effective when it showcases your data clearly, typically with the bars sorted in ascending or descending order. However, sorting your main data table is often not an option. Doing so might disrupt formulas, disturb a required chronological order, or just create chaos in a workbook shared with your team.

There is a simple, effective method to create a dynamically sorted bar chart that updates on its own, all while keeping your source data table intact. This walkthrough guides you step-by-step, showing you the functions and tips needed to build a professional chart that will allow you to present your data professionally.

Why Not Just Sort the Data Source?

The easiest path might be to right-click your sales figures and sort them. You get the bar chart you need quickly, and if your workbook is a quick and dirty analysis not to be shared, sure, go for it. However, most Excel sheets contain data for multiple purposes, and you need them clean!

Here are scenarios where sorting your main data is actually not the best idea or anything other than professional behavior!

• Maintaining Chronological Order: What if your data table tracks weekly progress and the week 1, 2, 3 pattern is what matters most? That data, even though its bars are not in the "perfect height order" on a chart, still needs to be in logical day-order as that is part of the story.
• Collaborating with Teams: Many people are working off one source. If it's constantly getting rearranged, it's going to be annoying for people who need that data for different things.
• It is Just an Input Table: Oftentimes, your data source table feeds multiple different charts, pivot tables, and formula calculations in one workbook, so sorting it just breaks all that stuff. A bar chart should never break your entire workbook when there is a solution.

But don't worry - these examples don't mean you're stuck. We can get exactly what you and your bosses are looking for: a clean bar chart without all the other issues in your report!

The Dynamic Sorting Approach: Building Your New Table

Our strategy here is actually quite simple for any skill level. We will just make a second small and smart table - a 'helper' table - in an open area of our spreadsheet to the side. The sole purpose of your helper table will be to fetch and rearrange all data from the original "untouched data." Your new chart can be connected only to this smart table!

The beauty of this method is that once set up, your chart updates dynamically based on the core original table, meaning as those numbers change, the chart will update too. Your sorting is automatic. It just needs some of Excel's most basic functions: LARGE() and INDEX(), MATCH() so we can execute all our logic.

Step-by-Step Tutorial: Sort Your Data for Your Bar Chart

Let's get to work now and create our own dynamically sorted bar chart, step by step, using some easy data for all of us to practice with as our guide:

Step 1: Start with Clean Data

Make your own table to follow along. My goal for right now is to have a chart showing the most followers going downwards. I want it on an empty part of my spreadsheet starting at Cell A1. Add a title on Row 1 with platform names and follower count numbers.

Now our sheet needs the helper table we are discussing for our chart. I will show that next to our source data in Cell D/E on my sheet. Let's just do that, with 'Sorted Followers' in D and 'Sorted Platforms' in E. The stage is set, and now for the formulas.

Step 2: Sort Values: The LARGE Function Comes to Help Us

We can now begin to pull our follower numbers from the original table, so in D2, type the formula:

=LARGE($B$2:$B$10, ROW()-1)

This LARGE() function requires your value range (followers in Cell B). Use the first argument for the complete range and the second argument as a dynamic number that changes with the drag to always fetch the largest number and so forth.

Absolute and Relative Referencing: ($)

Make sure to use two signs: $ for your range selection (e.g., $B$) so as you drag the cells, the range doesn't move, it must look at all the cells (B2 through B10 only). Without the $ sign, dragging could lead to errors.

Step 3: Find Your Categories Using INDEX()/MATCH()

Now, let's get all our platform names into the sorted chart. Do this by typing the formula in E2:

=INDEX($A$2:$A$10, MATCH(D2, $B$2:$B$10, 0))

The 0 is crucial for exact matching in the list, ensuring no bad value when it guesses the closest number. Everything should work perfectly now.

Step 4: Handling Duplicate Values

If the data table had a Twitter value also with the same follower count as LinkedIn, for example, our formula might get confused. To fix this, simply add a helper column in your source data table. In Column C, use this formula:

=B2 & "--" & COUNTIF($B$2:B2, B2)

This formula will append a count to duplicate values, ensuring every entry is unique. Then, update your helper tables with these new IDs.

Build Your Final Graph with Sorted Data

This step is straightforward: highlight your helper table with 'E' and 'F' together, go to the Insert menu, and then click Chart. Choose a bar chart for a clean presentation. Now, your chart is finally made!

You can hide Columns C and D since those were for tools and formulas - just right-click to hide on those columns. Your table looks professional now, with just your data and chart visible.

Final Thoughts

With this helper 'dynamic table' approach, you have now created an Excel chart that sorts itself without changing the source data. Your chart will look professional and clean, and your source data remains unsorted. You're welcome.

Our product Graphed is also here to help in this way. We built a tool to ensure data doesn't have to be broken. We allow your team to connect Google Analytics, Facebook, Shopify, and use simple English to create reports automatically with AI. Join us on Graphed for a free sign-up and explore building better data charts and reporting with us.