Advanced Filtering
If you’ve used Excel for more than 6 months, I’m sure you know how basic filtering.
Here are the steps to take your filtering to the next level.
Step 1 - Set up the dataset
The first row of your dataset should have unique headings. There should be no blank rows within the database. Make sure there is a blank row at the bottom of your dataset and a blank column to the right.
Step 2 - Create the criteria range Set up an area of your worksheet for the criteria range. Tip: If at all possible, place your criteria range in cells above your dataset. For example, place your dataset in rows 10 through 1000, and your criteria range in rows 1 through 10.
The headings in the criteria range should match the headings of your dataset. Any criteria on the same row has an applied AND. Any criteria on separate rows has an applied OR.
In this example, the salesperson = “Buchanan” AND Order Amount > 440.
In this example, the salesperson = Buchanan OR Order Amount > 440
Step 3 - Create the advanced filter
Select a cell in the dataset. Click the Data tab on the ribbon, then click Advanced. Choose to filter the list in place or copy the results to another location.
The filter will automatically select the List range.
Select the criteria range on the worksheet.
If you are copying to a new location, select a starting cell for the copy. Click OK.
Bonus Tip:
To copy and paste the filtered cells only, select the range, then press ALT Semi-colon. Copy, then Paste. | 