Using Filters in Excel

Filters in Excel are used to extract or look at a part of data from a table that meets a set of criteria. It is a convenient way to look at partial data within a large data-set.

Using Filters in Excel

Filters in Excel are used to extract or look at a part of data from a table that meets a set of criteria. It is a convenient way to look at partial data within a large data-set.

A desk with a keyboard, phone, and a chart

To apply filters in Excel, select the top row of a table and then clicking on Filter in the Data Ribbon.

Select the column headers and click on Filter in the Data Tab to apply filters

On this page, we will look at the following areas:

Filter Unique Values

The most commonly used method to filter data is clicking on the drop-down next to the column name and selecting the value(s) for which you want to see the data.

In the example below, if we want to filter on records of all those who are joining the webinar after learning about it from Ads then we can click on the drop-down on the column containing Source data and select Ads as the option.

Selecting one value for a column to filter data

The output is as below. Only records where the Source is Ads are displayed.

To determine which field or fields on which data is filtered, you can look at the filter or funnel-shaped icon next to the column(s) name. In this case you see the funnel-shaped icon next to Source column.

Filtered data basis the selection of a particular value in a column

Text Filters

Another way to filter data is using the Text Filters option. Click on the drop-down next to the column and bring your mouse cursor on Text Filters.

Select from the options that suit your needs the best. The Custom Filter option allows you to use multiple options to filter data.

In the above image we can see that we have clicked on the drop-down button corresponding to the Name column and selected Custom Filter… option from the Text Filters menu.

Once you click on the Custom Filter a dialog box as shown below appears:

Applying Custom Filter - Filtering data where the values in Column Name begin with Al

This gives us a chance to select our filter criteria, which is name ‘begins with’ ‘Al’. You could even give an option by selecting the ‘Or’ option and providing an additional criterion to filter.

The above filter option generates the following output. You will notice that the three records that are displayed have the name beginning with 'Al' as per our criteria.

Filtered data displaying all names starting with Al

Filter By Color

Sometimes we have data in a table where some of the rows have been colored and we want to filter basis color.

Excel has a provision to filter data based on color. In the example below all the rows where the Name cell is in Yellow color will be filtered.

The output of the above filter by color is as below:

All rows where the value of cells in Column Name are in yellow are displayed

Clear Filters

To remove a filter, you can click on the drop-down corresponding to the column where the filter has been applied and click on the 'Clear Filter From…' option as illustrated in the image below:

Alternatively, you can click on Clear in the Sort & Filter Group in the Data Tab.

Clear Filters by clicking on Clear in he Sort & Filter in the Data Tab

All Excel & Google Sheets Resources

Using Filters in Excel

Using Filters in Excel

Text Filters

Filter By Color

Clear Filters

CONTENT

COMPANY

LEGAL

CONTENT

COMPANY

LEGAL