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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

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

1. Using SORT Function

2. Utilizing RANK, INDEX and MATCH Functions

How to Do Multiple Level Data Sorting in Excel

Download Practice Workbook

Conclusion

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

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

Using Excel SORT function
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.

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.

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 ascending or descending order.

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.

Read More: How to Undo Sort in Excel

Similar Readings

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)

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

Eventually, in the Rank column, the ranks will be added 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.

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”

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

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

Similar Readings

How to Do Multiple Level Data Sorting in Excel

We can also do multiple-level data sorting by using Excel. Suppose, we have the 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.

Steps:

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

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

Similar Readings

Download Practice Workbook

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

Auto Sorting Multiple Columns.xlsx

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.

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

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

1. Using SORT Function

2. Utilizing RANK, INDEX and MATCH Functions

How to Do Multiple Level Data Sorting in Excel

Download Practice Workbook

Conclusion

Further Readings

Leave a reply Cancel reply