Use mixed addresses in Excel formulas

- [Instructor] On this worksheet called Employees, you'll see a whole series of green triangles in column F, also in column J, and there's also one in cell M3. These are part of what we call error checking rules in Excel. They're not necessarily errors, but they're warnings. And sometimes you want to see them and sometimes not, and it's quite possible, if you're looking at this same file, maybe you are not seeing them. Let me first point out that if you go to the File tab in the ribbon, down the left-hand side, choose Options, all the way at the bottom.

Under Excel Options, click Formulas. Then at the bottom here, Error checking rules, you'll see a bunch of them here. Now, are these all errors? Not really, but certainly they alert us to things going on in worksheets that we are concerned about. Sometimes they're not really errors. We can turn these on and off. If we don't mind a formula is referred empty cells, I know I don't, I might turn this off. For the moment, I don't want to change that, but in the background, why do we have green triangles there, and what do we do about it? Let me simply Cancel here.

When you see the green triangles and click on one of those cells, slide to the left, you'll see a yellow exclamation point, and as you slide over it, the popup message, in this case, says this cell contains a formula and is not locked to protect it from being changed inadvertently. You may or may not be familiar with the term lock and unlock and how we use it in Excel, but if that's not a concern for you and you don't care about that, possibly you'll say, well, I guess I'll turn off that indicator, it doesn't bother me.

Now, when you slide to the left, you'll also see a drop arrow, and this reminds us what the error, the so-called error, is. We could lock just that one cell if that were the issue, we could ignore the error, but we've got a whole column here. We might just jump immediately into Error Checking Options right here, and here's that same dialog box. So if it doesn't bother us about unlocked cells containing formulas, let's uncheck that and click Okay.

Now, on your computer, if you had that setting off, and you opened this same file, you would not have seen those green triangles earlier as I did, but I just turned off the feature. On the next worksheet over, Error Checking Rules, I've got that dialog box that we just saw, I've got a portion of it displayed here larger. I just want to point out a few other occurrences of this. I think the general idea here is, at different times, you do want to be warned of some of these, and I would imagine for some people, myself included, you want some of these boxes here always to be checked, and perhaps some of them, maybe not.

As I point to cell C14, there's a message there, off to the left. You see the green triangle first of all, the exclamation point indicator. The formula in this cell refers to cells that are currently empty. That doesn't bother me. Here's the formula, I'll double click it. So what, there are empty cells in there. That's all right. As it occurs to me, I don't need to be warned about that, so what will I do? Probably the fastest way here is I slide over the arrow just to the left, the drop arrow, Error Checking Options, I'll jump right in there and uncheck that box for Formulas referring to empty cells.

Uncheck the box, and as I click Okay, lower left corner, we don't see that anymore. Here's another one up here in C5. That looks okay, what's wrong with that? Slide to the left. Indicator, the formula in this cell differs from the formulas in this area of the worksheet. Well, that's okay, I don't want to be warned of those, I don't need to see those green triangles. Once again, drop arrow here, Error Checking Options, I'll go in here. Formulas inconsistent with other formulas in the region.

Now, again, for some people, this is important, and so it's a question of how you use Excel. Usually this doesn't bother me, I don't need to be notified of that. Click Okay. The other issue sometimes is you're preparing a worksheet, you're going to be using it in a presentation, you just don't want those green triangles out there. Some cases, you might turn off most of these, if that's the concern. A couple of other quick examples here. In cell C2, I think most of us know the problem here, you can't use the SIN function, that's a trigonometric function, on multiple cells.

And again, the warning there, perhaps one you would keep active, a value used in the formula is of the wrong data type. And one more over here. What's wrong with this? You can't use the MEDIAN on a single cell. So, different users will use this feature differently. If you somehow don't need to see very many of these, consider, on these error checking rules, unchecking some of the boxes. But from time to time, reevaluate their presence. And as we see them at different times, do take a look at them and decide how you can best use what we call error checking rules.