Advanced Filter Not Working in Excel

This tutorial shows some reasons an advanced filter might not work in Excel and how to avoid those issues.

 

 

Advanced Filter Not Working

Advanced filters in Excel extract data using more complex criteria than a standard filter. You are able to filter data to a different location from the original data, as well as setting a criteria range that enables you to filter on two or more columns of data using and/or scenarios. It’s also a great way to extract unique data from a list with duplicates.

If you find yourself in a situation where your filter isn’t working properly, it’s probably due to one or more of the reasons below.

Column Headers Don’t Match

To use an advanced filter, the column headers in the data section need to match the column headers in the criteria section.

 

 

For example, if your column headings are spelled differently in the data section from the criteria section, the advanced filter doesn’t work. Consider the filter setup below.

 

 

Although it’s pretty clear to any of us that Quantity and Qty are the same, Excel can’t recognize them as the same fields in an advanced filter. The headings need to match each other precisely.

Headings Over Multiple Rows

Another kind of mismatch occurs if any headings span multiple rows. You may get the error message:

Microsoft Excel cannot determine which row in your list or selection contains column labels, which are required for this command.

Consider the example below. Column headings need to be in a single row, but Columns B and C have one half of the heading in the first row, and the other half in the second row.

 

 

To solve this problem, make sure your headings are in a single row. Wrap the text if you still want to display the headings over two rows.

1. Type headings in the first row only.
2. Select the cells where you wish to wrap the text.
3. In the Ribbon, go to Home > Alignment > Wrap Text.

 

 

Now, the headings can be displayed in the same way, with the same column width, and also be usable in an advanced filter.

 

 

Dataset Error

If your data contains error values, then the rows containing those errors aren’t included in the filtered result; your filter is not returning 100% correct data.

 

 

You need to ensure that your data does not contain any error values. You can use Excel’s error checking command to find your errors, or look for cells with green triangles. If an error is located, replace the error with valid data.

For example, in the graphic above, the value 50 has an underscore (_) in front of it resulting in the formula in the total column not calculating. If you remove the underscore, then the 50 becomes a value and the formula in the total column can calculate correctly.

 

 

Now, this data is ready to work accurately with an advanced filter.

General Filter Issues

Incomplete Data Selection

If your data contains blank columns or rows, it can be easy to exclude some data from the filter. Excel doesn’t automatically recognize that the values below the blank row (or adjacent to a blank column) are part of the dataset.

 

It’s a good idea to select your data manually to ensure that you get all the data that you want to include. See our tutorials on deleting blank rows and blank columns.

Merged Cells

If your column headings contain merged cells, the advanced filter won’t work.

 

 

This also applies if there are any merged cells within the actual data. Make sure that any column headings and rows within the data do not contain merged cells.

 

 

To find and unmerge any merged cells, follow these steps:

1. Highlight your data.
2. In the Ribbon, go to Home > Editing > Find & Select > Find…

 

 

3. In the Find and Replace dialog box, click Format…

 

 

4. Tick Merge cells, and then click OK.

 

 

5. Click Find All to identify the merged cells in the dataset.

 

 

6. To remove the merge, select the cell, and then, in the Ribbon, go to Home > Alignment > Merge & Center.

 

 

7. You do not have to close the Find and Replace box while you do this which enables you to make sure that you unmerge all the necessary cells.

Hidden Columns

If you have hidden columns in your data, a filter may not work, depending on whether or not you have data in the hidden column(s).

If you click in a data table and apply a filter, you encounter the issue of incomplete data selection described above if the hidden columns are blank.

 

 

On the other hand, if the column is not blank but is hidden, then a filter is applied to the column but is unable to be used due to the column not being visible!

1. To unhide any blank columns, select all the data – including the columns to the right and left of your data to include any hidden columns.
2. Then in the Ribbon, go to Home > Cells > Format > Hide & Unhide > Unhide Columns.

 

 

All the columns in the data range should then be visible.

Hidden Rows

If you have hidden rows that are blank or do not contain the same kind/format of data as the range filtered on, you may get an error message or incorrectly filtered data. Filters work on rows that are initially hidden, but only if those rows are a part of a clean database.

To make sure all rows are unhidden in your data, select the data and then, in the Ribbon, go to Home > Cells > Format > Hide & Unhide > Unhide Rows.