If Excel reports that the database or list range is not valid while you use Advanced Filter, check that the selected list includes a single, complete header row and that the criteria range is set up correctly. Excel uses “database” to mean the worksheet list being filtered; it does not necessarily indicate a problem with a database connection. If you mean a different filter message, the fix may depend on its exact wording.
What the Excel database filter error means
Excel’s Advanced Filter works on a worksheet range that it treats as a list: one row of field names followed by records. In this feature, the list range is sometimes described as a database range. The error usually means Excel cannot interpret the range or its criteria as a valid list, or cannot use the requested destination for copied results.
This is different from turning on the ordinary column filters with Data > Filter. AutoFilter adds drop-down arrows to column headings and lets you narrow the visible rows. Advanced Filter, opened from Data > Sort & Filter > Advanced in Excel for Microsoft 365 on Windows, also accepts a separate criteria range and can copy matching rows elsewhere. The two features can produce similar results, but an Advanced Filter error does not automatically mean AutoFilter is broken.
Messages vary by the action and Excel version. A warning about a missing field name, an invalid list range, or copying filtered data to the active sheet points to different checks. Read the full message and note whether you chose Filter the list, in-place, or Copy to another location before changing the worksheet.
Check the list range and its headers
Start by confirming that the list range contains the column headings and all the records you intend to filter. The first row should identify each field, such as Region, Order Date, and Amount. Avoid blank header cells, merged heading cells, and extra title rows inside the selected range. If the worksheet has a title above the data, do not include that title as the list’s header row.
For a predictable result, keep the records in a rectangular block: one field per column, one record per row. A blank row or column within the data can make automatic range selection stop too early. When you open Advanced Filter, enter or select the intended range explicitly rather than relying on Excel to guess. Check that the range starts on the header row and ends after the last record, not on a subtotal or a note below the table.
Headers should be meaningful and distinct. Duplicate headings can make it unclear which field a criterion refers to, while an empty heading is not a reliable field name. If the list is an Excel table, check the visible table headers and confirm the table includes the records you want. You can use Table Design > Resize Table to adjust the table boundary if needed; the Table Design tab appears when a cell in the table is selected.
Set up the criteria range correctly
A criteria range is a small worksheet area that tells Advanced Filter which records to return. Its heading must match the corresponding list heading, and the condition goes below it. For example, if the data header is Region, use Region as the criteria heading—not a similar label such as Area. Matching the source heading exactly helps Excel identify the field. Put the criteria range outside the list so it does not overwrite or become part of the source data.
Criteria layout controls how conditions combine:
- Conditions on the same criteria row are combined as AND. A row with Region set to East and Status set to Open means both conditions must be true.
- Conditions on separate criteria rows are alternatives, or OR. One row for East and another for West returns records matching either row, subject to any other conditions on each row.
- A criteria heading with no condition beneath it does not add a useful restriction. Check for stray spaces or values in cells that you did not intend to include.
If you do not need criteria, leave the Criteria range box empty rather than selecting an unrelated block of cells. If you do use criteria, select the heading row as well as the condition rows. For criteria based on a formula, Excel’s Advanced Filter rules are more particular: the criteria heading should be different from the source field name or left blank, and the formula must evaluate to TRUE or FALSE for the first data row. If a formula criterion is involved, verify its references and test it in a normal worksheet cell first.
Fix the copy-to destination
When you select Copy to another location, check the Copy to box and the destination before running the filter. The destination heading cells should identify the fields to return, usually by using the same headings as the list. A misspelled field name or a heading that does not exist in the source can prevent the intended columns from being copied. If you want all fields, select or enter the appropriate output headings rather than assuming Excel will infer a custom selection.
Keep the destination separate from the source data and clear any old output rows that could be mistaken for the current result. In Excel for Windows, Advanced Filter’s copy-to workflow is intended for a destination on the active worksheet. If the source and destination are on different sheets, select the sheet containing the list, place the output area on that same sheet, run the filter, and then move or copy the results if necessary. Do not choose a destination that overlaps the source range.
For a first test, choose Filter the list, in-place. If that works but copying does not, the list and criteria are probably acceptable; focus on the Copy to headings, destination location, and overlap. If the in-place attempt fails too, return to the list and criteria ranges.
Run Advanced Filter step by step
- Make a backup copy of the workbook if the data is important, especially before reorganizing ranges or replacing output.
- Check the source block: one header row, no merged cells in the list, no blank header cells, and all intended records included.
- Place criteria outside the list, using the exact source field heading and the conditions below it. Leave the criteria box empty if you do not need conditions.
- Select a cell in the source list, then choose Data > Sort & Filter > Advanced.
- In the dialog, verify the List range, enter the Criteria range if applicable, and choose either Filter the list, in-place or Copy to another location.
- If copying, check the destination headings and location. Run the filter, then compare the result with a few known records to confirm the criteria behaved as intended.
On Excel for Mac, the Advanced Filter command is also available from the Data tab, though the ribbon layout can differ. If you cannot find it, widen the Excel window or inspect the Data tab’s filtering commands. Excel for the web may not expose the same Advanced Filter workflow as the desktop app; opening the workbook in Excel for Microsoft 365 on Windows or Mac is the practical option when the command is unavailable.
When the ordinary Filter command is the problem
If you are using the column drop-down arrows rather than Advanced Filter, troubleshoot the list separately. Select a cell in the data and choose Data > Filter. On Windows, Ctrl+Shift+L toggles AutoFilter; on Mac, use Data > Filter if the shortcut differs in your keyboard configuration. Ensure that the active range has a header row and that the filter arrows appear on the expected columns.
A filter that appears to omit records is often a range or data-consistency issue rather than a database error. For example, a blank row can cause Excel’s detected range to end before later records. Text values that look alike may also differ because one contains a leading or trailing space, or because some entries are stored as numbers and others as text. Dates imported from different sources can have similar inconsistencies. Check representative cells, remove unintended blank rows or columns inside the list, and use a consistent data type for each field.
Also check whether rows are already hidden manually or by another filter. Clearing a filter does not necessarily make manually hidden rows visible. Use Data > Clear to clear filter criteria, and inspect row numbers for gaps that indicate hidden rows. If the range is an Excel table, its filter arrows apply to the table; records outside the table will not be included until the table is resized.
Common mistakes and useful checks
- Including a report title as a header: Set the list range to begin at the actual column names, not a decorative heading above them.
- Selecting only the data rows: Include the field-name row in the List range.
- Using an incorrect criteria label: Copy the source heading into the criteria area to avoid spelling or spacing differences.
- Putting criteria inside the list: Move the criteria block to a separate area so it cannot be treated as another record.
- Expecting same-row criteria to mean OR: Put alternatives on separate criteria rows when the logic should be OR.
- Copying onto existing results: Use a clear, non-overlapping destination and verify the output headings.
- Assuming “database” means an external service: In this error context it commonly refers to the worksheet list range, not a server, database login, or internet connection.
Advanced Filter is useful for reusable criteria layouts and extracting selected columns, but it is not a live database query. If the source records change, rerun the filter to refresh a copied result. For ongoing analysis, a structured Excel table with ordinary filters, a PivotTable, or Power Query may be a better fit, depending on whether you need interactive filtering, summaries, or repeatable data-import and transformation steps.
Frequently Asked Questions
What does “Database or list range is not valid” mean in Excel?
It usually means Excel cannot interpret the selected source range as a valid list for Advanced Filter. Check that the range includes a proper header row and the intended records, then verify any criteria and copy-to ranges.
Why does Advanced Filter say the field name is missing or invalid?
One or more selected header cells may be blank, merged, duplicated, or outside the intended list. Use a single row of distinct field names and make sure the List range begins on that row.
Do criteria headings have to match the Excel column headings?
For ordinary Advanced Filter criteria, yes: use the corresponding list heading in the criteria range. Conditions below that heading determine which records match. Formula-based criteria follow special heading and reference rules, so check their setup separately.
Why can’t I copy Advanced Filter results to another worksheet?
The desktop Advanced Filter copy workflow expects the destination on the active worksheet. Run it to a clear area on the source sheet, then copy the extracted results to another sheet if needed.
Why are some rows missing after I use Excel Filter?
Check whether the selected range stops early, a blank row separates records, some rows are manually hidden, or the values are inconsistent. Clear filter criteria and inspect the source data and table boundaries.
Is Excel’s filter database error caused by a broken database connection?
Not usually. When the message appears in the Advanced Filter dialog, “database” generally describes the worksheet list being filtered. An external connection error would normally involve a separate import, query, or connection operation.
Related
- Excel Template for Quotation Comparison: Build One
- Excel Task List Template: Set Up and Customize Yours
- Excel Template for a Budget Plan: Build Your Own
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Excel MID Function Not Working? Causes, Errors, and FixesIf Excel’s MID function is not working, check that its arguments are in the right order, the starting…
- VLOOKUP Excel Not Working? Fix the Most Common ErrorsIf VLOOKUP in Excel is not working, first set its fourth argument to FALSE for an exact match,…
- Excel COUNTIF Not Working? Fix Formula and Criteria ErrorsIf Excel COUNTIF returns the wrong result, first check that it is looking at the intended cells and…
- Excel Filter Shortcut Keys Not Working? Fixes to TryIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off. If nothing happens,…
- Excel Not Working in Windows 11? Fix Common ProblemsIf Excel is not working in Windows 11, first restart the computer, check whether Excel opens in Safe…
- Excel Freeze Panes Not Working? Fix the ViewIf Excel Freeze Panes is not working, switch to Normal view, select the cell below and to the…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.