Google Sheets comes with plenty of useful features. If you love working in Excel, you’re sure to enjoy using some of these advanced options too.
You can perform different mathematical operations in Google Sheets, but sometimes it’ll return an error to you.
No one is happy to see an error code on their screen when they’re in the middle of work, but don’t worry. In some cases, the cause can be as simple as a typo.
For instance, if you see the #DIV/0 error, there’s a reason why.
To find out what it is, read our article.
Dividing a Number by Zero
When does the #DIV/0 occur? The Google Sheet formula can’t display a result if you’re trying to divide a number by zero. It’s also called the division error. You’ll see this code within the cell where there should be a result, and a message explaining that the function parameter two cannot be zero.
However, depending on your tasks, dividing by zero may sometimes be necessary.
But you probably don’t want this error to be displayed in your report. If you need to print it or submit it to someone to check it, it may look too messy. Luckily, you can cover up the error code by using the IFERROR function.
This function will go through your spreadsheet and identify errors. When you set up a replacement value for those errors, the function will display that value instead. You can set anything as an alternative. And if you don’t choose one, the formula will return a blank.
For instance, you can assign the “N/A” value to replace the error result, and that’s what you’ll see when the formula’s applied. It works much better than an error code that the person reading the report may not understand.
In some cases, a blank space or a zero may work even better. Another option is to enter a whole word, such as Error, to be displayed as the value of the formula. It’s up to you to choose what suits best. Whatever you pick, you won’t be seeing the error code anymore, and your problem is solved.
How to Use the IFERROR Formula
Never used the IFERROR formula before?
Here’s how you can apply it to your spreadsheet. It’s quite a simple one, and it’s worth remembering it since errors are quite common in Google Sheets.
The formula should look like this: IFERROR (value, [value_if_error])
The first “value” represents the element you’re testing – a formula or a cell reference. Value_if_error is what you’ll see on the screen if the formula identifies an error.
What Are Some Other Common Errors in Google Sheets?
While Google Sheets is an excellent organizational tool that can save you time, there are a few errors that you may encounter while applying different formulas. Whether you’ve just started out using this program or you’re already experienced, it’s bound to happen at some point.
Apart from the #DIV/0 error, you may also see the following.
- #N/A Error Message: This means that the value you’re looking for isn’t available. You may get this message if you’re using the search bar, and what you’ve entered doesn’t exist in the spreadsheet. You can correct it by using the IFERROR formula, so for example, it will display “Not found” instead of the error message.
- #VALUE! Error Message: You may receive this message when you enter the wrong data type into a cell. It often occurs when you enter text instead of numbers when trying to perform a math operation.
- You should also avoid entering spaces in cells to prevent your spreadsheet from displaying this message. Sometimes it’s also caused by you mixing up two types of date formats. Usually, double-checking the data fixes this error in no time.
- #REF! Error Message: If you’re missing a reference, you will probably see this message on your screen. You may be trying to reference a deleted one, especially when you delete a whole column or a row.
- #NAME? Error Message: This error indicates an issue in your formula syntax. Check if you’ve misspelled a function name, as that’s the most common cause of this error message. You might also have missed quotation marks or referenced a non-existing range. Double-check for typos, and you’ll likely solve the issue in a second.
- #NUM! Error Message: Your formula contains invalid numeric values. What does this mean? For instance, you’re looking for the fifth largest number in a column that only has four elements.
Google Sheets Math Made Simple
Formulas may sound overwhelming if you’re inclined toward social sciences, but Google Sheets doesn’t require advanced math skills. These formulas are easy to apply, and even if you get an error message, there are simple ways to solve them.
The division error (#DIV/0) may make your report look messy, but you can quickly get rid of it in a few steps. The best thing about it is that you can choose what you want to replace the error message with.
How often does this error message occur in your spreadsheets? What will you use instead of the code? Let us know in the comments section below.