We all make mistakes, luckily thanks to the Excel and Google Sheets error checking, it will help us quickly find them in our spreadsheet and solve them; It is one of those basic functions of Excel or Google Sheets that we need to know. A little further down we will see all the steps that we will have to follow to be able to use this interesting and useful function.
Background error checking in Excel
Errors in Excel functions and formulas usually appear as a small green triangle in the upper left corner of a cell. If we click on the cell that has an error, a drop-down list will appear, in this list we can choose the option we need.
In case the background error checking options are not activated. So this triangle will not appear, although the cell will still have the error.
In order to enable this useful option, which will make it easier for us to check for errors, we are going to have to go to the top of Excel in “File” and then we are going to go to “Options” in the lower left corner. A new window opens and here we will select “Formulas” in the left sidebar.
In this section, we are going to have to make sure to check the “Enable background error checking” checkbox and then we will click on accept. Generally, this option is checked by default.
How to use error checking manually in Excel
We can also do it manually. We simply go to the ribbon at the top and select Formula > Formula Audit > Error Checking.
In the new window that opens, “Error checking”, we will have to click on “Show calculation steps”.
If we have the option of automatic or background error checking, we can click on the triangle of the cell that has these errors and we will have the option “Show calculation steps” available in the context menu.
Next, we will have to click on “Evaluate” to be able to verify the error.
We will keep clicking on this “Evaluate” option, until we see the message “The next evaluation will result in an error”.
We will be able to appreciate the error in the formula of the dialog box.
We will click on “Close” and then on “Next” to be able to go to the next error. Then, we will click on “Track” to be able to locate it.
Excel will highlight the error with tracer arrows, which will let us see where the error originates. In this case, the #VALUE of the error comes from the #VALUE of the previous error, but the formula in the formula bar looks correct.
So, let’s click “Previous” to go back to the error from before. The cell pointer will change to E15. The formula in E15 is seen in the formula bar. Here we can visualize that the formula is manipulated in value (for example, in D15) with text (in A43).
Now we will click on Edit in the formula bar.
We will change the formula depending on what we want and we will click on Resume.
If there are no more errors left, then the check will end.
Check for errors in Google spreadsheets
In the case of Google Sheets, we can see the error check automatically. When a cell has errors, we can see it in great detail. In this way we will know what we must do to solve it.
As we can see, it is relatively easy to use error checking, both in Microsoft Excel and Google Sheets. Remember that if you have any other questions, you can leave it in the comment box that you will find a little further down.