How to Add a Filter in Excel to Display Relevant Data

If you work with large volumes of data, you need to learn how to filter Excel data so you can focus on the most relevant information.

Think about it.

If you have a data list that contains thousands of records but you need to focus on a very specific subset of data consisting of a few dozen records, then you need a way to pare down your list to a level that’s more manageable.

That’s where the ability to filter data in Excel becomes an incredibly useful skill.

See that guy in the photograph? He just learned how to filter in Excel. By the end of this article, you’ll know how to use filters in Excel too!

Filters Work with Data Lists and Tables

Before we go any further, you should know that filters work with data lists and tables. If you’re not sure what a data list is, take a look at the screenshot below.

Information in a data list is organized into rows and columns.

The top row in a data list contains column headings (field names). Each column represents a field, each row represents a record. Information arranged this way constitutes a data list (or database) in Excel.

An Excel table is very similar to a data list – with a lot of the sorting and filtering capabilities already “baked in”. Tables usually have special formatting applied that make them easier to work with.

When it comes to filtering, the process is nearly identical whether you’re working with a data list or table.

How to Enable Filters in Excel

Turning on a filter in Excel couldn’t be easier. You select a cell in your data list then click the Filter button in the Data > Sort & Filter group on the ribbon.

If you prefer keyboard shortcuts, pressing Ctrl + Shift + L will toggle the filter on (and off).

Look for the Drop-Down Arrow!

Once filtering is enabled, each column heading will have a drop-down arrow next to it.

How to Filter Data in Excel /w 3 Examples

Now that we know how to enable filters in Excel, how do you apply them? You click the drop-down arrow for the column you want to filter – then enter criteria to limit the display of data to records you’re interested in.

Criteria are the conditions that you specify to pare down the records in a data list.

Let’s look at a few examples.

Example #1: Use a Check Box to Filter Using an Exact Match

If I want to display a specific set of records in a data list, one option is to use the check boxes that are accessible via the drop-down menu next to each column header to filter for an exact match.

In the screenshot below, if I wanted to display employees that work in the Operations department, I would do the following.

1. Click the drop-down arrow next to the Dept column heading.
2. In the menu that appears, deselect the check box next to Select All, then click the checkbox next to Operations and click OK.

An exact match filter is applied to the list and only employees in the Operations department remain. The funnel symbol on the drop-down menu next to Dept indicates that the list is filtered by that column.

Example #2: Use a Text Filter to Focus on a Subset of Records

If I want to filter data so my list only includes specific names, I can use a text filter to specify the criteria Excel should use.

Here is how to limit entries in the name column to employees whose last names begin with the letter B.

1. Click the drop-down arrow next to the Name column heading.
2. In the menu that appears, point to Text Filters then click Begins With… from the second panel.
   The Customized Autofilter dialog box appears.
3. Type B next to begins with in the Customized Autofilter dialog box and click OK.

The data list is restricted to Names that begin with B. The funnel symbol on the drop-down menu next to Name indicates that the list is filtered by that column.

Example #3: Use Number Filters to Focus on a Subset of Records

If I want to focus on a subset of my data list based on a specific salary range, I would use number filters.

Here is how to use number filters to limit the data list to people who earn $80,000 – $90,000 per year.

1. Click the drop-down arrow next to the Salary column heading.
2. In the menu that appears, point to Number Filters then click Between… from the second panel.
3. The Customized Autofilter dialog box appears.
4. Type 80000 next to is greater than or equal to, then type 90000 next to is less than or equal to and click OK.

Here is the list after applying number filters. All of the salaries are in the $80,000 – $90,000 per year range.

How to Remove a Filter in Excel

Adding a filter to your data list is easy but how do you remove filters in Excel?

It depends what you want to do.

You can clear any criteria you’ve used to filter data or you can the Excel filter off altogether.

• If you want to clear the criteria for a specific column, click the drop-down arrow next to the column heading and select Clear Filter From “Column Name“. The clear filter command will reference the column you selected.
• To clear all of the criteria for your current data list, click the Clear button in the Data > Sort & Filter group.
• To turn filtering off, click the Filter button in the Data > Sort & Filter group.

What’s Next?

If you’ve found this article useful, you might be interested in learning how to filter Excel data faster with these 15 keyboard shortcuts.

These shortcuts will help you manipulate more data in less time… you’ll be the “office Excel guru” before you know it!

If you’re having trouble filtering data in Excel, check out Excel filter not working? 10 common problems and solutions.

See you next time!