Sorting data in Excel is quite a common task that we need to do. Sorting can be done based on several criteria. You can sort either single or multiple columns as well as rows in Excel. In this article, you will learn 4 ways to sort multiple columns in Excel with ease.
Quick Way to Sort Multiple Columns in Excel
To sort multiple columns in Excel,
- Select the whole data table.
- Press CTRL + SHIFT + L to apply Filter.
- Now click on the drop-down icon of the column headers and sort them.
Alternate Way #1: Use the Custom Sort Command to Sort Multiple Columns in Excel without Mixing Data
The quickest and easiest way to sort multiple columns in Excel is to use the Custom Sort command.
Now follow the steps below to learn the whole process.
Step_1: Select the entire table first.
Step_2: Then go to the Home tab.
Step_3: From the Edit group, click Sort & Filter.
Step_4: From the drop-down list, choose the Custom Sort command.
Step_5: Then the Sort dialog box will appear. From the Sort by drop-down (under the Column section), select the first column name. For this instance, I’m picking the Book column.
Step_5: After that choose your preferred order from the Order drop-down list. I’m choosing A to Z here.
Step_6: Click on the My data has headers command (right above the Order section) if your data table has a header.
Step_7: To apply the sorting criteria to another column, click on the Add Level command.
Step_8: Pick another column name from the Then by drop-down and change its order from the Order drop-down. You can customize your data list from the Custom List command inside the Order drop-down.
Step_9: Finally hit the OK button to apply changes.
After completing all the steps above, you will see your data table is sorted as per your customization like the image below:
Alternate Way #2: Use the Sort Command to Sort Multiple Columns in Excel
There is another technique that you can use which will lead you to the same Sort dialog box to sort multiple columns. This is how you will do it:
Step_1: Select the whole table.
Step_2: Then click the Data tab.
Step_3: Click on the Sort command from the Sort & Filter group.
Step_4: The Sort dialog box will appear then.
Step_5: Before bringing any changes, make sure that the My data has headers command is checked.
Step_6: Then click on the Sort by drop-down from the Column group and select the first column name you want to sort. In this case, I am choosing the Delivery column.
Step_7: Next, change the order from the Order column. I am choosing alphabetically A to Z order here.
Step_8: Click on Add Level if you want to sort another column.
Step_9: Choose another column to sort from the Then by drop-down.
Step_10: Then, change the Order of it. Hit OK to save the changes.
Note that, apart from sorting by the Cell values, you can also sort your table by using the Sort on command. Such as, by changing its Cell color, Font color, etc. (if your table has multiple colors in it).
Alternate Way #3: Use the Filter Command to Sort Multiple Columns in Excel
Step_1: Select the full row of the headers of the table. Now click on the Home tab.
Step_2: Hit click on the Sort & Filter drop-down located in the Editing group of commands and hit Filter.
Step_3: You will see small arrows of the Filter command will show up beside each header.
Step_4: Then select the first header from the column list. I picked the price column and sorted it by the Largest to the Smallest arrangement.
After sorting the price column, the column will appear with an arrangement like the image below:
Then again choose the next header and sort it as you like. By using this technique, you can sort the data from multiple columns.
- How to Sort in Excel by Date (3 Ways)
- How to Sort by Number in Excel (4 Techniques)
- How to Sort in Excel by Name (3 Techniques)
Alternate Way #4: Use the Conditional Formatting to Sort Multiple Columns in Excel
Another technique for sorting multiple columns is using Conditional Formatting where the columns will be highlighted with different colors or symbols. Don’t worry, the orders of your data will not change. The colors of the data will only point out different values of the data.
Note that, this command is only for numerical sorting arrangements (such as price, size, salary, date, etc.).
Step_1: Select the first entire column (of course with numerical values) from the table.
Step_2: Then hit the Home tab from the Excel ribbon, and click on the Conditional Formatting drop-down, where you will find the Color Scales drop-down.
Step_3: Now choose any of the color scales you like. I have selected the White-Red color scale for the Price column. The white color denotes the lowest price on the list, and the red color means the highest price.
Step_4: Next choose another column to sort. For example, I have selected the Delivery Date column as it contains numbers.
Step_5: Then again select the Color Scales command from the Conditional Formatting drop-down.
Step_6: This column doesn’t need to be of the same color scale as the previous column. You can pick any of the color styles you want. I have selected the Green-Yellow color scale where the yellow cells indicate the older dates and the deep green cells indicate the latest dates.
The resulting table looks like the image below:
How to Sort Rows in Excel
Most of the time when we sort data in Microsoft Excel, we sort the data by columns. But you can sort your data by rows also, just like you sort the columns. Sometimes you will need to sort the spreadsheet with rows in some cases.
Follow the steps below to sort data in rows in an Excel sheet:
Step_1: Select the entire table.
Step_2: Then click the Data tab from the ribbon.
Step_3: Then select the Sort command from the Sort & Filter group.
Step_4: You will see the Sort dialog box will pop up. Click on the Options button.
Step_5: Then change the orientation by selecting Sort Left to Right command.
Step_6: Then press OK.
Step_7: Now select your preferred row from the Sort by drop-down. They won’t show their names, so you will need to count them. For instance, I want to sort the Total number of assignments so I picked row 9.
Step_8: Then change its order from the Order drop-down. I gave it a Largest to Smallest order.
Step_9: Click OK to save the changes.
And it is done! The selected row will sort by the desired order like the image below, where I sorted the number of assignments of different subjects from the largest to smallest order:
I really hope you got a clear concept of how to sort single and multiple columns in an Excel worksheet. Following any of these techniques will help you to sort the data on your Excel sheet. But in case you have any confusion about any part of this article and face any problem sorting the columns, please feel free to mention it in the comment section.