MVP #23: Knowing when to use Sort or Filter

Once you begin working with a database of some sort in Excel, be it employee records from an ERP, Expense records from SAP or Sales records from a CRM; you will need to do some sort and filter operations.

And in this post, I will show you the trick to knowing when to use sort and when to use filter.

When to Use Sort
You use sort when you want to see a trend across all the entire database. 
Maybe you want to see the sales trend day-wise for all the records. Or you want to see the sales trend by business unit, or you want to see the sales trend by value (biggest sale to smallest sale).

The best way to sort is to select the entire table/database, right click, select Sort, Custom Sort.

Selecting the entire table first, ensures that no field is left out. Better than assuming no field will be left out.
And using Custom Sort gives you more control. It allows you to set sorting hierarchy. You could decide to sort by Year, followed by Month and then by Day (it they are on separate fields). Or sort by Sales region, and then Date. 

I'll recommend that you always use custom sort, even if you want to sort by just a single field.


When to use Filter
Filter lets you hide the entries you don't want to see. And it's mostly used when you want to copy out just a section of the entire database. And when you want to do some analytical check using just a small subset of the database. With filter, you not only see the segment you are interested in, you can run formulas on it without having to copy out the segment. You can decide to see counts of sales in a particular region, or even sum them.

And when used creatively, especially with charts, you can make a dynamic report (sort of a dashboard) with Filter alone.


We have come to the end of today's Excel tip. Don't forget to subscribe to my blog to receive the next tip as soon as I post it.