Last month we covered the basics of sorting a list in Excel. Now let’s look at some more advanced options including case sensitive sorting, sorting from left to right instead of top to bottom, sorting by color, and creating custom sort orders.
After you have selected the data you want to sort, click the Sort & Filter button from the Home tab of the ribbon and choose Custom Sort, or go to the Data tab and click the Sort button. In the sort window, click the Options button to find setting for two options.
The first option is Case Sensitive. By default it is turned off, so “ABC” and “abc” will be considered the same for sorting purposes. Check the box and lower case letters will be sorted before capital letters, so “abc” would come before “ABC”.
The second option is Orientation. The default setting is Sort to to bottom. In other words, it will sort by row. But you can change it to Sort left to right. That allows you to sort your columns instead of rows.
To sort by color, first choose your sort field in the Sort By drop down list. Then at Sort On choose Cell Color to sort based on the background color of the cells or Font Color to sort based on the color of the text. At Order select the color you want to be sorted first in your list and set it to On Top. Now click the Add Level button and repeat that process choosing the next desired color until you have added lines for all of the colors in your list. Click OK to sort your list.
The other choice in Sort By, “Cell Icon”, works the same way as sorting by color if you have used conditional formatting to apply icons to cells.
Custom sort orders are useful when you need to sort some way other than alphabetically or numerically, such as months or days of the week. In fact Excel has built in custom sort orders for those. For our example lets say you have four sales regions, North, South, East, and West. If you sort your list by region, normally they would be sorted alphabetically: East, North, South, West. But if you want them in a different order, when setting up your sort, leave Sort On set to Values and change Order to Custom List. The Custom List window will open so you can choose a list that has been previously created. To make a new one, select NEW LIST. Type your list into the List Entries section. Put each entry on a separate line by pressing the Enter key after each one. When you’re done click the Add button to add your new list to the list of custom lists. Then select your new list and click OK to apply your custom sort order.
