IntroductionWelcome| 00:04 | Hi, I am Dennis Taylor, and welcome to
Excel 2007: Data Validation in Depth.
| | 00:10 | If you need your data to be accurate and
you want to enter it quickly, then this
| | 00:14 | is the right course for you.
| | 00:16 | I'll be showing you how to limit the
types of numbers you can enter, and how to
| | 00:19 | set up error and warning messages.
| | 00:21 | We will look at dropdown lists as a
way to ensure valid data entry and save a
| | 00:26 | lot of typing effort.
| | 00:28 | And we will be examining methods
for controlling the entry of date/time
| | 00:31 | information, as well as
limiting the length of text entries.
| | 00:35 | These are quick and easy ways to
greatly increase data accuracy.
| | 00:40 | Let's get to it with Excel
2007: Data Validation in Depth.
| | Collapse this transcript |
| Using the exercise files| 00:01 | If you have access to the course
files, you may wish to store them in a
| | 00:04 | convenient location, such as the desktop,
and then when necessary open the files
| | 00:10 | as I am doing here and recognize that
there is a file for each chapter in the course.
| | 00:15 | If you don't have access to the files,
you may wish to create your own files and
| | 00:19 | work with the data as you
proceed through the course.
| | Collapse this transcript |
|
|
1. Controlling the Limits of Numeric DataTesting for whole numbers and decimals| 00:00 | Excel has a variety of data
management capabilities and techniques.
| | 00:05 | Many of them are found on
the Data tab in the Ribbon.
| | 00:08 | You will see quite a few of them listed here.
| | 00:10 | What most of these have in common is
that they are designed to handle and
| | 00:14 | manipulate existing data.
| | 00:16 | A different kind of tool here is Data
Validation and its primary purpose is to
| | 00:21 | prevent bad data from going into worksheet.
| | 00:25 | In other words, we are dealing with data
before we create it, with data validation.
| | 00:30 | In this worksheet, some quantities need
to be entered in column B. And for this
| | 00:35 | particular application, all
values must be between 1,000 and 5,000.
| | 00:40 | And we want to make sure that there are
no typos, that there are no letters put
| | 00:45 | in here, and most important, that
all values fall within that range.
| | 00:51 | Many, many times, when you're setting
up data validation rules, or criteria as
| | 00:55 | they are called, you will select the
entire column. It doesn't have to be that.
| | 00:59 | And in a different environment,
different situation, maybe it's going to be just
| | 01:03 | for these cells or those cells or a
whole row, but in this case, a whole column.
| | 01:08 | Let's just click column B. Even though
there's data in B1, that won't make any difference.
| | 01:14 | On the Data tab, you see
the choice Data Validation.
| | 01:19 | There are lots of settings here.
| | 01:21 | The word Allow, every time I see this, I
want to say, "how about the word require?"
| | 01:27 | In a certain sense, we are doing both.
| | 01:29 | We want to allow certain kinds of
data to go in here, but we might want to
| | 01:34 | restrict it simply on the basis of,
for example, with the quantities, here for
| | 01:39 | the items in question, these
must be whole numbers, no decibels.
| | 01:43 | So let's choose Whole Number.
| | 01:45 | Immediately we get a prompt,
Minimum and Maximum, between these values.
| | 01:50 | Often this will be the choice people
will make, but do be aware the drop
| | 01:54 | arrow here exposes the same kinds of
choices you might be familiar with if
| | 02:00 | you're familiar with
Excel's Data filter feature.
| | 02:02 | And lots of choices here,
pretty obvious in intent.
| | 02:06 | In this case, let's say we want them to
be between these two values. And, by the
| | 02:10 | way, these are inclusive. The value
5,000, if we provide it here, is allowed but
| | 02:17 | certainly not 5,001--
and 1,000 is allowed at the lower end.
| | 02:21 | Let's just screen out the entries.
| | 02:25 | We put in an entry here.
| | 02:26 | Here is a value of 1200.
| | 02:27 | That's just fine. Not a
problem. 900, not good enough.
| | 02:33 | A bit later, I will show you
how to customize this message.
| | 02:36 | This doesn't explain what went wrong,
but it does tell us that the entry is
| | 02:39 | incorrect and we have to make another entry.
| | 02:41 | Now, we chose Whole Numbers here.
| | 02:44 | If this were a salary column, you might
want to do something similar. On yearly
| | 02:48 | salaries, many, many times,
pennies are not used there.
| | 02:51 | So, there, too, it might be
appropriate for using a whole number.
| | 02:54 | Maybe this is a value or a cost or a price,
| | 02:57 | so in column C we might want to do
the same thing with a data validation rule
| | 03:03 | that restricts the
information in terms of range,
| | 03:05 | but in this case does allow for
decimals--particularly if they were prices.
| | 03:11 | And here, too. Maybe all the items in
this list have a value between $50--I'll just
| | 03:17 | put in 50 here--and a
maximum of 300. Same general idea.
| | 03:24 | And sure enough, we can type in decimals here.
| | 03:26 | Maybe this item is 59.95.
| | 03:29 | That's fine, not a problem.
And 299.95, that's okay, too. But 309.95,
| | 03:37 | that's beyond the limits.
| | 03:41 | And certainly, whole numbers will
work here as we type through this.
| | 03:44 | It's not saying it has to be a decimal,
so 200 is fine enough.
| | 03:48 | And along with this, of course, you
probably choose formatting, and for many
| | 03:51 | people, a fast way to do this is
to simply click Column C, go to the Home tab,
| | 03:56 | maybe the Comma button is just fine.
| | 03:58 | The main idea here is we are
restricting the range of the entries.
| | 04:02 | If you forget what the validation rule is,
you can click one cell. That all is necessary.
| | 04:08 | Go to the Data tab, choose Data
Validation and see what the rule is there, just
| | 04:12 | to re-visit it when necessary.
| | 04:14 | So many, many times, setting up a
data validation rule is fast and easy.
| | 04:19 | You have seen examples here of
dealing with whole numbers and decimals.
| | 04:22 | And in both cases here, we use data
validation criteria that kept the values
| | 04:27 | within certain ranges.
| | Collapse this transcript |
| Using the Input message box| 00:00 | In this worksheet, a data validation
rule has been set up in column B that
| | 00:05 | restricts the entries so that they are
between 30,000 and 90,000 inclusively.
| | 00:10 | You might not know that when
you're putting in an entry here.
| | 00:13 | Maybe you think you have got it on good
authority that Alicia's salary here is
| | 00:17 | 29,500, and you type the
entry and you press Enter.
| | 00:21 | And you are confronted with an error
message, and you don't know what those limits are.
| | 00:25 | So, what you might do is cancel this.
| | 00:28 | And if you are responsible for setting
up the data validation, let's say, from
| | 00:31 | now on, what you might do is click
on column B, go to Data Validation.
| | 00:37 | Take a look at the rule.
| | 00:38 | See the minimum and the maximum. Maybe
you didn't know that, or of course you
| | 00:42 | could have been the one who wrote it,
| | 00:44 | you do know it. But the idea could be,
why don't we explain to users before they
| | 00:48 | even try to type by way of an input message.
| | 00:52 | Now, an input message will appear any time
you click in one of the cells in this range--
| | 00:58 | in this case, it's the entire column.
And the input message is preferably short
| | 01:02 | but at least to the point.
| | 01:04 | What is that we are trying to say here?
| | 01:06 | For example, "Must be between."
| | 01:09 | You can say it in your words
of course. 30,000 and 90,000.
| | 01:20 | You can provide a title if you wish,
something along the lines of caution or
| | 01:24 | reminder or warning, something of that nature.
| | 01:30 | And you can be a little bit more emphatic,
| | 01:32 | although that Must is pretty strong.
Something like that is reasonable. Click OK.
| | 01:38 | And every time we click in
one of these cells, we see this.
| | 01:41 | And presumably, this is
going to screen a lot of errors.
| | 01:44 | People are going to be a little bit
more careful as they put an entry in here
| | 01:48 | because we do see this. We do see the reminder.
| | 01:51 | So it's a great tool to further
enhance the idea behind data validation, not
| | 01:57 | only set up the requirements, but have
a good visual that reminds people what
| | 02:01 | needs to go into these cells.
| | Collapse this transcript |
| Using the Error Alert tab| 00:00 | In this worksheet, data validation
criteria have been set up in column B to
| | 00:04 | restrict the salaries between a given range.
| | 00:07 | And when you click on any cell in column
B, the pop-up reminder explains exactly
| | 00:12 | what needs to go into these cells.
| | 00:15 | And so, typos are unlikely,
but they still will happen.
| | 00:19 | And if they do occur, we
get another error message.
| | 00:23 | Now, of course in some cases
maybe you didn't have an input message.
| | 00:29 | It's unlikely that you need both of these,
but on the other hand, you might want
| | 00:32 | to consider one over the other.
| | 00:34 | You certainly can use both.
| | 00:36 | And some people love this idea because
you can have your own text appear in here
| | 00:41 | describing what needs to be done.
| | 00:43 | So, let's talk about what's called
an error alert. Let's change this.
| | 00:49 | Simply cancel this.
| | 00:52 | The data validation rule here applies
to column B, so let's select column B.
| | 00:57 | And on the Data tab in the Ribbon, let's
go to Data Validation, and here's the
| | 01:02 | existing input message.
| | 01:04 | We might be using a variational matter,
maybe the actual words if we wish, so we
| | 01:08 | might highlight this.
| | 01:09 | Press Ctrl+C to copy.
| | 01:11 | Then let's go to Error Alert.
| | 01:14 | And maybe use the message here,
possibly change the wording a little bit,
| | 01:19 | enhance it, be more emphatic, that sort of thing.
| | 01:21 | Put in a title if you wish.
| | 01:23 | Whatever you see here will appear in
that dialog box that occurs when there is
| | 01:28 | an error. And maybe you'll say
something funny, sarcastic, cute whatever.
| | 01:34 | People love doing this.
| | 01:35 | Maybe that's okay, nothing too outrageous.
| | 01:40 | Put in some of your own spacing, that sort of thing.
| | 01:42 | Now, again, emphasizing this example,
| | 01:45 | maybe this is redundant because we
already have an input message, but think of it
| | 01:49 | as being one or the other,
or possibly both, as you wish.
| | 01:52 | But the next time an error occurs,
maybe we are going to get a message
| | 01:56 | that's a little more helpful, or at
least it emphasizes what needs to be done
| | 02:00 | here, and we see it here.
| | 02:02 | So, it's a complement to, or in addition
to, or instead of that other entry that
| | 02:08 | we called an input message.
This is called an Error Alert.
| | Collapse this transcript |
|
|
2. Setting Up Drop-Down Lists (Pick Lists)Understanding short list and long list variations| 00:00 | One of the most popular features of
data validation, and one of its greatest
| | 00:04 | timesaving tools, is the ability to
force users to pick entries from a list.
| | 00:11 | We don't want anybody typing anything
in column B here. We want them to see a
| | 00:15 | list, like the list that's currently in
column J to the right here, so they can
| | 00:20 | just pick these entries.
| | 00:22 | And in column C, we don't want them
typing State names; we would like to see a
| | 00:26 | list like we're seeing in column K. And
even more powerful, take a look at column
| | 00:30 | D for Department and then the entries
in column L. Imagine the huge time saved
| | 00:37 | when people are not typing these entries.
| | 00:40 | Recognize also the incredible amount
of standardization that's going to take
| | 00:43 | place when all these entries, whenever they
exist here, are going to look exactly the same.
| | 00:49 | Some people are going to not put the
period behind Mfg and forget the slash, that
| | 00:54 | sort of thing, and everything is
going to be spelled correctly
| | 00:57 | if you provide people with the pick list.
The amount of time saved is enormous.
| | 01:03 | There are some different examples we
want to show you here--and recognize the
| | 01:06 | different lengths of these as an issue as well.
| | 01:09 | For the moment, the placement of
these is on the current worksheet.
| | 01:12 | It's a good place to start. Eventually
you might want to put them elsewhere.
| | 01:15 | Let's look at column B, and this
ultimately might be a huge list.
| | 01:20 | So I'm selecting the entire column.
| | 01:22 | Whenever you set up data validation,
think how big the data might be eventually,
| | 01:27 | and it's often just a little bit
faster if you use the whole column.
| | 01:31 | Certainly, in other kinds of worksheets,
it might be appropriate to select a row,
| | 01:35 | or maybe even a range. But you
know where the data needs to go.
| | 01:38 | In this case, we're selecting column B, Data tab,
| | 01:43 | Data Validation. The Allow
option here will be List.
| | 01:50 | The Source of the list--and
you don't have to do anything;
| | 01:52 | you want have to collapse the dialog
box here--simply jump out and go highlight
| | 01:56 | the cells right there. Click OK.
| | 02:01 | From now on, in column B, when we see an
arrow, we make a choice as needed. No typing.
| | 02:11 | If your hands are on the keyboard, you
can press Alt+Down Arrow. Get to the list
| | 02:15 | this way, use your arrow keys come
to the entry you want, press Enter.
| | 02:18 | It's fast and it's easy.
| | 02:21 | If you type a letter, you could be fooled
into thinking the sequence works that way.
| | 02:26 | If I type the letter C right
now, there is the word Contract.
| | 02:29 | Now the only reason that appears here
is because the AutoComplete feature, which
| | 02:34 | is usually turned on in Excel, recognizes
that in the contiguous list above there
| | 02:39 | is already an entry that begins
with C. So, that's coincidence.
| | 02:43 | If we happen to put in an
H here, nothing happens.
| | 02:47 | You could type A, but the
point of this is not to type.
| | 02:49 | We want to get here fast by either
clicking the down arrow or using Alt+Down Arrow.
| | 02:55 | And if I type the letter F
right now, nothing helps at all.
| | 02:58 | I want to put in full-time,
but it hasn't been used yet.
| | 03:01 | So that supports the idea of this.
| | 03:03 | That's why we have the pick list, and we
use the arrows. There is that Full Time
| | 03:07 | that I want to put in.
| | 03:09 | Now the State entries of
course are going to be longer.
| | 03:12 | We have got 51 of them there. That includes DC.
| | 03:14 | Similarly here. Same idea.
| | 03:16 | There is where we want the State entries
to go. We don't want anybody typing anything.
| | 03:21 | Aren't you glad you don't have to type
Massachusetts and other long state names?
| | 03:26 | Data Validation > Allow > List.
Click here on the Source panel first.
| | 03:31 | Then let's highlight these cells out here.
| | 03:33 | There we go down to the bottom.
| | 03:35 | The difference we'll note here after
setting this up is that the list, because
| | 03:39 | it's longer, will have a scrollbar,
and you'll have to scroll to find these.
| | 03:47 | If there are more than eight
entries, you have a scrollbar.
| | 03:49 | Now you can move pretty fast through here.
| | 03:51 | Now, you might want to
reconsider the order of these.
| | 03:54 | Right now, it's in order by population.
But we're not going to type Pennsylvania;
| | 03:58 | we'll just click on it. And we're not
going to type Massachusetts; we'll find in
| | 04:01 | there pretty fast. There it is,
and so on. Fast and easy.
| | 04:05 | And in column D Department, this is
the huge list in terms of length, lots of
| | 04:10 | entries there, in terms of some of these
entries, quite long. Same general idea one
| | 04:14 | more time here: Column, D Data Validation,
this time List, and the Source will be
| | 04:21 | these cells right here. Okay.
| | 04:27 | Fast and easy to set up and a
huge amount of time savings here
| | 04:30 | since we don't have to type some of
these long phrases here. Fast and easy.
| | 04:35 | A real popular future when it comes
to the various data validation tools:
| | 04:40 | the pick list idea.
| | Collapse this transcript |
| Sequencing and placing lists| 00:00 | In this worksheet, various data
validation criteria are used in column B for
| | 00:06 | pick lists for status, column C,
pick lists for state entries, and in column D,
| | 00:11 | pick lists for departments.
| | 00:13 | There are two major concerns with
lists of this type. One is the following:
| | 00:18 | the source lists are on the
same worksheet as the data.
| | 00:23 | Now that's not wrong, and depending upon
who is using these, that might be just fine.
| | 00:28 | However, if the lists are nearby and
visible, they could be tempting, or certain
| | 00:33 | people shouldn't see them, or they
could change them, and so one simple approach
| | 00:37 | might be just to hide the
lists. Hide the columns.
| | 00:40 | That's not very foolproof really.
| | 00:43 | Another idea might be to move these
lists rightward, way off to the extreme right
| | 00:48 | edge, hide the columns.
| | 00:49 | That's another possible approach.
But if you move them to another worksheet in
| | 00:55 | the workbook, these lists will not work
unless you give them a range name, and
| | 01:00 | then we have to also
redefine the source of the lists.
| | 01:05 | And it is a rule that lists
must be in the same workbook.
| | 01:10 | You cannot use a pick list by way of
data validation when the list is in
| | 01:15 | a different workbook.
| | 01:16 | There is no way to set that up.
| | 01:18 | Let's imagine for the moment that we
want to put these in a different location,
| | 01:22 | say on a different worksheet.
| | 01:23 | So I am going to take the data here in
column J--eventually, we'd probably do this
| | 01:28 | with all three of them--and simply
right-click and cut and then put these onto
| | 01:33 | Sheet1 right there. Paste.
| | 01:38 | Come back into the worksheet OtherLists,
and attempt to use this now. What happens?
| | 01:44 | We don't see any choices at all.
| | 01:48 | We need to give a range name to that
list. I put it on Sheet1, and you can easily
| | 01:53 | apply a range name by selecting the data.
| | 01:56 | And then to the left of the formula bar,
this is referred to as the name box,
| | 02:01 | click the drop arrow to see if there
are any other existing range names,
| | 02:05 | because you don't want to be duplicating
them, and then type in a name that has no spaces.
| | 02:10 | So I want to type StatusList here.
| | 02:12 | You can use upper- and lowercase.
| | 02:14 | You can use underscore,
but no spaces. It now has a name.
| | 02:18 | Now that still doesn't make this list work
because we now need to change the definition.
| | 02:23 | So click column B on the Data tab,
Data Validation. It is a list. The previous
| | 02:31 | location is no longer a valid
reference. We need to type =StatusList.
| | 02:40 | Now, when we click here, choose the
drop arrow, we see our choices. It works just
| | 02:44 | fine. And eventually we'll do the same
thing with column C and probably with
| | 02:48 | column D. If you are sending this
workbook to other people, you might say well,
| | 02:53 | couldn't they click on
Sheet1 and discover these lists?
| | 02:56 | Well, possibly they could. On the other
hand, you could right-click and hide that sheet.
| | 03:04 | And of course, the next question might be,
well, couldn't someone else come along
| | 03:07 | right-click and unhide
that sheet? Yes that's true.
| | 03:10 | Someone could do that.
| | 03:11 | I'm going to do it here. But that
will be prevented if you also include
| | 03:18 | protection of workbook.
| | 03:20 | So as a follow up to the hidden sheet--
now I've unhidden it, of course. It is
| | 03:24 | back now. But if it were hidden and you
didn't want others to see it, you could
| | 03:28 | go to the Review tab and protect the
workbook, Protect Structure and Windows,
| | 03:35 | and provide a password and reconfirm the
password, and then another person could
| | 03:40 | not get to that hidden sheet.
| | 03:42 | So you might have different security
concerns here and there, but in most
| | 03:46 | situations, it's a good idea to have
lists like this located not on the same
| | 03:52 | worksheet but another worksheet.
| | 03:54 | It's a good idea when you're setting
them up though, to have them nearby.
| | 03:58 | Now there is another concern as well, and it
might have come up in this situation here.
| | 04:03 | This list for states is in order by population.
| | 04:07 | Now maybe that's a rough measure of the
frequency of usage, but it isn't always.
| | 04:12 | And maybe, for whatever reason, a state
like North Carolina or Massachusetts
| | 04:17 | maybe it occurs very often in
you experience, and will occur often.
| | 04:23 | Every time you need to get to it,
you have to scroll down to get to it.
| | 04:26 | What if Massachusetts appeared near the
top of list, or at least in here where
| | 04:30 | we wouldn't have to do scrolling?
| | 04:32 | That would make it better.
| | 04:34 | Well, one option would be, in certain
circumstances, if we wanted to alphabetize
| | 04:39 | it, we could do that.
| | 04:40 | Now that's a good idea, too.
| | 04:41 | It doesn't mean the states with a
highest frequency necessarily are going to be
| | 04:46 | there first, but alphabetizing
a list will make some sense.
| | 04:49 | Like this list is over here, but if
we've decided that Massachusetts is going to
| | 04:54 | occur often, let's put
it at the top of the list.
| | 04:57 | And you can simply drag this upward,
drag an edge of it with the Shift key.
| | 05:01 | Now strangely enough, we
don't want to put it on top;
| | 05:04 | we want to put it in second position.
But if we do want it to be on top, we
| | 05:08 | have to put it here and then take the top
sheet, use the Shift key, and drag it down to here.
| | 05:13 | Let's go check the order of this
now: Massachusetts then California.
| | 05:20 | Now what if in column D we've got similar needs?
| | 05:25 | Let's say that in column D, after
working with this for a bit, we recognized
| | 05:28 | and someone has given us a list, and
we're going to have lots of entries coming
| | 05:32 | out of Manufacturing.
| | 05:33 | Well, they don't appear here
in the early portion of this.
| | 05:37 | We might want to put that name near the top.
| | 05:39 | Let's do that just like we did before.
| | 05:41 | Remember, we want to drag
it into the second position.
| | 05:44 | First, hold down Shift, drag it into
this location, and then take the top entry
| | 05:49 | and put it into the second position.
| | 05:52 | And again, a quick check here and
we'll see it's at the top of list.
| | 05:57 | So we don't have to go scrolling for
it when we need it, and we have realized
| | 06:00 | that we need it often.
| | 06:02 | Now, what if we need to add a name to
the list? Rather than having to redefine
| | 06:07 | this, let's put a name at the bottom
here. It says Marketing--new department here.
| | 06:13 | It doesn't have too many people.
But we want to put it in its proper place
| | 06:16 | here alphabetically.
| | 06:18 | If we leave it on the bottom as I'm
doing so far here and we try and find the
| | 06:22 | entry, it's not going to be there on the bottom.
| | 06:25 | But if we drag this into the list
using the Shift key and probably put it in
| | 06:32 | this logical alphabetical order, use
the Shift key and put it right there--
| | 06:36 | we should let go over the mouse when you
are dragging this way--now it's there.
| | 06:40 | Our last entry is Research/Development
as before, but Marketing should appear up
| | 06:44 | here in the M's. And so on our list here
we do see Marketing. There it is in its
| | 06:51 | appropriate place. And the last
entry is Research/Development.
| | 06:56 | Now, the same thing is true with
the list that's on the other sheet.
| | 06:59 | If we go to Sheet1, if we need to add
a new category here, we've got a new
| | 07:04 | category called intern or temporary
or something like that, initially type it,
| | 07:09 | put it at the end here, and
then simply drag it in here.
| | 07:13 | This also expands the meaning of the range name.
| | 07:16 | So, by dragging this into here
somewhere like that--there we go--
| | 07:19 | the range name now has a different
meaning. And when we come back to the
| | 07:23 | OtherLists here, we will see
that choice there as well.
| | 07:27 | So the two major concerns we have with
lists are the order of the data and the
| | 07:31 | placement of the lists.
| | 07:33 | And the more you get familiar with this,
the more likely you are, when setting up
| | 07:37 | these lists, to have them in an
intelligent order from the beginning.
| | 07:42 | And many, many times the standard
order would be to alphabetize them and then,
| | 07:46 | with a few exceptions, put
those frequently used ones on top.
| | 07:50 | The original order of the state here
maybe wasn't the best, but it could have
| | 07:53 | matched pretty much with your usage pattern.
| | 07:55 | So you'll decide the best ways to do these.
| | 07:58 | So two major concerns with pick lists: the
order of the lists and where they are placed.
| | 08:03 | And again remember, pick list must
be in the same workbook for the data
| | 08:08 | validation rule to work, and if they
are on a separate sheet, they must be
| | 08:12 | referred to by a range name.
| | Collapse this transcript |
| Creating multitiered lists| 00:00 | In this worksheet, there is a data
validation rule in column A that allows us
| | 00:05 | to pick state names. We see them here.
| | 00:08 | Recognize the state names also appear
in column D as the source of the list.
| | 00:14 | Now, it would be really powerful if in
column B we could pick a city out of
| | 00:19 | this list of cities here. But of
course if we've chosen California, we would
| | 00:23 | like the city names to be
only these next to California.
| | 00:29 | In the case of Indiana right here, we
would just want to see these names pop up.
| | 00:33 | So, using different features of Excel,
including data validation, we want to set
| | 00:39 | up a pick list in column B that's going
to vary and it's going to be based on the
| | 00:43 | entries in column A.
| | 00:45 | This feature otherwise doesn't have an
official name in Excel. Maybe we call
| | 00:49 | it multi-tiered list, multi-tiered pick list.
And setting it up isn't really that difficult.
| | 00:55 | It's going to take just a little bit of time.
| | 00:57 | In column B, we want to see the state names.
| | 01:02 | Now, let's click column B, set up the
data validation rule, and what we're going
| | 01:07 | to be using here is a function
that perhaps you have not used.
| | 01:11 | I don't use it very much in other
circumstances, but it certainly works
| | 01:15 | here. And the Allow option here is List,
Source equals, and the function is called INDIRECT.
| | 01:29 | It's a hard function to
explain except by way of example.
| | 01:33 | Now for every entry in column B, we
want to be basing on the entry in column A.
| | 01:38 | At the moment, the active cell is in B1,
so we want to refer to the column A
| | 01:43 | entry, so we put in A1.
| | 01:47 | That's how we start the set up, and this
simply means that our entry B1 and A1
| | 01:53 | which contain the word
City and State, we don't care.
| | 01:56 | We do want to continue here.
| | 01:58 | Now, we now need to assign these city
names here--the name AZ, and these city
| | 02:07 | names CA, and so all the way across.
| | 02:11 | So let's select this entire list,
and rather than doing this many times,
| | 02:16 | let's use a feature available on the
Formulas tab that allows us to create names
| | 02:22 | from Selection, automatically
generate names from selected cells.
| | 02:27 | This is a great little feature.
| | 02:29 | Create range names from
values in the Left column.
| | 02:34 | In other words, the range name AZ is
going to refer to a list of names that
| | 02:40 | includes Chandler, Gilbert, Glendale,
Mesa, et cetera, all the way across, and
| | 02:44 | in California: Anaheim, Bakersfield,
and all the way across, and so on. Click OK.
| | 02:50 | Now, earlier our reference used
indirect, and so the indirect reference that
| | 02:56 | refers into column A in
this example is referring to CA.
| | 03:01 | So what do we see in our list now? Those
California cities. Maybe it's Los Angeles here.
| | 03:07 | This is an Indiana entry.
| | 03:08 | Which city are we looking for in
Indiana for our list here, and we might see it
| | 03:13 | this way. We have to scroll up and find it.
| | 03:15 | Perhaps it's Indianapolis, and so on.
| | 03:17 | Then we'll try another state here.
| | 03:19 | Let's pick Colorado, and the
entries for Colorado are these.
| | 03:23 | Let's pick Fort Collins.
There we go, and so on.
| | 03:27 | We see how it works.
| | 03:28 | Now there is a slight flaw in
it, which can be remedied.
| | 03:31 | It's not a big deal here, really.
| | 03:32 | I am going to pick Nebraska. There are only two
cities listed there. And we don't see them at first, so
| | 03:39 | we have to scroll up and find them.
| | 03:40 | So that's a slight annoyance.
And the reason is that when we define these
| | 03:45 | names, we use this entire region, so
all those empty cells to the right are
| | 03:50 | part of the definition.
| | 03:51 | Let's select these and delete those
spaces through a not-so-well-known
| | 03:57 | feature, but easy to get to: on the
Home tab, the rightmost button, Find &
| | 04:03 | Select > Go To Special.
| | 04:06 | We're going to choose Blanks.
| | 04:08 | Click OK. Just the blank
cells in this range are selected.
| | 04:12 | Next step, right-click and delete them,
Shift the cells leftward, click OK.
| | 04:20 | And now that little annoyance we saw from
before is no longer going to be there.
| | 04:24 | We only see two entries here.
| | 04:25 | That was Omaha. And we can proceed
with this list as we choose not having
| | 04:30 | those blanks there.
| | 04:32 | So it's an ideal feature, and in theory,
we could even have a third level if we
| | 04:37 | had stores within each of these cities.
| | 04:39 | That's going to take a lot more work
as well, but you get the general idea.
| | 04:42 | A powerful feature to be sure: multi-
tiered data validation--one pick list based
| | 04:49 | on another pick list.
| | Collapse this transcript |
|
|
3. Date ControlsSetting date limitations with basic controls| 00:01 | If you would like to ensure that all the
date entries within a given column fall
| | 00:04 | within a range--for example, in
column B here, the order dates: we want them
| | 00:09 | all to be in the year 2011--
| | 00:11 | we can use a data validation set of
criteria revolving around the word Date.
| | 00:15 | Now you might recognize something right
away. Cell B2 has an impossible date in it.
| | 00:21 | If this column were wider, that would
jump out at us immediately because Excel
| | 00:25 | automatically left-aligns text entries.
| | 00:28 | A proper date automatically
gets aligned on the right.
| | 00:31 | So this little bit of validation happens
| | 00:33 | you could say, in a certain sense,
already. But it doesn't prevent the entry;
| | 00:38 | it just brings out the fact that it was wrong.
| | 00:40 | The data validation we're about to use
will take care of that issue as well.
| | 00:44 | In other words, it'll prevent these issues.
| | 00:46 | But data validation
doesn't correct existing data.
| | 00:50 | Let's say from now on in column
B we want a data validation rule.
| | 00:56 | Data tab > Data Validation.
| | 00:58 | We want to make sure that we use Date.
| | 01:03 | And as with numerical entries, decimals,
whole numbers, the first choice that comes
| | 01:08 | up is between. And many, many times, I
think this would be the obvious choice.
| | 01:12 | You want all the dates that are going
to go into this column or this selected
| | 01:16 | range to fall within a certain range of dates.
| | 01:19 | Same set of choices here as with numbers.
Maybe it's simply a question of keeping
| | 01:24 | the date after a certain period
or before a certain date between,
| | 01:27 | let's say. It would be a common choice.
And if we want these all to be in the year
| | 01:31 | 2011, so 1/1/11 and then 12/31/11. Good enough.
| | 01:40 | Click OK.
| | 01:41 | And of course, we always want
to test it out a little bit.
| | 01:48 | And that's unacceptable for the
obvious reason; it's the wrong year.
| | 01:51 | As always in data validation, if you
would like to customize this message--
| | 01:55 | it's an error alert--
| | 01:56 | you can take care of that if you wish,
or provide an input message that reminds
| | 02:00 | people what the entry should be.
| | 02:03 | When you click Retry, by the way, if
it's just an editing issue, it's going to
| | 02:06 | make a little bit faster than cancel.
| | 02:08 | In other words, we will jump in here
and perhaps that should've been 2011,
| | 02:12 | February 3rd, that idea.
| | 02:14 | And those impossible dates that I
suggested up here, let's put one of those in
| | 02:18 | just to make sure. 11/31/11 an impossible date.
| | 02:23 | And it does get rejected
through data validation,
| | 02:25 | although earlier before applying data
validation we saw how it was left aligned.
| | 02:29 | Now it's even prevented from being entered.
| | 02:33 | Same thing would happen of course with
February 29 in this year, which doesn't exist.
| | 02:37 | So in its basic form, data validation
allows you to control the limits of data
| | 02:43 | entries within the selected range.
| | Collapse this transcript |
| Setting date limitation formulas| 00:00 | In this worksheet, we would like to
make sure that the entries in column D, the
| | 00:04 | shipping dates, occur on or after the order date.
| | 00:08 | And maybe based on the information for
this particular organization, maybe these
| | 00:12 | items have to be assembled and then
packaged, and let's imagine that the
| | 00:16 | shipping date must be more than
two days after the order date.
| | 00:20 | We want to make sure that the
entries in column D fit those criteria.
| | 00:25 | Now if the entries are going to go
into column D and we selected this, on the
| | 00:30 | Data tab, Data Validation, first
instinct might be let's allow Date.
| | 00:37 | But there are no real choices in here,
other than those that allows to set outer
| | 00:41 | limits, or put the dates between two other dates.
| | 00:44 | And so what we need use in this
example is not Date, but Custom--
| | 00:50 | meaning custom formula.
| | 00:51 | And the formula is pretty easy, although I
don't think you would write it instinctively.
| | 00:56 | At the moment, I've selected column
D. And whether I would specifically
| | 01:00 | highlighted the range, or in this case
selected a column, there is an active cell:
| | 01:05 | it's D1. You see that just to
the left of the formula bar.
| | 01:08 | This formula is going to read =D1.
| | 01:10 | I don't really have to capitalize it,
but it's going to be easier to read.
| | 01:13 | =D1>C1, even though we can see that
D1 and C1 don't really contain dates.
| | 01:22 | By inference, when we say D1, we mean
every cell in column D is going to be
| | 01:28 | compared with the cell to its left.
| | 01:31 | And we want to make sure that the entry
in column D is greater than the entry in
| | 01:37 | column C by two days--
| | 01:40 | in other words, more than two days, +2.
| | 01:43 | That's the master formula.
| | 01:45 | And we're going to get a message here, and
it surprises people, but we will click OK.
| | 01:49 | "The Formula currently evaluates to an error."
| | 01:52 | And that simply means literally that D1
and C1 don't work, or don't fit our criteria.
| | 01:58 | We don't care about that.
| | 01:59 | In other words, we do want
to continue. There we go.
| | 02:01 | So I am going to put in the fifth. This
is not more than two days, so this should
| | 02:05 | generate an error, 1/5/11, and it does.
| | 02:10 | And as with other data validation
examples, you might want to alter the error
| | 02:14 | alert or possibly insert an input message to
control this to explain why this didn't work.
| | 02:19 | Let's do a retry here and
change to the sixth, and it will work.
| | 02:24 | Another thought might also have occurred to you.
| | 02:26 | Suppose I put in the eighth here.
What day of the week is this?
| | 02:31 | Do you do shipping every day of the week?
| | 02:33 | You might have a phone bank where you take
orders all the time, but maybe you don't
| | 02:36 | do shipping every day of the week.
| | 02:37 | Well you might or might not know
there is a function called WEEKDAY.
| | 02:40 | And it's a little disappointing to
people at times because when they first
| | 02:45 | try this, they're expecting to see Sunday,
Monday, Tuesday, etc.; they just see a number.
| | 02:49 | 7, by the way, means Saturday. 1 means Sunday.
| | 02:54 | So let's say in this example here, if
the weekday is 7 or 1, let's say we want
| | 02:59 | to prevent those kinds of entries.
| | 03:01 | We don't want Saturdays and Sundays in here.
| | 03:04 | And it would be appropriate to put in an
input message as well to explain this to people.
| | 03:08 | But let's first confront the
idea of what we will do here.
| | 03:11 | Now, you can build a formula outside of
here, and I think I'll do that now just
| | 03:16 | for readability purposes.
| | 03:17 | In other words, we want to
alter the data validation formula.
| | 03:21 | Let's just build it out here for the moment.
| | 03:23 | It's going to start with the word AND.
| | 03:25 | And we'll include two arguments.
| | 03:29 | The first argument is that the
entry must be more than two days.
| | 03:33 | So we saw that argument earlier, and even
though I am in a row 2 for the moment, I
| | 03:37 | am going to use the same
reference to D1 that I did before.
| | 03:40 | Might as well just click on it here. D1>C1.
| | 03:46 | That's one of our two arguments.
| | 03:47 | And the second one is that the
WEEKDAY of D1 is not equal to 7--
| | 03:54 | that means Saturday--comma, and we also--and more
likely, even--don't do shipping on Sunday.
| | 04:02 | So I am going to use Ctrl+C here
and then click out here and press
| | 04:06 | Ctrl+V. Change that to 1.
| | 04:10 | So, suddenly there's a lot going on here.
| | 04:12 | And this is the formula which typically
you don't build outside of that dialog
| | 04:16 | box, but you certainly can.
| | 04:18 | At least we can see it clearly here.
| | 04:19 | So what are we about to say?
| | 04:21 | We're about the set up data validation
criteria for column D that says the following:
| | 04:26 | the entry in column D has to be bigger
than C1, and not only bigger, but just
| | 04:31 | like before--and I had forgotten this--
| | 04:34 | more than 2 days larger,
| | 04:35 | and the entry that we put in cannot be 7--
| | 04:40 | that's Saturday--and it
cannot be 1, which is Sunday.
| | 04:46 | The WEEKDAY of it can't be 1.
| | 04:47 | So let's highlight all this and copy it.
| | 04:49 | I am just going to press control Ctrl+C.
I'll leave it here momentarily, put
| | 04:53 | a space, press Enter.
| | 04:55 | Back into column D, let's reset
the data validation rule now to be--
| | 05:01 | I am going to press Ctrl+V
to paste what we just saw.
| | 05:06 | And dragging across this, we can
see the entire formula this way.
| | 05:09 | So when we click OK, we'll get
that same message about D1 and C1.
| | 05:14 | Yes, we want to continue.
| | 05:16 | The entry is already here, so data
validation doesn't make a change there.
| | 05:20 | But I will change this
to a 6. No problem there.
| | 05:23 | That's all right.
| | 05:25 | Maybe later, just to test of course,
| | 05:27 | I am going to put in the ninth,
which is Sunday, and it doesn't work.
| | 05:33 | So the next thing to have done, if you
didn't do it earlier, would be simply to
| | 05:36 | cancel. And then although I won't really
complete the issue, just point out that
| | 05:40 | we should go back into Data, Data Validation--
and even though we see this again, that's fine--
| | 05:46 | provide an input message that indicates
that the entry has to be more than two
| | 05:50 | days larger, and it cannot be
Saturday, and it can't be Sunday as well.
| | 05:55 | You definitely want to have probably
the input message and/or an error alert
| | 05:59 | just to explain the people
why an entry didn't work.
| | 06:02 | But you can see, the more you know
about Excel functions the more likely you
| | 06:06 | are to start to use functions in data
validation rules, like we're seeing right here.
| | 06:12 | This entry, as we now see it, prevents
Saturdays and Sundays, and it makes sure
| | 06:16 | that the shipping date is more
than two days after the order date.
| | Collapse this transcript |
|
|
4. Time ControlsLimiting time entries with basic controls| 00:00 | In addition to allowing us to control
data entries by way of data validation,
| | 00:05 | Excel also offers a time
variation for data validation as well.
| | 00:10 | Let's imagine in this particular
worksheet that the orders that have been placed
| | 00:14 | in column B did occur during a certain
time of day but this company only takes
| | 00:19 | orders from 6 a.m. until 8 p.m.
within their time zone.
| | 00:23 | We want to make sure that the
time entries fall within that range.
| | 00:27 | Along with the data validation
rule, we should also consider some
| | 00:30 | formatting options.
| | 00:32 | Let's first tackle data validation for
column C. Data tab, Data Validation, Allow >
| | 00:41 | Time. And as with numbers and dates, the
same kinds of structures here. I think
| | 00:46 | between is likely to be the most common,
but here and there you want all times to occur
| | 00:51 | after a certain time or before--
and certainly lots of options to handle those situations.
| | 00:56 | Starting time, you could type in
number of different ways. Probably the best
| | 00:59 | way would be to put in 6:00, which does
mean 6 a.m. The end time you could either
| | 01:06 | type in 8:00 p.m. Other
people might type 20:00, 2800 hrs.
| | 01:13 | as it sometimes is stated this way. Click OK.
| | 01:18 | And sure enough, an entry like 4 o'clock--
and time entry should be typed with a colon--
| | 01:23 | that's not acceptable because it's 4 a.m.
It's before this company is open for
| | 01:30 | taking orders, so we'll have to
re-enter that. Maybe it meant to say p.m.
| | 01:34 | By the way, couple of
variations here on typing this.
| | 01:36 | If we type 4 p, that's good enough.
| | 01:39 | If there are whole-
number hours, you can do that.
| | 01:41 | And if you want to type 5 p.m., you
could type it this way, type in all other
| | 01:48 | letters if you wish.
Someone else might type it in this way.
| | 01:51 | And data validation doesn't care here,
as long as these fit that timeframe.
| | 01:57 | And so to accompany data validation rules
related to time, it's probably a good idea to
| | 02:02 | format these as well.
| | 02:04 | You could right-click column C and
choose Format Cells. That's one-way. And the
| | 02:09 | recommended choice here--one of
two choices, actually--under time:
| | 02:13 | The second choice handles the 24-hour-
type style. If you're familiar and used to
| | 02:18 | working with it that environment, that's fine.
| | 02:20 | If you like to see the a.m./p.m., the
third choice would make sense, the 1:30
| | 02:24 | p.m. option. Click OK. There we go.
| | 02:28 | And so as we make entries now--maybe
this is 4:45 or 4:43 in the afternoon.
| | 02:34 | You can type it that way.
That's good enough.
| | 02:35 | There we go. And it gets displayed this way.
| | 02:37 | Time as a control under data
validation certainly has its merits, and we saw a
| | 02:41 | couple of examples here, and they
should be accompanied by formatting as well.
| | Collapse this transcript |
| Limiting time entries with formulas| 00:00 | In this worksheet, there is a data
validation rule for column B that ensures
| | 00:05 | that all the dates occur in 2011. And in
column C--I'm clicking column C--a data
| | 00:11 | validation rule here--Data tab, Data
Validation--is that the times of day are
| | 00:17 | between 6 a.m. and just one second
short of 8 p.m., slightly different than
| | 00:22 | previous one we might have seen. Click OK.
| | 00:26 | Now it's been decided in his company that
for analysis purposes it's going to be
| | 00:31 | easier to work with this information if the
times of sales are in increments of 15 minutes.
| | 00:37 | In other words, this might be 4:30, 4:45,
and next one might be 10:30, 10:15 whatever.
| | 00:44 | You may or may not be aware
of function called MINUTE.
| | 00:48 | This function tells us the minute
that's embedded in the time entry. So 37, I'll
| | 00:54 | double-click here, and so 32 and 45.
| | 00:57 | Now, if we want to make sure that all
these are evenly divisible by 15, you may
| | 01:04 | or may not know that there's a
function called MOD. And once again, if you know
| | 01:09 | about Excel functions here and there,
you get ideas about how they might be
| | 01:13 | used in data validation.
| | 01:15 | I'm going to put a comma 15 right here.
| | 01:18 | The MOD function is a strange
function. What it does is it calculates the
| | 01:22 | remainder and a division.
| | 01:24 | So the minute here it's 37. We're going
to divide it by 15, and the answer here
| | 01:29 | is the remainder that we get
when we do divide by 15: 7.
| | 01:34 | We have got 7 left over. And down here
you'll quickly see we have got 2 left
| | 01:39 | over here and nothing left over here.
| | 01:41 | We want the remainder to be zero, so we
need to put this all together into a new
| | 01:46 | data validation formula, and
this will be the heart of it.
| | 01:50 | Now, let's go back and look at the
data validation choices that we have.
| | 01:56 | Right now, the choice is to allow time.
Time only gives us of these kinds of
| | 02:02 | options, as with date and with numbers.
| | 02:04 | We can only put them between a range, have
a top limit or a lower limit, and we see
| | 02:08 | quite few variations. But what we
really need in this case is something not so
| | 02:12 | obvious at first, but a custom formula.
And we need to embed the idea that we
| | 02:18 | used previously in the range, and that
means that we have to, in effect, create a
| | 02:24 | new formula, and we need three arguments here.
| | 02:29 | And the first one is the actual hours
have to be between that 6 a.m. and 8 p.m.,
| | 02:35 | so there's other function called HOUR.
| | 02:37 | You can imagine, it works just like MINUTE.
| | 02:39 | The hour of C1, we want to make sure
that it's greater than or equal to six,
| | 02:46 | meaning 6 a.m. Comma. And because I
don't want to retype all this, I'm going to
| | 02:51 | simply highlight this portion of it,
press Ctrl+C, paste it here, Ctrl+V, change
| | 02:59 | just the ending part of it.
| | 03:00 | We want to make sure that
the hour is less than 20.
| | 03:04 | It can't be 8 p.m.
| | 03:05 | It's got to be before that.
| | 03:08 | So those are the two criteria to take
care of what was previously taken care of
| | 03:13 | with the actual Time choice here.
| | 03:15 | That's the extent of it.
| | 03:17 | And the next option is to use the MOD
function like we just saw here, along with minute.
| | 03:23 | You want to get the minute of the entry.
| | 03:26 | And in all cases here, we're using C1,
which acts as a substitute, for the moment,
| | 03:32 | for all the entries in column C.
We want to make sure that this minute of C1,
| | 03:38 | when divided by 15, has a remainder of
zero. So there is a lot going on here, and
| | 03:44 | again this part of the formula right
here simply ensures that the minute portion
| | 03:50 | of the entry that might be made
here has to be evenly divisible by 15.
| | 03:55 | In other words, it has to be zero
or 15 or 30 or 45, so we click OK.
| | 04:01 | The error message here simply refers to
the fact that C1 by itself doesn't fit.
| | 04:06 | We'll simply continue, choosing yes.
| | 04:09 | So existing entries remain unchanged.
Data validation doesn't change those.
| | 04:14 | We'll put in the new entry down here.
How about 10:52? Not acceptable. As in all
| | 04:23 | data validation situations,
| | 04:24 | we probably should provide either an
error alert message or an input message to
| | 04:29 | explain why this isn't acceptable.
| | 04:31 | Let's just simply click a Retry here.
| | 04:33 | 50 is not going to work either--again,
things you would check out, at least initially.
| | 04:38 | That's not good enough
either. Let's do a retry.
| | 04:41 | It could be 45. That will work. There we are.
| | 04:46 | Again, looking at the formula.
And it's lengthy. Data validation.
| | 04:51 | In fact, we can even see it all at once here.
| | 04:53 | What I do sometimes in working with
these two is either prepare them outside of
| | 04:57 | the Data Validation dialog box and
then paste them in, or sometimes come back
| | 05:02 | and highlight all of this, press Ctrl C,
just do a cancel to get out here, and
| | 05:07 | just display it out here, space and then
Ctrl+V, just to take a good look at it and make
| | 05:12 | sure it's doing exactly what I want it to.
| | 05:14 | So a lot of creativity is capable
here using data validation in time-
| | 05:20 | restrictive situations and not using
the time choice from the set of criteria,
| | 05:25 | but using a custom formula.
| | Collapse this transcript |
|
|
5. Text Length ControlsLimiting text length with basic controls| 00:00 | In this worksheet, we need to put
Social Security Numbers in column A. And as
| | 00:03 | you probably know, Social Security Numbers should
always be--must always be--nine characters long.
| | 00:11 | Amongst the data validation choices
found on the Data tab, the various
| | 00:17 | criteria include Text length, and the
choice here, like many of the others, because
| | 00:22 | it gives us quite a few options here,
| | 00:25 | in this case we want the length to be
equal to 9--not 8, not 10, or anything else.
| | 00:31 | Nothing else is acceptable.
It's got to be 9. Click OK.
| | 00:35 | So we'll put in one here.
| | 00:39 | I'll put one extra one just to make a
test. And as always with data validation, we
| | 00:44 | might consider putting in either an
error alert or an input message or both
| | 00:49 | explaining why this is unacceptable.
| | 00:51 | Let's do a retry here. Maybe that last
character doesn't belong there. Click
| | 00:56 | Enter and we have this.
| | 00:57 | Now it's also a good idea--and it's
outside the realm of data validation--to
| | 01:02 | include a formatting option here.
| | 01:04 | I'm going to right-click column A.
That's one of the many ways we can get to
| | 01:08 | Format Cells and make the choice
Special under Category. Choose Social Security
| | 01:14 | Number, and what this feature does is
that it inserts hyphens in the format,
| | 01:20 | not in the content.
| | 01:21 | If you click on one of these cells,
you can look at the formula bar, or if you
| | 01:24 | happen to double-click, the hyphens
aren't really there and you don't want to
| | 01:27 | be typing them, but it certainly is
an ideal display, and that's what most
| | 01:30 | people are used to.
| | 01:31 | So that's outside of data validation,
but the rule here works well, and similarly,
| | 01:36 | in column B maybe in your organization
you got employee IDs and they're always
| | 01:41 | six characters, so same basic idea here.
| | 01:44 | We want to allow a text length only of
six. And of course if it were a range, we
| | 01:50 | could do that as well, but say in this
case it's going to be six, so all the
| | 01:54 | entries have to be six characters long.
| | 01:56 | And similarly, phone numbers these
days, in most cases nearly always, these
| | 02:01 | are 10 characters long.
| | 02:02 | Let's do that sort of thing as well, too.
| | 02:04 | Again, real fast here, Text length
equal to 10. And here, too, as with Social
| | 02:10 | Security Number, probably what you do
with this is include a format, and that
| | 02:14 | format in the same location. Simply
right-click Format Cells--one way to get to
| | 02:19 | it--a Special Category, Phone number.
| | 02:22 | This includes in the display, as you'll see here,
| | 02:30 | parentheses and the hyphen and the
space. And that should be accompanied in it,
| | 02:35 | too, like the format for Social
Security Numbers, outside of data validation.
| | 02:40 | And the reasons for setting up the
data validation rule, of course, are to make
| | 02:43 | sure that we always use 10 digits in
column C, six digits in column B, and the
| | 02:48 | Social Security, nine digits here.
The Text length option for data validation.
| | Collapse this transcript |
| Limiting text length with formulas| 00:00 | Column A in this worksheet is
designed contain Social Security Numbers, and
| | 00:04 | there is a validation rule set up
for Column A. On the Data tab, Data
| | 00:09 | Validation, there is the rule. The Text
length here must be nine characters--no
| | 00:14 | more, no fewer; that's it.
| | 00:16 | However, you can see that in cell A3
there's a letter and letters don't belong
| | 00:21 | there. And furthermore, if Caps Lock were
on, something like this might happen as
| | 00:25 | well. There's a capital I in there,
but it's still nine characters, so the data
| | 00:34 | validation rule is essentially working.
| | 00:36 | Now, I'll do this one more time. Sometimes
you'll see this, too. There's a capital O in there,
| | 00:42 | but it's still nine characters.
| | 00:44 | Now, one fix here, or at least something
we draw attention to this, would be making
| | 00:50 | the column wider--that certainly would
help--and lot of you know that there is a
| | 00:55 | format built-in, under Format Cells.
Right-click Format Cells and the whole
| | 00:59 | column, choose Special, Social Security
Number, and although this will not prevent
| | 01:05 | the entries from going in, it certainly
draws attention to them because these
| | 01:09 | have letters in that doesn't fit the
scheme and we don't see Social Security
| | 01:12 | Numbers the way we typically do.
But let's even prevent these from occurring in
| | 01:17 | the first place by changing the data
validation rule here, and what we want to do
| | 01:23 | is effectively indicate that
the entry must be a number.
| | 01:28 | Now you may or may not know that there
is a function called ISNUMBER, and I'll
| | 01:32 | just put it in a couple
characters out here. Okay, ISNUMBER.
| | 01:43 | In this case here, of course, as we see
here, it looks pretty obvious. Is this a
| | 01:47 | number? Yes. And how about the other three?
No, they have to be all numbers. They might
| | 01:53 | contain numbers, but you can see
what's happening here. This function is called ISNUMBER.
| | 01:58 | So we want to change the data
validation rule for column A, Data Validation, and
| | 02:04 | we no longer use the Text length
choice here. We'll use Custom, meaning Custom
| | 02:09 | Formula, =AND. Two things need to be
true, and you may or may not be aware of
| | 02:16 | function called LEN. Think of the word length.
| | 02:20 | The length of the entry here in column A--
now we use A1 is a substitute for all
| | 02:26 | the entries in column A--the length
of this must be equal to 9. That's the
| | 02:32 | criteria that had existed earlier.
And along with this, we want to make sure
| | 02:37 | that this is a number. As you're
typing these, by the way, they don't need to be
| | 02:40 | capitalized; later they will
appear that way. I've got Caps Lock on.
| | 02:44 | ISNUMBER, we want to make sure that
that is a number. So, two criteria are in
| | 02:51 | place here. Click OK and there we go.
And from on--now data validation doesn't
| | 02:56 | correct it's already here, but this
time we'll try putting in a letter.
| | 03:02 | It's nine characters.
| | 03:03 | However, there's an O in there;
that's not a zero. And as always, we would
| | 03:08 | considered changing the error alert or
providing an input message to indicate
| | 03:13 | what has gone wrong here.
| | 03:14 | So from now on the rule in Column A
is, not only must the entry be nine
| | 03:20 | characters, but it must be a number,
and so alphabetic characters like we see
| | 03:24 | there are no longer acceptable.
| | 03:26 | Of course in these situations, we want
to clean this up. Perhaps that was a one.
| | 03:30 | We will make it be a one and--
Too many characters. There we go.
| | 03:36 | And in here, this should be a zero.
| | 03:38 | There we go, and those are all
cleaned up. And this one of course is totally
| | 03:42 | unacceptable; just get rid of it.
| | 03:44 | So we've altered the data validation
rule in column A--once again quick look
| | 03:49 | at the formula here.
| | 03:50 | To make sure that it's nine
characters long--that's the way it had been
| | 03:53 | earlier--we still want that to be true,
but at the same time we want to make
| | 03:57 | sure that the entry is a number,
does not contain any text characters or
| | 04:01 | special characters.
| | Collapse this transcript |
|
|
6. Specialized Custom Formula ControlsPreventing duplicate entries| 00:00 | Column B entries in this worksheet
should not include any duplicates, and
| | 00:04 | yet there's one there.
| | 00:05 | You can quickly see that cell B4 is
a duplicate entry, same as cell B2.
| | 00:11 | Now, one approach to identifying these
could be a completely different feature
| | 00:16 | called Conditional Formatting.
| | 00:18 | It's on my Home tab. We could just do a
quick check there under Highlight Cell
| | 00:22 | Rules > Duplicate Values. And immediately
we see what's going on here. But that
| | 00:27 | doesn't prevent the entry, and there's
no direct way using data validation to do
| | 00:33 | this other than by way of a special formula.
| | 00:36 | On the Data tab, choosing Data
Validation, under Allow, no specific choice here
| | 00:42 | will screen out duplicates;
| | 00:44 | however, a custom function will.
And rather than creating it here at first, I
| | 00:49 | want to cancel this and introduce to
some of you, although others of you may be
| | 00:53 | aware, of a function called COUNTIF.
| | 00:57 | In English, here is what we might be
saying. We're looking throughout the
| | 01:01 | column B, or any given range, comma, and we want to
know how often this particular entry occurs.
| | 01:09 | How many times does 219876 occur as a
unit there in column B? Well, we can see
| | 01:17 | here that it occurs two times.
| | 01:20 | Double-click to copy this down, and
again with such a small set of entries, we
| | 01:24 | can easily verify what's going on here.
| | 01:26 | This is the COUNTIF function, and all
we really need to do is to take the same
| | 01:30 | idea and incorporate it into
the data validation formula.
| | 01:35 | In fact, if I drag this up here,
this will do some of the typing for us.
| | 01:38 | So I'm simply going to double-click
this and copy it. Now, you don't always have
| | 01:42 | to build a formula in this way,
but it's helpful at times.
| | 01:45 | I'm going to press Ctrl+C here and then Escape.
| | 01:49 | Let's jump into Column B on
the Data tab > Data Validation.
| | 01:54 | Let's set up the custom
formula to prevent duplicates here.
| | 01:59 | Using this custom formula, we want to
make sure that the entries in column B
| | 02:04 | occur once and only once.
| | 02:06 | So as I paste in the formula that was
previously written--it's not quite there--
| | 02:10 | but here is what we want to say.
| | 02:11 | For every entry in column B, and here we
use B1, which will act as a substitute
| | 02:17 | for all entries in column B as the
entries are made, we want to make sure
| | 02:21 | that this count is equal to one. So, as
an entry occurs, we want to make sure that
| | 02:26 | it's there once and only once.
| | 02:29 | Now data validation does nothing with
existing data. It doesn't correct the
| | 02:32 | entries that we have already, so we
still see this one. But let's make another
| | 02:37 | entry and I'll try and put
in 134267. Not acceptable.
| | 02:44 | Once again, you might want to consider
using an error alert message or an input
| | 02:48 | message to explain why this isn't
working. And of course if we make a change to
| | 02:52 | this one we can certainly change it to
something different, which should be the
| | 02:55 | case anyway. But we ever try and change
it back to what it had been that's, not
| | 02:59 | going to work because it is a duplicate.
| | 03:01 | It occurs more than once. And of course
we don't want to let it occur more than
| | 03:05 | once. The data validation rule does the job.
| | Collapse this transcript |
| Locating data validation rules| 00:00 | If you make an entry in column C in
this worksheet, you might be surprised.
| | 00:04 | Now, I haven't put in the name of the
department for this person, but I'm looking at
| | 00:07 | my list here, and this is Ann Todd here.
So I'm going to put in her salary of
| | 00:10 | 102,000, and I press enter and I get a
message that doesn't explain what's wrong,
| | 00:17 | but it says this is unacceptable. And of
course what's in effect here is a data
| | 00:21 | validation rule. And clicking on the
cell, we can certainly, on the Data tab,
| | 00:26 | click data validation and see what that rule is.
| | 00:29 | Salaries in this list must be under 99999.
| | 00:35 | Now, question might come up in a
worksheet that you have never seen or maybe when
| | 00:39 | you haven't seen in a long time--and
imagine of course some worksheets with many,
| | 00:43 | many columns--you want to know
where data validation rules exist.
| | 00:48 | There are two different command
sequences that you will want to explore.
| | 00:52 | On the Home tab, the rightmost button,
Find & Select, has a choice called Data
| | 00:58 | Validation, and this will highlight
all cells in this worksheet that have
| | 01:03 | data validation rules.
| | 01:05 | Now, you can nearly always tell by
context whether they are going to be the
| | 01:10 | same rules. We wouldn't have the same
rule in column C as in column D, and, by
| | 01:15 | the way, some data validation rules
are for ranges of this size or that size,
| | 01:20 | and sometimes they will exist even in
different columns at the same time, the same rule.
| | 01:25 | So the other aspect of this that you
might want to consider--perhaps in this
| | 01:29 | worksheet not so much--but if you
realize that there's a data validation rule
| | 01:33 | here, you would want to know which
other cells have that same rule. And so with
| | 01:39 | this cell being active and then
choosing Find & Select on the Home tab, a not-
| | 01:45 | so-obvious choice at all is Go To
Special, and here you will see a choice called
| | 01:51 | Data validation. And if we simply click
OK, we'll get the same cells we saw before.
| | 01:57 | In other words, highlight all the
cells in this worksheet that have data
| | 02:01 | validation rules in place.
| | 02:02 | If we click Same, what this means is
after clicking OK, we're going to be
| | 02:08 | highlighting all the cells that have
the same data validation rule as the
| | 02:13 | current cell has. Click OK and now we
see this section highlighted. And, by the
| | 02:18 | way, to quickly pick up on the
extremities of this--in other words, the extent
| | 02:22 | of the range--just press Ctrl+Period a
few times, and you we'll see how far this
| | 02:27 | particular definition extends.
| | 02:29 | So when you need to track down where
data validation exists in a worksheet, you
| | 02:34 | can either select all the cells within
the worksheets that have data validation
| | 02:38 | rules or just select the ones that
have the same data validation rule as the
| | 02:43 | current cell. And again, it's on the Home tab,
| | 02:46 | Find & Select. Either choose Data
Validation, or to find those with the same rule,
| | 02:52 | Go To Special, and then Data Validation > Same.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | Upon completion of this course, you
might want to check out lynda.com, click
| | 00:05 | Software, click the letter the E,
choose Excel, and you'll see a list of courses
| | 00:11 | for Excel 2010 and Excel 2007.
| | 00:14 | I might recommend you check out
Working with Dates and Times, or Creating
| | 00:19 | Business Budgets with Curt Frye, or my
Advanced Formulas and Functions course,
| | 00:24 | and check back here from time
to time for additional offerings.
| | 00:27 | Thank you for watching.
| | Collapse this transcript |
|
|
