How to Use Advanced Filters in Excel

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.

Setup Your Spreadsheet

Step

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

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

Type your filters into the criteria range.

Applying Advanced Filters

Step

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

Step

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

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

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

Press “OK,” to apply the filter.