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

 

Related Posts

If Cell is This Value or That Value

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of logical test. If you want to see if a cell is A or B, and if one of ...

If Value is Greater Than A Certain Value
If Value is Greater Than A Certain Value 1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the logical test result. If you want to see if a value in one cell is greater than a specific value, ...

If Cell is Not Blank
If Cell is Not Blank 6

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

VBA Macro For VLOOKUP From Another Sheet
vba macro for vlookup from another sheet1

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you how to use VBA code to vlookup data ...

If Cell is Blank
If Cell is Blank_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if a cell is blank or not, and leave some ...

If Cell Equals Certain Text String
If cell equals certain text_1

IF function is frequently used in Excel worksheet to return you expect “true value” or “false value” based on the result of created logical test. If you want to see if cell equals a certain text string like “Win”, you ...

If Cell Contains Either Text1 or Text2
If cell contains text1 or text2_1

IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to see if cell contains certain substring1 like “abc” or substring2 like “def”, and returns true ...

If Cell Contains Certain Text OR Equals Certain Text

IF cell equals certain text IF function is frequently used in Excel worksheet to return “true value” or “false value” based on the logical test result. If you want to test values to see if they equal certain text like ...

VLOOKUP From Another Sheet Not Working
vlookup from another sheet not working3

In the previous post, you should know that how to fix or remove the #N/A error when using VLOOKUP formula to lookup value from another sheet. And this post will show you reasons why your VLOOKUP formula is not working ...

If Cell Begins with One of Three Supplied Characters
If Cell Begins with One of Three Supplied Characters

If you want to test values to see if they begin with some given specific characters like “x”, ”y”, or “z”, you can create a formula with COUNTIF and SUM functions to return results. EXAMPLE You can see “TRUE” or ...

Sidebar