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.
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.
Then Go To Special dialog displays.
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.
Step 3: On Go To Special dialog, check on Formulas to enable Errors option, check on Errors, then click OK.
Verify that all error value cells are selected.
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’.
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.
