How to sort a bar chart without sorting data in Excel – step-by-step guide

Here's how you can do it easily!
Last Updated on May 2, 2024
Logo of Microsoft Excel displayed with a large green "X" on a smaller green square, set against a gradient pink and orange background, featuring a bar chart and "PC Guide" in the corner.
You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

If you want to learn how to sort a bar chart without sorting data in Excel, then you’ve landed on the right page.

In Excel, when handling worksheets filled with sales figures, it often becomes necessary to visualize data in a sorted manner through a bar chart without actually rearranging the underlying data. This approach helps comprehend the sales trends in ascending or descending order without tampering with the original dataset.

Sorting a bar chart while keeping the dataset intact is not only straightforward but also a great way to save time and maintain data integrity. And in this guide, we will explore three efficient and easy steps to achieve this in Excel.

How you can sort a bar chart without sorting data

Imagine we have a dataset from the sales department of the XYZ Corporation. This dataset includes a list of products with corresponding units sold that have already been ranked based on their sales performance. The information is laid out neatly across columns A and B, with the rankings specified in column C. Our objective is to construct a bar chart that reflects the sales data in either ascending or descending order without altering the original arrangement of the data. We will utilize Excel’s powerful RANK, INDEX, and MATCH functions to accomplish this. These functions will allow us to visualize the data effectively while preserving the original dataset’s structure.

Step 1: Use a RANK Function

In this step, we’ll use the RANK function in Excel to organize our bar charts while keeping our dataset unchanged. This method is particularly useful when you want to visualize data according to their rank but wish to leave the original data sequence intact. Here’s how we can proceed with the given dataset.

A spreadsheet in Excel displaying a list of products with columns titled “product name,” “units sold,” and “rank,” color-coded in blue and yellow.
  • Firstly, we’ll select cell C4 to start applying the RANK function right next to our ‘Units Sold’ data for the ‘Refrigerator’.
  • The formula we’ll use in cell C4 is as follows:

=RANK(B4, $B$4:$B$13, 0)

In this formula, B4 represents the ‘Units Sold’ for the ‘Refrigerator,’ $B$4:$B$13 is the range of the ‘Units Sold’ for all products, and 0 denotes that we are sorting in descending order. The dollar sign ($) is used to create an absolute reference, which ensures that when we copy the formula down the column, the reference to our range remains constant.

  • Upon pressing ENTER, cell C4 will display the rank for the ‘Refrigerator’ based on the units sold.
A screenshot of an Excel spreadsheet with a formula for sorting product sales, highlighting cell c4 containing the rank formula and its output "3".
Refrigerator rank
  • Next, we’ll use the AutoFill feature to extend the RANK function to the rest of the cells in column C. Simply drag the fill handle from C4 down to C13 to apply the formula to all products.
Screenshot of an Excel spreadsheet with a "using rank" header, showing columns for product name, units sold, and rank; formula for ranking in cell C5 is highlighted. Additionally, a sorted bar
Rank extended

As a result, each product will have a rank assigned according to the number of units sold, allowing us to create a bar chart that is sorted by these ranks without altering the original order of our data. The final outcome will be a neatly organized bar chart, with the products ranked from highest to lowest or vice versa, depending on the chosen sort order. The process ensures that the dataset in columns A and B remains as shown above while our visual representation is sorted.

Step 2: Merge INDEX and MATCH functions for ascending-order sorting

For our given dataset, we’re going to utilize the INDEX and MATCH functions to sort the bar chart data without changing the underlying dataset. This is a powerful technique to visually represent data in an ordered fashion without disrupting the original data layout. Here’s how we can apply these functions to our sales data:

  • Initially, we’ll select cell D4. This is where we’ll input our combined INDEX and MATCH formula to determine the corresponding ‘Units Sold’ for each ‘Product Name’ based on its rank.
  • Here is the formula we’ll use in cell C4:

=INDEX($A$4:$A$13, MATCH(ROWS($E$4:E4), $C$4:$C$13, 0))

  • After pressing ENTER, cell D4 will display the ‘Units Sold’ for the product with the lowest rank.
Screenshot of an Excel spreadsheet with a formula in cell f4 displaying an index and match function to sort product names and units sold by rank.
Units sold

Formula Breakdown:

  • MATCH(ROWS($E$4:E4), $C$4:$C$13, 0) finds the position of the rank number in the range $C$4:$C$13. ROWS($E$4:E4) will increase as we drag the formula down, giving us sequential ranks starting from the smallest rank.
  • INDEX($A$4:$A$13, …) then returns the ‘Units Sold’ value from the range $A$4:$A$13 that corresponds to the rank position found by the MATCH function.
  • Then, we’ll use the AutoFill feature to copy this formula down from F4 to F13. As a result, column F will display the ‘PRODUCTS’ in ascending order of rank, while the original Units sold data in column B remains unchanged.
Sort bar chart in Excel
Products in ascending order
  • Now, we’ll move to the next step, which involves repeating a similar process to sort the ‘Units Sold’ in accordance with the rank. Select cell G4 and enter the following formula:

=INDEX($B$4:$B$13, MATCH(ROWS($E$4:E4), $C$4:$C$13, 0))

Excel spreadsheet showing two columns for product names and units sold, with an index/match formula in a selected cell to sort data based on units sold.
  • After entering this formula and using the AutoFill feature again, column G will list the ‘Units Sold’ sorted by the ascending order of their sales rank.
Sort bar chart in Excel
Units sold data

By completing these steps, we’ll have a new range of cells (D4:E13) displaying our sorted bar chart data by rank, without altering the original data sequence in columns A and B. 

Step 3: Build an unsorted data bar chart

Now, let’s proceed to visually sort our bar chart using the dataset we’ve prepared without actually rearranging the underlying data in Excel. We will be using Excel’s charting tools to create a bar chart that reflects the sorted order of sales without altering the original sequence of entries. Follow these steps to create your sorted bar chart:

Firstly, we need to select the data that will be included in our chart. For our dataset, that means selecting the range that encompasses the ‘Rank’, ‘Product Name’, and ‘Units Sold’ columns, specifically from the sorted list (columns E to G).

  • Click and drag to select the data from cells E4 to G13.
  • Navigate to the ‘Insert’ tab on the Excel ribbon.
  • Within the ‘Charts’ group, click on the ‘Bar Chart’ icon.
  • Choose the ‘2-D Bar’ from the drop-down options.
Screenshot of an Excel worksheet with two bar charts comparing sales of various products, one chart sorted by sales data and one not, and a row selected in the spreadsheet.
2-D Bar

By following these steps, Excel will generate a 2-D bar chart that visually sorts the products according to the number of units sold in descending order while your original data set remains intact.

Excel spreadsheet displaying a table titled "bar chart without sorting data" and an Excel bar chart comparing units sold of various products like refrigerators and coffee makers.
Bar chart created

After creating the chart, it’s always a good practice to verify its dynamic nature. If we were to change a ‘Units Sold’ value, such as updating the units for ‘Electric Grill’ to a different number, the chart should automatically reflect this change and re-sort the bars accordingly.


In summary, by using Excel’s functions smartly, we’ve learned how to create a sorted bar chart without changing our original data. This is a neat trick to keep your data in place while still getting a clear picture of trends through visual analysis. It’s an easy and efficient way to make your data tell a story without disrupting the raw numbers.

Learn more about Excel and its functions through these helpful guides:

Abdul is a tech writer and Editor for PC Guide, specializing in all things tech, gaming, and hardware.