Create on-screen warnings and reminders with comments or data validation messages

- [Instructor] Providing documentation for Excel worksheets is not something that every Excel user is interested in, and yet you can provide valuable information for anybody using this worksheet or another worksheet by using either what we call comments or possibly a feature in data validation. It doesn't have a formal name, I'm using the term reminders here, but let's explore these opportunities to provide more information. On this particular worksheet here, as you're looking at this, maybe it's the first time you're seeing it. You might notice the red triangle in cell I7.

As I slide the mouse over that cell, there's a popup, "See Jennifer for more details on this unusual formula." Well, you'll click that cell, perhaps, or look in the formula bar, and maybe that raises some questions, you'll call Jennifer, maybe not. Slide off the cell, the popup tip disappears. Let's add a tip. Maybe I've got a similar thought about cell G3. Not about the formula so much, but I'm just wondering about the accuracy of that data. And of course, I'm talking about a scenario whereby different people might be viewing this particular worksheet.

We can add a comment in a number of different ways. I can right-click and insert a comment, we see that option. If you go to the Review tab in the ribbon, you'll see a choice called New Comment, and notice also there's a keystroke shortcut, Shift F2. So I'm pressing Shift F2, and what we're going to be seeing here is a popup rectangle, the user's name on it, in this case, my name. And so I'll type in something either explanatory or noteworthy just to get the attention of someone who might be using this. I'm doubtful about this entry, so I'll say, "Is this correct? "Call Jim," or something to that effect.

Now, while I'm here too, maybe I don't like the look of that, I can right-click on the edge of this and we'll see an option called Format Comment and you'll see some choices in here. Maybe I want a different background color. I might even eventually add more comments and maybe I'll even color code them in some way. I could make some other changes here as well, but that's good enough for now. Also, I can shrink this a bit while it's active. Click outside of it, we don't see it any more. Slide over that at a future time, we see this. We can also make all the comments on a worksheet appear by simply, on that Review tab, clicking Show All Comments.

We see them that way, or turn it off. We can make this one visible or that one visible for a time. I'll slide over the other comment, in cell I7, right-click, and Show/Hide Comments, but this time just that one. Or at a later time I could come back and turn that off, or just go back to Show Comments again, that shows all of them, click that again at the top of the screen there, they disappear. Now, another process to use data validation. Now, here's a different worksheet, and I'm about to change that entry over in cell I5.

Larry Weber's gotten a promotion here, he's going to be making 122,000 now. But as I click this cell, look what pops up. Reminder, must be less than 130,000. So I'm going to type in 122,000, but if I were to type in 132,000, maybe there was a typo, press Enter, what happens? It doesn't match the data validation restriction. We can see that clearly, clicking Cancel gets rid of the entry completely, clicking Retry puts me into edit mode, and I'll change that into the 122,000 that it should've been.

So there's a popup tip accompanied by a data validation rule, it looks like it's present in all the cells here in column I. Now, there's also data validation over in column B. I'll click there, my friend Mike here has moved to a different organization. For the moment I've forgotten the name of it, but I'll probably recognize it if I see a list. This uses a so-called pick list. There's no popup tip, there's no warning here that tells me what to do, but like a lot of users, I've seen this often, I see the drop arrow here, and there's an alphabetized list of all the departments and maybe I'll find the one that I'm thinking of.

Oh, there it is, process development. I forgot the name of it. Now, I could certainly add a tip to the existing data validation rule here if I wanted to. If I click Column B, often, but certainly not always, a data validation rule is applied to an entire column. As it is here, I'll go to the Data tab. There's data validation, drop arrow there, I'll choose Data Validation, and I can add Input Message. I think in this case it probably would be obvious as to what to do, but I can choose Input Message, give it a title if I wanted to, and provide an input message, for example, click the arrow, you must pick from the list.

I think really it's actually unnecessary here, but I'm going to use this over in column A, this idea. Now, if you select column A and go to Data Validation, start exploring some of the rules, some of the things that you can accomplish under Settings, nothing quite works here for making sure those names are in reverse order. So I'm not interested for the moment in providing some kind of a rule that actually works, but why not just use data validation to provide an input message? So that's what I'm going to do. Input message, title, we can use anything we want here.

The title can be up to 32 characters. A word like caution, reminder, warning, something like that, I'm going to put in reminder, colon. And then a message, the message can be up to 255 characters. I just might want to say here, just to remind the user, type, you know, last name, comma, first name. That should be good enough. And of course the user would probably see the other entries as we see them in there, too, so we'll do that.

And click OK. Now, any time we click over here in column A, we see that entry. That doesn't mean that I've got any control over it or any user couldn't violate this, 'cause that's going to happen. There's no real rule here, we're simply using data validation to provide a popup tip, reminder, whatever we want to call it here. So I think you can see in different ways how both of these features can come into play. It's the kind of thing we often don't do with Excel, we say we'll get to it later, but think of how this will reduce certain errors either by using data validation or simply, in this case, using just a popup tip.

And in the prior worksheet over here we see these popups in the form of comments can help too, either as comments or as data validation input messages.