Excel offers powerful sorting tools for advanced data organization. It allows you to sort columns in ascending or descending order based on alphabets, numbers, colors, and more. You can do it using the built-in Sort Tool and SORTBY function.
Sorting a single column by alphabets or numbers is pretty straightforward.
But things get complicated when you wish to sort complex datasets with many columns.
Let’s quickly discuss how to sort by multiple columns in Excel in this article. It will be your go-to resource for data organization using Microsoft Excel, so read until the end.
Here are the two methods we are about to discuss. You can click on the links to jump to that particular section.
How do you sort by Multiple Columns using the Sort Tool in Microsoft Excel?
The Sort tool allows you to sort the datasets by any number of columns.
It is one of the most preferred methods when it comes to sorting datasets by multiple columns in Excel. It gets the job done quickly and accurately without much effort.
Here’s how to use the Sort tool,
- Open the Excel spreadsheet
- Go to the sheet containing the table that needs to be sorted
- Choose any cell of the table and press “CTRL + A” on your keyboard to select the entire table
- Now, hover over the main menu and click on the “Data” tab
- Choose the “Sort” option from the list
- A new popup will appear on your screen
- Make sure to tick the box before the “My data has headers” option
- Next, select the first column to be sorted by clicking on the dropdown beside the “Sort by” option. Refer to the following image,
- Keep the selection to “Cell Values” in the Sort On section
- The City of each employee needs to be sorted in ascending order, so keep the selection “A to Z” in the Order section
- Now, click on the “Add Level” button in the upper-rightmost corner of the popup
- A set of options will appear as shown below,
- Follow the previous few steps to add the next column that needs to be sorted
(Here, we are sorting the Department column in descending order) - Finally, once done, click the “OK” button
You can sort any number of columns using this method. Make sure to prioritize the columns while sorting properly.
Most of the time, the Sort tool will automatically detect table range and header, so you don’t need to worry about errors. But let’s quickly check the selected table range specifically while dealing with huge datasets.
How do you sort by Multiple Columns using the SORTBY function in Google Sheets?
The SORTBY function is used to create a dynamic table that auto-updates whenever you change something in the main table. You can sort any number of columns using this function.
It is a simple array formula. The general syntax for the Sort function is as follows,
=SORTBY(array,by_array,[sort_order],[array/order],…)
Here is a quick explanation for each argument,
- “array” – Here, you need to provide the table range that needs to be sorted
- “by_array” – This argument needs to be replaced with the first column range that you wish to sort in either ascending or descending order
- “sort_order” – It allows you to sort selected columns in ascending or descending order. If you type “1,” it will sort data in ascending order. Similarly, if you put “-1,” then it will sort the data in descending order
Let’s learn to use the function to sort by multiple columns in Google Sheets.
Step #1 – Create A New Table
To use this function, you must create a new table in the same spreadsheet or a separate one.
We have come up with the following,
Make sure to use the same header sequence for the table.
Step #2 – Sort the data by Multiple Columns
It is the main step in using the SORTBY function to sort the dataset.
- Click on the cell below the first header, “City“
- Type “=SORTBY”
- Select the first option from the popup or press the “Tab” on your keyboard
- Now, select the table range without headers, as shown below
- Press “,” on your keyboard to move to the next argument
- Enter the first column range as “A3:A14” in the place of the by_array1 argument
- Press “,“
- Next, let us type “1” as we are planning to sort the City column in ascending order
- Press “,” on your keyboard
- Now, for the next column, we need to replace the by_array2 argument with the range “C3:C14“
- Press “,“
- Type “-1” to sort the next column named as Department in descending order
- Close the brackets by typing in “)“
- Press “Enter” on your keyboard
It will instantly create a new table, which is sorted by City in ascending order in the first column and Department in descending order in the second column.
PRECAUTION: The most common mistake while using the SORTBY Function
You must keep all the cells of the new table empty while using the Sort function.
Otherwise, it will return the error “#SPILL!,” as shown in the following image.
To Summarize: Sort By Multiple Columns In Excel
Sorting multiple columns using the Sort tool is easy as you have to deal with the visual interface rather than typing in the formula manually, as in the case of the SORTBY function.
However, if you wish to create a dynamic table that auto-updates every time you make changes in the main table, we suggest you go for the Sort function.
I hope this article helped you learn to sort data by multiple columns in Excel.
Feel free to comment below if you have any doubts or are stuck somewhere while using either the Sort tool or the SORTBY Function. Our team will answer all of your questions as soon as possible.