How to Use Filters in Microsoft Excel

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.

2. Go to the “Data” tab and click “Filter” in the “Sort & Filter” section of the ribbon.

3. You’ll see filter buttons added to each column header in your dataset.

4. 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.”

5. Your dataset will update to display only the filtered data.

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.

2. 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.

3. Optionally, you can add another condition using the AND or OR operator. Use the boxes to set up that condition.

4. When you finish, click “OK” to see your filtered data.

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.

2. 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.

3. Click “OK” and you’ll see your updated dataset.

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.

2. 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.

3. Your dataset will update.

Clear or Turn Off Filters

• To remove the filter later, click the same filter button and choose “Clear Filter From.”

• To turn off the filter and remove the buttons, deselect the “Filter” button in the ribbon.

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.

2. 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.

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.

• 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".

• 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.

• 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.

• 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.

• 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.

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.

2. 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.

3. Enter the dataset in the “List range” field. You can also select the cells by dragging your cursor through them, which populates that field.

4. 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.

5. If you want to locate distinct records, check the box for “Unique records only.” Select “OK” to apply the filter.

6. You’ll see the filtered data either in place or your selected location.

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)

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

=FILTER(A1:D6,B1:B6="South")

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")

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))

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))

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

Frequently Asked Questions

Image credit: Pixabay. All screenshots by Sandy Writtenhouse.

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.