How to Sort Bar Chart Without Sorting Data in Excel (with Easy Steps)

While working in Microsoft Excel with the sales-related worksheets, sometimes we need to sort a bar chart without sorting data so that we can easily understand the ascending or descending order of sales. sorting a bar chart without sorting data in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn three quick and suitable steps to sort bar charts without sorting data in Excel effectively with appropriate illustrations.

How to Sort Bar Chart Without Sorting Data in Excel: 3 Quick Steps

Let’s say, we have a dataset that contains information about several Sales representatives of XYZ group. The Names of the Sales representatives and their sales in several months are given in columns B, and D respectively. From our dataset, we will create a bar chart, and we will apply the RANK, INDEX, and MATCH functions to sort the bar chart without sorting data in Excel. Here’s an overview of the dataset for today’s task.

Step 1: Use RANK Function to Sort Data

In this portion, we will use the RANK function to sort bar charts without sorting data. Let’s follow the instructions below to sort bar charts without sorting data by using the RANK function!

First, select cell D5 for the convenience of our work.

After that, write down the RANK function in that cell. The RANK function is,

=RANK(C5,C$5:C$14,0)

Where C5 is the number of the RANK function, C$5:C$14 is the ref of the RANK function, and 0 is the Descending order. We use the dollar ($) sign for the absolute reference of a cell.

Hence, simply press ENTER on your keyboard. As a result, you will get 2 which is the output of the RANK function.

Now, AutoFill the RANK function to the rest of the cells in column C.
After completing the above process, you will be able to apply the RANK function which has been given in the below screenshot.

Step 2: Combine INDEX and MATCH Functions to Sort in Ascending Order

Here, we will apply the INDEX and MATCH functions to sort bar charts without sorting data. These functions are very useful for sorting bar charts without sorting data. Using these functions, firstly, we will find out the Sales representative’s name after that we will find out the corresponding sales of the Sales representatives. Let’s follow the instructions below to sort bar charts without sorting data by using the INDEX and MATCH functions!

First of all, select cell D5 for the convenience of our work. After that, write down the INDEX and MATCH functions in that cell. The INDEX and MATCH functions are,

=INDEX(B$5:B$14,MATCH(F5,D$5:D$14,0))

Formula Breakdown:

Inside the MATCH function, F5 is the lookup_value, D$5:D$14 is the lookup_array, and 0 is used for the Exact match.
Inside the INDEX function, B$5:B$14 is the reference, and MATCH(F5,D$5:D$14,0) is the row_num of the INDEX
We use the dollar ($) sign for the absolute reference of a cell.

Hence, simply press ENTER on your keyboard. As a result, you will get Dalton as the output of the INDEX and MATCH functions.

Now, AutoFill the INDEX and MATCH functions to the rest of the cells in column G.

Again, select cell H5 for the convenience of our work. After that, write down the INDEX and MATCH functions in that cell. The INDEX and MATCH functions are,

=INDEX(C$5:C$14,MATCH(F5,D$5:D$14,0))

Formula Breakdown:

Inside the MATCH function, F5 is the lookup_value, D$5:D$14 is the lookup_array, and 0 is used for the Exact match.
Inside the INDEX function, C$5:C$14 is the reference, and MATCH(F5,D$5:D$14,0) is the row_num of the INDEX
We use the dollar ($) sign for the absolute reference of a cell.

Hence, simply press ENTER on your keyboard. As a result, you will get $9,737.00 as a return for the INDEX and MATCH functions.

Now, AutoFill the INDEX and MATCH functions to the rest of the cells in column H.
After completing the above process, you will be able to apply the INDEX and MATCH functions which have been given in the below screenshot.

Step 3: Create Bar Chart Without Sorting Data

Now, we will sort bar charts without sorting data from our dataset. To do that, we use the Insert ribbon. Let’s follow the instructions below to learn!

First of all, select data ranging from G4 to H15.

Hence, from Insert ribbon, go to,

Insert → Charts → 2-D Bar

As a result, you will be able to create a 2-D Bar chart without sorting data.

Now, we will check our bar chart whenever it works or not. We will change the sale value of Zuschuss from $9,158.00 to $11,000.00. We notice that the 2-D Bar chart sorts automatically by changing the data which has been given in the below screenshot.

Alternative: Sort Bar Chart Using Excel SORT Function

If you want to achieve the sorting using only the SORT function without additional helper columns, you can do the following:

Select a cell (e.g., E5) and use the given formula to sort the entire dataset based on the sales column:

=SORT(B5:C14,2,-1)

In this formula:
B5:D14 is the range of your data. “2” specifies the 2nd column to sort (sales column in this case), and “-1” indicates descending order.

Now, you’ve got the sorted data in cell range E5:F14.
After that, to create the sorted bar chart, select the first cell (e.g. E5) of sorted data, then go to the Insert tab, and select 2-D Bar Chart.

As a result, you will get the Bar chart based on the sorted sales data.

Note: Excel SORT function is available in only Microsoft 2021 and later versions.

Things to Remember

👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.

👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Sort Bar Chart Without Sorting Data.xlsx

Conclusion

I hope all of the suitable steps mentioned above to sort a bar chart without sorting data will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

<< Go Back to Excel Bar Chart | Excel Charts | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

Kyle Mar 25, 2023 at 4:09 AM

What if two of your sales values are identical resulting in two same rankings?
- Reply
  Musiha Mahfuza Mukta Mar 27, 2023 at 12:44 PM
  
  Thanks, KYLE, for your query. If there are identical values, then it will give same Rank. Also, the RANK function will skip one Rank. For your better understanding, I’m changing the sales value of Janifer to $9158. So, Zuschuss and Janifer get the same rank (2nd). Thus, the 3rd rank will be missed. Here, you must re-write the Rank of F7 cell to “2” and change the array for INDEX-MATCH function using in G7 an H7 cell. Basically, you need to set the array without Zuschuss information. Below, I have attached the whole scenario.
  There is another way, if you want to get unique Rank for all. Like Zuschuss comes first than Janifer so Zuschuss will get 2nd rank and Janifer will get 3rd rank. In this case, you just need to change the formula in D column given below: =RANK(C5,$C$5:$C$14,0)+COUNTIF($C$5:C5,C5)-1
  You don’t need to change the array of INDEX-MATCH function.
  Regards
  Musiha Mahfuza Mukta| Team Exceldemy
Carlos Salazar-Velasquez Mar 27, 2023 at 6:22 AM

Hi Md. Abdur Rahim Rasel, first, I wanted to thanks for your excellent website and the amazing help you provide – it is one of the best all around.

Second, I tried your approach to sort bar chart without sorting the data and every single time, either using your example excel or my own file, the results after doing the INDEX, MATCH, it is #N/A.
- Reply
  Musiha Mahfuza Mukta Mar 27, 2023 at 1:53 PM
  
  Thank you, CARLOS for your comment. You have to use the correct array (Sales Rep
  or B5:B14) in INDEX function and Rank Column (D5:D14) in MATCH function. Also, while using the Fill Handle icon, you have to freeze both arrays. The most important part, you must write Rank 1,2,3.. manually in General format in F column.
  There may have extra space or Apostrophe (‘) in the F column where you insert Rank numbers manually. You should remove all extra spaces.
  You can see our article related MATCH function error. The link is: https://www.exceldemy.com/excel-match-function-not-working/#Case_1_NA_Error
  For getting basic idea of INDEX-MATCH function you can see the examples from this article https://www.exceldemy.com/excel-index-match-example/
  Still, you are facing the problem then please comment with your used formula and sample dataset.
  Regards
  Musiha Mahfuza Mukta| Team Exceldemy.