In Excel, when you open the sorting dialog box, it allows you to sort multiple (two, three, or even more) columns. In this tutorial, we will learn how to do this multiple-column sorting.
Steps to Sort Multiple Columns
- First, select any cell in your data and go to the Data Tab > Sort.
- In the dialog box, click on the “Sort by” drop-down and select the column you want to use to sort as the first order. You can also change the order from “Smallest to Largest” to “Largest to Smallest”.
- Now, click the Add Level button to add the second column for sorting. And again, click on the “Sort by” drop-down and select the column name. At this point, you have used two columns for sorting.
- In the end, click OK to sort the data.
When you click OK, it will sort the data using the two columns, age and then Date Joined. You can see in the below snapshot that the ages are sorted from lowest to highest, and people with the same age are sorted by “Date Joined”.
And if you want to sort data by using more than two columns, you need to add one more level or any number of levels you want.
You can also change the level of the columns you have already added by using the Move Up and Move Down buttons.
The order of the levels matters. Excel sorts by the first level, and it only uses the second level to sort the rows that have the same value in the first column, and so on.
Other Options in the Sort Dialog Box
- My data has headers: keep it ticked so the header row stays on top and the drop-downs show your column names.
- Delete Level and Copy Level: remove a level you don’t need, or copy one and just change its column.
- Sort On: sort by Cell Values, Cell Color, Font Color or Conditional Formatting Icon for each level.
- Options: make the sort case-sensitive, or sort left to right instead of top to bottom.
Sort Multiple Columns with a Formula
If you have Microsoft 365 or Excel 2021 and later, you can use SORTBY to get a sorted copy of the data that updates on its own when the data changes. The following formula sorts A2:C10 by column B (Age) in ascending order and then by column C (Date Joined) in ascending order:
=SORTBY(A2:C10,B2:B10,1,C2:C10,1)
Use -1 instead of 1 for descending order. You can get the same result with SORT by giving it the column numbers and the orders in curly brackets:
=SORT(A2:C10,{2,3},{1,1})
Frequently Asked Questions
How many columns can I sort by at once?
The Sort dialog box allows up to 64 levels, so you can sort by up to 64 columns in one go.
Can I sort one column ascending and another descending?
Yes. Each level has its own Order drop-down, so you can choose Smallest to Largest for one column and Largest to Smallest for another.
Why did my rows get mixed up after sorting?
You probably selected only some of the columns before sorting. Select a single cell inside the data (or the whole table) so Excel sorts complete rows together.
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.