To filter out error rows in Excel for Microsoft 365 on Windows, select your data, choose Data > Filter, open the filter arrow for the relevant column, and deselect the error values you want to hide. For more control—especially when a row should be excluded if any of several columns contains an error—add a helper column using ISERROR, then filter that column to FALSE.
Use the column filter to hide error values
AutoFilter is the fastest option when errors appear in one column and you want to hide the rows containing them without changing the underlying cells. It changes which records are displayed; it does not remove errors, fix formulas, or delete data.
- Make sure the data has a header row and that the cells form a continuous range. Click inside the range or select it.
- On the ribbon, choose Data > Filter. Filter arrows appear in the header cells. In Excel for Windows, Ctrl+Shift+L toggles filters on or off.
- Open the arrow in the column that contains the errors. In the checklist, clear the check mark beside each error value you want to exclude, such as #N/A or #VALUE!. If needed, clear Select All first, then select the ordinary values you want to keep.
- Choose OK. Excel displays rows that match the remaining checked values and hides the others.
To show only the problem rows instead, select only the error values in the checklist. The list reflects values in that column, so the exact entries depend on the workbook. If an error type is not visible, check whether another filter is already restricting the rows; clear that filter and inspect the list again. Excel for Mac has the Data > Filter command as well. Menu layout and keyboard shortcuts can differ by Mac version and keyboard settings, so the ribbon command is a dependable route.
What filtering does—and what it does not do
A filter hides rows that do not meet the selected criteria. Hidden rows remain in the worksheet, and their formulas and values remain unchanged. Clear the column filter to reveal them again. This makes filtering useful for reviewing a clean subset or preparing a temporary view, but it is not a repair for a broken formula.
Filtering also works on whole rows, not just the error cell. If a row has #N/A in the lookup column, excluding #N/A from that column hides the entire record from the filtered view. Other columns in that row are hidden along with it. This matters when you are reviewing or exporting records: a filtered-out row has not been deleted, but a copied visible-only selection will not include it.
An error value is different from text that merely looks like an error. A genuine #DIV/0! or #REF! is an Excel error value. A cell containing the characters ‘#DIV/0!’ as ordinary text is text, so Excel may list it as a text item instead. Filtering by the visible label can be confusing if the source mixes these cases.
Use a helper column for precise control
A helper column is often the clearest method when you need to make the rule explicit, filter for more than one error type, or check a row across several columns. It also makes the result easier for another person to understand than a filter whose settings are hidden in a dropdown.
With the value to check in C2, enter =ISERROR(C2) in a new helper column on row 2, then fill the formula down. ISERROR returns TRUE for any Excel error value and FALSE for a non-error, including a blank cell. Filter the helper column to FALSE to hide rows where column C contains an error; filter it to TRUE to see only those rows. ISERROR catches #N/A as well as errors such as #VALUE!, #REF!, and #DIV/0!.
To flag a row if any cell in columns A through D contains an error, put this formula in the helper column on row 2 and fill it down: =SUMPRODUCT(--ISERROR(A2:D2))>0. The result is TRUE if at least one cell in that row is an error, and FALSE if none are. Filter for FALSE to keep rows with no errors in that range. Adjust A2:D2 to cover the columns that define a problem for your task; including unrelated columns can hide rows for errors that do not matter to your analysis.
Keep the helper column if the workbook needs a repeatable review process, or remove it after you finish. If you convert the range to an Excel table using Insert > Table, a formula entered in a table column generally fills down as a calculated column, and the table’s header filters are available for the helper results too.
Return clean rows with the FILTER function
If you want a separate, automatically updating result rather than hiding rows in the source, the FILTER function can return only rows whose chosen column is not an error. FILTER is available in Microsoft 365 and Excel 2021 or later. It returns a spilled array, so leave enough empty cells below and to the right of the formula for the results.
With records in A2:D100 and the column to test in C2:C100, enter this formula in an empty area: =FILTER(A2:D100,NOT(ISERROR(C2:C100)),"No non-error rows"). It returns each row for which the corresponding C cell is not an error. If no rows qualify, the formula displays the supplied message. The source data is not changed.
The condition only checks column C. An error in A, B, or D does not exclude that record unless you include those columns in the test. Also, FILTER can return an error in the output if a returned row contains an error in another column. To exclude a row whenever any cell in A:D is an error, you can use a row-by-row helper column instead; it is easier to audit and avoids relying on a more complex array condition.
In Excel for the web, FILTER is also available with a Microsoft 365 account, and the result spills into adjacent cells. Older Excel versions without dynamic arrays cannot use this formula as written; use AutoFilter or a helper column instead.
Filtering versus fixing or replacing errors
Choose filtering when you need to temporarily focus on valid records, investigate failures separately, or leave source calculations intact. Choose a formula repair when the error indicates that the calculation itself is wrong. For example, #DIV/0! may mean a denominator is zero or missing, while #REF! can indicate that a formula refers to a deleted cell. Hiding those rows makes the sheet look cleaner but does not resolve the cause.
IFERROR can replace an error with a chosen result, but this changes what downstream formulas and readers see. For example, wrapping a lookup in IFERROR with an empty string may make missing matches look like ordinary blanks. That can be appropriate when a missing result is expected, but it can also conceal a broken reference or an unexpected calculation problem. Decide whether an error is an expected outcome before replacing it.
Blanks are not errors. If you also want to remove blank records, use the filter checklist to deselect (Blanks), or add a separate condition to a helper formula. Do not assume that filtering out errors will automatically remove empty cells or text such as “N/A.” Those are distinct values and need their own criteria.
Common filter problems and checks
- The error remains visible: Confirm that the filter is applied to the column containing the actual error, and that the specific error value is unchecked. A similar-looking text entry may be a separate checklist item.
- Rows you expected to see are missing: Another column may still have an active filter. Open its dropdown and choose Clear Filter from [column name], or use Data > Clear to clear filters across the range.
- New rows are not included: If the filtered range was selected manually, added rows may sit outside it. Convert the range to a table with Insert > Table, or turn the filter off and reapply it to the expanded range.
- The helper column shows unexpected results: Check that formulas were filled through every data row and that the tested range covers the intended columns. ISERROR returns FALSE for blanks and text, so those will remain unless you add separate checks.
- A formula result is not refreshed as expected: Verify that Excel calculation is set to Automatic under Formulas > Calculation Options, particularly if the workbook uses manual calculation.
When copying filtered results, take care to copy visible cells rather than assuming hidden rows will be omitted in every operation. Excel provides Home > Find & Select > Go To Special > Visible cells only for selecting visible cells in a filtered range. This is especially useful before copying a filtered subset to another sheet.
Which method should you choose?
Use the built-in dropdown for a quick view when one column identifies the errors and you do not need a lasting rule. Use a helper column when the rule must check several fields, be easy to inspect, or work in older Excel versions. Use FILTER when you want a separate live result and have Microsoft 365 or Excel 2021 or later.
For a shared workbook, document what the helper column means and whether FALSE means “no errors in the checked fields” or something narrower. For a one-time cleanup, keep the original data intact until you have checked the filtered results. If you need to permanently remove records, review the selected rows first and make a backup; filtering itself is reversible, but deleting rows is not as easily undone after the workbook has been saved or further edited.
Frequently Asked Questions
Can Excel filter only error cells?
Yes. Apply Data > Filter, open the dropdown for the column, and select only the listed error values. Excel then displays rows with those errors in that column.
How do I hide #N/A but keep other errors?
Open the filter dropdown for the relevant column and deselect #N/A while leaving other error values selected as needed. If the workbook’s checklist is difficult to manage, use a helper formula that tests for the specific error type.
Does filtering errors delete the rows?
No. AutoFilter hides rows that do not match the selected criteria. Clear the filter to display them again; deleting rows is a separate action.
How do I filter out errors across an entire row?
Add a helper column with a formula such as =SUMPRODUCT(--ISERROR(A2:D2))>0, adjusted to the row’s relevant columns. Filter the helper results to FALSE to show rows with no errors in that range.
Can I filter errors without changing the original data?
Yes. AutoFilter only changes the view. The FILTER function can also return matching records in another area without altering the source, provided your Excel version supports dynamic arrays.
Related
- Excel File With Table Filters: How to Add and Use Them
- Excel File Too Large? How to Make It Smaller in Excel
- Excel File Too Large to Email? Practical Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter Multiple Items in Excel: A Clear GuideTo filter for several items in Excel, open the filter arrow in the column heading, clear Select All,…
- Excel Formula to Create a List Based on CriteriaIn Microsoft 365 or Excel 2021, use the FILTER function to create a list from rows that meet…
- Excel Filter Cells Containing Text: Steps and OptionsTo filter Excel cells that contain a particular word or phrase, select your data, choose Data > Filter,…
- Excel COUNTIF Within a Year: Date Range FormulaTo count cells with dates within a specific year in Excel, use COUNTIFS with two date criteria: =COUNTIFS(A2:A100,">="&DATE(2024,1,1),A2:A100,"=1/1/2024").…
- Excel Table Filter Out Duplicates: Keep One Copy SafelyYes—but Excel’s regular table filter does not have a built-in “show only unique rows” switch. To keep one…
- Excel Tables: Do New Rows Keep Formulas?Yes. In Excel for Microsoft 365 on Windows, a table normally carries a calculated-column formula into new rows,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.