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.

**Read More:** How to Make a Bar Graph Comparing Two Sets of Data in Excel

### 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.

**Read More:Â **How to Show Difference Between Two Series in Excel Bar Chart

### 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.

**Read More: **How to Change Bar Chart Width Based on Data in Excel

## 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)`

**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.

## 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.

**Related Articles**

- How to Make a Bar Graph with Multiple Variables in Excel
- How to Make a Bar Graph in Excel with 2 Variables
- How to Make a Bar Graph in Excel with 3 Variables
- How to Make a Bar Graph in Excel with 4 Variables
- How to Make a Percentage Bar Graph in Excel
- How to Show Number and Percentage in Excel Bar Chart
- Excel Bar Chart Side by Side with Secondary Axis

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

What if two of your sales values are identical resulting in two same rankings?

Thanks, KYLE, for your query. If there are identical values, then it will give same Rank. Also, the

RANKfunction will skip one Rank. For your better understanding, I’m changing the sales value ofJaniferto$9158. So,ZuschussandJaniferget the same rank (2nd). Thus, the3rdrank will be missed. Here, you must re-write the Rank ofF7cell to “2” and change the array forINDEX-MATCHfunction using inG7anH7cell. Basically, you need to set the array withoutZuschussinformation. Below, I have attached the whole scenario.There is another way, if you want to get

unique Rankfor all. LikeZuschusscomes first thanJanifersoZuschusswill get2ndrank andJaniferwill get3rdrank. In this case, you just need to change the formula inD columngiven below:=RANK(C5,$C$5:$C$14,0)+COUNTIF($C$5:C5,C5)-1You don’t need to change the array of

INDEX-MATCHfunction.Regards

Musiha Mahfuza Mukta| Team Exceldemy

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.

Thank you, CARLOS for your comment. You have to use the correct array (

Sales Repor

B5:B14) inINDEXfunction andRankColumn (D5:D14) inMATCHfunction. Also, while using theFill Handleicon, you have to freeze both arrays. The most important part, you must write Rank1,2,3..manually inGeneralformat inF column.There may have

extra spaceorApostrophe(‘) in theF columnwhere you insert Rank numbers manually. You should remove all extra spaces.You can see our article related

MATCHfunction error. The link is:https://www.exceldemy.com/excel-match-function-not-working/#Case_1_NA_ErrorFor getting basic idea of

INDEX-MATCHfunction you can see the examples from this articlehttps://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.