Join Dennis Taylor for an in-depth discussion in this video Use mixed addresses in Excel formulas, part of Excel Tips Weekly.
- [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.
Author
Updated
4/13/2021Released
1/16/2015Note: Because this is an ongoing series, viewers will not receive a certificate of completion.
Skill Level Intermediate
Duration
Views
Q: Why can't I earn a Certificate of Completion for this course?
A: We publish a new tutorial or tutorials for this course on a regular basis. We are unable to offer a Certificate of Completion because it is an ever-evolving course that is not designed to be completed. Check back often for new movies.
Related Courses
-
Excel: PivotTables for Beginners
with Dave Ludwig23m 52s Beginner -
Presentation Tips Weekly
with Jole Simmons3h 48m Intermediate -
Excel: Lookup Functions in Depth
with Dennis Taylor1h 8m Intermediate
-
Excel Tips - New This Week
-
Previous Episodes
-
Quick formatting tips8m 9s
-
Create an organization chart8m 56s
-
Auditing9m 1s
-
Adding comments and shapes7m 35s
-
Creating an Excel template7m 57s
-
Adjust banded-row formatting14m 35s
-
Dealing with circular errors8m 20s
-
Sorting tips and shortcuts8m 40s
-
Freeze Panes and Split5m 30s
-
Calculate % of change5m 41s
-
How to adjust names5m 45s
-
Sorting by moving columns4m 16s
-
Using the Solver Add-in4m 29s
-
Create picture links5m 37s
-
Display large values5m 16s
-
Create a powerful macro4m 40s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Use mixed addresses in Excel formulas