Excel Data Filter feature gives you many ways to filter the data – based on text, value or date. 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
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: 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 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).
Filter Cells with Bold Font Formatting. Dynamic Excel Filter – Filters Data as you Type.
