Google Sheets is extremely user-friendly and reliable but if you use it regularly, <\/span>you will eventually run into an error<\/b>. This can happen for a variety of reasons, which we\u2019ll explore in this article.<\/span><\/p>\n Google Sheets error handling is a fundamental concept<\/b> when creating spreadsheets. And the IFERROR formula is the best tool for the job.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”common” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n These errors show up whenever a formula breaks. Understanding them is the first step in choosing whether to fix them or to hide them with IFERROR.<\/span><\/p>\n This is probably the most frequent type of error in Google Sheets. It occurs when you use an incorrect data type for the expected input arguments, as illustrated below.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-1.png” alt=”IFERROR GOOGLE SHEETS 1″ title_text=”IFERROR GOOGLE SHEETS 1″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Usually, this shows up when you intentionally or accidentally delete rows, columns, or sheets that are referenced in other cells.<\/span><\/p>\n If I remove the <\/span>Operand A<\/b> column from the previous example, a #REF error pops up as shown below.<\/span><\/p>\n Notice that the #REF! error even appears inside the formula, indicating that it lost a reference in that placeholder. Learn more about this particular issue in <\/span>How to solve the #REF! Error in spreadsheets<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-2.png” alt=”IFERROR GOOGLE SHEETS 2″ title_text=”IFERROR GOOGLE SHEETS 2″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Google Sheets generates this error when it comes across a function that it doesn\u2019t recognize.<\/p>\n To demonstrate that further, I\u2019ll try using WHATIF (which is not an actual function) instead of IF.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-3.png” alt=”IFERROR GOOGLE SHEETS 3″ title_text=”IFERROR GOOGLE SHEETS 3″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When the expected input arguments do not match with what you provide, the num error might show up. Take a look at the first example in the snapshot below.<\/p>\n A negative number is not expected in the SQRT function, hence the error.<\/p>\n This error can also appear when the number to display is out of range, as seen in the second example where the result is too large for the spreadsheet to compute.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-4.png” alt=”IFERROR GOOGLE SHEETS 4″ title_text=”IFERROR GOOGLE SHEETS 4″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is another common error usually associated with lookup functions when they cannot find a match for the value they are asked to find.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-5.png” alt=”IFERROR GOOGLE SHEETS 5″ title_text=”IFERROR GOOGLE SHEETS 5″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”how” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you are using a function and it keeps returning errors, you can \u201cwrap\u201d the formula with IFERROR. This will give you a cleaner spreadsheet with data that\u2019s easier to read.<\/span><\/p>\n It can be used with many common functions including <\/span>Query<\/span><\/a> and <\/span>Vlookup<\/span><\/a>.<\/span><\/p>\n IFERROR works by scanning a formula\u2019s output for errors. If it finds one, it replaces it with any value you specify. This function is usually used to hide errors following the logic \u201cif error return blank\u201d. If the formula has no error, it simply returns the intended output.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IFERROR(value, [value_if_error])<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_id=”how_to” _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the first example below, you can see the effect of adding IFERROR to the beginning of the function.<\/span><\/p>\n I\u2019m working with the #N\/A! error that I showed previously. As you\u2019ll recall, this appears when a lookup function can\u2019t find the input it\u2019s searching for.<\/span><\/p>\n Here, I\u2019ve added the IFERROR function before the VLOOKUP.<\/span><\/p>\n If there is no error, the IFERROR function simply outputs the VLOOKUP output that it would normally generate.<\/span><\/p>\n If there is an error, however, instead of simply displaying an error message, I ask it to display the text \u201cinvalid input\u201d. This enables you to avoid ugly and unexplained error messages in your spreadsheet.<\/span><\/p>\n In the example below, I\u2019ve done this with the following formula:<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =Iferror(Vlookup(“Stephen”,A2:B8,2,False),”Invalid input”)<\/strong>.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-6.png” alt=”IFERROR GOOGLE SHEETS 6″ title_text=”IFERROR GOOGLE SHEETS 6″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n In the following example, we\u2019ll see how to make #div\/0 show as blank.<\/span><\/p>\n As you might have guessed, this occurs when you divide a number by 0.<\/span><\/p>\n This is quite common when you have a function that you drag down to apply to a group of cells, and some of the cells are missing data or contain zeros.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-7.png” alt=”IFERROR GOOGLE SHEETS 7″ title_text=”IFERROR GOOGLE SHEETS 7″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now, I\u2019ll modify the formula to include the IFERROR function.<\/p>\n As you can see below, this gets rid of the error message.<\/p>\n By adding IFERROR to the front of the formula, it simply returns a blank cell.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/IFERROR-GOOGLE-SHEETS-8.png” alt=”IFERROR GOOGLE SHEETS 8″ title_text=”IFERROR GOOGLE SHEETS 8″ module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Yes, you can use IFERROR with arrays <\/b>by using the array formula function<\/a>. To do it but you must add it within the formula as shown below.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =Arrayformula(iferror(E4:E\/B3:B.””))<\/p>\n [\/et_pb_text][et_pb_text module_id=”a” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you\u2019ve seen, the IFERROR function is great for masking error warnings and replacing them with clean, blank cells or a meaningful message or value.<\/p>\n Be careful, however, with when and where you apply this function. Hiding all errors can make it difficult to troubleshoot<\/strong> a spreadsheet when the error is affecting functionality. For instance, it doesn\u2019t make sense to mask a #REF! error, because you need to know which cell reference is lost in order to fix the error and keep your spreadsheet working properly!<\/p>\n If you found this article helpful, please share it with your colleagues and fellow spreadsheet-users via the social media buttons on the left! For more Google Sheets tips, check out our other articles below or our 5 best tips for structuring your spreadsheet data<\/a>.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" Instead of displaying ugly error messages, IFERROR enables you to clean up your spreadsheet and replace errors with other values. This could be a blank cell, a number, or a relevant message that you and your team can understand. Google Sheets is extremely user-friendly and reliable but if you use it regularly, you will eventually […]<\/p>\n","protected":false},"author":39,"featured_media":27679,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Google Sheets is built in such a way that the expressions return appropriate values in accordance with the given inputs. However, more often than not, we may encounter certain unpleasant errors. This happens due to one or more reasons as explained in this article. To handle such errors, we have the IFERROR<\/a> formula.\nCommon errors in Google Sheets<\/h2>\n
The #VALUE! error<\/strong><\/h3>\n
#REF! error<\/h3>\n
#NAME! error<\/h3>\n
#NUM! error<\/h3>\n
#N\/A! error<\/h3>\n
How IFERROR works<\/h2>\n
Handle Google Sheets errors with the IFERROR function<\/h3>\n
IFERROR\u00a0Syntax<\/h3>\n
\n
How to use IFERROR<\/strong><\/h2>\n
How to get rid of n\/a<\/span><\/h3>\n
How to get rid of div\/0<\/span><\/span><\/h3>\n
Can you use IFERROR with arrays?<\/span><\/h3>\n
<\/h3>\n
A note about hiding errors<\/h3>\n
Did you like this post?<\/h2>\n
Errors? How do they look like?<\/h3>\n
The #VALUE! error<\/h5>\nPerhaps, this is the most frequent error type. It occurs when we use an incorrect data type for the expected input arguments as illustrated below.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror1.png\")
\nThe #REF! error<\/h5>\nUsually, this springs up when we intentionally or accidentally delete rows, columns or worksheets, that are referenced in other cells. From the above examples, let's try removing the \"Operand A\" column, and this is what happens. Notice that even within the formula, the #REF! shows, indicating it lost a reference in that placeholder.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror2.png\")
\nThe #NAME! error<\/h5>\nGoogle Sheets throws this up when it sees any function that it doesn't recognize. To understand that further, let's try by using WHATIF, instead of IF in the third row.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror3.png\")
\nThe #NUM! error<\/h5>\nWhen the expected input arguments do not match with what we provide, this might show up. For instance, consider the first example in the snapshot below. A negative number is not expected in the SQRT function, hence the error. This can also surface when the number to display is out of range, as shown in the snapshot below.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror4.png\")
\nThe #N\/A! error<\/h5>\nThis is another common error usually associated with the lookup functions when they cannot find a match for the value they are asked to find.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror5.png\")
\n\nWe can give these errors a meaningful facet, using the handy IFERROR formula.\nSyntax<\/h3>\nIFERROR(value, [value_if_error])<\/strong><\/span>\n
\n \t
Examples: IFERROR formula<\/h3>\nGiven below are a few use cases where we put this formula to use.\n\n
![\"IFERROR](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/iferror6.png\")
\n\nTherefore, as we can see, the IFERROR formula is used to mask errors with rather meaningful values. But it isn't ideal to mask every error - doing that will make it difficult to troubleshoot. For instance, it doesn't really make sense to mask #REF! error, because we need to be aware which cell reference is lost.\n\nRead the blog post below to learn more about a similar formula \"How to solve the #REF! Error on spreadsheets<\/a><\/strong>\"","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-3066","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3066","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/39"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=3066"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/3066\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/27679"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=3066"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=3066"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=3066"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}