Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
The Excel program is able to determine when a cell contains an error, and in some cases identify what type of error has occurred. In addition to displaying error messages instead of formula results, Excel will also display indicators in the cell where it thinks the formula could be incorrect, inconsistent, or contain some other mistake. In this movie, I'll show you how to manage those indicators and turn off the ones you don't want to appear. So in this worksheet I have a couple of potential errors - well, one definite error and one possible error. I'll start with the definite error, and that's here in cell B7, where I have Average per Sale, and the cell displays the divide by zero error.
So let's see what's happening. I'll take a look at the formula. So it's dividing the value in B3, the sales revenue by the value in B4. So there is the problem. There is no value in B4, so that means that it's trying to divide by zero, which is not allowed in mathematics. So we need to put a value in B4. So I'll just type in, let's say we had 50 sales, and I'll press Return, and now we get our average value per sale, and the error message goes away.
I'm going to go back and change this value to 0, so we get our error once again, and I want to show you something that you can use to get more information on any errors that appear, and that is by clicking this error options button. When you hover over that error options button, Excel displays a tooltip that gives you more information about the error. In this case, the formula or function used is dividing by zero or by empty cells. If you click the button, you see it has a down arrow here, indicating it's clickable, then you get a menu of options.
First, it tells you what the error is, and then it gives you a list of options that you can use to get more information about the error. For example, you can trace it, you can ignore it, you can edit it in the formula bar, and so on. I'll just press the Escape key and turn my attention to this other error here. Now this cell, as you'll notice, has a green triangle at the top left-hand corner of the cell. That is what Excel uses to indicate an error by default. You'll also see the same indicator here in cell B7. So let's take a look at the error in cell G3.
If I click the cell and hover over the error options button, it says that "The formula in this cell refers to a range that has additional numbers adjacent to it," and when I look at the formula on the formula bar, I see that's true. I'm drawing the value for this formula, which finds the sum of sales from January through March. So I'm drawing the values from cells E3 through E5, these three cells. What Excel sees is an entire range of cells here that are part of the same group that are not included in the formula, and it just flags it and says, are you certain that this formula is correct.
There are other cells that you could be including. In this case, it is correct, so it's really not an error. If you want, you can control which error indicators Excel displays when it examines your formulas. To do that, you open the Preferences dialog, and for that you can click Excel > Preferences. I'll click Preferences, and then in the Preferences dialog, under Formulas and Lists, you can click Error Checking. So there's an entire set of rules that Excel follows, and by default all of them are turned on.
There are only two that I will usually turn off, and those two are here in the middle. "Flag formulas that are inconsistent with the formulas in adjoining cells," in other words, if you have a sum formula in one cell and an average formula in another, that might be considered inconsistent and Excel will flag it. Well, normally that type of worksheet construction is intentional, so I don't check for it. So I'll clear that check box, and then you have "Flag formulas which omit other cells in the region." That was the one I just showed you in the worksheet where I was just finding the total for sales from January through March and didn't worry about April through December.
When I created my worksheets, that particular construction is intentional, so I will clear this error. Everything else I usually leave turned on. If as you use Excel you decide you want to turn them off, just remember to come back here, and you can do so by clearing their check box. When you're done, click OK, and Excel will no longer indicate cells that contain those errors, and as you'll see, cell G3 no longer has an error indicator in the top left-hand corner. Excel tries to be helpful by indicating errors, and indicating divide by zero or whether a formula name is incorrect is extremely helpful.
If you think Excel goes too far, you can turn off error indicators for specific error types that you don't want it to detect.
Get unlimited access to all courses for just $25/month.Become a member
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.