In the last lesson, we got acquainted with the basics of sorting in Excel, analyzed the basic commands and sort types. This article will focus on custom sorting, i.e. customizable by the user. In addition, we will analyze such a useful option as sorting by cell format, in particular by its color.
Sometimes you may encounter the fact that the standard sorting tools in Excel are not able to sort the data in the required order. Luckily, Excel allows you to create a custom list for your own sort order.
Create a custom sort in Excel
In the example below, we want to sort the data on the worksheet by T-shirt size (column D). Normal sorting will arrange the sizes in alphabetical order, which will not be entirely correct. Let’s create a custom list to sort sizes from smallest to largest.
- Select any cell in the Excel table that you want to sort. In this example, we will select cell D2.
- Click the Data, then press command Sorting.
- A dialog box will open Sorting. Select the column by which you want to sort the table. In this case, we will choose sorting by T-shirt size. Then in the field Order click Custom List.
- A dialog box will appear Lists… Please select NEW LIST in section Lists.
- Enter T-shirt sizes in the field List items in the required order. In our example, we want to sort the sizes from smallest to largest, so we will enter in turn: Small, Medium, Large and X-Large by pressing the key Enter after each element.
- Click the Addto save the new sort order. The list will be added to the section Lists. Make sure it is selected and click OK.
- Dialog window Lists will close. Click OK in the dialog box Sorting in order to perform custom sorting.
- The Excel spreadsheet will be sorted in the required order, in our case, by T-shirt size from smallest to largest.
Sort in Excel by cell format
In addition, you can sort an Excel spreadsheet by cell format rather than content. This sorting is especially useful if you use color coding in certain cells. In our example, we’ll sort the data by cell color to see which orders have uncollected payments.
- Select any cell in the Excel table that you want to sort. In this example, we will select cell E2.
- Click the Data, then press command Sorting.
- A dialog box will open Sorting. Select the column by which you want to sort the table. Then in the field Sorting specify the sort type: Cell Color, Font Color, or Cell Icon. In our example, we will sort the table by column Payment method (column E) and by cell color.
- In the Order select a color to sort. In our case, we will choose a light red color.
- Press OK. The table is now sorted by color, with light red cells at the top. This order allows us to clearly see outstanding orders.
