How to Select All Error Value Cells in Excel

Sometimes we may get error values like #N/A or #NAME? in cells after applying formulas with errors. We can through pressing Ctrl key and clicking on each error value on worksheet to select all of them one by one. This way is time consuming and boring. So, if we want to select all of them at one time and clear them or replace them with comments, how can we do? This article will introduce you to select error value cells at the same time in a convenient way.

Precondition:

See screenshot below. We get some errors when applying VLOOKUP function to look up levels for people in table 1 refer to the source in table 2. We want to select all error cells now.

How to Select All Error Value Cells 1

Method: Select All Error Value Cells Via Go To Special Function


Step 1: Select whole table 1 in worksheet.

Step 2: Click Home in the ribbon, then click the small triangle belongs to Find & Select icon in Editing group, then select Go To Special option.

How to Select All Error Value Cells 2

Then Go To Special dialog displays.

How to Select All Error Value Cells 3

In this step, you can also press hotkey F5 directly to trigger Go To dialog, then click on Special button on this dialog to load Go To Special dialog.

How to Select All Error Value Cells 4

Step 3: On Go To Special dialog, check on Formulas to enable Errors option, check on Errors, then click OK.

How to Select All Error Value Cells 5

Verify that all error value cells are selected.

How to Select All Error Value Cells 6

If you want to clear errors or update comments, see below steps. If you just want to select all error value cells, you can ignore below steps.

Step 4: Replace error with ‘Not Found’. Directly enter ‘Not Found’ in the first found error cell and then press Ctrl + Enter simultaneously. Then all errors are replaced with ‘Not Found’.

How to Select All Error Value Cells 7

Step 5: Clear error values. Directly press Backspace to delete error value in the first found error cell and then press Ctrl + Enter simultaneously. Then all errors are cleared.

How to Select All Error Value Cells 8

 

Sidebar