Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Auto Sort Multiple Columns in Excel (2 Useful Methods)

If you are looking for how to auto sort multiple columns in Excel, then you are in the right place. Data sorting is an amazing tool to arrange your information systematically as per your requirements. There are many cases when we need to sort multiple columns automatically. Excel has no fixed tool to auto-sort your data. There are some easy ways of auto sorting your data in your worksheet including VBA macro. In this article, we will discuss three ways to auto sort multiple columns in Excel.

Read more: Excel Auto Sort When Data Changes


Download Practice Workbook

Download this practice sheet to practice while you are reading this article.


2 Ways to Auto Sort Multiple Columns in Excel

To auto sort multiple columns in Excel, we will discuss two ways of doing it.

  1. Using Excel SORT function
  2. Utilizing RANK, INDEX and MATCH functions

To discuss the methods, we have made a dataset named Dataset of Sales. It has column headers as Sales Rep. and Sales. The dataset is like this.

how to auto sort multiple columns in excel

We need to sort this dataset. Let’s discuss the methods.


1. Using SORT Function

Excel offers the SORT function to sort multiple columns easily. We also need to ensure that if one of the values changes, the data range will be re-sorted systematically. We can use the “SORT” function to achieve this type of condition. The SORT function mainly rearranges the data alphabetically. Suppose, in the following dataset we need to sort Sales Rep. alphabetically in Column E and the sorting of Sales will also change according to the change of Sales Rep.

Using SORT Function 

Steps:

  • Firstly, we need to use the SORT function.
  • So, write the formula in the E5 cell like this.
=SORT(B5:C15,1,1)

Here,

  • “Array” is our data range (B5:C15). B5:C15 refers to the cell range of Sales Rep. and their corresponding Sales.
  • [sort_index] is the column we want to sort on (1)
  • [sort_order] is where we can specify whether you want to arrange in the ascending or the descending order.

how to auto sort multiple columns in excel

  • Secondly, press ENTER.
  • Eventually, we’ll see that the Sales Rep. names are sorted alphabetically in A to Z

Note: As we are using Microsoft 365, we have got all the outputs from E5:F15 by only inserting the formula in the E5 cell. If you use another version of Microsoft you may need to use the Fill Handle to get all the outputs.

  • Now, if we enter a random name in the B9 cell, it will take a place in the E Column by auto alphabetical sorting.

how to auto sort multiple columns in excel


2. Utilizing RANK, INDEX and MATCH Functions

We can use the RANK function and the combination of INDEX and MATCH functions to auto sort multiple columns. The RANK function gives a number of rankings of the selected cells. Then the INDEX and MATCH functions sort the data according to rank. To use the functions for auto sorting, we need to follow the steps below.

Steps:

  • Firstly, write the following formula in the D5 cell like this.
=RANK(C5,$C$5:$C$14)

Utilizing RANK, INDEX and MATCH Functions

  • Secondly, press ENTER.
  • Thirdly, use the Fill Handle by dragging down the cursor while holding the right-bottom corner of the D5

how to auto sort multiple columns in excel

  • Eventually, in the Rank column, the ranks will add for different Sales Rep. as outputs.

From the result, we can see that the ranks are not given serially. Now, we will auto-sort this Rank and Sales Rep.

  • To do this, create another table, and in the “Rank” column, input 1 to 10 serially.

how to auto sort multiple columns in excel

  • Fourthly, use INDEX and MATCH functions to sort the ranks.

  • So, we need to write the formula in the G6 cell like this.
=INDEX($B$5:$B$14,MATCH(F6,$D$5:$D$14,0))

Here,

  • “Array” is the range of cells from where we want to return a value (B5:B14). Block it by pressing F4
  • To pick the row number, use the “MATCH” function within the “INDEX”
  • For “lookup_value” select the Rank number (F6)
  • For “lookup_array” select the array (D5:D14). Block it by pressing F4
  • Select “Exact match”

how to auto sort multiple columns in excel

  • Eventually, press ENTER and use the Fill Handle.
  • Consequently, the output will be like this.

Now we have got out auto-sorted data. If we change the data in the raw table, the value will auto-sort in the sorting table


How to Do Multiple Level Data Sorting in Excel

We can also do multiple-level data sorting by using Excel. Suppose, we have a following dataset with column headers as Region, Sales Rep. and Sales. We need to sort the Region column by A to Z format and then sort Sales as Larger to Smaller value format. We just need to follow some simple steps.

How to Do Multiple Level Data Sorting in Excel

Steps:

  • Firstly, select the cells B4:D15.
  • Secondly, go to Data > choose Sort.

how to auto sort multiple columns in excel

  • Eventually, a Sort window will appear like this.
  • Thirdly, select Region in the Sort by box as we want to sort based on Region Then choose A to Z in the Order box.
  • As we want to sort Sales too, we need to add another secondary option. For this, click Add.

  • Fourthly, select Sales in the Then by box and choose Largest to Smallest in the Order
  • Fifthly, click OK.

  • Consequently, we’ll see that the Region column is sorted according to A to Z order first and then the Sales column of individual sales is sorted according to Largest to Smallest

how to auto sort multiple columns in excel


Conclusion

Using these methods, you can easily auto sort multiple columns in Excel. If you have any confusion or query, feel free to comment. Don’t forget to support.


Further Readings

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo