How to Filter Data in Excel (A Quick Tutorial!)

Introduction

Hey Anylearners! Filtering was one of first skills I learnt Excel and it was honestly the main reason why I had picked it up. I was new to handling data then but was amazed by how easy it is to learn this. I’m sure you would be able to learn this in no time, and I guarantee you will need this knowledge one day (either at work or in school). Read on for a quick introduction on how to filter data in Excel. Enjoy!

Filtering in Excel is one of the basics of data manipulation, to better understand and bring out relevant information from your dataset. Filtering is simple, yet powerful and anyone can learn it with proper instructions on how to apply it.

In this tutorial you will learn how to:

1. Filter by Numbers data
2. Filter by Text data
3. Filter by Date data
4. Filter by Color
5. Filter with search
6. Remove filters

When working with large datasets in Excel, a major challenge for most users would be to sieve out important information. This is where the Filter tool in Microsoft Excel can be used to powerfully narrow out relevant data in a simple way.

What is a Filter?

In Excel, a filter (or Autofilter) is a feature that allows data to be selectively narrowed down to specific rows in a spreadsheet. It filters data according to its value, format or specific criteria.

Within a spreadsheet, the filters appear on the header row, with dropdown menus at each header cell.

Adding a Filter

Requirement: Dataset must include header rows for each column of data.

1. Select any cell within the dataset
2. Add filter by selecting the Data tab and then the Filter button

Result:

Applying a Filter

After adding a filter, dropdown arrows should appear in the header row for each column of the dataset. However, the filter has not been applied yet.

1. Click dropdown arrow for column of choice
2. Uncheck the Select All option
3. Check your option of choice from the list
4. Click Ok

In this example, we apply a filter to only show data from Pens. This is done be checking the option Pen.

Result

After applying the filter, the header of the filtered column should show a filter icon.

Filtering Text Data

In Excel, text data can be filtered through basic equality conditions. This can be useful to select data according to specific characters and where their positions are. However, filtering by text is not commonly used, except when using the Equals filter, where it is normally used to identify a specific word or sentence.

Excel’s filter offers options to filter text according to several criteria:

• Text that are Exactly Equal to certain characters
• Text that are Not Equal to certain characters
• Text that Begins With certain characters
• Text that Ends With certain characters
• Text that Contains certain characters
• Text that Does Not Contain certain characters

Filtering Number Data

Excel’s filter also allows Number filters using these criteria:

• Basic Number Condition Operators
  • E.g Equals, Does Not Equal, etc
• Top (N) values | Top 10…
  • Filter and display rows with the top N values, with N being a specified value
• Values above the average value | Above Average
  • Filter and display rows with above-average values
• Values below the average value | Below Average
  • Filter and display rows with below-average values
• Custom filters | Custom filters…
  • Allows up to 2 criteria using AND and OR

Filtering Date Data

Excel’s Date filters allow the user to quickly and powerfully select data from specific time periods. As a default, Excel would group the dates according to the year, month and day for each date in the column. This feature can be used to quickly hide or show any information that is specific to the year, month and date.

Excel’s filter also allows filtering by Date with the following criteria:

• Basic Number Condition Operators
  • E.g Equals, Does Not Equal, etc
• Day
  • E.g Yesterday, Today, Tomorrow
• Week
  • E.g Last Week, This Week, Next Week
• Month
  • E.g Last Month, This Month, Next Month
• Quarter
  • E.g Last Quarter, This Quarter, Next Quarter
• Year
  • E.g Last Year, This Year, Next Year
• Year to Date
  • Filters rows from current year to current date
• All Dates in the Period
• Custom filters | Custom filters…
  • Allows up to 2 criteria using AND and OR

Filtering by Color

Excel offers color filters as well. This can be useful in some cases where conditional formatting is done to identify categories or status of a certain entry. By selecting according to Cell Color, rows belonging to specific categories can be displayed. If you do use color as a form of indicator, this filter will be a great way to display rows with colors of choice.

Example

Removing a Filter

To remove a filter that has been applied:

1. Click on Data tab
2. Under the Sort & Filter section, Click on Clear to clear the applied filter

To remove the dropdown arrows:

1. Click on Data tab
2. Under the Sort & Filter section, Click on Filter to remove arrows.

That’s all for this introductory tutorial on how to filter data in Excel! By now you should know how to create a filter, apply text, number, date and color filters on a dataset and remove it after use. This tutorial covers the basics of using the Autofilter in Excel (2007 onwards).

And of course, this is just an introduction, with more features that filters have to offer. There are many more advanced tutorials to come, so watch this space. Thanks for reading!