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.


Download Practice Workbook

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


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

Let’s say, we have a dataset that contains information about several Sales representatives of XYZ group. The Name 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 our today’s task.

sort bar chart in excel without sorting data


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.

Use RANK Function to Sort Bar Without Sorting Data

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

Use RANK Function to Sort Bar Without Sorting Data

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

Use RANK Function to Sort Bar Without Sorting Data

Read More: How to Change Bar Chart Width Based on Data in Excel (with Easy Steps)


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 to sort 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.

Combine INDEX and MATCH Functions to Sort Bar Without Sorting Data

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

Combine INDEX and MATCH Functions to Sort Bar Without Sorting Data

  • 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 of the INDEX and MATCH functions.

Combine INDEX and MATCH Functions to Sort Bar Without Sorting Data

  • 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 Combine Two Bar Graphs in Excel (5 Ways)


Similar Readings


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.

Create Bar Chart Without Sorting Data

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

Create 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 sort automatically by changing the data which has been given in the below screenshot.

sort bar chart in excel without sorting data

Read More: How to Create a Bar Chart in Excel with Multiple Bars (3 Ways)


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.


Conclusion

I hope all of the suitable steps mentioned above to sort 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

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo