An Introduction to Excel Data Filter Options

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:
Excel Data Filter - Data Set

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)
Excel Data Filter - Button In Excel Ribbon

Once you apply filter to the data, you will see a filter icon in each of the header cells of your data Filter Icon in Excel

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:
Excel Data Filter - Based on Text

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:
Excel Data Filter - Text Filters

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:
Excel Data Filter - Based on NumbersSince 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
Excel Data Filter - Number Filter

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:
Excel Data Filter - Based on DatesDate 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).
Related Tutorials on Data Filtering:
  • natalie says:

    I was very easy and practical to understand.

  • >