Excel Filter Introduction

excel filter demo

Your worksheet may contain lots of data and sometimes it’s only painful if you require to find/go through only a few rows out of hundreds.

Thanks to Excel built-in functionality, you may filter data and return only desired rows or columns by certain criteria.

For example, you want to get only rows where the country is the USA.

In this tutorial, you will learn how to apply the filter for getting the subset of data in various versions of MS Excel.

How to filter data in Excel?

Follow these steps to filter the data in Excel:

Step 1:

Select any cell in the worksheet/data set that you want to filter.

Excel filter select

Step 2:

Go to “Data” tab in the ribbon

Find the “Sort and Filter” group and press “Filter” as shown below

Excel filter

Step 3:

You should see a dropdown arrow in the first row that contains the headers.

Step 4:

As you press any dropdown, you will see unique values there based on the data in that column.

In our example sheet, we have products worksheet and a header with the two possible values:

In Stock or Out of Stock

You can see below, upon clicking the dropdown, it gives us the option to filter the results based on the “In Stock” or “Out of Stock” values.

Excel filter dropdown

As I checked the “Out of Stock” to see which products are finished, and press the “Ok” button, it shows only those products that are Out of Stock.

Excel filtered data

How to remove that filter?

Don’t worry, your actual data is in place while viewing at the subset of data. In order to bring the whole data back, just do this:

Go to “Data” –> “Sort & Filter

There, press the “Clear” button and filter should be removed.

In order to remove the dropdown arrows in the column headers, press the “Filter” button as well.

Tip: You may also press the “Filter” button straightaway for removing the filter and dropdowns too.

Filtering the data based on numbers

Excel offers plenty of options to filter data based on the type of content in the cell as well. For example, we have a “Price” column and want to filter the data and get those records which price is greater than $2. This is how you can do it:

Step 1:

Select any cell in the range of cells or table.

Step 2:

Go to “Data” –> “Sort & Filter” and press “Filter”.

Step 3:

Click on the Number column dropdown (Price for our example), “Number Filters” and there you can see many options. For example,

  • Equals
  • Does Not Equal
  • Greater Than
  • Greater Than Or Equal To
  • Less Than
  • Less Than Or Equal To
  • Between
  • Top 10
  • Above Average
  • Below Average

Excel filtered Numbers

As I selected the “Greater Than” option and entered the value 2:

Excel filtered Numbers 2

The result of the data is shown below:

Excel filtered Numbers data

Similarly, you may apply other filters like narrowing down the data on the basis of the price which value is greater than or equal to $5.

Similarly, for the Sales sheet, showing only those records which number of sales exceeds 50 and so on.

An example filtering text column

Just like numbers, you may narrow down the data based on the text columns. The “Filter” pop up gives the following options for text filtration:

Excel textfilter

  • Equals
  • Does Not Equal
  • Begins With
  • Ends With
  • Contains
  • Does Not Contain

For the example, I am going with “Begins With” option and entered the letter “T”:

Excel text filter begi

And this is the result:

Excel begin with

The Date Filters

Just like the text and number filter options, Excel gives lots of options for filtering the data based on date columns. So, if you have a date column and choose to Filter the data by “Data”, “Sort & Filter” and selecting the date column dropdown, you can see the following options:

Excel date filter

Just select the appropriate option and filter the data on the basis of the date column.