Excel 2007’s Filters tool is right at home with large tables of data. 

You can use filters 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 filters to more than one column, with each additional filter being added to the current filter(s) to further reduce the set of data displayed.

For this Excel Filters 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.

How to Insert Filters in Excel 2007

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.

How to insert a filter in Excel 2007

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

Filter drop down arrows

That’s it. Your filters are now inserted!

How to Use Filters in Excel 2007

You can create 3 types of filters using the filter buttons:

1.       Filter your table by numerical values – available when the column contains numbers

2.       Filter your table by text that meets a certain criteria – available when the column contains text

3.       Filter your table 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 of filters relevant to the type of data in the column.

How to use filters in Excel 2007

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

How to tell you have a filter in place

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

How to tell what filter is applied

Text Filter 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.       Filter 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.

How to insert a text filter in Excel 2007

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

How to insert a custom filter in Excel 2007

Number Filter 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.

How to insert a number filter in Excel 2007

How to Filter 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)

How to filter dates and times in Excel 2007

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 based on the filter. 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.

How to apply a quick filter in Excel 2007

How to Clear Filters

To remove a Filter from a column simply click on the filter button and select ‘Clear Filter’.

Or to clear all filters from your worksheet 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 it. Since most of it is self explanatory I won’t bore you now.

Download the practice workbook here.

Excel Filter 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. Each filter is 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.

Share This

Please share this or leave a comment and I'll make sure you get a personal reply.

Leave a Comment

Current day month ye@r *

{ 35 comments… read them below or add one }

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

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

naresh June 10, 2013 at 3:53 pm

superb

Reply

Mynda Treacy June 10, 2013 at 7:32 pm

Thank you, Naresh :)

Reply

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

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

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:

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

Kind regards,

Mynda.

Reply

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

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

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

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

Emmanuel July 26, 2012 at 12:03 am

great tips above

Reply

Mynda Treacy July 26, 2012 at 7:08 am

Thanks, Emmanuel :)

Reply

Coredo D Revilleza July 16, 2012 at 5:47 pm

Thank you. It was so nice

Reply

GOPAL June 27, 2012 at 1:47 pm

Thanks

Reply

Mynda Treacy June 27, 2012 at 2:21 pm

:) You’re welcome.

Reply

Fiona June 11, 2012 at 8:45 pm

Thanks

Reply

Mynda Treacy June 12, 2012 at 2:23 pm

You’re welcome :)

Reply

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

BC April 27, 2012 at 11:54 am

Helpful tips. Thanks.

Reply

Mynda Treacy April 30, 2012 at 10:26 pm

Cheers, BC :)

Reply

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

vamshi January 23, 2012 at 7:52 pm

Thanks

Reply

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

Previous post:

Next post: