How to Use Advanced Filters in Excel

By William Nagel

Microsoft Office Excel allows you to filter the data in your spreadsheets. The basic filter is easy to setup and use, but Excel also comes with a powerful "Advanced Filter" that allows you to sort and filter spreadsheets with a library of commands. Advanced Filters are quick to setup. They're customizable, and they allow you to view only the data that you want to see. Use advanced filters when you want to hide irrelevant data and quickly locate important data.

Things You'll Need

  • Microsoft Office Excel

Setup Your Spreadsheet

Step 1

Insert at least three rows above your list range. These rows comprise your "criteria range." You will enter filter commands in this area. The "list range" contains the data you want to filter.

Step 2

Label the columns of your criteria range. Each column in the criteria range should have a label identical to its corresponding column in the list range. For example, if the column is labeled "Profits" in the list range, it should be labeled "Profits" in the criteria range.

Step 3

Type your filters into the criteria range.

Applying Advanced Filters

Step 1

Use the cursor to highlight the list range. Make sure to include column headers in this selection.

Step 2

Select the "Data" tab and push the "Advanced" button on the "Sort and Filter" panel. The "List Range" field will automatically populate with the range you selected in Step 1.

Step 3

Choose how you want your data displayed. Select "Filter the list, in-place," to sort your data on the current spreadsheet. Select "Copy to another location," to put the filtered data on a different spreadsheet.

Step 4

Push the button on the right end of the "Criteria Range" box. Use the cursor to highlight the criteria range and filter commands. Make sure to include the column headers in this selection. Press "Enter," to confirm your selection

Step 5

Press "OK," to apply the filter.

Tips & Warnings

  • Microsoft Office Excel contains dozens of filter commands that allow you to filter both numerical and textual data. Visit the Microsoft Office Excel Website for information on using these commands. See Resources.
  • You can use Advanced Filters in older versions of Excel by selecting the "Filter" sub-menu from the "Data" menu and choosing "Advanced Filter..."