Using Excel’s Advanced Filter Feature

The Advanced Filter is a command in Excel that allows you to filter your data. It is useful when you have a list of criteria that you would like to use in your filtered record set. The Advanced Filter command is found on the Data Tab. You can either filter your data in place (default) or you can copy your filtered records to another location on the same worksheet.

adv filter example

The list range is your data set that you are starting with. You will want to make sure that your list range includes your header row. The criteria range will include at least one of your header labels and then the criteria below it. Optionally, you can check the box for unique records only.

data set

This example I have a data set with the following header list: Region, Date, Category, Specs, and Number. I want to see the following records that contain the numbers 1101, 1102, 1103, 1104, and 1105. Yes, I could click the filter button and select the values from the checkboxes but that can be a lot of extra time spent scrolling up and down to get the correct ones. So instead just copy down the values you want to filter under the header. This will be someplace other than the dataset. It can be a list on the active sheet or another. In my example I have it in a different tab.

filter list

The best practice is to start by clicking your data set. Then click the advanced filter button. It only says Advanced but after you click it the advanced filter box will pop up. Verify that the list range is correct with the header row included. Click in the criteria range and then select the range. Make sure your header is typed exactly the same as the data set header. If you have one extra space after the header it can mess up the results. Click Ok if you are ready to filter the results in the original data. Click copy to another location then select a range on the active worksheet as the data set in which you want the top left cell to begin your extraction of the data filter.

steps to advanced filter

The final result yields a filter where the row numbers turn blue if filtered in place. If I had filtered to another location then it would essential be an extra output. You would select that option when you want to leave the original data set alone.

final output

Click here if you want to download my practice training file.

Click hereif you want to download my Excel keyboard shortcuts cheat sheet.

If you like this post and want my future posts to deliver to your email you can sign up for that on the right side bar.

About Steve Chase

I'm a proud husband and dad to 4 boys! Microsoft Certified Trainer and Boy Scout Leader are some of things I call myself. The Cincinnati Reds are my favorite team! When not outdoors, I enjoy working with documents in Microsoft Office, QuickBooks, Visio and Photoshop. My wife, Erin, shares her awesome recipes on her blog at
This entry was posted in Excel and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s