How to Filter a MS Excel Data Set with Multiple Criteria (Advanced Filtering)

© Ugur Akinci
Let’s imagine you’re working with the following data set:

Let’s say you want to find only those transactions in which SHOES were sold for LESS THAN $20.
Follow these steps:
(1) Enter a couple of empty rows to the top of your table.
(2) Copy your HEADER row and paste it to the top added row:

(3) Under the newly created GROSS header, enter “<20” since we are interested only those items that cost less than $20.
(4) Under the newly created ITEM header, enter “Shoe” since we are interested only those items that are shoes.

(5) On the DATA tab, click Advanced Filter button to display the Advanced Filter dialog box:

(6) Place your cursor in the LIST RANGE field, then with your mouse select the whole original data set, INCLUDING the HEADERS.
(7) Place your cursor in the CRITERIA RANGE field, then with your mouse select the whole newly added criteria cells, INCLUDING the HEADERS:

(8) OPTIONAL: Select another location to copy the results to by selecting the “Copy to another location” options button, placing your cursor in the “Copy to” field”, and then clicking your mouse in the intended upper-left cell of the new location.
(9) Back in the Advanced Filter dialog box, click OK for the result:

(10) Change your filtering criteria any way you like and repeat.
NOTES:

• If you would like the new data set replace the old one, do NOT select the “Copy to another location” options button. Just select the “Filter the list, in-place” option button.
• If you want only the unique records, then select the “Unique records Only” check-box.