Excel Select Cells with Error Information

To select all cells in an Excel worksheet that contain errors (like #DIV/0!, #N/A, #VALUE!), use the Go To Special feature: press Ctrl+G (or F5), click Special…, choose Formulas, uncheck everything except Errors, and click OK. This instantly highlights every error cell in the current selection, and you can then clear, inspect, or format them all at once.

Why You Need to Find Error Cells

Spreadsheets grow organically. A formula that worked yesterday breaks today because a source cell was deleted, a lookup table changed, or a divisor became zero. Error values propagate: one bad cell can make dozens of dependent formulas show errors, masking the real issue. Manually scrolling through thousands of rows looking for red or green indicators is slow and unreliable. The Go To Special method is built into Excel precisely for this task, and it works in every modern version (2010, 2013, 2016, 2019, 2021, and Microsoft 365) on both Windows and Mac.

How Go To Special Works

The feature scans the selected range and identifies cells whose value is an error constant or whose formula result is an error. It does not care about cell formatting, font color, or conditional formatting—it looks purely at the computed value. When you choose Formulas > Errors, Excel selects every cell that currently evaluates to one of the seven error types:

• #DIV/0! – division by zero or empty cell
• #N/A – value not available (common with VLOOKUP/XLOOKUP)
• #NAME? – unrecognized text in a formula
• #NULL! – intersection of two ranges that do not overlap
• #NUM! – invalid numeric value
• #REF! – reference to a deleted cell or sheet
• #VALUE! – wrong data type in an argument

Note that #SPILL! (dynamic array spill range blocked) and #CALC! (array calculation error) are also detected in recent Excel versions, though they are not always listed in older documentation.

Step-by-Step: Select All Error Cells

1. Select the range you want to check. If you want the entire worksheet, click the triangle at the top-left corner (where row numbers meet column letters) or press Ctrl+A once (twice if the cursor is inside a used range).
2. Press F5 (or Ctrl+G) to open the Go To dialog.
3. Click Special… at the bottom-left.
4. In the Go To Special dialog, select Formulas.
5. Under the Formulas option, uncheck Numbers, Text, and Logicals—leave only Errors checked.
6. Click OK. Excel will select all cells that currently contain an error value.

If no errors exist, Excel shows a message: “No cells were found.� If your selection includes hidden rows or columns, Go To Special still finds them, but it will not select cells in hidden rows/columns unless you unhide them first—this is a common gotcha.

What to Do After Selecting Error Cells

Once the error cells are selected, you have several options. The most obvious is to press Delete to clear the contents, but that often creates more problems—deleting a formula is rarely the fix. Instead, consider:

• Inspect the formula: Look at the formula bar to see which reference is broken. Use Trace Precedents (Formulas tab > Trace Precedents) to see which cells feed into it.
• Wrap with IFERROR: If the error is expected (e.g., a lookup that might not find a match), you can wrap the formula in =IFERROR(original_formula, "") to display a blank or a custom message. But do this only after you understand the cause—masking errors can hide data problems.
• Format them: Apply a fill color or font color to make error cells stand out permanently. Select the error cells, go to Home > Fill Color, and choose a light red. This helps you spot them later even after you fix the underlying issue.
• Copy them elsewhere: With error cells selected, press Ctrl+C, then paste to a new sheet to create a list of problem locations. Excel will paste the error values themselves, which you can then use to audit.

If you only want to select errors in a specific column or a small block, select that range first, then run the same Go To Special steps—it will only search within your selection.

Common Problems and Misconceptions

One frequent misunderstanding is that Go To Special > Errors will find cells with conditional formatting that looks like an error (e.g., red text). It will not—it only finds actual error values. Another issue: if your worksheet has many errors, the selection might include thousands of cells, and pressing Delete will wipe all formulas, not just the errors. Always check the formula bar before deleting. Also, Go To Special does not work on protected sheets—unprotect the sheet first (Review > Unprotect Sheet). And on Mac, the shortcut is Control+G (not Command+G, which opens the search bar).

Alternatives to Go To Special

If you prefer a more visual approach, you can use Find & Select (Home tab > Find & Select > Go To Special…), which is the same dialog. Another method is to use the Formulas tab > Error Checking button—this cycles through errors one by one, which is useful when you want to fix them individually rather than select them all. For a permanent overview, you can add a column that checks for errors with =ISERROR(A1) and then filter on TRUE. That approach is handy when you want to keep the error list visible without re-running Go To Special every time.

Keyboard Shortcuts and Efficiency Tips

Once you memorize the sequence, it takes about three seconds: F5 → Alt+S → Alt+E → Enter (the Alt shortcuts work in the dialog). On a laptop without a dedicated F5 key, use Ctrl+G. If you find yourself doing this often, you can add the Go To Special command to the Quick Access Toolbar: right-click the dialog command (or use File > Options > Quick Access Toolbar, search for “Go To Special�, add it). Then it is one click away. For very large datasets, selecting all errors across 100,000 rows works fine, but the selection may be slow to render—be patient.

Frequently Asked Questions

Q: Why doesn’t Go To Special find my errors?
A: The most common reasons are: the cells are in hidden rows/columns (unhide first), the sheet is protected, or the errors are actually text strings that look like errors (e.g., a cell containing the text “#N/A� as a string, not a formula result). Go To Special only finds real error values, not text.

Q: Can I select all error cells in multiple sheets at once?
A: No, Go To Special works only on the current selection in the active sheet. To do multiple sheets, you must run it on each sheet individually. A VBA macro can loop through sheets, but that is beyond the built-in feature.

Q: How do I count how many error cells exist?
A: After selecting them with Go To Special, look at the status bar—it shows “Count: Nâ€� while multiple cells are selected. Alternatively, use =SUMPRODUCT(--ISERROR(range)) to count errors in a specific range without selecting anything.

Q: What is the difference between Go To Special > Errors and the Error Checking button?
A: Go To Special selects all error cells at once, letting you bulk-handle them. Error Checking (Formulas tab) walks through each error one by one, showing a dialog with options like “Edit in Formula Bar� or “Ignore Error.� Use the former for a quick audit, the latter for careful review.

Q: Does this method work in Google Sheets?
A: No, Google Sheets does not have an exact equivalent. You can use the “Filter by conditionâ€� on a helper column with =ISERROR(A1), or use the “Findâ€� feature (Ctrl+H) with the error text, but it will not select all error values in one step.

Related

• Spotify Premium Offline Not Working: Android & iPhone Fixes
• MIUI Quick Settings on Any Android: How-To
• Pin Programs to Start Menu in Windows 10