How to Sort Multiple Columns in Excel (5 Quick Approaches)

In a practical work scenario with Microsoft Excel, we have to sort and filter multiple columns frequently in a large range of data. In this article, you’ll get to know all of the useful and quick techniques to sort multiple columns in Excel.

The above screenshot is an overview of the article which represents an example of sorting data in multiple columns. You’ll learn more about the dataset as well as the methods and functions to sort and filter Excel data in the following sections of this article.

How to Sort Multiple Columns in Excel (5 Quick Approaches)

1. Using Sort & Filter Command to Sort Multiple Columns

To sort multiple columns in Excel at a time, using the Sort & Filter command is the best solution. It’ll let you add & choose different options to sort any range of data. In our dataset below, there are 6 columns containing some random names of computer brands, device types, model names, countries of origin, dates of release, and prices of the devices.

We want to add 2 criteria for sorting columns in our dataset. We’re going to sort the names of the countries of origin by the order of Z to A. After that, the device prices will be sorted by smallest to largest.

📌 Step 1:

➤ Select the entire table data first.

➤ Under the Home ribbon, select the Custom Sort command from the Sort & Filter drop-down in the Editing group of commands. A dialogue box will open.

📌 Step 2:

➤ Tap on the Sort by drop-down & select Country of Origin.

➤ Change the order by Z to A from the Order drop-down.

➤ Click on Add Level to assign another criterion.

📌 Step 3:

➤ Now select Price from the next drop-down.

➤ Change its order from Smallest to Largest.

➤ Press OK & you’re done.

Like in the picture below, you’ll have your sorted data for all columns at once. In Column G, the prices are sorted based on the orders of the country names as we’ve assigned the order for prices as the secondary criteria for sorting.

2. Assigning Filter Options on the Table Headers

By using the Filter buttons on the table headers, you can sort the columns more easily. But in this case, you cannot add criteria for two or more columns simultaneously.

📌 Step 1:

➤ Select all the headers of the table first.

➤ Under the Home tab, choose Filter command from the Sort & Filter drop-down in the Editing group of commands. You’ll find the Filter buttons on your table headers.

📌 Step 2:

➤ Now click on the Country of Origin drop-down.

➤ Select the order- Sort Z to A.

➤ Press OK.

You’ll get the sorted columns based on the countries of origin. You can sort any other columns too based on your criteria with Filter options from the column headers.

3. Conditional Formatting to Sort Multiple Columns

If you want to sort your data or columns by highlighting with colors or symbols you have to choose Conditional Formatting. But this method won’t change or modify the orders of your data at all. Rather, it’ll only highlight the data in a column or row based on the sorting criteria. Assuming that we want to sort the column for Date of Release by highlighting the data.

📌 Steps:

➤ Select the entire column for the Date of Release.

➤ Under the Home ribbon, from the Conditional Formatting and Color Scales drop-downs, choose the Green-White or any other color scale you prefer. You’ll be shown a preview of that color scale in your assigned column.

➤ Press Enter & you’re done.

You’ll be displayed the assigned column with Green-White color scales where the full green part denotes the latest dates and light green or white ones are for older dates. This is how you can sort the data by highlighting them with colors.

You can sort the column for prices too with similar or another range of color scales. Here, the prices are sorted in an ascending order & then if you use color scales then it’ll look like this in the following picture.

Sorting by color scales depends on the available numerical data. If you want to sort text strings in a column or a row, then you have to look for another method or customize the selected data from Conditional Formatting.

4. Inserting SORT Function to Sort Multiple Columns

When you have to sort a range of data in another area in your spreadsheet and if you don’t want to copy the original data either, then using the SORT function can be a useful solution. Let’s get introduced to this SORT function first.

• Objective of the Function:

Sorts a range of an array.

• Syntax:

=SORT(array, [sort_index], [sort_order], [by_col])

• Arguments:

array- Range of data or cells that you want to sort.

[sort_index]- Column or row number that’ll be sorted.

[sort_order]- Ascending(1) or Descending(-1) order.

[by_col]- You have to choose whether the sorting will be done by column or by row.

In the picture below, another table has been added under the 1st one where we’ll apply the SORT function based on the data in the original table.

📌 Steps:

➤ Select the output Cell B26 & type:

➤ Press Enter and you’ll find the sorted columns in the second table.

We’ve only sorted the prices here by largest to smallest. Inside the function, the 1st argument is B5:G23 which is the selected range of data to be sorted. Here sort_index or column number has been chosen as 6 since the 6th column represents the prices. ‘-1’ in the 3rd argument means, we’re sorting the data in descending order. In the 4th argument, the logical function FALSE has been chosen to assign the sorting by rows, not by columns.

5. Applying SORTBY Function to Sort Multiple Columns

By using the SORTBY function, you can add multiple criteria for sorting columns. The syntax of this formula is:

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2])

Based on our dataset, we’ll sort the column for Country of Origin first, and then the prices will be sorted by smallest to largest.

📌 Steps:

➤ Select Cell B26 and type:

➤ Press Enter and you’ll be shown the sorted array at once in the second table.

Inside the function, 1st argument is the selected array of data that has to be sorted. The 2nd and 3rd arguments are the range of cells- E5:E23 & -1 respectively. It means the text data in Column E will be sorted by the alphabetical order of Z to A. These two arguments combine the first criteria for sorting. The second criteria consists of the arguments G5:G23 and ‘1’ which indicates that the prices in Column G will be sorted by smallest to largest.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Concluding Words

I hope all of these methods mentioned above will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

How to Sort Multiple Columns in Excel: Knowledge Hub

• How to Sort Two Columns to Match in Excel
• How to Sort Alphabetically with Multiple Columns in Excel
• How to Sort Multiple Columns in Excel Independently of Each Other
• How to Sort Columns in Excel Without Mixing Data

<< Go Back to Sort in Excel | Learn Excel