Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using AutoFiltering.

Using AutoFiltering

by Allen Wyatt
(last updated December 28, 2015)


Filtering a list means displaying only a part of it. You provide the criteria you want used and then Excel displays only those list records that match the criteria. Filtering is especially useful if you have a large list and you want to work with only a subset of the records in the list.

The easiest way to filter your list is to use the AutoFilter feature. You do this by following these steps:

  1. Select any cell in your list.
  2. Display the Data tab of the ribbon.
  3. In the Sort & Filter group, click the Filter tool. Excel determines where your column (field) labels are located and adds pull-down arrows to the right side of each label's cell.

AutoFilter is now alive and well in your workbook. If you click on one of these pull-down arrows, Excel displays the unique values in that column (field). You can then select one of the values and Excel displays only those records that match that value for that field. (The pull-down arrow then turns blue.) All the rest of the records in the list will be hidden.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (6612) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Using AutoFiltering.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...


Editing Graphic Objects

Want to change the way that a graphic object appears in your worksheet? You need to edit it, then, using the techniques ...

Discover More

Transferring Fonts

Do you want to transfer fonts from one computer system to another? It is relatively easy to do, but there is one important ...

Discover More

Drawing Lines

Lines are one of the most common graphic elements to be added to documents. Here's how you can add the lines you want.

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Getting a Count of Unique Names

When you've got a column full of names, you may want to get a count of how many of those names are unique. You can make quick ...

Discover More

Dealing with Text Length Limits and AutoFilter Drop-Down Lists

Excel has some built-in limits on what you can do with the program. When you run into those limits, it can be frustrating to ...

Discover More

Setting Up Custom AutoFiltering

The filtering capabilities of Excel are very helpful when you are working with large sets of data. You can create a custom ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 0?

2016-08-17 11:25:50

Scott Renz

And Lucy, by VBA code I mean it is used in an Excel macro. Cheers!

2016-08-17 11:20:41

Scott Renz

Hi Lucy,

That was the two lines of VBA code that I used to filter only one column. The first line checks to see if filtering is on already and turns it off if it is. The next line then turns filtering on for only one column. Cheers!

2016-08-17 04:51:35


Thank you SO much Willy! I knew it had to be something pretty simple, I converted my table to a range and Voila I can apply a filter to just one column.
I'm a happy bunny thanks :)

2016-08-15 06:16:39

Willy Vanhaelen

Obviously your table is formatted as an "Excel table". In that case Excel doen't allow you to filter on only one column.

If you don't need the features of an Excel table you can convert it to a range and then you will be able to filter on only one column.

2016-08-15 03:43:07


Thank you for replying. I have tried just highlighting the one column I want the filter on, and doing this still applies the filter option to every heading of my table. I literally just want people viewing the sheet to be able to filter by the first column of my table only. I feel like this should be easy to do!
Scott, are you talking about using the Advanced filter option? I have tried this as well, but I am clearly doing something wrong as just get an error message. If you are not referring to this, then I'm lost!

2016-08-12 13:17:19

Scott Renz

If ActiveSheet.AutoFilterMode Then Cells.AutoFilter

2016-08-12 05:51:06

Willy Vanhaelen


Just highlight the one column you want and click Filter.

2016-08-11 08:55:50


Can anyone tell me how to just apply a filter to ONE column of a table ONLY please? I have searched the web for advice on this and have unsuccessfully tried a couple of macros, nothing seems to work!

2016-01-07 13:05:20

Scott Renz

I had a problem when there were blank rows on the spreadsheet. Filtering only showed data before the blank row.

To resolve this, I inserted a new column "A" and put values in every cell in column "A". After that, I was able to filter and see all the values in a given column since no rows were blank.

2015-12-29 18:06:37


You are very welcome Liz!

2015-12-29 10:17:46


Donald; thank you - that worked! As it was a shared workbook, i had to create a new workbook, but it solved the problem.

Christian: I did not understand your comment? Please clarify.

DP: I need to research converting a range to a table - could not figure it out by myself. Thank you.

2015-12-29 02:44:02


HINT (I discovered recently in Excel 2013, maybe it existed previously): you can type a string of caracters instead of selecting with the mouse. Much more efficient with long lists!

2015-12-28 11:58:54


I primarily use filtering with Excel tables. This is the fastest and easiest way to have the full features of filtering. As long as the data is in truly tabular format (no blank rows and no blank columns) converting a range to a table is very easy. I know this feature has been covered on this site before. Starting with converting a range to a table and then using filtering should have been discussed.

2015-12-28 10:53:57


Hello Liz,
Have you tried 'Find & Select' ~ 'Go To Special' ~ 'Visible Cells Only'?
Copy the data and paste onto another worksheet, then renumber.

2015-12-28 10:11:07

Liz Stone

I have my filtered list which shows rows 1,2,7,9,22, etc.
I want to number those rows only in a new column as 1,2,3,4,5...
Excel will not let me do that. I know how to click and drag to normally get 1,2,3,4...into a column, but not in a filtered list. The list is really about 350 rows long, and putting in the numbers by hand is too tedious.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.