Introduction to Using Filters to Refine Data in Excel

Add to Favorites
Author:

Filtering allows you to hide rows of data which you are not interested in so that you can easily look at the rows you want to see. Its as easy to use as sorting and can be basic or very complicated depending on how you use it.

In this tutorial I will just go through basic filtering. To filter a table you just select the column headings or the whole table itself then select Filter from 1 of 2 places. These are the same locations as the Sort feature was on the Home and Data tabs within the previous tutorial. The Filter function is always beside Sort by default in Excel. (Right-click then selecting Filter works slightly differently here and isnt as useful)

After selecting Filter, arrows should appear beside each of the columns in your selection. (Note: When filtering you always need column headings as the filter rules you apply do not affect the first row of your selection)

 

For this example I am going to filter out the surname Jackson. To do this I select the down arrow beside the Surname column heading. The following menu appears:

To filter all you do is untick the values you want to ignore. So as I wanted to ignore Jackson I unticked the box beside it. Notice how Jackson is only shown once here but appears twice in my table. This is because only distinct values are shown as filter options.

Alternatively I could have unchecked select all and then checked only the ones I want to see. It doesnt really matter which way round you do this, it all depends on the data set, what you are looking to filter and which is the quicker option. After you finish selecting your filter, click ok and the table will update. My table now appears like so:

 

Rows 4 and 6 are now hidden. The filtered row numbers now turn blue to indicate filtering has been applied on those rows. There is also a filter symbol beside the column used to filter the data. As another example I will filter the Date Added column. Like before I just select the down arrow beside the Date Added column. I get the following menu:

Whats cool about date formatted columns is that the filtering can be expanded to individual days or contracted to individual years. If I filter only rows with a date added in April I get the following:

All the other rows have dates in May.

Number and date filtering also have more advanced features as they can be numerically evaluated. So for example if I select filtering on the Age column this time I can do a basic filter via the checkboxes like before but I can also select Number Filters to filter the table based on the numeric values of the Age column.

I could filter where Age is less than 30 for example but this is getting into the more advanced filtering options and involves setting filter rules which we will discuss in a later tutorial.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Similar Content on TeachExcel
Filter Data in Excel - AutoFilter
Macro: This free Excel macro filters data in Excel using the autofilter feature in an Excel macro...
Filter Data in Excel to Show Rows/Data That Meet Multiple Criteria for One Field - AutoFilter
Macro: This free Excel macro filters data in Excel based on multiple criteria for one field in th...
Filter Data in Excel Without the Filter "Arrow" Appearing in the Filtered Column - AutoFilter
Macro: This Excel macro filters data in Excel without the filter "arrow" appearing in the column ...
Filter Data in Excel to Display Records that Contain a Value Between Two Values - AutoFilter.
Macro: This free Excel macro filters data to display only those records or rows that contain a va...
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
Macro: This Excel macro filters data in Excel to display results that contain 1 of 2 possible val...
How to Create and Manage a Chart in Excel
Tutorial: In this tutorial I am going to introduce you to creating and managing charts in Excel. Bef...
Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course