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

Excel/Google Sheets: Full Row Reference

Excel or Google sheets supports both full row references and full column references. This article will talk about the usage of full row references. Full Row reference is another cell reference in Excel or Google Sheets, which is used to ...

Excel/Google Sheets: Full Column Reference

Full column reference is another cell reference in Excel or Google Sheets, which is used to reference the entire column, for example, if you want to reference the entire column A, you can use the following reference format A: A. ...

Excel/Google Sheets: Expanding Reference

An expanding reference is a reference type in Excel and Google worksheets that extends the range of a cell when a formula is copied to the cell below or to the right. In this article, we will explain how to ...

Excel/Google Sheets: Mixed Reference

In Microsoft Excel Spreadsheet or Google sheets, there is another cell reference, mixed references, where part of the reference is absolute, part of the relative. This article will describe how to use mixed references through specific examples. Mixed Reference When ...

Excel/Google Sheets: Absolute Reference

In Microsoft Excel spreadsheet or Google Sheets, the cell reference is the cell or cell area address or name in the worksheet, the cell reference contains two types of references, namely, relative references and absolute references. Absolute Reference An absolute ...

Sidebar