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.
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:
=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
- Auto Sort in Excel Without Macros
- How to Sort in Excel by Number of Characters
- Excel Sort by Name
- Excel Sort by Column Without Header
- Excel Sort Column by Value
- How to Sort Multiple Columns in Excel Independently of Each Other
- How to Sort Alphabetically in Excel with Multiple Columns
- How to Perform Custom Sort in Excel
- How to Add Sort Button in Excel
- How to Sort by Last Name 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)
- 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
Read More: How to Sort and Filter Data in Excel
Similar Readings
- Excel Auto Sort when Data Changes
- How to Sort Data in Alphabetical Order in Excel
- How to Put Numbers in Numerical Order in Excel
- How to Sort Alphanumeric Data in Excel
- How to Sort Multiple Columns in Excel
- How to Auto Sort Multiple Columns in Excel
- How to Sort Columns in Excel Without Mixing Data
- Sort and Filter in Excel Not Working
- Excel Not Sorting Numbers Correctly
- Excel Sort by Cell Color Not Working
- Excel Sort Largest to Smallest Not Working
- How to Remove Sort in Excel
- Excel Sort Not Working
- How to Sort Drop Down in Excel
- How to Sort Merged Cells in Excel
- How to Sort Merged Cells of Different Sizes in Excel
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
Read More: How to Sort Two Columns in Excel to Match
Similar Readings
- Excel Sort by Row not Column
- How to Sort Rows in Excel
- Excel Sort Rows by Column
- How to Arrange Numbers in Ascending Order in Excel Using Formula
- How to Sort by Date in Excel
- Excel Sort Dates in Chronological Order
- How to Sort Data in Excel Using Formula
- How to Sort Dates in Excel by Year
- Excel Sort by Date and Time
- How to Use Excel Shortcut to Sort Data
- How to Sort by Ascending Order in Excel
- [Fix] Excel Sort by Date Not Working
- Excel Sort and Ignore Blanks
- Excel Auto Sort when Data is Entered
Download Practice Workbook
Download this practice sheet to practice while you are reading this article.
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
- How to Sort Dates in Excel by Month and Year
- How to Sort Excel Sheet by Date
- How to Sort by Month in Excel
- Sort IP Address in Excel
- Random Sort in Excel
- How to Sort Birthdays in Excel by Month and Day
- How to Sort Excel Tabs
- How to Sort by Color in Excel
- How to Remove Sort by Color in Excel
- Advanced Sorting in Excel
- How to Sort Duplicates in Excel
- Excel Sort Unique
- How to Sort Numbers in Excel
- How to Auto Sort Table in Excel
- Advantages of Sorting Data in Excel
- Difference Between Sort and Filter in Excel