How to Easily Filter Data in Excel With This One Feature (Video)

April 7, 2017

The filter function in Microsoft Excel let's you easily sort through data.

Excel is an incredibly detailed and nuanced application. Most Excel users work with the application for years and barely scratch the surface of what the application is capable of.

That’s one of the reasons we created the course Excel Quick Tips – a collection of quick one-minute tips that introduce really useful features in Excel that you may not know about.

For example, check out this Quick Tips movie for the Filter feature. Basically, if you have an Excel document that is full of tons of data, but some of that data is irrelevant to your current task, you can use the Filter tool to temporarily show only the data you want:

How to use the filter feature in Excel

To use the Filter feature, you will need to select any cell in your sheet that contains data, then go to the Home Ribbon and find the Sort & Filter Menu. In that menu, simply click the Filter option to turn on the filter tools.

When you enable the filter tools, a menu button will appear in every column in your sheet. Then, all you need to do is open the menu on the column that contains the data you want to sort.

Take a look at the screen shot below.

I clicked on the menu in Column D. That column contains addresses. I can filter the data in the entire sheet so that it only shows rows that contain data in Row D that match my criteria. In the Quick Tips movie, I went to the “Text Filters” submenu and chose the “Contains” option. Then, I set “London” as my search term, filtering my sheet to only show rows where Column D contains the word London.

But, there are lots of other filter criteria that you should experiment with. If I chose “Does Not Contain” in the Text Filters submenu, I could filter my sheet to only show data where the address column does not contain the word London – showing me everything except my London customers. The “Equals” or “Does Not Equal” options are great for filtering a column that contains specific numeric values.

Also, keep in mind, I chose to filter by the data in Column D, so I opened the menu attached to that column. But, I could have set up a filter on any column in my sheet.

You can even set up filters on multiple columns at the same time. So, after setting up the address filter in Column D, I could have added another filter to Column B. So, I could filter my sheet to only show me my customers in London who have “CEO” in their title.

You will need to experiment with the filter options in your spreadsheets to identify the information you want.

Want to learn more about Excel? Watch our course, Excel Quick Tips, to see more one-minute tips like these.