Microsoft Excel provides you with useful tools to analyze and review your data. You can use filters to find data quickly and conditional formatting to highlight specific data. Another useful tool is Excel’s built-in sorting function.
You can sort the data alphabetically with a simple click. But if you want to sort using a value, a color, or multiple columns, take a look at custom sorting.
Open custom sorting in Excel
The custom sort tool is available in the desktop version of Excel on Windows and Mac. It was added to Excel for the web in mid-2021. It works the same way, no matter what version you’re using. So, open up your spreadsheet in Excel, and let’s get started.
- Select the data you want to sort.
- Go to the tab Beginning and click Sort and filter in the Edit section of the ribbon.
- Please select Custom order in the drop-down list.
This displays the Sort box where you configure the options you want to use.
Use Custom Order in Excel
We will start with a simple custom order using just one level.
- If your data range has headers, check the box in the upper right. This will display those headings in the column sort options.
- Choose the column you want Sort by. Note that all the data in your selection will be sorted, not just that column.
- Select what you want Sort by. You can choose between cell values, cell color, font color, or conditional formatting icon.
- Choose the Sort criterion for classification. This drop-down list will adjust depending on the Sort option you select. For example, if you choose Cell Values, you can choose from A to Z, Z to A, or a Custom List. But if you choose Cell or Font Color, it will pick the color and display those at the top or bottom.
- When done, click To accept to apply sorting to your data.
As you can see here, we have sorted our Shoes column by Cell Color and decided to show those cells at the top.
Use additional Custom Order options
At the top of the sort window you will see additional options for your custom order. If you want to do detailed sorting, these are the tools you will want to use.
To sort using more than one column, complete the Sort by, Sort by, and Sort sections. Then click Add level in the upper left. Now you can add the same options for the next column you want to sort by.
When you use multilevel sorting, Excel sorts the data according to the levels that you have configured in the window. For example, in our setup, Excel will sort by Shoes and then Jackets using a Cell Color for both.
If you keep adding more levels to the classification, you may want to change the order of the levels. Select a level and use the arrows Upload and Go down to sort the ranking levels.
To remove a level from the custom classification, select it and click Remove level.
Another option is to copy an existing level and then make a slight adjustment to it. Select the level and click Copy level. Here, we copy our Jackets level and then decide to sort by Cell Values.
Another way to use the custom order is to adjust the case sensitivity and orientation. Click the button Choices from the toolbar. You can then check the box to Be case sensitive and choose between Sort from top to bottom or Sort from left to right. Click on To accept to apply the options to the custom order.
Sort your data in Excel
By sorting the data, you can more easily find the exact data you are looking for. And you may want to provide this neat view to other people who are reviewing your spreadsheet.