How to Use Filters in Microsoft Excel

Drawing a filter funnel

When you have an Excel spreadsheet with hundreds or even thousands of rows of data, finding the exact data you need can be challenging. But by using Excel filters, you can locate that data quickly.

You can use a basic filter, which adds buttons to sift through data, an advanced filter to find data that meets various specific conditions, or the FILTER function to display a value if the data isn’t found.

Let’s look at how to use each of these methods to filter data in Microsoft Excel.

How to Use a Basic Autofilter in Excel

The basic Autofilter in Excel is the easiest method and works well with most spreadsheets. Choose the data you want to display, then see your existing dataset update to show only those values.

  1. Select the data you want to filter by dragging through a cell range or selecting entire columns and rows.
Selected cells for a filter in Excel
  1. Go to the “Data” tab and click “Filter” in the “Sort & Filter” section of the ribbon.
Filter on the Data tab in Excel
  1. You’ll see filter buttons added to each column header in your dataset.
Filter buttons in Excel
  1. Select the button for the column you want to filter. In the pop-up window, enter a value in the Search field, or check the boxes at the bottom for those items you want to display, and click “OK.”
Filter Search field and checkboxes in Excel
  1. Your dataset will update to display only the filtered data.
Filtered data in Excel

Use Number, Text, or Date Filters

Using the basic filter in Excel, you can go beyond the basics. Instead of choosing the exact items you want to see, you can add a condition. This works for data that includes numbers, text, and dates. Let’s look at an example of each.

Follow the same steps above to select the data and apply the filter to display the buttons.

Select the filter button for the column you want to use to sort. In the pop-up window, move to Number Filters, Text Filters, or Data Filters.

Number Filters

If you have numbers in your data, choose to filter by amounts that are equal to or not equal to, greater than or less than, above or below average, or even the top 10.

  1. Choose the condition you want to add in the pop-out menu.
Number Filters in Excel
  1. If your selection requires additional input, you’ll see the “Custom Autofilter” box appear. Confirm the condition in the first drop-down box, then add the value in the text box, or use its drop-down list to select one.
Number Filters condition
  1. Optionally, you can add another condition using the AND or OR operator. Use the boxes to set up that condition.
Number Filters optional second condition
  1. When you finish, click “OK” to see your filtered data.
Number filter applied in Excel

Text Filters

Text filters work similarly to number filters in Excel. You can add a condition like “begins with,” “ends with,” “contains,” or “does not contain.”

  1. Choose the condition you want to add in the pop-out menu.
Text Filters in Excel
  1. Enter your condition(s) in the “Custom Autofilter” box. Notice that you can enter a question mark (?) to represent a single character or asterisk (*) for a series of characters.
Text Filters condition
  1. Click “OK” and you’ll see your updated dataset.
Text filter applied in Excel

Date Filters

Like numbers and text, you can add conditions to a basic date filter. Choose a condition like “before,” “after,” “between,” “today,” or “yesterday” as well as “next” or “last week,” “month,” “year,” or “quarter.”

  1. Choose the condition you want to add in the pop-out menu.
Date Filters in Excel
  1. If your selection requires input, enter it into the “Custom Autofilter” box. Optionally, add another condition if you’d like, and click “OK” to apply the date filter.
Date Filters condition
  1. Your dataset will update.
Date filter applied in Excel

Clear or Turn Off Filters

  • To remove the filter later, click the same filter button and choose “Clear Filter From.”
Clear Filter From in the Excel filter menu
  • To turn off the filter and remove the buttons, deselect the “Filter” button in the ribbon.
Deselect Filter in Excel

How to Use an Advanced Filter in Excel

When you need a more precise filter or one that places the filtered data elsewhere rather than updating the existing data, use the advanced filter in Excel.

  1. To use the advanced filter, make sure your data contains column headers or “labels.” In this example, you can see our labels Supervisor, Sector, Department, and Sales.
Labels in a dataset for a  filter in Excel
  1. Insert at least a few rows above your dataset. This is where you’ll enter the criteria for your filter. Enter the same labels as those in your dataset.
Criteria range for an advanced Excel Filter

Enter the Criteria

Enter the conditions for your advanced filter using comparison operators for “equals” or “does not equal,” “greater than” or “less than,” “greater than or equal to,” and “less than or equal to.” Also, place the text or values within quotation marks for a format that looks like this: ="=condition".

Below are some examples:

  • Equal to North: ="=North"
  • Not equal to North: ="<>North"
  • Greater than 500: =">500"
  • Less than 500: ="<500"
  • Greater than or equal to 500: =">=500"
  • Less than or equal to 500: ="<=500"

When you enter the condition, Excel recognizes it and formats it as needed for the filter.

Criteria entered for an Excel advanced filter
  • For a basic example, we are filtering our data by the Supervisor name Brown. In the first row in the Supervisor column, we entered the condition as ="=Brown".
One condition for an advanced filter
  • To meet two conditions in the same column, enter the criteria in the corresponding column in different rows. Here, we are filtering by both Brown and Martin. We entered ="=Brown" in the first row of the Supervisor column and ="=Martin" in the second row of the Supervisor column.
Two conditions for an advanced filter
  • To meet two conditions in different columns, enter the criteria in the corresponding columns and in the same row. We are filtering by both Brown and South. We entered ="=Brown" in the Supervisor column and ="=South" in the Sector column.
Two conditions using AND for an advanced filter
  • To meet any condition, enter the criteria in the corresponding columns but in different rows. We filtered by Brown or Food by entering ="=Brown" in the first row of the Supervisor column and ="=Food" in the second row of the Department column.
Two conditions using OR for an advanced filter
  • To meet all plus any conditions, use a combination of different columns and rows. To filter by Brown and North or Food, we entered ="=Brown" and ="=North" in the first row of the Supervisor and Sector columns, then entered ="=Food" in the second row of the Department column.
Three conditions for an advanced filter

Apply the Filter

After you enter the conditions you want to use, it’s time to put the advanced filter to work.

  1. Go to the “Data” tab and select the “Advanced Filter” button in the ribbon.
Advanced Filter on the Data tab in Excel
  1. When the “Advanced Filter” box opens, set up the filter. Start at the top by choosing where you want to place the filtered data. If you select “Copy to another location,” the “Copy to” field becomes available for you to enter that location.
Advanced Filter location for the copied data
  1. Enter the dataset in the “List range” field. You can also select the cells by dragging your cursor through them, which populates that field.
Advanced Filter List Range
  1. Enter the “Criteria range” in that field. This is the range above your dataset where you added the new rows. Again, you can drag your cursor through the range to populate the field automatically.
Advanced Filter Criteria Range
  1. If you want to locate distinct records, check the box for “Unique records only.” Select “OK” to apply the filter.
Advanced Filter Unique Records Only and OK button
  1. You’ll see the filtered data either in place or your selected location.
Advanced Filter applied in Excel

Tip: if you want to find duplicates rather than unique values, check out our how-to for finding and removing duplicates in Excel.

How to Use the FILTER Function in Excel

One more way to filter in Excel is with the same named function. Using the FILTER function, you can filter by a number, text, or date using its cell reference. Additionally, you can enter a value if the formula doesn’t find the items.

The syntax for the formula is FILTER(range, include, not_found), where only the first two arguments are required.

To get started, we are using a simple example. We are filtering the range A1 through D6 by the range B1 through B6, where the item equals the value in cell B2 (South).

=FILTER(A1:D6,B1:B6=B3)
Filter function formula with a cell reference

You can also use this formula with the item to filter by in quotation marks:

=FILTER(A1:D6,B1:B6="South")
Filter function formula with text

We received two results for our filter using both formulas.

We are now filtering the same ranges and including the optional argument for if no result is found. It’ll display as “None.”

=FILTER(A1:D6,B1:B6="Down","None")
Filter function formula with the not found argument

Use AND or OR with the FILTER Function

If you want to filter by multiple criteria with the function, you can insert an asterisk (*) to meet both conditions (AND) or a plus sign (+) to meet either condition (OR).

We are filtering the same dataset by South and Apparel using this formula:

=FILTER(A1:D6,(B1:B6=B3)*(C1:C6=C3))
Filter function AND formula

We received one result, as expected. Although there are two records with South in the range B1 through B6, only one of them also has Apparel in cells C1 through C6.

To show how the OR operator works for the FILTER function, we are filtering the same dataset by South or Food with this formula:

=FILTER(A1:D6,(B1:B6=B3)+(C1:C6=C6))
Filter function OR formula

This time we received three results: two including South and one including Food.

Frequently Asked Questions

What’s the difference between filtering and sorting?

As you’ve learned here, filtering shows data that matches your conditions and hides the rest. Sorting, on the other hand, rearranges your data in order, such as alphabetical or chronological.

Is there a shortcut to filter in Excel?

To activate the Autofilter in Excel, use the keyboard shortcut Ctrl + Shift + L on Windows or Command + Shift + F on Mac.

Then, choose the column and press Alt + Down arrow on Windows or Option + Down arrow on Mac to open the filter pop-up and make your selection.

How can I filter cells in Excel with formulas?

Select a range of cells or your entire sheet. Go to the “Home” tab and choose “Find & Select -> Go To Special.” In the box, mark “Formulas” and optionally check the items beneath it, then select “OK.” You’ll see the cells containing formulas highlighted.

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

Is this post useful?
Subscribe to our newsletter!

Our latest tutorials delivered straight to your inbox

Sandy Writtenhouse

With her BS in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She wanted to help others learn how technology can enrich business and personal lives and has shared her suggestions and how-tos across thousands of articles.