sodcalculator

10 Tech How-To

Excel Select Cells with Error Values: Quick Fix

Guide 10-52508 · Updated · Step by step

To select all cells containing error values in Excel, use the Go To Special feature: press F5 or Ctrl+G, click Special, choose Formulas, uncheck everything except Errors, and click OK. This instantly highlights every cell with an error like #DIV/0!, #N/A, or #VALUE! in your current selection. Below, we cover the exact steps, how to extend this to entire sheets, and how to handle errors once selected.

Why You Need to Select Error Cells

Errors in Excel are not just visual clutter—they break downstream calculations, charts, and pivot tables. A single #REF! in a SUM range can silently hide totals, and #DIV/0! errors often appear when a formula references an empty cell. Selecting all error cells at once lets you audit, fix, or delete them efficiently instead of hunting through thousands of rows manually. This is especially useful in large financial models, imported data, or sheets with many VLOOKUP formulas that return #N/A when a lookup value is missing.

How Go To Special Works

Go To Special is Excel’s built-in cell-selection engine. It can select blanks, constants, formulas, and—critically—cells that contain errors. When you choose Formulas and then check only Errors, Excel scans every cell in your current selection (which can be a range, multiple ranges, or the entire sheet) and selects only those cells whose formula result is an error value. This includes all error types: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.

The selection is dynamic—it does not filter rows or hide anything; it simply selects the offending cells. You can then type a correction, press Delete, or apply formatting to all of them at once. Note that Go To Special does not select cells that contain error values as text (e.g., a cell with the literal string “#N/A” typed as text)—it only catches cells where the formula evaluates to an error.

Step-by-Step: Select All Error Cells in a Sheet

  1. Click any single cell in the sheet (or select a specific range if you only want to check that area).
  2. Press F5 (or Ctrl+G) to open the Go To dialog.
  3. Click the Special button in the bottom-left corner.
  4. In the Go To Special dialog, select the Formulas radio button.
  5. Under Formulas, uncheck Numbers, Text, and Logicals—leave only Errors checked.
  6. Click OK. Excel selects all error cells in the current range.

If you want to select errors across the entire sheet, click any single cell first, then follow the steps. Excel treats the current region as the whole worksheet when you have a single cell selected.

Keyboard Shortcuts and Quick Alternatives

The fastest keyboard-only method is: Ctrl+G → Alt+S → O → Enter → Enter. This sequence opens Go To, selects Special, chooses Formulas, and confirms—leaving only Errors checked by default after you uncheck the others once. If you do this often, consider adding the Go To Special button to your Quick Access Toolbar (right-click the ribbon → Customize Quick Access Toolbar → choose Go To Special).

Another alternative for a quick visual scan is to use Conditional Formatting to highlight error cells with a fill color, but that does not select them—it only colors them. For actual selection, Go To Special is the only built-in method. If you need to select errors in multiple sheets at once, you must group the sheets (Ctrl+click sheet tabs) before using Go To Special, but be aware that editing grouped sheets applies changes to all selected sheets.

Common Problems and Misconceptions

“Go To Special doesn’t find my errors.” This usually happens because the errors are actually text strings, not formula results. For example, if you imported data and the cell contains the characters #N/A as text, Go To Special will not select it. To fix, you can use Find & Replace (Ctrl+H) to search for #N/A and replace with a blank, or convert text to real errors by using a formula like =NA().

“It selects the whole column instead of just errors.” If you have an entire column selected before opening Go To Special, it will scan only that column—which is correct. But if you have a merged cell or a table with hidden rows, the selection may appear larger than expected. Always check the status bar to see how many cells are selected.

“I want to select errors only in a specific column.” Select that column first (click the column header), then run Go To Special. It will limit the search to that column. Similarly, you can select a non-contiguous range by holding Ctrl while selecting multiple areas, and Go To Special will scan all of them.

What to Do After Selecting Error Cells

Once selected, you have several practical options:

  • Delete the contents – Press Delete to clear the error cells. This is useful if you want to replace them with blanks or new data.
  • Overwrite with a value – Type a number or text and press Ctrl+Enter to fill all selected cells with that value.
  • Wrap with IFERROR – Instead of deleting, you can edit the first selected cell’s formula to use IFERROR (e.g., =IFERROR(original_formula, "")) and then press Ctrl+Enter to apply to all selected cells. This preserves the formula logic but suppresses the error display.
  • Format them – Apply a red fill or bold font to make errors stand out for later review.
  • Copy them to a new sheet – Press Ctrl+C, go to a new sheet, and paste to create a list of all error locations for auditing.

Be careful when deleting errors: if the error is part of a larger formula (e.g., an intermediate calculation), deleting the cell will break the formula that references it. In that case, use IFERROR or fix the underlying cause.

Limitations and When to Use Alternatives

Go To Special has a few limitations. It cannot select errors that are conditional formatting results (those are not cell values), and it does not work on protected sheets unless you have permission. Also, if your sheet has thousands of errors, selecting them all and pressing Delete might remove data you actually need—always review the selection first.

For more advanced error handling, consider using Excel’s Error Checking tool (Formulas tab → Error Checking) which cycles through errors one by one and offers suggestions. Or use Find & Replace with #N/A to replace specific error types. For a permanent solution, use IFERROR or IFNA in your formulas to prevent errors from appearing in the first place.

Frequently Asked Questions

Q: Can I select error cells in a filtered list?
A: Yes, but only if you select the visible cells first. After applying a filter, select the range that includes hidden rows, then press Alt+; (Go To Visible Cells Only) before using Go To Special. Otherwise, Go To Special will include hidden rows in the selection.

Q: How do I select only #N/A errors, not other errors?
A: Go To Special selects all error types at once. To filter specific errors, use Find (Ctrl+F), type #N/A in the Find what box, and click Find All. Then select all results from the list (Ctrl+A in the dialog) and close. This selects only cells containing that exact error text.

Q: Why does Go To Special not select errors in a table?
A: It does, but if the table has structured references, the error may be in a calculated column. Make sure you have selected the table data range (not just the header). If the table is in a separate sheet, you need to be on that sheet when you run Go To Special.

Q: Can I select error cells in Excel Online or Google Sheets?
A: Excel Online does not have Go To Special. In Excel Online, you can use Find (Ctrl+F) and search for # to find most errors, but it will also match text. Google Sheets has a similar feature: use Find and replace (Ctrl+H) and search for #N/A to see matches. There is no one-click “select all errors” in either web app.

Q: What if I want to select errors in a specific row only?
A: Select that row first (click the row number), then run Go To Special. It will scan only the selected cells in that row.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →