Data filters are essential for reporting and decision making. In Excel, you will get filter arrows by default when you create a new table. In this guide, we're going to show you tips about how to filter Excel Tables.
How to add filters
Excel shows filter controls (arrows) when you create a table by default. Alternatively, you can add or remove filters from the Design or Data tabs in the Ribbon. Let's begin with the Design tab properties.
- Click on a cell in your table
- Open the Design tab
- Check / uncheck the Filter Button option in the Table Style Options section
The alternative to this method is using the Data tab for filtering options.
- Click on a cell in your table
- Open the Data tab
- Click the Filter button to display or hide filter arrows
How to use filters
Begin by clicking the filter arrow on a column you want to filter. A dialog with sorting and filtering options will be displayed.
Excel will automatically detect the structure of your data and make smart suggestions. You will get an option specific to the active column's data type in addition to the default options.
For example, in the screenshot above, there are 4 items for filtering a text column:
Clear Filter From "Generation"
Clears the filtering and lists all items for the active column. This is the default option for all data types. Label includes the column name as well.
Filter by Color
This option allows filtering based on the background color of the cell. This is the default option for all data types.
Text Filters
The "Text" here indicates that this column contains text values. This option is contextual, and depends on the data type, "Number" for numbers, "Date" for date values etc.
Item List
Item list displays each item in the column to allow filtering out specific items. Although this section is available for every data type as well, its type will depend on the data type of the column. If you use date values, for example, the dates will be grouped by years and months.
Tips
You can filter more than one column at a same time. Excel displays items that meet every filtering condition.
You can use the Clear button under the Data tab to clear all filters with a single click.
