Sort and Filter | Chapter 1 | Custom sort

Sort & Filter

Meaning of Sort in Excel.

Rearrangement of rows on the basis of a content of a particular column.

Sort & Filter

Located under Home Tab 
By clicking the Sort & Filter Drop down arrow

Sort type

We are going to discuss with a data sheet. If required one can download here.

DATA Sheet

There are immense opportunities for filtering and sorting. 

Excel automatically understands the sorting opportunity accordingly.
For instance when the active cell is under Date column the sorting field will change from 'Sort A to Z' or 'Sort Z to A' to 'Sort Oldest to Newest' or 'Sort Newest to Oldest'.


By default it is 'Sort A to Z' or 'Sort Z to A'.

How to sort?

• Active any cell of the column on the basis of which you want to sort.
• Click 'Sort & Filter'.
• Sort A to Z or lowest to highest or newest to highest accordingly.

Excel effectively understands column headers. Excel won't include column headers such as Order Date, Region, Rep, Item etc as the item for sort.
Now if we delete the header row excel will sort the entire data as it cannot identify any header row as such.
Again in the above data sheet if we select the entire data (including header row) excel will sort according to date, since it is the first column in the data sheet. 


If we consider each row of the data as a 'Data Record' Excel will sort each data record on the basis of your choice. You can try this by coloring a data record with a specific color of your choice.

Custom Sort

Custom sort is interesting and I always prefer one to go with custom sort. May be it is complex.

Custom Sort window

In the case of the above data sheet Excel automatically finds the header row as you can see 'My data has headers' check box is checked. If there no header row then excel will sort column by column name say 'A', 'B' etc. Even one can de check the associated check box of 'My data has headers' and perform sort by column name.

Add Level is very interesting options in custom sort. Say, you want to first sort the data sheet by date (say Oldest to newest) and then by Region (say A to Z).

The sort window is somewhat as follows.

Click Ok
You can also shift levels up or down in multi column sort. 

Custom List

Custom List is another very important aspect of Custom Sort. 

For instance, in the case of the concerned data sheet if we want to sort the 'Item' in the order of Desk, Pen, Pencil, Pen Set, Binder.

That means neither A to Z nor Z to A, just an order of our own.

Under Order there is an option called Custom List.

Here we can find other orders that Excel Follows. Default lists cannot be deleted.

Click Custom List

 

Write in the 'List Entries Text Box'
"Desk, Pen, Pencil, Pen Set, Binder" 
Alternatively first write Desk and press Enter then write Pen and press Enter and so on. As it is said 'Press Enter to separate list entries' in the window itself.
Click Add and the new series will be shifted to the left under Custom List window.
Then Click Ok.
This custom series will be highlighted in the custom sort window 'Order'.
This list will remain in the excel unless one deletes the same.