Excel’s built-in filters hide rows that do not match, so you cannot use a standard table filter to show only matching records while keeping every source row visible. To achieve that result, leave the source table untouched and show matching records elsewhere with the FILTER function or Excel’s Advanced Filter set to copy results to another location.
What Excel filtering does to rows
When you filter a table or range with the dropdown arrows in its header, Excel temporarily hides rows that fail the selected conditions. The records are still in the workbook: clearing the filter makes them visible again, and filtering does not delete them. But they are not displayed in their original positions while the filter is active. That behavior is part of how AutoFilter works, not a setting that can be switched to “filter without hiding.”
This distinction matters when a worksheet is being used as a fixed report, a form, or a carefully arranged schedule. Hidden rows can make the sheet look as though information has disappeared, and formulas or charts may treat hidden records differently depending on their design. If the requirement is literally that every row stays visible, use a separate results area or a separate worksheet rather than filtering the source range.
It is possible to make nonmatching records less prominent with formatting, but that is not the same as filtering. The rows remain in place, and readers can still see them. For a clean list containing only matches, a separate output is usually clearer.
Best option for a live results list: FILTER
In Microsoft 365 for Windows, the FILTER function creates a dynamic results range from a source range or Excel table. The original rows remain visible, while the formula returns only records that meet the condition in a different area. When the source data or condition changes, the returned list updates automatically. FILTER is available in Microsoft 365 and Excel 2021 or later; it is not available in older perpetual versions such as Excel 2019.
For example, suppose an Excel table named Table1 has columns named Order, Region, and Sales. Put the region to search for in cell G2. With those fields in Table1 and G2 containing West, enter this formula in an empty area outside the table:
With the source records in Table1 and the requested region in G2, use =FILTER(Table1,Table1[Region]=G2). If three source records have Region set to West, the formula returns those three records, across all the table’s columns; none of the source rows is hidden.
The formula’s output is called a spilled array: Excel fills the neighboring cells needed for the results. The output area must be clear, and the formula cannot be placed inside an Excel table. The table’s column headers are not included in the returned data, so add or copy headers above the formula output if readers need them. Keep the output far enough from other worksheet content to leave room for the list to grow.
Filter by more than one condition
FILTER can combine conditions. Multiplying two logical tests means both must be true, which works like AND. For instance, a sales list might need to show orders from one region that are also open. Set the requested region in G2 and the requested status in H2.
With Table1 containing Region and Status columns, G2 set to West, and H2 set to Open, use =FILTER(Table1,(Table1[Region]=G2)*(Table1[Status]=H2)). If two records meet both conditions, the formula returns those two complete records. A record that is West but Closed will not appear.
For an OR condition—records matching either of two tests—add the logical tests instead of multiplying them. Whichever approach you use, make the criteria cells easy to identify and label them. A coworker can then change the criteria without editing the formula. If no record matches, FILTER returns an error unless you provide its optional third argument for an alternate result; choose a message that suits the report and make sure it will not be mistaken for a record.
Use Advanced Filter to copy a snapshot
Advanced Filter is useful when you want a one-time copy of matching records rather than a live formula result. It leaves the source rows in place and copies matching records to another location. The copied list does not automatically refresh when the source data changes, so run Advanced Filter again when you need an updated snapshot.
- Set up the source data with a single header row and no blank header names. Create a criteria range elsewhere on the sheet using the exact header name of the field you want to test. Put the condition beneath that header. For example, a Region header with West directly below it selects West records.
- Select a cell in the source list. On the Windows desktop version, choose Data > Sort & Filter > Advanced.
- In the Advanced Filter dialog, choose Copy to another location. Check that the List range includes the source headers, set the Criteria range to the small criteria area, and set Copy to to a blank destination area.
- Choose OK. Check that the copied results have the fields you need and that the source rows are still visible.
To set two conditions that must both be true, put them on the same criteria row under their respective headers. To allow either of two conditions, put the criteria on separate rows. Advanced Filter has more criteria-range rules for comparisons and formula-based criteria, so check the dialog’s Help if the criteria are more complex than a simple exact match. Excel for the web does not offer the same full Advanced Filter workflow as the Windows desktop application; use a formula or open the workbook in desktop Excel if you need this method.
Choose between a formula and a copied list
The right method depends on whether the result should stay current and how the worksheet will be used. A dynamic formula is generally convenient for a working dashboard or a report whose criteria change. Advanced Filter is practical for producing a dated extract that should not change until someone deliberately creates a new copy.
- Choose FILTER when the output should respond to changed source data or criteria, and your Excel version supports dynamic arrays.
- Choose Advanced Filter when you need a fixed copy and are working in desktop Excel.
- Use a separate worksheet when readers need a clean view but the original data should remain available for auditing or editing.
- Use the regular table filter when hiding nonmatching rows is acceptable and you want to inspect the source table directly.
A copied output and a live output should not be confused. Advanced Filter produces a snapshot; a FILTER formula recalculates. Neither method alters the original records just by displaying selected ones, but people can still edit or delete the source data if the worksheet is not protected. If the output will be shared, label it “Snapshot” or “Live results” so readers know what they are looking at.
Other ways to keep the source view intact
For a short list, sorting the data to put relevant records together may be enough, but sorting changes row order and does not remove nonmatching records from view. Conditional formatting can highlight matching rows while leaving all rows visible. You can apply a rule to a range based on a value, or add a helper column that marks records as “Match” and filter a separate output from that marker. These approaches are useful when the goal is emphasis rather than a compact list.
Another option is to place the source data on one worksheet and the report on another. That separation makes it clear which area is the editable source and which is the presentation. A formula on the report sheet can refer to the source table, or a copied list can be refreshed manually. If users need independent ways to explore the same source, Excel’s Sheet View feature can help prevent one person’s view choices from disrupting another’s. A Sheet View still uses filtering behavior; it does not make a filtered table display every hidden row.
Do not treat grouping or hiding columns as a substitute for filtering. Grouping can collapse rows, and manually hiding rows also removes them from view. Those features do not create a visible, filtered list while retaining every record on screen.
Common problems and fixes
- The FILTER formula shows #SPILL!: Clear cells in the intended output area, including cells that look blank but contain spaces or formulas. A merged cell in the spill area can also prevent the results from appearing.
- The formula returns unexpected matches: Check the spelling of the table’s column name and the value in the criteria cell. Extra spaces or inconsistent source values can cause exact comparisons to miss records.
- The output does not include headings: Dynamic array results return matching data rows, not the table’s header row. Add headers separately above the formula output.
- New records are missing from Advanced Filter results: The copied list is a snapshot. Run the operation again, and verify that the List range includes the new source rows.
- Advanced Filter rejects the criteria: Make sure the criteria header exactly matches a source header. For basic criteria, put the condition below the header and ensure the selected criteria range includes both cells.
- The source still looks filtered: Clear the table’s filter using its header dropdown or the Data tab’s Clear command. A separate results formula does not automatically clear an existing filter on the source table.
When formulas are copied to other workbooks, structured table references can be easier to read than fixed cell ranges, but the table name and column headers must exist in the workbook. If you use a plain range instead, make sure the source range covers likely new records or convert the data to an Excel table first. Tables expand when new records are entered directly below them, which helps keep structured references aligned with the source.
Frequently Asked Questions
Can I filter an Excel table without hiding rows?
No. A normal table filter hides rows that do not match. Use FILTER to display matching records in a separate area, or use Advanced Filter’s Copy to another location option.
Does FILTER remove or change the original records?
No. It returns a separate result array and does not delete or move the source rows. Editing the source can change the returned results because the formula recalculates.
Can I use FILTER inside an Excel table?
No. Dynamic array formulas spill into neighboring cells, and spilled formulas cannot spill from inside a table. Enter the formula in a blank worksheet area outside the table.
Will an Advanced Filter copy update when the source changes?
No. It creates a static copy. Run Advanced Filter again to produce a refreshed result, or use a dynamic formula when automatic updates are important.
Can I keep all rows visible and still identify matches?
Yes. Use conditional formatting or a helper column to mark matching records. That keeps rows visible, but it does not create a list containing only matches; use a separate output for that.
Which approach to use
If “without hiding rows” means that the original data must remain visible, avoid applying AutoFilter to that source table. Use FILTER for a live, criteria-driven list in Microsoft 365 or Excel 2021 and later, or Advanced Filter to create a deliberate snapshot in desktop Excel. If you only need to draw attention to matching records, highlighting them in place is simpler. Keeping the source and results separate makes the behavior understandable and reduces the chance that someone mistakes hidden records for missing data.
Related
- Excel Data Validation List: Build a Dynamic Source
- Excel Data Validation List Based on a Cell: How It Works
- Excel Data Validation List: Keep Dropdowns in A-to-Z Order
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Is INDIRECT Volatile in Excel? Performance ExplainedYes. INDIRECT is a volatile Excel function, so it recalculates whenever Excel recalculates, even when the cells named…
- Excel Conditional Formatting If: How Rules WorkIn Excel, conditional formatting applies a format when a cell or formula meets a condition—in effect, an “if…
- Excel Filters: How to Sort and Show the Rows You NeedTo filter data in Excel for Microsoft 365 on Windows, select a cell in your data, choose Data…
- COUNTIF Function Examples in ExcelIn Excel for Microsoft 365, COUNTIF counts cells in a range that satisfy one condition. With statuses in…
- Excel SUM: How to Add Numbers with a FormulaTo add numbers in Excel, select the cell where you want the total, type a SUM formula, and…
- Excel Formula to Shuffle Names RandomlyTo shuffle names in Excel for Microsoft 365, enter this formula in an empty cell: =SORTBY(A2:A20,RANDARRAY(ROWS(A2:A20))). It returns…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.