Advanced Filter Not Working in Excel

Written by

Mel Jenkins

Reviewed by

Laura Tsitlidze

Last updated on February 13, 2023

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


AdvancedFilterNotWorking intro


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.


advancedfilternotworking column match


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.


advancedfilternotworking column dont match


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.


AdvancedFilterNotWorking headings 2 rows


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.


AdvancedFilterNotWorking text wrap


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


AdvancedFilterNotWorking headings 1 row


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.


AdvancedFilterNotWorking data error


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.


AdvancedFilterNotWorking result fixed


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.

AdvancedFilterNotWorking incomplete data


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.


AdvancedFilterNotWorking merged cells


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.


AdvancedFilterNotWorking merged data


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…


advancedfilternotworking find


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


advancedfilternotworking find format


  1. Tick Merge cells, and then click OK.


advancedfilternotworking find select merge


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


advancedfilternotworking find merged cells


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


advancedfilternotworking unmerge cells


  1. 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.


advancedfilternotworking hidden column excluded


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.


AdvancedFilterNotWorking 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.


AdvancedFilterNotWorking unhide rows

AI Formula Generator

Try for Free

See all How-To Articles