• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

How to Use Excel Filters

You are here: Home / Excel / How to Use Excel Filters
How to Use Filters in Excel 2007
January 28, 2011 by Mynda Treacy

Excel Filters are right at home with large tables of data. 

You can use them to limit the data displayed in your table to only that which meets one or more criteria you specify.

When your data is filtered Excel hides the rows that do not meet your criteria.

You can apply them to more than one column, with each additional filter being added to the current set to further reduce the data displayed.

For this tutorial I’ve borrowed some data from Phil (my husband).

You see Phil is a keen Eve fan.  No, Eve is not another woman, although I do sometimes feel a bit widowed by Eve.  Eve is an online sci-fi computer game where you fly fantasy space ships, in a fantasy galaxy, fighting fantasy battles to feed your obsession with geeky stuff. 

I won’t bore you with anymore details on how the game works, other than to say there’s a lot of buying a selling of (fantasy) commodities and as a result the game generates large tables of data.  Perfect for filters.

Inserting Excel Filters

1.       First make sure your data is in a tabular format. That means there are column headings or labels, but no row labels, and preferably no blank columns within your table.

2.       On the Data tab in the Ribbon go to the Sort & Filter section and click on the Filter button.

excel filters icon on ribbon

3.       Down arrows will appear in each column heading like this:

example

That’s it. You're ready to go!

How to Use Filters

You can create 3 types of filters:

1.       by numeric values - available when the column contains numbers

2.       by text that meets a certain criteria - available when the column contains text

3.       by cells that meet a format style – available when the column has font or cell formats.

Clicking on any of the filter arrows opens a menu of tools where you can select from the different types relevant to the type of data in the column.

filter by colour menu

Note: The icon on the filter button changes when you have a filter in place.

example

Tip: To see what filter is applied hover your mouse over the icon and a screen tip will appear.

tooltip

Text Options

1.       Sorting by A to Z and vice versa is as it suggests. Just be aware that once you select one of these sort options you cannot undo it.

2.       Sort by Colour is great if you’re using Conditional Formatting, or even if you just have different coloured text or cell fill.

3.       By colour allows you to only display rows that meet the colour criteria you choose, whether it be the font colour or the cell colour.

4.       Or simply tick or un-tick the items you want to filter from the list.

5.       Text Filters (see menu below) allow you to specify criteria that matches a text value.

text filters menu

From the menu above I selected Text Filter > Equals and the window below opens to allow you to specify your criteria.

custom filter dialog box

Number Options

If you select Filters on a number column (see image below) you get slightly different menu options, including Less Than, Greater Than, Top 10, and Averages to name a few.

number filters menu

Dates or Times

When you apply a filter on a column containing dates you are presented with different options in the menu (see image below)

date filters menu

You will notice in the list that the dates are grouped by year, then month and so on.

You can either select specific dates from this list or you can select a time sensitive filter by clicking on Date Filters > and then selecting from the options available.

The time sensitive filters, like Today, Tomorrow, Next Month etc. are dynamic, meaning they will automatically update as time goes on. Each time you open the workbook Excel will reapply the filters based on the current date on your computer.

Tip: if you use a date filter it’s a good idea to format your dates. For example; if you’re filtering by month, format the date column as mmm-yy.

How to Apply a Quick Filter

You can apply a quick filter based on the criteria that matches the active cell (see image below).

For example; my active cell below contains the text ‘Rens’ in a red font with pink fill. When I right click on the cell, and select Filter, Excel brings up a list of options based on the active cell.

quick filter menu

How to Clear Filters

Simply click on the filter button and select ‘Clear Filter’.

Or to clear all click on the Data tab of the ribbon and select ‘Clear’ from the Sort & Filter group.

So, you can see from the options available in the windows above that there is a vast range of criteria you can specify. I recommend you have a play around with them. Since most of it is self explanatory I won’t bore you now.

Enter your email address below to download the sample workbook.



By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the practice workbook here. Note: This is a .xlsx file. Please ensure your browser doesn't change the file extension on download.

Warnings!

1.       Summing filtered data with the SUM, COUNT, AVERAGE, MIN, and MAX formulas (and some others) results in Excel including both the hidden and visible cells in your formula. If you only want to sum the visible cells in a filtered set of data you need to use the SUBTOTAL Formula.

2.       When you use the Find tool to search filtered data Excel will only search the data that is visible. To search all of your data you need to clear your filters.

3.       Using the sort options in the filter is not reversible.

4.       You cannot apply more than one filter to one column, they're mutually exclusive.

5.       Make sure each column only contains one type of data. E.g. don’t have dates and text in the same column. The filter can only be applied to one type of data in each column.

How to Use Filters in Excel 2007

More Excel Posts

excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.
Easily Remove Password Protection from Excel Files

Easily Remove Excel Password Protection

How to remove Excel password protection when you’ve forgotten the password. Works for sheets, workbooks and read only files.
Import data from a picture to Excel

Import Data from a Picture to Excel

Import data from a picture to Excel. Works with pictures from a file or the clipboard and loads it to the spreadsheet.
excel online

5 Excel Online Features Better than Desktop

5 Excel Online Features Better than Desktop including searchable data validation, track changes, single line ribbon and more.

10 Common Excel Mistakes to Avoid

10 common Excel mistakes to avoid, including merge cells, external links, formatting entire rows/columns and more.
new Excel features

Cool New Features in Excel for Microsoft 365

Cool New Features in Excel for Microsoft 365 including the navigation pane, smooth scroling, unhide multiple sheets and more.
dynamic dependent data validation

Dynamic Dependent Data Validation

Dynamic Dependent Data Validation with dynamic array formulas like FILTER make it quick and easy to set up.
QAT

Excel Quick Access Toolbar

The Excel Quick Access Toolbar is not only a handy for your mouse, but it also enables some super easy keyboard shortcuts.

Share and Collaborate in Excel

Share and Collaborate in Excel just like Google Sheets! Show changes, custom views, threaded comments with @ mentions and more.
Workbook Protection

Excel Workbook Protection

Excel Workbook protection can prevent your users from breaking your reports while still allowing interaction with Slicers and refreshing.
Category: Excel
Previous Post:Excel Pivot Table Video TutorialExcel Pivot Table Video Tutorial
Next Post:Excel VLOOKUP to the Left Using CHOOSEExcel VLOOKUP to the Left Using CHOOSE

Reader Interactions

Comments

  1. Estella Foo

    September 20, 2016 at 2:02 pm

    hi, can we use “search” in the filter if we need to have more than one name for solar system appear. eg Filter solar system for Rens and Auga? Can we have more than 2 name using this function?

    Thank you.

    Estella

    Reply
    • Mynda Treacy

      September 20, 2016 at 4:31 pm

      Hi Estella,

      You can filter for up to 2 criteria if you use Filter > Text Filters and then specify OR in the dialog box. However, 2 is the maximum filter criteria using this method.

      For more than 2 you need to use Advanced Filters.

      Mynda

      Reply
  2. Sarah

    October 9, 2015 at 9:28 pm

    Your website has so many helpful tips, thank you!

    I thought I was pretty confident with filters, but I’ve come across an issue with an inherited spreadsheet where the last row #1068 always appears, no matter which column I’m filtering on – in most cases it shouldn’t be showing. I’ve checked that there are no blank rows, no hidden rows, and the formatting of all rows is the same. I’ve removed the auto filter and put it back in different ways with no improvement. The data isn’t in an Excel Table.

    Do you have any other ideas I can try please??

    Thank you…

    Reply
    • Catalin Bombea

      October 9, 2015 at 10:14 pm

      Hi Sarah,
      Are you sure that the stubborn row is included in the filtered area? Try removing the filter, select the entire range and apply the filter.
      Let us know if this was the case 🙂
      Cheers,
      Catalin

      Reply
  3. Tom

    April 26, 2014 at 8:51 am

    Now this is a spicy meatball!!! Never has drilling for the data to facilitate a quick report been easier! Not as fulfilling as a pivot table, but if you need data quick, this little trick can have a report on the bosses desk before his bellowing quits ringing….thanks for sharing and for making it so easy to understand. I have learned more from your site in the few months I’ve been here, than I have in…well, years! You are the best, and keep it coming!

    Reply
    • Mynda Treacy

      April 26, 2014 at 9:04 am

      Aw, thanks Tom. Glad you liked this ‘spicy’ tip 🙂

      Mynda.

      Reply
  4. William

    March 3, 2014 at 9:22 am

    A filter can be applied on any column, but it is not easy to see which column has a filter applied. Can one cell be colour formatted if a filter is applied in that column, or can the drop down icon be colour formatted if that icon is being used?

    Reply
    • Catalin Bombea

      March 3, 2014 at 11:13 pm

      Hi William,
      You already have 3 visual indications that the Filter Mode is active: the drop down icon is different, the row numbers are blue, and you have filter results in Status Bar, like: “25 of 2300 records found”. The conditional formatting cannot be applied, and the column filter cannot be changed…
      Catalin

      Reply
  5. naresh

    June 10, 2013 at 3:53 pm

    superb

    Reply
    • Mynda Treacy

      June 10, 2013 at 7:32 pm

      Thank you, Naresh 🙂

      Reply
  6. naresh

    June 7, 2013 at 9:06 pm

    good please try again another one

    Reply
    • Mynda Treacy

      June 7, 2013 at 9:40 pm

      Thanks, Naresh 🙂

      Reply
  7. boss

    May 7, 2013 at 1:05 am

    AN TO QUOTE THE WORDS OF MY TEACHER THIS IS ONE REALLY HELPFUL SITE. 🙂

    Reply
    • Mynda Treacy

      May 7, 2013 at 1:36 pm

      🙂 thank you, Boss.

      Reply
  8. Dave

    May 4, 2013 at 10:34 pm

    Thank you for these pages, they are so helpful! I have some data that I would like to filter BY subtotals, is this possible?? Say I have several entries for each customer, and I want to ignore customers who have spent a total that is greater than a certain amount in future analyses…

    Reply
    • Mynda Treacy

      May 5, 2013 at 7:07 pm

      Hi Dave,

      If you use the Subtotals tool it automatically groups the data for you so that you can summarise the data by the subtotals etc. No need to use Filters too. See an example here:

      https://www.myonlinetraininghub.com/how-to-insert-subtotals-in-excel

      Kind regards,

      Mynda.

      Reply
  9. JItender Kumar

    February 8, 2013 at 7:53 pm

    If i have a data and if used filter maximum for find out raw by raw and i know , i required a macro for the particular data… will you pl help me out…. pl.. if you have any solution pl provide me…..

    Reply
    • Carlo Estopia

      February 8, 2013 at 8:10 pm

      Hi Jltender Kumar,

      Please send your file through HELP DESK.

      I don’t see you will need a macro for this.

      We have a built in function called
      MAX.

       syntax = MAX(Range) 

      for example, =MAX(A1:A45).

      Cheers.

      CarloE

      Reply
  10. aslam qadeer

    January 25, 2013 at 12:12 am

    the best lesson for excel filtering

    Reply
    • Mynda Treacy

      January 25, 2013 at 9:47 am

      Cheers, Aslam 🙂 Glad you liked it.

      Reply
  11. ravi

    October 3, 2012 at 3:52 pm

    information was very useful. good

    Reply
    • Mynda Treacy

      October 3, 2012 at 4:14 pm

      Cheers, Ravi 🙂

      Reply
  12. Kent

    October 2, 2012 at 3:10 am

    I find your tutorial extremely helpful. You wrote:
    “First make sure your data is in a tabular format. That means there are column headings or labels, but no row labels, and preferably no blank columns within your table.”

    I also immediately discovered that a blank row immediately below the filter row will cause problems.

    Reply
    • Mynda Treacy

      October 2, 2012 at 6:21 pm

      Cheers, Kent. Thanks for pointing out another key rule for filters 🙂

      Reply
  13. Emmanuel

    July 26, 2012 at 12:03 am

    great tips above

    Reply
    • Mynda Treacy

      July 26, 2012 at 7:08 am

      Thanks, Emmanuel 🙂

      Reply
  14. Coredo D Revilleza

    July 16, 2012 at 5:47 pm

    Thank you. It was so nice

    Reply
  15. GOPAL

    June 27, 2012 at 1:47 pm

    Thanks

    Reply
    • Mynda Treacy

      June 27, 2012 at 2:21 pm

      🙂 You’re welcome.

      Reply
  16. Fiona

    June 11, 2012 at 8:45 pm

    Thanks

    Reply
    • Mynda Treacy

      June 12, 2012 at 2:23 pm

      You’re welcome 🙂

      Reply
  17. Shekar

    May 13, 2012 at 7:46 pm

    Excel Filter Warnings!

    6. Reiterating what Mynda mentioned above regarding \no blank columns within your table\:

    Filtering data with merged cells is not a good idea.

    Reply
  18. BC

    April 27, 2012 at 11:54 am

    Helpful tips. Thanks.

    Reply
    • Mynda Treacy

      April 30, 2012 at 10:26 pm

      Cheers, BC 🙂

      Reply
  19. vamshi

    January 23, 2012 at 8:25 pm

    thanks for the pdf.

    Reply
    • Philip Treacy

      January 23, 2012 at 9:27 pm

      You’re welcome 🙂

      Reply
  20. vamshi

    January 23, 2012 at 7:52 pm

    Thanks

    Reply
  21. Tarun

    October 17, 2011 at 6:00 pm

    Great

    Reply
    • Mynda Treacy

      October 17, 2011 at 8:04 pm

      Thanks Tarun. I appreciate your feedback.

      Reply

Trackbacks

  1. Excel AGGREGATE Function • My Online Training Hub says:
    October 6, 2015 at 10:01 am

    […] when I hide some rows, by filtering out the South and West regions, my AGGREGATE formula returns a result that ignores the hidden rows, […]

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x