0

For Excel for Windows, there's an option to search a sheet for errors and go straight to them, under the "Find and select" icon in the ribbon.

Excel for Mac 2011 doesn't have a "Find and select" ribbon icon.

On a sheet without thousands of cells, how can you scan it for errors?

1

Depending on what your workbook's displaying,

Edit > Find [#] (i.e. enter the hash sign # as your target text) Within: [Workbook] Search: [By Rows] Look in: [Values]

screen cap of Find dialog

may be less of a pain. In most of my workbooks, the only cells displaying the hash sign are error messages, and as far as I know, all Excel error values (#DIV/0, #NAME?, #REF! etc.) display text starting with #. Even if you have dozens of hash signs in non-error cells, this will be a lot faster than having to wade through the Edit > Go To… dialog on every sheet.

If you want to search for errors without recalculating, you may have cells which display a calculated value that's destined to turn into an error. For example, enter =2+C4, then delete column C. If recalculation is set to manual, the cell will continue to show an unchanged value, but the formula will become =2+#REF! You can find cells in this state by changing the search parameter to Within: Formulas. This can stop recalculation from propagating a cascade of #REF! errors that takes a long while to unravel.

Hope this helps.

0

It's similar to in Windows, but slightly more buried. Edit > Go To..., then click Special, then select Errors under Formulas. Then look for the selected cell in the bottom left of the screen.

enter image description here enter image description here

Annoyingly there doesn't seem to be any "Go to next" option and it forgets the settings each time, so you need to do loads of clicks each error. If you need to check for erros often, might be worth trying to make a macro (not sure if this is possible).

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .