Wherever there is data, there is a need to filter it.
Excel Data Filter feature gives you many ways to filter the data – based on text, value or date.
Excel Data Filter
Suppose you have the data as shown below:
To activate Excel data filter for this data, select the entire data and click on the Filter button in the Data tab in Excel ribbon (keyboard shortcut – Control + Shift + L)
Once you apply filter to the data, you will see a filter icon in each of the header cells of your data 
When you click on this filter icon, you can access all the filter and sorting options available for that column of the data. There are three kinds of data filtering that can be applied:
- Based in Text Data
- Based on Numbers
- Based on Dates
Filtering based on Text Data
This kind of filter would be applicable for the first two columns of this data set (Sales Rep Name and Quarter), as it has text data in it. Once you have applied the filter, click on the filter icon at the right of the header cell.
When you click on the filter icon, it displays the drop down as shown below:
Since Excel has identified that the column has text data, it automatically displays the option for text filters. You can either type the text in the Search bar, check/un-check names, or use the in-built text filter conditions.
Suppose you want to filter all the Sales Rep Names whose name begin with the alphabet “A”. To do this, select the Begins With condition in Text Filter conditions:
Filtering based on Numbers
This filter can be applied on the last column in out data set (for sales values). Once you have applied the filter, click on the filter icon at the right of the header cell.
When you click on the filter icon, it displays the drop down as shown below:
Since Excel has identified that the column has numbers, it automatically displays the option for Number filters. You can either type the number in the Search bar, check/un-check number values, or use the in-built text filter conditions.
Suppose you want to filter all the sales value above 3000. To do this, select the Greater than option from the Number Filters list
Filtering Based on Dates
This filter can be applied on the second last column in out data set (for date). Once you have applied the filter, click on the filter icon at the right of the header cell.
When you click on the filter icon, it displays the drop down as shown below:
Date filter lets you filter dates based on current date. For example, you can filter on conditions such as today, tomorrow, or yesterday. Similarly, you can can filter on current/next/previous month, quarter, or year.
To remove filters from your data set, go to data tab and again click on the filter icon (or use keyboard shortcut – Control + Shift + L).
Note:
- Excel determines which type of filter to apply based in the data set. If there are more text values than numbers, then text filter is applied, else number filter is applied.
- In case you need more functionality in filtering data, use Exel Advanced Filter.
- Apart from filtering, you can also access all the sorting criteria after applying filter (Learn all about data sorting in Excel).
1 thought on “An Introduction to Excel Data Filter Options”
I was very easy and practical to understand.