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)

15

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

MORE FROM ALLEN

Turning Off HTML Conversions

Don't want Word to load up your HTML documents as formatted text? There are a couple of ways you can instruct Word to be more ...

Discover More

Creating a Shortcut for Pasting Values

Excel's Paste Special command is used quite a bit. If you want to create some shortcuts for the command, here's some ways you ...

Discover More

Working with Lotus 1-2-3 Spreadsheets

If you've got some older data around your office that started in an old Lotus 1-2-3 system, you may want to open it in Excel. ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Toggling AutoFilter

Want a tool that will help you toggle AutoFilter on and off? Excel provides some tools you can use, but you need to be ...

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

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
Subscribe

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

View most recent newsletter.

Comments

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. 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 nine minus 5?

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

Lucy

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

@Lucy
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

Lucy

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
Columns("C:C").AutoFilter


2016-08-12 05:51:06

Willy Vanhaelen

@Lucy

Just highlight the one column you want and click Filter.


2016-08-11 08:55:50

Lucy

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

Donald

You are very welcome Liz!


2015-12-29 10:17:46

Liz

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

Christian

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

DP

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

Donald

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
Subscribe

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.