I’m a great fan of keyboard short cuts and have used Ctrl-G, Go To, for many years. This allows you to jump to particular cells, or named ranges, so very nice for navigating. Last year Sotiris Schizas of Hellenic Petroleum, introduced me to Go To Special – and it’s a beautiful tool, that I am still learning how to leverage. One of its many useful features is to select all the cells in a worksheet that have errors in them - #REF, #NUM, #VALUE etc.
You can access Go To Special via the Home table, Editing section, as well as Ctrl-G.
Defaults |
|
Select Formula |
Untick Numbers, Text and Logicals |
| When you press OK it will jump to the first cell on the sheet with an error in it, and highlight all the others. So you can get on with repairing things. |  |
If errors are a built-in possibility you might find it useful to shelter your formulae within an IFERROR command.
=VLOOKUP(“Fred”,A1:A8,2) will return #N/A if Fred is not in the list.
But
=IFERROR(VLOOKUP(“Fred”,A1:C8,2),””) will return the value for Fred, if he is in the list, and a blank if he is not.
=VLOOKUP(“Fred”,A1:A8,2) will return #N/A if Fred is not in the list.
But
=IFERROR(VLOOKUP(“Fred”,A1:C8,2),””) will return the value for Fred, if he is in the list, and a blank if he is not.
I became a bit tired of all the clicks needed to do this, so I recorded the sequence as a macro, and saved it in my Personal macro book. Now I just have to hit Ctrl-e to jump to the first error on the current sheet. If there are no cells with errors, the macro runs for a bit then ends with “Run Time error 1004 – No Cells Were Found”. That is not very elegant, but good enough for personal use.
From Kathy's Hotel Room 16th May 2017.
Comments and suggestions gratefully received via the usual e-mail addresses or here.