How to Use Excel Filter to Find Items in Your Data

If you maintain a large spreadsheet with dozens of columns and hundreds, or even thousands, of rows, it can be difficult without an Excel filter to find specific pieces of data.

Sure, you could use Excel’s find function to search for each match, but this can be time-consuming, especially if you have a lot of matches.

Excel filter

The better option is to use the Excel filter options. With an Excel filter enabled, finding data in your large spreadsheet becomes quite simple.

To see how an Excel filter works, let’s take a look at the following simplified spreadsheet which shows widget sales for a fictitious company:

Excel filter sample spreadsheet -magoosh

Let’s say we want to see only the widget sales for widget #94638. We can do this by using Excel’s built-in filter feature.

To apply the Excel filter, click on the column A header and then hold down the Shift key on your keyboard while clicking on the column D header. This will select everything in columns A through D.

Next, make sure you are on the Home tab and click the Sort & Filter button located in the Editing section of the Home tab. From the drop-down list of options that appears, click Filter.

Excel filter option

Once you do this, Excel will place drop-down arrow buttons in the first row (the column headers) of each cell of columns A through D in your spreadsheet.

Using the Excel filter

Since we want to find only widget #94638, we want to click the drop-down arrow button next to the Widget column header in cell A1. When we do this, Excel will show a list of all unique widget numbers with a checkbox to the left of each one of them. By default, all of the checkboxes are checked.

Excel filter by widget

To filter your sales list to just widget #94638, you want to first uncheck every checkbox in the list of widgets. A simple way to do this is to uncheck the box next to “(Select All)”. Then scroll down the list of widgets until you see widget #94638 and check the box to the left of it. Click the OK button when you are finished.

Note: If your spreadsheet has a large number of unique values, you can type the first few characters of the item you wish to filter by in the Search box above the list.

Your spreadsheet now shows only the rows that match our filter of widget #94638. Notice the row numbers to the left of each row. They will be blue in color to show that there are skipped (or filtered out) rows between that row and the next row.

Excel filter rows

Check out our Excel Lesson Videos to learn more about advanced features like filtering in Excel.

Multiple Excel filters

If you desire, you can apply another filter to your already filtered results. Let’s say you only want to see widgets in your list of sales for widget #94638 that were sold in region 3.

To do this you click the drop-down arrow button next to Region, uncheck Regions 1 and 2 and click the OK button. Your spreadsheet now looks like this:

Excel filter with two filters applied

Reset the filters

In order to return to your full list of sales, you will need to reset the filters you have set. Notice in the sample image above that any row containing a filter has a filter icon in place of the drop-down arrow. These are the columns we will need to reset.

To reset a filter, click on the filter button in the column header and select the “Clear Filter From…” option. You’ll need to do this both for the Widget and the Region column.

Excel filter - clear filter

As you can see, the Excel filter options are a quick and easy way to dissect the data in your large spreadsheets.

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!