Filters in Excel

What are the Filters in Excel?

Filters in excel can be used to filter down the data in your worksheet and hide the rest of the data from a view. This also works as the grouping of data. filters can be used from the sort and filter option from the editing section in the Home tab or we can use the keyboard shortcut using CTRL + SHIFT + L, to use filters we need to select the header of the table and use any one of the options above.

How to use Data Filters in Excel? (with Examples)

You can download this DATA FILTERS Excel Template here – DATA FILTERS Excel Template

Example #1

Let’s consider a data set of sales from different countries.

DATA FILTERS in Excel Example 1

Follow the steps to add a filter in excel –

  • Step 1: Select any cell from the dataset.
  • Step 2: Click on the Data tab in the Sort & Filter group.
  • Step 3: Then Click Filter to apply the filter in the given data set.
  • Step 4: Click the arrow next to Country and filter the given data set by country India.

DATA FILTERS in Excel Example 1-1

And the output is as follows:

DATA FILTERS in Excel Example 1-2

Example #2

In the second example, we apply the filter to the quarter column in the above ‘sales’ data set.

DATA FILTERS in Excel Example 2

After applying the filter of the quarter, we get the below data.

DATA FILTERS in Excel Example 2-1

Example #3

In the third example, we apply the filter of numbers of data sets.

Let’s consider the below-given data set and apply the filters on the basis of requirements.

DATA FILTERS in Excel Example 3

There are the following Number filter conditions available in the data filters, as shown in the below tables.

Conditions can be:-

= Equals

<> Not equal to

> greater than

>= greater than or equal to

< less than
<= less than or equal to

between

DATA FILTERS in Excel Example 3-1

Now apply the custom auto filter on the condition is greater than 150.

DATA FILTERS in Excel Example 3-2

After applying the sorting on Oct month is greater than 150, the output will be as follows:

Example 3-3

Example #4

Let’s apply the filter on the data set of Names that have ‘a’ in the name.

Example 4

Now apply the custom auto filter on the condition it contains ‘a.’

Example 4-1

The output will be:-

Example 4-2

Things to Remember

  • You cannot apply the filter on two datasets in a single excel sheet.
  • You cannot perform any calculations or data manipulation in the filter out data.

Recommended Articles

This has been a guide to Filters in excel. Here we discuss how to use Data Filters in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>