0

For complex Google Sheets with many tabs (my teams effectively employ Google Sheets as a sort of database in these scenarios), is there a relatively-easy way to search for any and all formula errors (across all tabs) like these errors without having to create a script?

I'm thinking something like a Find and replace option that offers a Find formula errors checkbox.

The purpose: when adjusting formulas, named ranges, and the like: it's quite cumbersome for a spreadsheet designer/maintainer to manually look through all formulas in all tabs to find if anything is broken. We'd rather employ some automated-find-any-errors mechanism to avoid such efforts.

I've performed non-trivial web searches and searched all the features I could find within Google Sheets, and I see nothing like this feature. The only other option I see is writing a significant, embedded script or other automation, which is what we're looking to specifically avoid.

Improve this question
5
  • Please add a brief description of your search/research efforts as is suggested in How to Ask.
    – Rubén ♦
    Nov 29, 2020 at 20:26
  • I spent roughly 20 mins web searching and all I found was references to iserror() and iferror()-like questions and answers. I found ZERO reference to any functionality like I describe above. @Rubén - does this background meet the mark/requirements?
    – Johnny Utahh
    Nov 30, 2020 at 2:44
  • You could also mention what you have tried so far (add all of this to the question, by the way, have you already look at the Google Sheets menus, functions list and help articles?).
    – Rubén ♦
    Nov 30, 2020 at 2:52
  • @Rubén - I appreciate you're applying SE policies here. However: I've been posting tech question for decades. I believe I've supplied sufficient background for all your points for anyone who has any appropriate experience with Google Sheets or Microsoft Excel. Let's leave it be. If there's a real problem, then let's point it out. Otherwise, I feel adding more background then I've already done (which imo is significant) makes the question unnecessarily long - which per my decades of experience dissuades people from reading the question. Regardless: I sincerely appreciate your efforts.
    – Johnny Utahh
    Nov 30, 2020 at 16:43
  • I just made a suggestion, no downvote, no close vote. It is up to you to take the suggestion or not. Any way, have you already tried to use find and replace to search for #N/A, #VALUE!, etc.? How about using adding an auxiiary sheet togehter with IFERROR, IFNA?
    – Rubén ♦
    Nov 30, 2020 at 16:47

1 Answer 1

Reset to default
2

Google Sheets doesn't include that as a built-in feature. Please send a feature request direclty to Google through the Google Sheets Help menu

At this time the menu shows "Help Sheets to improve".

It's worthy to mention that FIND AND REPLACE can highlight / navigate through the cells diplaying an error, i.e. #N/A

You could even use regular expressions to search for multiple errors.

Improve this answer
1
  • 1
    Super helpful, thanks @Rubén !
    – Johnny Utahh
    Nov 30, 2020 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.