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?

Improve this question

2 Answers 2

Reset to default
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.

Improve this answer
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).

Improve this answer
1
  • Belatedly, when I 'go to' Errors, all errors on the sheet are selected with the first one being active. You can edit each cell and tab to the next selected cell and cycle through them all that way. You could also do something like colour them all yellow so you can easily identify them all. Finally, Tools > Error Checking… allows you to prev/next through each error cell. While this is eight years late, just adding it in case someone else has this issue.
    – Mockman
    Commented Oct 5, 2022 at 6:17

You must log in to answer this question.

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