You can sort your data by more than alphabetical order. Check out the custom sort in Excel to sort by multiple columns or using colors.
Microsoft Excel provides you with helpful tools for analyzing and reviewing your data. You can use filters to find data quickly and conditional formatting to make specific data pop. Another handy tool is the sort feature built into Excel.
You can sort data alphabetically with a simple click. But if you want to sort using a value, color, or multiple columns, check out the custom sort.
Open the Custom Sort in Excel
The custom sort tool is available in the desktop version of Excel on Windows and Mac. And it was added to Excel for the web in mid-2021. It works the same way, no matter which version you’re using. So, open your spreadsheet in Excel, and let’s get started.
- Select the data you want to sort.
- Go to the Home tab and click Sort & Filter in the Editing section of the ribbon.
- Pick Custom Sort from the drop-down list.
This displays the Sort box where you set up the options you want to use.
Use the Custom Sort in Excel
We’ll start with a simple custom sort using just one level.
- If your data range has headers, check the box on the top right. This then displays those headers in the Column sorting options.
- Choose the column you want to Sort By. Keep in mind that all data in your selection will be sorted, not just that column.
- Select what you want to Sort On. You can choose from Cell Values, Cell Color, Font Color, or Conditional Formatting icon.
- Pick the Order for the sort. This drop-down list will adjust depending on the Sort On option you select. For instance, if you pick Cell Values, you can choose A to Z, Z to A, or a Custom List. But if you choose a Cell or Font Color, you’ll pick the color and display those to the top or bottom.
- When you finish, click OK to apply the sort to your data.
As you can see here, we sorted our Shoes column by Cell Color and decided to display those cells at the top.
Use Additional Custom Sort Options
You’ll notice additional options for your custom sort at the top of the Sort window. If you want to perform a detailed sort, these are the tools you’ll want to use.
Multiple Levels
To sort using more than one column, complete the Sort By, Sort On, and Order sections. Then, click Add Level on the top left. Now you can add the same options for the next column you want to sort by.
When you use a multilevel sort, Excel sorts the data according to the levels you set up in the window. For example, in our setup, Excel will sort by Shoes and then Jackets using a Cell Color for both.
If you continue to add more levels to the sort, you may want to change the order of the levels. Select a level and use the Move Up and Move Down arrows to arrange the sort levels.
To remove a level from the custom sort, select it and click Delete 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 copied our Jackets level and then decided to sort by Cell Values.
More Options
One more way to use the custom sort is to adjust case-sensitivity and orientation. Click the Options button in the toolbar. You can then check the box for Case sensitive and choose between Sort top to bottom or Sort left to right. Click OK to apply the options to the custom sort.
Sort to Spot Your Data in Excel
By sorting your data, you’ll be able to spot the exact data you’re looking for easier. And you may want to provide this sorted view to others reviewing your spreadsheet.
For more, take a look at these helpful Microsoft Excel tips.
Microsoft Excel, Productivity
This article teaches you how to reverse the order of a list in Excel. For example, we want to reverse the list in column A below.
1. Enter the value 1 into cell B1 and the value 2 into cell B2.
2. Select the range B1:B2, click the lower right corner of this range, and drag it down to cell B8.
3. Click any number in the list in column B.
4. To sort in descending order, on the Data tab, in the Sort & Filter group, click ZA.
Result. Not only the list in column B, but also the list in column A has been reversed.
If you have Excel 365 or Excel 2021, use SEQUENCE, SORTBY and ROWS to sort a list in reverse order. The following formula is pretty awesome.
5. First, use the SEQUENCE function to generate a list of numbers. The SEQUENCE function below has 4 arguments. Rows = 8, Columns = 1, Start = 1, Step = 1.
Note: the SEQUENCE function, entered into cell B1, fills multiple cells. Wow! This behavior in Excel 365/2021 is called spilling.
6. The SORTBY function sorts a range based on the values in a corresponding range. Use -1 (third argument) to sort in descending order.
7. Nest the SEQUENCE function inside the SORTBY function.
8. If you have a longer list of say 20 names, change the value 8 to 20 in the formula shown above, or even better, use the ROWS function.
Note: the ROWS function simply counts the number of rows in a range.