IntroductionWelcome| 00:04 | Hi, I am Dennis Taylor, and welcome to
Excel 2010: 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:20 | set up error and warning messages.
| | 00:22 | We will look at dropdown lists as a way
to ensure valid data entry and save a lot
| | 00:26 | of typing effort, and we will examine
methods for controlling the entry of date/
| | 00:31 | time information, as well as
limiting the length of text entries.
| | 00:35 | These are quick and easy ways to
greatly increase data accuracy.
| | 00:39 | Let's get to it with Excel
2010 Data Validation in Depth.
| | Collapse this transcript |
| Using the exercise files| 00:00 | If you have access to the course
files, you may want to store them in a
| | 00:03 | convenient location--perhaps the
Desktop--and open them as I am right now, and
| | 00:09 | choosing, for example, chapter 3.
| | 00:12 | All of the worksheets associated
with chapter 3 are stored in this
| | 00:15 | particular file right here.
| | 00:16 | If you do not have these files available,
feel free to use your own data as you
| | 00:21 | work through the course.
| | Collapse this transcript |
|
|
1. Controlling the Limits of Numeric DataTesting for whole numbers and decimals| 00:00 | Unlike Excel's many database
management tools which are designed to handle
| | 00:04 | information that's already existing, data
validation controls data at the point of entry.
| | 00:11 | In column B, we want to put in some whole
numbers; in column C, perhaps some dollar amounts.
| | 00:16 | In its basic form, data validation will
allow us to control a range of cells and
| | 00:22 | what goes into them.
| | 00:23 | And certainly, in some situations,
you will specifically highlight cells
| | 00:27 | because the data is not
going to go beyond that range.
| | 00:30 | In this example, let's say we don't know how
many entries are likely to be here eventually.
| | 00:34 | Let's just select column B. And the
feature we are looking for is on the Data
| | 00:39 | tab in the Ribbon, Data Validation.
| | 00:43 | Just click the icon.
| | 00:44 | Of course, you might read
the tip as we see it here.
| | 00:46 | We are going to control data before it
ever gets into this particular worksheet.
| | 00:52 | And many times when you are you
dealing with just pure values, you might give
| | 00:55 | some thought as to whether you
want whole numbers or decimals.
| | 01:00 | It doesn't really make any difference.
| | 01:01 | Let's say in the example here we are
talking about maybe items that are being
| | 01:05 | sold, and based on the items in question, they
must be whole numbers, so we will choose that.
| | 01:11 | If this were a salary column, you might
want to choose whole numbers--if we are
| | 01:15 | talking about a yearly salary.
| | 01:17 | On the other hand, for certain other kinds
of data, maybe you want to allow decimals.
| | 01:21 | Note the word allow here, in a certain sense,
does mean allow, but it also means require.
| | 01:28 | If we put in a whole number here,
it will not accept decimals.
| | 01:31 | And we are immediately prompted with a
'between' here, and we can put in values.
| | 01:37 | The last time this was used,
maybe these numbers were used.
| | 01:40 | We don't necessarily have to use
those of course; we can use other
| | 01:43 | constructions as well.
| | 01:45 | Maybe we simply want to put an upper
limit on these values, or a lower limit, and
| | 01:50 | you can see all the variations that are here,
| | 01:52 | if we want them between a certain range.
Maybe the range we are trying to use
| | 01:56 | here is between 1,000--and
that is inclusive, by the way.
| | 01:59 | It will allow 1,000 and a maximum of 5000.
| | 02:05 | So, we want to make sure all entries fall
within that range inclusively. Click OK.
| | 02:11 | We are also saying that
it must be a whole number.
| | 02:14 | So, of course, values like 1,200,
that's just going to work just fine. 1300,
| | 02:18 | 2300, these are all fine as
long as they are within that range.
| | 02:23 | Let's put in a decimal and
see what happens. 2400.5,
| | 02:29 | and we see a prompt.
| | 02:30 | We can click Retry,
| | 02:33 | click in here and make a
change, probably get rid of that.
| | 02:35 | That's certainly is the one option.
| | 02:36 | If you need to look up the value and
it's not here--suppose, you put in the
| | 02:40 | value here with a decimal and we just
know it's wrong, we can't look it up right
| | 02:45 | now--cancel, of course would be
the appropriate response sometimes.
| | 02:49 | In column C, if these are dollar values,
and if we do want to allow decimals
| | 02:54 | here--once again, selecting the entire
column--which is the most likely way to
| | 02:58 | approach the values for column C,
Data Validation, this time we will allow
| | 03:02 | decimals and between a certain
amount, maybe these are dollar amounts.
| | 03:07 | In the example here, they might range from $50.
| | 03:09 | We don't necessarily need the decimal
in right here if you want. These are
| | 03:13 | all going to be between $50 and $300--or
they must fall within that range, 50 and 300.
| | 03:21 | So we could type whole numbers.
| | 03:23 | There's nothing wrong with that.
| | 03:25 | Let's say a value like 78.
| | 03:26 | That's going to be just fine.
| | 03:29 | We didn't really format yet.
| | 03:30 | We could have done that ahead of
time or later. That's all right.
| | 03:36 | It does allow decimals, but why did that fail?
| | 03:39 | It's outside the range, so we will do a retry.
| | 03:41 | That should have been 65.
| | 03:43 | So I think you quickly get the
idea how this works in its basic form.
| | 03:47 | Just by controlling data at the
point of input, you will be up to prevent
| | 03:51 | long-range problems that occur when data is bad.
| | 03:54 | Quick and easy to use in its
basic form, data validation.
| | Collapse this transcript |
| Using the Input message box| 00:00 | In this worksheet, there is a data
validation rule established for column B. Now,
| | 00:05 | if you're not the person who has set up
a data validation criterion, you don't
| | 00:09 | always know that they're going to
happen, and when you see them sometimes you
| | 00:12 | are a little bit surprised.
| | 00:13 | I am going to put in a salary here for
Alicia Franklin of $105,000, and when I
| | 00:19 | press Enter, I get a
message that says it's not valid.
| | 00:23 | But I don't really have a clue
as to what the limits of this are.
| | 00:26 | I see that there's some kind of rule.
| | 00:28 | Well, one option here is to just cancel
this, and with that active cell in place,
| | 00:34 | go to the Data tab, choose Data
Validation--and actually just click the button
| | 00:40 | itself--and you will see the
rule for that particular cell.
| | 00:43 | This, by the way, doesn't point out or tell
us which other cells have the same criteria.
| | 00:48 | We can see here that the salaries must
be whole numbers between 30 and 90,000
| | 00:53 | inclusively, and I tried to type in
a larger number. That didn't work.
| | 00:57 | Now, what if we were to alert people
ahead of time so that anytime someone
| | 01:02 | clicked in one of these cells in this
particular column that they got an input message?
| | 01:07 | Let's cancel here and reconsider,
and let's act as if we were the ones
| | 01:11 | setting this up now.
| | 01:12 | In column B, this is where the
data validation is already existing.
| | 01:17 | Let's go to the Data Validation
button and choose Input Message.
| | 01:23 | And what this is going to give us
ultimately is a pop-up box anytime we click on
| | 01:27 | one of the cells in column B.
| | 01:30 | So the input message could
be fairly straightforward.
| | 01:33 | Put it in your own words. It must be
between 30,000 and 90,000, and you probably
| | 01:42 | want to put commas in there as
well to make it more readable.
| | 01:45 | You don't necessarily need a title,
but a word like caution, reminder, warning,
| | 01:51 | something along those lines,
| | 01:53 | as you choose. You don't need a colon,
but that makes it look a little bit nicer.
| | 01:57 | Click OK. So, what happens now?
| | 01:59 | Well, there you see it.
| | 02:00 | Anytime we click here, click there,
| | 02:02 | it looks like I put my commas on the
wrong place, but that's easily adjusted.
| | 02:06 | You see what's happening.
| | 02:08 | So, that certainly tells the user what
the restrictions are, and it's fast and
| | 02:13 | easy to set up: an input
message for data validation.
| | Collapse this transcript |
| Using the Error Alert tab| 00:00 | In this worksheet, column B has
data validation criteria set up.
| | 00:05 | And as I click in cell B2,
I see the pop-up reminder,
| | 00:09 | so I am not going to type in a
value below 30,000 or above 90,000.
| | 00:14 | So, I will type in a number, for
example, for the particular cell I'm
| | 00:16 | looking at, 43,218. Not a problem.
| | 00:21 | I've got another cell, and I am looking at
my sheet of paper here. It's $67,509.50.
| | 00:29 | Enter. And the error message here is pretty generic.
| | 00:33 | It says it's not valid, and I'm saying to
myself, "Well, it falls between the range.
| | 00:37 | I wonder what's going on here."
| | 00:39 | I can't figure out what's happening here.
| | 00:41 | What do we need to do in the example here?
| | 00:44 | If we were to cancel and then jump to
data validation, we will see the rules,
| | 00:49 | and what we might have overlooked and
it might take us a while is that this
| | 00:52 | must be a whole number.
| | 00:54 | It wouldn't have made sense possibly
for the input message to say that, we
| | 00:59 | certainly could alter this
and that might be one approach.
| | 01:02 | Another approach could be error alert.
| | 01:05 | Now, it's a little bit of redundancy
perhaps--and I am going to simply take this
| | 01:09 | text right here and press Ctrl+C--but
we also have control over the error alert
| | 01:15 | that pops up after an error has been made.
| | 01:18 | So, I am more or less just going to
put this right here, but at least it
| | 01:22 | explains--although not
fully--what's going on here.
| | 01:26 | Why don't I precede this or include it,
alter in such a way that the message
| | 01:30 | reads, "Must be a whole number and,"
let's say what the way we want, "must be."
| | 01:41 | Now, some people love this feature,
because they like the idea that their words
| | 01:45 | are here in what looks like an official
Excel dialog box. And so sometimes they
| | 01:50 | will get either cute or fancy
sarcastic or have fun with the titles and the
| | 01:54 | messages sometimes as well, too.
| | 01:56 | So maybe I will do that just a little
bit here by putting in the word WRONG!
| | 02:00 | That's not that funny or
anything, but still, you get the idea.
| | 02:05 | So, the next time someone happens to
either use a number that's out of range--
| | 02:09 | and again, that would be unlikely with
the input message already there--but with
| | 02:12 | the error alert, we now have control over it.
| | 02:14 | So, here's a value here, and
suppose it happens to be a decimal.
| | 02:18 | And there is the error message that we see here,
in our own words, and it gets the point across.
| | 02:24 | I often will use either an input
message or the message here, the error alert,
| | 02:31 | not both. But you have the
choice of doing both of those.
| | Collapse this transcript |
|
|
2. Setting Up Drop-Down Lists (Pick Lists)Understanding short list and long list variations| 00:00 | To simplify data entry and save a lot
of time and ensure accuracy, you can use
| | 00:07 | Excel's data validation capability
that forces entrants to pick from a list.
| | 00:12 | Imagine in column B here--we'll start
with a small example--the only entries we
| | 00:17 | want to see here in column B are Full
Time, Half-Time, Hourly, and Contract.
| | 00:22 | At the moment those words are found
over here in column J. In other words, they
| | 00:26 | exist in the form of a list.
| | 00:28 | Now eventually, we would might want to
put these elsewhere, but for starters,
| | 00:32 | it's okay to have them there.
| | 00:33 | Let's click column B because we want
the entire column to be able to contain
| | 00:38 | only those entries that we currently
see in column J. Then on the Data tab,
| | 00:44 | choose Data Validation.
| | 00:46 | The Allow option here is List.
| | 00:50 | Click in the panel for Source.
| | 00:52 | We don't have to collapse the dialog box;
| | 00:54 | simply jump out and
highlight these cells. Click OK.
| | 00:59 | In column B now, we see the drop arrow.
| | 01:02 | Choose the one we want.
Come down to the next one.
| | 01:04 | If you're going through the list somewhat
faster, or you're looking on the sheet of paper,
| | 01:08 | you might consider using the keyboard.
| | 01:10 | It's Alt+Down Arrow.
| | 01:12 | There are your choices.
| | 01:13 | Use the arrow key, come to
the one you want, press Enter.
| | 01:17 | And we don't necessarily have to do
them in order, but you can use Alt+Down
| | 01:20 | arrow in any of these cells.
| | 01:22 | That's certainly one method. The other method,
| | 01:24 | the mouse: same idea, same approach.
| | 01:27 | Do not type the first letter.
| | 01:29 | You might be fooled into
thinking that's going to work okay.
| | 01:32 | If, for example, I put in the
letter C here and press Enter, puts in
| | 01:36 | Contract like that.
| | 01:38 | That's only because it's
already existed there up above.
| | 01:40 | So you might find that working for you,
but don't count on that, particularly if
| | 01:44 | you've got a list with entries
that begin with the same letter.
| | 01:48 | If I type H and I am thinking Half-Time,
well, that's not Half-Time; it's Hourly.
| | 01:55 | Click the drop arrow.
| | 01:56 | Choose Half-Time, if that's what we want.
| | 01:59 | Now with States here, we've got more
States than just four, of course, the order
| | 02:04 | of these we'll talk about it little bit
later, but here's a list of the states.
| | 02:06 | They're in population order.
Same idea, column C here,
| | 02:11 | Data Validation, Allow, List.
| | 02:13 | The source of this is column K. Rather than
clicking the column letter, it's best to
| | 02:18 | highlight the actual cells and then click OK.
| | 02:26 | Even more typing is going to be saved
here, particularly with the longer states.
| | 02:30 | They're in the same order that we
see them over there on the right.
| | 02:33 | Notice one difference here.
| | 02:35 | When you have more than eight entries,
you will see a scrollbar here, and so
| | 02:40 | the order of these starts to
become a bit more important.
| | 02:42 | Maybe this is Pennsylvania, and
so on. Fast and easy.
| | 02:46 | Not only are we saving time,
we're not going to misspell words like
| | 02:49 | Massachusetts and some other
unusual spellings in there as well.
| | 02:53 | And even more powerful would be what
we're about to do in column D. Same idea,
| | 02:57 | but look at the list in column L.
Substantially wider for some of those entries,
| | 03:03 | and many people misspell
Environmental, and there are certainly some other
| | 03:07 | variations in here that you
should be thinking about as well.
| | 03:10 | What if it's important to you that the
names of some of these organizations do
| | 03:14 | contain an ampersand, for
example, the way we see here?
| | 03:18 | Or maybe this doesn't have a period behind it.
| | 03:20 | You want some kind of standardized way
to display certain names here, and you
| | 03:25 | want these to be consistent all the time,
particularly if a worksheet is going
| | 03:30 | to use some logic that needs to
check for the spelling of these.
| | 03:33 | So by providing a list for people, you
automatically screen out those typos and
| | 03:39 | you standardize the entries.
| | 03:41 | One more time here in column D, Data
Validation, Allow, List. Click in the
| | 03:47 | Source panel, jump out and highlight
cells L1 down to the bottom. Click OK.
| | 03:55 | Same idea, easily found, and think of
all the typing that we're not doing as
| | 04:01 | we make these choices.
| | 04:02 | The pick list idea, the dropdown list,
is for many people the major reason for
| | 04:08 | using data validation.
| | Collapse this transcript |
| Sequencing and placing lists| 00:00 | In this worksheet there are three
sets of data validation criteria.
| | 00:04 | In column B we've got status entries.
| | 00:07 | That list is currently in column
J. You see it off to the right.
| | 00:10 | Column C, state names. We see that off
to the right in column K. And then column
| | 00:15 | D for the Departments, that's
out in column L to the right.
| | 00:19 | When you set up data validation rules
it's not a bad idea to have the data on
| | 00:23 | the same worksheet so you
can check out the results,
| | 00:26 | but as a long-term solution this
probably isn't the best location.
| | 00:30 | Depending upon the environment, you
might simply want to hide these columns.
| | 00:35 | Nothing wrong with that.
| | 00:36 | Others who are using this particular
worksheet might uncover them, possibly
| | 00:41 | maliciously or by mistake.
| | 00:42 | They could change the content.
| | 00:44 | It may not be a good solution.
| | 00:46 | Another approach could be to take these
three columns, move them way, way off to
| | 00:50 | the right, and then hide the columns.
| | 00:52 | Better yet, why not put
these on a separate worksheet?
| | 00:55 | Now, in prior versions of Excel, if
you did move a list like this to another
| | 01:00 | worksheet, it wouldn't work
properly until you gave them a range name.
| | 01:05 | It's still true, however, that lists
that are used in data validation criteria
| | 01:11 | must be within the same workbook.
| | 01:14 | In other words, they must be in this file;
| | 01:17 | they can be on other sheets.
| | 01:19 | Prior versions require that if they were,
you had to refer to them by a range name.
| | 01:24 | So we're concerned about
the location of the lists.
| | 01:27 | The other concern is the order of the lists.
| | 01:31 | Every time we choose a state
here, we see the names this way.
| | 01:36 | We don't necessarily know how to find a state.
| | 01:38 | Probably, it would've been
better to have alphabetized these.
| | 01:41 | Now if California entries appear very,
very often--maybe it's not exactly in
| | 01:46 | sync with the population--
| | 01:47 | but if California does appear most often,
you might want to keep it at the top
| | 01:51 | and alphabetize the remainder.
| | 01:53 | Or based on your analysis of these
three entries, if these first three states
| | 01:58 | comprise the most frequent choices, keep
them on top and then alphabetize what's left.
| | 02:03 | So we want think of the order
and we can change the order.
| | 02:07 | And a third concern is what if we need
to add entries to the list? Or change the
| | 02:12 | order of just an entry or two?
| | 02:14 | Those are the concerns that we have.
| | 02:16 | Let's first talk about the idea that
where these are located can be changed.
| | 02:21 | Let's use states for an example here.
| | 02:24 | We don't want this list right here anymore.
| | 02:26 | We're going to highlight it, and we can do
this in a couple of different ways of course:
| | 02:30 | right-click and cut, or Ctrl+X. I am
going to put this on Sheet1, anywhere out
| | 02:37 | here. Right-click and paste.
| | 02:39 | So it's out here now.
| | 02:40 | So what happens to our pick list? Click here.
| | 02:43 | There's the drop arrow.
| | 02:45 | There's the choices. We're all set.
| | 02:47 | Now, could we've referred to
that location from the beginning?
| | 02:50 | Yes, we could have.
| | 02:51 | And so when you're setting up a
list, you can refer to another sheet.
| | 02:57 | We probably would want to
move the other two lists as well.
| | 02:59 | And again, they could be on the same sheet;
| | 03:01 | they could be on different sheets.
| | 03:02 | Remember, in prior versions of Excel
you had to give them a range name first.
| | 03:06 | And I could've done all three at once.
| | 03:08 | So I'll just take these together,
| | 03:11 | right-click, and Cut, and I'll go
to that same Sheet1, and put them out
| | 03:16 | here: right-click, Paste. There we go.
| | 03:18 | So they're over here now.
| | 03:19 | They're not on the OtherList over here,
| | 03:27 | and yet they still work properly.
| | 03:28 | Now, how about the order of the lists?
| | 03:32 | We can first with the Department list here.
| | 03:35 | The most common choice in this list
might be Manufacturing, but we have to
| | 03:38 | scroll to it every time we need to use it.
| | 03:41 | So let's get it at the top of the list.
| | 03:43 | How can we do that?
| | 03:44 | Keep in mind that we could have done
this earlier in thinking out how the list
| | 03:48 | should've been here.
| | 03:49 | We don't always have that option.
| | 03:51 | But let's do consider, as we jump
back to Sheet1, where the list is.
| | 03:56 | We want Manufacturing on top of this.
| | 03:58 | Now, you may know that you can drag a
cell with the Shift key and insert it.
| | 04:02 | So where do we want to put this?
| | 04:04 | Well, as I drag upward--and I am holding down
the Shift key--we want it on top, don't we?
| | 04:10 | Unfortunately, if we put it on top, the
cell reference is not going to follow.
| | 04:14 | We need to put it right
here, at least for the moment.
| | 04:17 | So I am dragging top or bottom edge
with the Shift key, putting it right here,
| | 04:22 | letting go of the mouse first.
| | 04:24 | ADC, we're going to drag it downward
| | 04:26 | using the Shift key. Put it underneath.
| | 04:28 | Let's go check out that list
and see how it's working now.
| | 04:33 | There's Manufacturing at the
top of the list. It works just fine.
| | 04:37 | We might consider the same kind of
thing with states as we suggested earlier.
| | 04:41 | Let's go back to Sheet1 where I put
this list and consider another aspect of
| | 04:45 | how we adjust the list.
| | 04:47 | We've added a new Marketing department.
| | 04:49 | Where are we going to put it?
| | 04:51 | If we put it at the bottom of the
list, it will not appear in here.
| | 04:54 | Let's type it here first, and maybe
it's not going to have that many people.
| | 04:59 | We should be able find it simply by
inserting it in its proper location.
| | 05:03 | So we'll simply drag this
upward with the Shift key and put it
| | 05:07 | alphabetically right in there.
| | 05:09 | It appears to have indenting also.
| | 05:11 | I'll un-indent that. There we go.
| | 05:13 | And how is our list going to look now?
| | 05:17 | Marketing will be there alphabetically
in the appropriate spot.
| | 05:20 | The last entry here is Research/
Development, and that is the last entry in the
| | 05:24 | list as we jump back here
and check that out as well.
| | 05:27 | Research and Development.
You want to insert from within.
| | 05:31 | If one of these organizations no
longer exists, you'll want to delete a cell--
| | 05:36 | not erase it or move, but simply right-click
and choose Delete and then Shift cells up.
| | 05:45 | So we are removing the group,
International Clinical Safety, from the list.
| | 05:50 | It's between Executive Education
and Logistics. And now it's gone.
| | 05:54 | Let's see what happens in our
list here as we try and use it.
| | 06:00 | It's not there anymore, as you would expect.
| | 06:03 | So the order of the lists, the placement
of the lists, and the ability to add new
| | 06:08 | entries and take out entries are
critical features that we need to be thinking
| | 06:12 | about if we're using the dropdown
list capability of data validation.
| | Collapse this transcript |
| Creating multitiered lists| 00:00 | In column A in this worksheet, data
validation criteria have been set up
| | 00:04 | so we click the arrow, choose the state we want.
| | 00:09 | Now what if the city choices that we
need here will vary based on the state?
| | 00:14 | What if we can have a pick list here that
only showed us the California cities, a
| | 00:20 | pick list here that only
showed us the Colorado cities?
| | 00:23 | In other words, a pick list that
depends upon data that's in the adjacent
| | 00:28 | column to the left.
| | 00:30 | Now this features are a little
tricky to set up, but it's really powerful.
| | 00:33 | I think you can see that it could be
applied to other kinds of data as well.
| | 00:37 | We want a pick list in column B based on what
happens in column A. Column A is already set up.
| | 00:44 | Once again, if you are curious about how
those rules are established, just click
| | 00:48 | on one of these cells,
| | 00:50 | go to the Data tab, choose Data
Validation, and you'll see a rule that shows
| | 00:55 | that this list is coming out of the
cells D1 to D12 on this worksheet.
| | 01:01 | You may eventually want to have this
list placed on a different worksheet, but
| | 01:05 | for now it's right here, just fine.
| | 01:07 | Now in column B, we want to be able to
say that whatever is in column A is going
| | 01:13 | to guide us as to what we will be
choosing, and the not-so-obvious choice is
| | 01:18 | going to be a function called Indirect.
| | 01:21 | Now, we're selecting column B, and so
for the moment the active cell is B1.
| | 01:27 | As we go to data Validation, the
Validation criteria under Allow will be List,
| | 01:37 | and here is the unusual step: =indirect,
and we're going to be referring to cell A1.
| | 01:45 | Just type it in. There it is.
| | 01:47 | Now, because B1 is the active cell, it
means--even though we're not saying this
| | 01:53 | literally--that for every cell in
column B, we want to be looking at the data
| | 01:58 | to its immediate left.
| | 02:00 | Now A1 and B1, we don't really care about;
| | 02:03 | nevertheless A1 is referred to in the formula.
| | 02:06 | As we click OK here, this message, a bit
disconcerting, simply tells us that the
| | 02:12 | indirect reference to A1 doesn't
really work, and we don't care about that.
| | 02:15 | Do we want to continue? Yes, we do.
| | 02:18 | Now, this doesn't work yet.
| | 02:20 | Click the drop arrow. Nothing happens.
| | 02:23 | We want CA, or California,
to refer to these names.
| | 02:28 | We want IN for Indiana to
refer to these names, and so on.
| | 02:32 | So let's select all of these cells
here, and then on the Formulas tab,
| | 02:39 | Create from Selection.
| | 02:41 | What we want to do is to create a
range name called AZ and have it refer to
| | 02:47 | those cells to the right, and CA and
have it refer to those cells to the right,
| | 02:52 | and on and on and on.
| | 02:54 | We want to create names from values in
the left column, not the top row. Click OK.
| | 03:02 | So CA, the range name CA, refers to all
these cells, and so when we click here
| | 03:08 | and use the drop arrow, we
see the California cities.
| | 03:12 | Maybe it's Sacramento here that we are choosing.
| | 03:14 | For Indiana, we're seeing South Bend.
| | 03:17 | We might have to scroll here.
| | 03:18 | We want to use Fort Wayne.
| | 03:20 | For Colorado, we see some cities.
| | 03:22 | We want to use Denver, and so on.
And as we pick new states here and there,
| | 03:27 | different states, or whatever states,
we will see the choices we need.
| | 03:31 | There is one aspect to this
| | 03:32 | that's a little disconcerting, and that
is that we do have to do some scrolling
| | 03:35 | sometimes, and that's because, for
example, like in Iowa, the name IA refers to
| | 03:42 | all of these cells, including the empty ones.
| | 03:45 | So there is a fix for this as well.
| | 03:47 | Let's highlight all of these cells here,
and get rid of the blanks, by doing what?
| | 03:53 | On the Home tab, the extreme right button,
Find & Select > Go To Special. Choose Blanks, OK.
| | 04:04 | Just the blank cells are selected.
Right-click on any of the blank cells,
| | 04:09 | Delete, Shift the cells leftward.
| | 04:13 | Now, that's the least critical
part of what we've done here.
| | 04:16 | But from now on, after we've chosen
the state--actually choose states that
| | 04:20 | doesn't have too many entries
in it for the best reference--
| | 04:23 | click Kansas here. The choice drop arrow,
| | 04:26 | we don't see those
trailing spaces. There we go.
| | 04:30 | So, a power tool to be sure, that any
feature that takes away our ability to--or our
| | 04:36 | error-prone ability to--make typing
mistakes is great, and so using a two-tiered--
| | 04:42 | for want of a better term here--data
validation setup that involves pick lists
| | 04:47 | is very, very efficient.
| | Collapse this transcript |
|
|
3. Date ControlsSetting date limitations with basic controls| 00:00 | Using Excel's data validation criteria,
you can control the entry of dates.
| | 00:06 | Take a look at column B here.
| | 00:07 | You probably recognize that cell B2
contains an impossible date. The next date is okay.
| | 00:13 | We want to have control over the date entries.
| | 00:17 | Let's click column B and
take a look at Data Validation.
| | 00:20 | We find it on the Data tab.
| | 00:24 | The Settings > Allow > Date, and in its
basic form, we see we can control dates
| | 00:30 | by keeping them within a certain
starting and ending date timeframe, and the
| | 00:35 | same choices that we see with values:
not between, so not equal to a certain
| | 00:40 | date, less than, and so on.
| | 00:42 | So there are all kinds of different
scenarios here as to control dates, and,
| | 00:45 | by the way, this feature will also
trap those impossible dates, like the one
| | 00:50 | we're seeing in B2.
| | 00:51 | Now a reminder, too, when you do apply
data validation--and let's say in this
| | 00:55 | example here, we want all these
entries to be within the year 2011--
| | 01:00 | the starting and ending dates are inclusive.
| | 01:02 | So we'll put in 1/1/11, and then for
the ending date, 12/31/11, and click OK.
| | 01:13 | Note that the entries that are
already there are not flagged.
| | 01:16 | However, it is worth pointing out in
Excel, if the columns were wider, we'd see
| | 01:19 | the bad date this way.
| | 01:20 | But from now on, certainly in this
column, if we do attempt to put in a date
| | 01:25 | like that--and no matter how we
type, it's going to catch this;
| | 01:28 | this is an impossible date--
| | 01:31 | And we get the error message.
| | 01:33 | As in prior examples, anytime you have
these error messages, if you wanted to
| | 01:36 | customize these, you certainly can do that.
| | 01:39 | But ideally in the best of all worlds,
we would get rid of this entry, and from
| | 01:43 | now on of course, all of our
dates are going to be accurate.
| | 01:46 | The feature is almost self-
explanatory in terms of its options.
| | 01:49 | Once again, under Data Validation
for Date type choices in the example
| | 01:54 | here, we're simply restricting them
to a certain timeframe, but we've got
| | 01:58 | those other choices as well.
| | 01:59 | It's an easy-to-use feature, and it
certainly makes data entry much more reliable
| | 02:05 | when it comes to date type entries.
| | Collapse this transcript |
| Setting date limitation formulas| 00:00 | In this worksheet, data validation rules
in columns C require that all the dates
| | 00:05 | occur in the year 2011.
| | 00:08 | Column D doesn't have any rules yet,
but in putting in the shipping dates for the
| | 00:12 | items that have been ordered, we are
going to make sure, first of all, that the shipping
| | 00:16 | date is later than the order date.
| | 00:18 | Now depending upon the environment of
course, you might want to set up a rule to
| | 00:22 | make sure that the shipping date is
equal to or greater than the order date.
| | 00:26 | But what if in this environment that
the items being ordered require some
| | 00:31 | assembly and packaging, and shipping
cannot occur within a two-day time frame?
| | 00:36 | In other words, it has to
be more than two days later.
| | 00:39 | We want to make sure that the entries
in column D are more than two days later.
| | 00:43 | In this case, maybe we don't want to
restrict these to the year 2011 because
| | 00:49 | toward the end of the year some of the
items might be shipped in the later year.
| | 00:53 | So clicking column D here and setting
up data validation, the first thought
| | 00:58 | might be, as you look at the dialog
box, that the validation criteria we are
| | 01:01 | trying to set up here involves using
probably a Date criterion, but it is not;
| | 01:07 | it is going to be Custom--
actually a custom formula.
| | 01:11 | And the formula will
involve using the active cell.
| | 01:16 | Now, I did select column D, and I
highlighted cells starting in D2. We probably see
| | 01:22 | D2 as the active cell.
| | 01:24 | See, in the upper-left corner here, to
the left of the Formula bar, but we are
| | 01:27 | seeing D1 right now,
| | 01:29 | so we will use this in the formula.
| | 01:31 | The formula here is not so obvious, but once
you see it, it will start to make some sense.
| | 01:35 | = D1>C1+2. Now although
literally we don't see the meaning here,
| | 01:45 | what this means is any entry in column D
must be more than two days greater than
| | 01:52 | the entry in column C. And so even
though we are not using D1 and C1, the formula
| | 01:58 | involves those two cells.
| | 02:00 | So when we click OK here, sometimes it
is a little startling at first when you
| | 02:04 | see this message, and it simply means the
D1 and C1 don't fit our little formula.
| | 02:09 | Do we want to continue? Yes, we do.
| | 02:12 | So, simple test here. How about the 5th,
| | 02:14 | 1/5/11, the 5th of January? Why is that wrong?
| | 02:21 | Our formula indicates the
shipping date must be more than 2 days,
| | 02:26 | not 2 days, but more than 2 days later.
| | 02:28 | We will do a retry and we will make this 6,
for example. How's that looking?
| | 02:34 | Fine, so we can put in any date here that's
more than two days later than the order date.
| | 02:39 | For every single cell here, the
comparison is made with the cell to its left.
| | 02:45 | Now what if we are making adjustments
here and we put in a different date?
| | 02:47 | I am going to put in an 8
here for the shipping date.
| | 02:51 | Then this thought might occur to you:
what if one of the dates that you put
| | 02:55 | in here is a Sunday?
| | 02:56 | Maybe you don't do shipping
on Sundays. Is that a Sunday?
| | 03:00 | Or maybe don't do shipping on Saturdays.
| | 03:02 | Let's also screen out
those kinds of entries as well.
| | 03:05 | Now, you may or may not be familiar with
this function, =weekday, an Excel function.
| | 03:11 | And in looking at a date, it will tell
us the day of the week--not initially in a
| | 03:16 | very meaningful way.
| | 03:17 | It gives us a value.
| | 03:18 | 7 does mean Saturday. 1 means Sunday and so on.
| | 03:23 | So if we don't want entries for the
weekday is equal to 7 or 1, that would be
| | 03:27 | Saturday or Sunday, we can
screen out those as well.
| | 03:31 | So what I would like to do here is
prepare this formula outside of, although you
| | 03:35 | can certainly do it with inside, data validation.
| | 03:38 | Let me just copy it in.
| | 03:39 | So I would like to use this function
here along with the current restriction
| | 03:45 | that the shipping date being more than
two days greater than the order date, and
| | 03:49 | so the function will read something
like this. And, meaning we have got two
| | 03:53 | criteria we want to set up here.
| | 03:56 | The first one we already wrote, but I
will write it again, and D1. Don't really
| | 04:01 | have to capitalize, but it
is easier to see this way.
| | 04:03 | D1>C1+2, and weekday of D1<>1, meaning
Sunday, and another one of these. And I will
| | 04:17 | just copy this and paste
it and change it slightly.
| | 04:20 | Ctrl+C here, click right here, Ctrl+V, 7.
Now the formula is not going to
| | 04:26 | stay into this cell.
| | 04:27 | I am just typing it here so
you can see it more clearly.
| | 04:30 | And here's the formula that we are
about to use as a validation formula.
| | 04:34 | And in English, it reads as follows.
In column D, every entry must be more than
| | 04:40 | two days larger than the corresponding
entry in column C, and the weekday of the
| | 04:46 | entry in column D cannot be
equal to 1--that's Sunday;
| | 04:50 | can't be a Sunday--and it cannot
be equal to 7, which is Saturday.
| | 04:55 | So all of this here is going
to be the validation formula.
| | 04:59 | You don't necessarily need to prepare it this way.
| | 05:01 | You can do this within the data validation
dialog boxes, which is what we are about to do.
| | 05:06 | But here all I am doing is highlighting
this and copying with Ctrl+C, and I might
| | 05:11 | want to leave it here for
a moment for documentation,
| | 05:13 | so I will just put in a space and press Enter.
| | 05:16 | Go back to Data Validation. Click
column D > Data Validation, and instead of this
| | 05:22 | rule, I'm simply going to press Ctrl+V
here to paste in the formula that I just
| | 05:28 | had displayed earlier.
| | 05:30 | Sometimes when you look at
these, they are a little clipped off.
| | 05:32 | I am going to drag
leftwards so we can see it all.
| | 05:34 | It looks a little funny at times.
| | 05:36 | So there is that same formula, which all
this could have been prepared within here.
| | 05:40 | Click OK.
| | 05:41 | Now the existing date
that's there will not change.
| | 05:44 | Data validation doesn't do
anything with existing data;
| | 05:48 | it is the new data that it is going to react to.
| | 05:51 | Once again, we get this kind of a
message, simply meaning that cells D1 and C1
| | 05:55 | don't fit our rules.
| | 05:56 | We do want to continue anyway.
| | 05:58 | So as an example here, I'll
try and change this to the 9th.
| | 06:01 | That's a Sunday. And we see that it doesn't work.
| | 06:05 | Now ideally, what we should do here to
accompany all this--and that was shown in
| | 06:10 | previous movies--going back to Data
Validation, provide either an input message
| | 06:15 | that describes the dates cannot be
Saturdays and Sundays, or possibly and create
| | 06:21 | an error alert message that
explains why an entry is incorrect.
| | 06:25 | You don't necessarily need to do those,
but it's helpful to explain to some
| | 06:28 | people what went wrong.
| | 06:29 | So on a different entry here and
I'll just type in a date here, 1/8/11.
| | 06:35 | That's a Saturday and we get the same
message. And of course a correct entry here
| | 06:39 | looks like 7 is going to
work there. Here we go.
| | 06:42 | That's a Friday. That's just fine.
| | 06:43 | So you get the idea.
| | 06:45 | The more you know about Excel functions,
the more inclined you are likely be to
| | 06:50 | use more sophisticated
approaches to controlling data.
| | 06:53 | So column D from now on has entries
that have to meet all three of the criteria
| | 06:58 | that we set up in this data validation formula.
| | Collapse this transcript |
|
|
4. Time ControlsLimiting time entries with basic controls| 00:00 | In this worksheet, there is a data
validation rule for column B that restricts
| | 00:05 | all of the dates to the year 2011.
| | 00:07 | If this company only takes orders from
6 a.m. until 8 p.m., similarly, we'd like
| | 00:13 | a rule in column C related to times
that makes sure that all the time entries
| | 00:18 | fall within that range.
| | 00:21 | Click column C, on the Data tab, Data
Validation. And under Validation criteria,
| | 00:27 | Allow > Time, and similar to Values and
Decimals and Data entries, we start off
| | 00:34 | by at least exploring some of the
choices here between a certain timeframe,
| | 00:39 | equal to, greater than.
| | 00:40 | Certainly, equal to wouldn't make any sense
here, but greater than, less than might
| | 00:44 | apply sometimes between.
| | 00:46 | So we're going to put in
the Starting time of 6 a.m.
| | 00:48 | For example, 6:00 would be the way to enter it.
| | 00:52 | We could also put in 6 a.m. if
we wished. Ending time, 8 p.m.
| | 00:56 | We might type it this way.
| | 00:58 | It should work too. Click OK.
| | 01:01 | So put an entry in here, 6:30.
| | 01:05 | We could type the entry
that way. That's just fine.
| | 01:07 | How about 6:30 p.m.?
| | 01:09 | You could type it this way.
| | 01:11 | I would caution you though: probably
you want to do some formatting here.
| | 01:14 | You could type it this way if you
wish, by the way. That's fine, too.
| | 01:17 | But let's put in a time
that's outside the frame.
| | 01:19 | 4:30 is automatically interpreted as 4:30 a.m.
| | 01:24 | That's outside the frame of the data
validation criteria that we set up,
| | 01:28 | so obviously that's not acceptable.
| | 01:31 | How about 8:30 p.m.?
| | 01:31 | Whether you type it 8:30 p, that
is a valid entry if we didn't have data
| | 01:38 | validation rules, but it's not acceptable here.
| | 01:40 | If we type in 24-hour style--17:00, for
example, that's 5 o'clock. Do it that way.
| | 01:49 | That's just fine.
| | 01:50 | So a variety of techniques here for
entering the data, but data validation is
| | 01:54 | focused only on the values
that you put in, not the display.
| | 01:58 | So ideally, on the timeframes here,
what you can do, either before or after
| | 02:02 | setting up the data validation, simply
right-click column C--one of many ways
| | 02:07 | to get to a Format Cells--and put in a
standard timeframe that fits your particular needs.
| | 02:12 | The two most common ones are the
second one, the so-called 24-hour style, or
| | 02:17 | the third one that shows a.m. p.m.
| | 02:20 | Now, no matter how you type this, if
the time is within the valid range, it's
| | 02:24 | going to appear in this style.
| | 02:26 | So a sale at 4:30 p.m.
you could type it as 16:30.
| | 02:32 | It will display this way.
| | 02:33 | It's well within the timeframe.
| | 02:35 | If you try and put in
8:30 p.m., type it this way.
| | 02:38 | It's not going to work, as
you would expect, and so on.
| | 02:41 | So, easy to set up, and most of the
time I think when you're using this
| | 02:44 | particular data validation criteria
set for Time, it's likely to be within
| | 02:50 | a certain timeframe: beginning and ending time.
| | Collapse this transcript |
| Limiting time entries with formulas| 00:00 | In this worksheet, a validation rule has
been set up in column B to restrict all
| | 00:04 | the dates to the year 2011.
| | 00:07 | In column C, there has been set up a
time validation rule that makes sure that
| | 00:13 | all of the times are from 6 a.m. until
just short of 8 p.m.: before 8 p.m., 6
| | 00:18 | a.m., and thereafter.
| | 00:21 | Someone has decided that it's going
to be a little bit easier to read under
| | 00:24 | tabulate information
| | 00:25 | if the actual minutes that occur
in these times are multiples of 15--
| | 00:30 | in other words, if these were 4:30,
4:45, 10:30, 10:45, 11 o'clock, 11:15.
| | 00:36 | We want all these entries to have the
minutes to be either zero, 15, 30, or 45.
| | 00:40 | Now you may or may not know that
there is a way to calculate actual minutes
| | 00:46 | here, and this particular function right
here will simply show us the minutes of
| | 00:52 | a particular data entry, 37,
and we can see quickly here a 32 and 45.
| | 00:58 | Now the original data validation rule
here--click column C, Data Validation on
| | 01:05 | the Data tab--restricts the entries.
| | 01:08 | You see the timeframe here.
| | 01:10 | Under Validation criteria, we use Time.
| | 01:13 | Now, if we also want to control the
minute entry here, it's going to require
| | 01:17 | a formula, and we'll have to use the Custom
Validation criteria entry here instead.
| | 01:25 | So we still need to incorporate this idea
that we want to keep them within that timeframe.
| | 01:30 | So I think it's best when we do this
sort of thing to prepare the formula
| | 01:34 | outside of the Data Validation
dialog box, and then paste it in.
| | 01:39 | So perhaps starting right here--and I
could drag this up even though C1 doesn't
| | 01:43 | have a date, and we are going to be
using this as one of our three criteria.
| | 01:48 | We want to use the AND
function to encompass all of these.
| | 01:52 | So in English, we would like to say
that the minute entry of all of the dates
| | 01:56 | that we have here are going to be evenly
divisible by 15, and you might or might
| | 02:02 | not be familiar with a function called MOD.
| | 02:04 | The MOD function allows us to
calculate the remainder in a function.
| | 02:10 | So let's say that we will have a value here.
| | 02:13 | It's going to be one through 60.
| | 02:15 | The MOD function allows us to say, if
we divide this value by 15, we're going
| | 02:21 | to get a remainder.
| | 02:22 | We want to make sure that
this remainder is always zero.
| | 02:26 | So if the number is a 15, we
divide by 15. The remainder is 0.
| | 02:30 | If it's a 30, we divide by 15.
| | 02:32 | The remainder is 0. And so on.
| | 02:35 | So that's quite a bit
happening here all at once,
| | 02:37 | but this is one of the three criteria we need.
| | 02:40 | The other two relate to the time.
| | 02:43 | There is another function
called Hour. We'll choose Hour.
| | 02:46 | We want the hour of C1 to be greater
than or equal to 6, meaning 6 a.m.,
| | 02:53 | comma and a similar construction,
| | 02:56 | so I'll just copy this and
change it slightly. Ctrl+C here.
| | 03:01 | Click here, Ctrl+V. We also want to make
sure that the hour is less than 20, and
| | 03:07 | then put in the less than symbol here.
| | 03:10 | So, these three criteria. Now, the
formula we're going to paste in to the data
| | 03:15 | validation rule, but just to check it out here
| | 03:17 | again, the three things we're trying
to do is to make sure that the minute
| | 03:20 | entry is evenly divisible by 15, that
the hourly entry is greater than or equal
| | 03:26 | to 6--meaning 6 a.m.--and the hourly entry
is before 8 p.m. is less than or equal to 20.
| | 03:33 | That's the hour of the day.
| | 03:35 | Now we wouldn't really leave the
formula here for now. I am going to put a
| | 03:39 | space in front of it, so we can--at
least for a while--highlight all of this to
| | 03:44 | copy it, Ctrl+C, and simply press
Enter to put it here for the moment.
| | 03:48 | Let's come back to column C, Data Validation.
| | 03:53 | We're not going to be using the
existing criteria the way it's structured here.
| | 03:57 | We want to use Custom. And instead of
this entry here, I am pressing Ctrl+V to
| | 04:03 | paste in the long formula that you just saw.
| | 04:06 | So there is our new formula.
| | 04:07 | We'll click OK. And this currently means
that simply cell C1 doesn't work. And we
| | 04:13 | don't care about that.
| | 04:14 | We do want to continue.
| | 04:16 | Now, data validation does not
do anything with existing data,
| | 04:20 | so we'll have to change these manually.
| | 04:22 | Let's make a new entry here.
| | 04:23 | Here is another entry and it's 1:45 p.m.
| | 04:27 | How about 1:47 p.m.?
In other words, we'll violate the rule.
| | 04:31 | You can type it this way.
| | 04:32 | That's a valid entry under normal
circumstances, but not here. It's not valid.
| | 04:36 | Let's do a retry.
| | 04:38 | Change that to 5, press Enter, and it
does work, and we can try a few more
| | 04:43 | just to test it out.
| | 04:45 | It's also a valid way to type an
entry if you simply type in, if it's
| | 04:49 | only hour, for example---
| | 04:51 | What if it's 2 p.m.? 2 space p is a
valid way to type these. There we go.
| | 04:56 | Now also to accompany this, you
should use formatting in column C that
| | 05:01 | standardizes your entries.
| | 05:03 | In the example here, I previously had it set up,
but you could easily change it if you want to.
| | 05:08 | I'll right-click column C--one of
many ways to get to Format Cells--and
| | 05:13 | consider either this time format that
uses the a.m. p.m., or the previous one
| | 05:18 | that uses the 24 hour style.
| | 05:20 | Whatever fits your needs best here,
| | 05:22 | that should be done also, either before
or after you apply the data validation.
| | 05:26 | But in column C, we've got a
pretty substantially long formula.
| | 05:31 | At first, it looks a little complicated,
| | 05:32 | but ultimately, it's doing three things.
| | 05:35 | It's making sure that any minute entry
that we put in here, once we've set up
| | 05:39 | the rule, is going to be an even
multiple of 15, and the hours are going to be
| | 05:44 | within 6 the 6 a.m. to 8 p.m. timeframe.
| | 05:47 | So in the examples here, we can jump
back in and change them. And, by the way,
| | 05:51 | we can't change them to a 6, or a 3
or something like that for the minute.
| | 05:55 | This will not work. But we certainty
could change it to a zero or make it be 45.
| | 05:59 | If it's as long as a multiple of 15 and
to be consistent, we would do this one
| | 06:04 | as well, too, probably change it to that.
| | 06:07 | So quite a few elements of creativity you
can use when setting up time formulas by
| | 06:11 | way of data validation.
| | Collapse this transcript |
|
|
5. Text Length ControlsLimiting text length with basic controls| 00:00 | With data validation, you can control
the length of entries in a range of cells.
| | 00:05 | For example, in column A, if we want to
put in Social Security Numbers, these are
| | 00:09 | always nine characters in length,
and we don't want any entries that consist of
| | 00:13 | eight or 10 characters, or
anything else that's not nine.
| | 00:17 | Employee IDs in your organization
might be always six characters in length.
| | 00:22 | You want to make sure that that's true
by putting in a validation rule here, too,
| | 00:26 | that controls the length of the entry.
| | 00:28 | Phone numbers these days are
nearly always 10 characters.
| | 00:32 | You could put restrictions here, too, by
way of data validation. And although this
| | 00:36 | feature is called Text length, we
could even use it for certain kinds of
| | 00:40 | numerical information, although
usually there's a better way by using other
| | 00:45 | criteria, for example, to control prices.
| | 00:47 | Let's focus on column A here.
| | 00:49 | We want to make sure that the
Social Security Number entries here are
| | 00:53 | always nine characters.
| | 00:55 | You should also accompany this
with an Excel feature for formatting.
| | 00:59 | I'm right-clicking column A here to go
into Format cells. There is a Number
| | 01:04 | category called Special, and one of
its choices is Social Security Number.
| | 01:09 | What this does is that in the display
of the values we will see the hyphens.
| | 01:15 | If you don't use this feature, you
won't see the hyphens, and it's not a great
| | 01:19 | idea to type them anyway, so use this.
| | 01:21 | This is highly recommended.
| | 01:23 | Do that. Now you can do this before
or after the data validation rule.
| | 01:26 | Let's go to the Data tab, Data Validation.
| | 01:30 | We simply want to ensure that the
length of the entry here, the Text length,
| | 01:36 | is not between certain number of characters,
but it is exactly equal to, in this case 9.
| | 01:44 | That's the restriction.
| | 01:45 | I'm just going to type in nine characters here.
| | 01:47 | Not a problem. Eight
characters, not good enough. Retry.
| | 01:57 | I missed the one in front of it,
whatever it was. Of course, I am making these up,
| | 02:00 | and it is not same as yours,
And sure enough, if we put in 10 characters,
| | 02:06 | that's too many, and so all of these fail.
| | 02:08 | As always with data validation, you
might want to put in either an input message
| | 02:13 | or an error alert message that explains
why these entries are incorrect. But you
| | 02:18 | see how easy it is to set up.
| | 02:20 | And then the first is
employee ID, of course same idea.
| | 02:22 | I want to complete this, but here, too,
you might want to consider a Text length.
| | 02:26 | And if your organization uses employee
IDs that are exactly 6 characters long,
| | 02:30 | then that's what you would use here as well.
| | 02:33 | And for Phone Numbers, too, same idea.
Here, too, as with Social Security Number, a
| | 02:37 | good idea to format these and use
this special built-in category for phone
| | 02:42 | number, and this we will put
in the parentheses and hyphens.
| | 02:45 | It should work just fine.
| | 02:47 | So the data validation rule here to
accompany the formatting should also include
| | 02:52 | the Text length entry that
is equal to 10, 10 characters.
| | 03:00 | So here's a phone number. Good enough. Might
need to make the column wider. There we go.
| | 03:07 | And sure enough, if we type not enough
characters, we get this kind of a message,
| | 03:13 | as you would expect. And if
we type too many, same idea.
| | 03:16 | So fast, easy ways to control
Text length, and I mentioned just briefly,
| | 03:20 | you could use these for numbers.
| | 03:22 | If all the prices for particular items
in this group are three characters, sure,
| | 03:27 | you could do that. But it would be
probably better in a situation like this,
| | 03:30 | rather than using Text length--and
again, I emphasize you can use Text length
| | 03:34 | for numbers--but probably it would be
better here to use either whole number or
| | 03:38 | decimal, and then as the case might
be, put in the minimum and maximum.
| | 03:42 | That would be a better way to
use this particular feature here.
| | 03:45 | But there's no question that using
Text length has its merits, as we've seen
| | 03:49 | in the examples here.
| | Collapse this transcript |
| Limiting text length with formulas| 00:00 | In column A of this
worksheet called Text Formulas,
| | 00:03 | there is a data validation rule that
ensures that the Social Security Numbers
| | 00:08 | are always nine characters long.
| | 00:10 | That number must be that length;
| | 00:12 | it can't be any more or any fewer.
| | 00:14 | And that's just fine. It works great here.
| | 00:16 | Notice that there's no
formatting that exists here.
| | 00:19 | That probably should be applied,
but that is a different issue.
| | 00:21 | So I'm going to put in a Social
Security Number, and probably no one would ever
| | 00:25 | type one like this, but you certainly could.
| | 00:27 | This has nine characters, but note
that it does have a letter in it.
| | 00:31 | And here and there someone might see an
I or zero and somehow think that might
| | 00:36 | occur there, and you could certainly
imagine certain kinds of code numbers where
| | 00:40 | maybe it's not clear that they should be
numerical, but here it is. And the data
| | 00:45 | validation rule as it
exists doesn't work right now.
| | 00:48 | So let's change it and make
sure that the entry is numerical.
| | 00:53 | The data validation rule that we
already have is in place, and here it is, and
| | 00:57 | it's all about the Text length.
| | 00:58 | If we want to use other criteria
along with this, we have to change the
| | 01:04 | choice here to be Custom, meaning custom formula,
and there are two things we want to do here.
| | 01:10 | We want to retain this idea, so we need
the And function, = And, and then the
| | 01:16 | two criteria within this will be,
first of all, the one related to length.
| | 01:21 | You might or might not know
| | 01:22 | there is a function called LEN, meaning length.
| | 01:25 | You want to be sure that the length of
these entries--and we'll use A1; it acts as
| | 01:30 | a substitute for all cells in column A--we
want to be sure that the length of A1 equals 9.
| | 01:37 | That's one of our two criteria. And the
other one is that we want make sure that
| | 01:42 | this is a number. And sure enough,
there's a function called ISNUMBER.
| | 01:48 | We want to make sure that A1 is a number.
Both criteria we want to be true here. Click OK.
| | 01:54 | Now data validation doesn't alter
existing entries, so that's still incorrect,
| | 01:59 | and we would change it manually.
| | 02:00 | But let's just test this out a little bit.
| | 02:02 | Let's put in something like that B
entered. 9 characters, but it doesn't work.
| | 02:11 | Now because we have a new data
validation rule and just revisit that briefly
| | 02:17 | here back to Data Validation to show it again.
| | 02:21 | Two criteria: the length must
be 9 and the entry must be a number.
| | 02:26 | Now you could have another kind of code,
something along the lines of what you
| | 02:30 | might be seeing in column B. This
might be three characters, four characters,
| | 02:34 | five characters, whatever. But let's
say that the rule here is that this must
| | 02:38 | begin with a number. And the first
one does, the second one doesn't.
| | 02:43 | A little bit of background in text
functions might help here, and so if you had
| | 02:47 | this kind of need, you might know an
easy function to use is called Left.
| | 02:52 | There is a similar function called Right.
| | 02:53 | This simply allows us to look at a
cell and to extract the left character--
| | 02:59 | that would be 1--the two
leftmost characters, a 2, and so on.
| | 03:03 | So the leftmost character here, sure
enough, is a 2, and if we copy the formula
| | 03:08 | down to here, it's a T.
| | 03:11 | Now, is this a number?
| | 03:14 | Here's a function called--which we
just saw--NUMBER. Is this a number?
| | 03:20 | Well, it looks like a number to me.
| | 03:23 | This one doesn't; that seems to be
doing the right thing, but this doesn't, so
| | 03:26 | that might throw you a little bit.
| | 03:27 | There is another function we need here,
and it's called VALUE, and so we want to
| | 03:33 | take the value of this. Is that a number?
| | 03:40 | Yes, it is.
| | 03:41 | So backtracking a little bit, we want to
create a data validation rule here that
| | 03:46 | makes sure that the leftmost
character is a number, so a data validation rule
| | 03:53 | here and here, too, a custom function.
Equal. We want to be focused on only the
| | 04:01 | leftmost character here, so L-E-F-T
the left character. And once again, we use
| | 04:08 | the active cell as a substitute for all
the other ones. Comma 1, that means the
| | 04:13 | first character only.
| | 04:15 | We want to be sure that that is a number,
but we want to be looking at the value
| | 04:23 | of that leftmost character. And often
when you do these, of course, it's really
| | 04:30 | to make a mistake regarding how many
parentheses we need here. There we go.
| | 04:34 | I think that looks right.
| | 04:36 | Sometimes when you click OK here, you
are warned that your formula is incorrect,
| | 04:41 | but it could be syntactically
correct and logically wrong.
| | 04:44 | In this case, the syntax is correct.
| | 04:47 | Once again, existing entries are not altered.
| | 04:50 | Let's see if this works.
4, 5, 6, of course that should work.
| | 04:53 | And, by the way, this doesn't have any
restriction of length just yet--maybe
| | 04:56 | we'll do that later--but that's
in place and that seems to be okay.
| | 04:59 | But how about those that begin with a letter?
| | 05:01 | That one is not acceptable.
| | 05:03 | And as always, we might consider changing
the error alert to explain what's going on.
| | 05:07 | So the more you know about various
Excel functions, the more likely you are to
| | 05:12 | expand your use of data-validation type
formulas, and in this example here, we
| | 05:17 | saw how it was used to control
various text-length types of entries.
| | Collapse this transcript |
|
|
6. Specialized Custom Formula ControlsPreventing duplicate entries| 00:00 | For certain kinds of situations, you
might want to preclude duplicate entries.
| | 00:05 | In other words, you might prefer unique
entries--say, for example, in column B.
| | 00:10 | The first employee ID and the
third one happens to be identical.
| | 00:14 | That's the kind of thing
you sometimes need to prevent.
| | 00:17 | There's no obvious choice here under
Data Validation, Data Tab > Data Validation.
| | 00:23 | None of these choices here
seems to tackle that issue head on.
| | 00:28 | However, a custom formula
will allow us to get there.
| | 00:31 | Let's build this outside of the
Data Validation dialog box first.
| | 00:36 | The idea is, every time an entry is
made, we want to compare this with other
| | 00:40 | entries in the same column, to make
sure that it exists once and only once.
| | 00:46 | You may be familiar with
a function called COUNTIF.
| | 00:51 | We're looking in column B, throughout
column B, comma. And let's just imagine for
| | 00:58 | the moment we're talking about
cell B2. You can click on it or type it.
| | 01:02 | So what we're going to get out of this
is the count of how often the B2 entry
| | 01:07 | appears in column B. It appears there twice.
| | 01:12 | Now, all we really need to do is to
take a variation on this and in effect say,
| | 01:18 | 'We want the count of any
cell that gets entered here.
| | 01:22 | We want to make sure that it's there once
and only once,' the actual current entry.
| | 01:27 | So this our starting point and will
change a bit, because we will be referring
| | 01:32 | to B1 in our formula.
| | 01:33 | Let's simply copy this, Ctrl+C, press
Escape, go to column B, and set up a data
| | 01:39 | validation criteria formula under
Allow > Custom, a custom formula.
| | 01:49 | I'm going to press Ctrl+V now to paste this in.
| | 01:51 | It's not quite there.
| | 01:53 | We want to make sure that the count of
B1, since that's currently the active
| | 01:57 | cell, it acts as a
substitute for all the others.
| | 02:01 | And anytime we make an entry, we
want to compare that entry with all the
| | 02:05 | entries in column B. So the number of
times that this item should appear here
| | 02:10 | must be equal to 1.
| | 02:11 | In other words, it's there
once and only once. Click OK.
| | 02:16 | So, test it out again here.
| | 02:18 | I'm going to type in '134267'.
The entry is already there.
| | 02:23 | As always, we might want to customize
the error alert message to indicate what's
| | 02:28 | happening here. Slight change. Maybe
it was a missed type entry. Put an 8 in.
| | 02:32 | That should work just fine.
| | 02:34 | Remember, the prior entries
don't get screened out yet.
| | 02:37 | That's up to you to clean those up, if
you're applying the rule with data already here.
| | 02:41 | We'll change that to a 5.
It shouldn't be a problem.
| | 02:44 | That's what it should have been all along.
There here we ago. But we certainly can't
| | 02:48 | enter in that top number,
219876. It's there once.
| | 02:53 | This would be twice. Can't do it.
| | 02:56 | So you can restrict unique entries using
this data validation formula here, once
| | 03:01 | again, display of it, as we see it right here.
| | 03:04 | And of course, in different
columns use different letters there.
| | 03:07 | It works fine, easy. Familiarity with
the COUNTIF function helps you understand it
| | 03:11 | better, but it's easy to use.
| | Collapse this transcript |
| Locating data validation rules| 00:00 | As you work with certain worksheets,
you may encounter a validation rule and
| | 00:05 | you didn't know it was there.
| | 00:06 | For example, I might click in column
C3 here. Now, I haven't put in the person's
| | 00:10 | name yet, nor the department, but I'm
going to put in his salary right now, and
| | 00:13 | it's 102,000. And I type
this, and I get a message.
| | 00:18 | And the message doesn't tell me
what's wrong with it or anything, so I'm a
| | 00:20 | little concerned about that.
| | 00:23 | It raises the larger question, are there
other data validation criteria in place
| | 00:28 | in this worksheet, and how do we find them?
| | 00:30 | And with it this worksheet
has many, many more columns?
| | 00:34 | You would want to know
where data validation exists.
| | 00:38 | There are two easy approaches to it.
| | 00:41 | On the Home tab, the rightmost button,
Find & Select, click it, and here is a
| | 00:47 | choice that says Data Validation.
| | 00:49 | When you make this choice, it highlights
all cells in the current worksheet that
| | 00:54 | have a data validation rule.
| | 00:56 | Now it's up to you to figure them out.
Possibly, although certainly not in this
| | 01:00 | case, adjacent columns might have the
same rule. Certainly not here. But it
| | 01:05 | looks as if, as is often the case,
there's a data validation rule for column A,
| | 01:10 | column C, and column D. The next step
often is, for the particular column in
| | 01:15 | question, say in column C, right-click on
one of the entries and just in column C,
| | 01:22 | click on just one of the entries in
column C, and then on the Data tab, jump
| | 01:27 | into Data Validation to see what the rule is.
| | 01:31 | All the entries here have
some kind of a restriction.
| | 01:34 | It says, the maximum salary here is 99999.
| | 01:38 | So the 102,000 wasn't
accepted and now we know why.
| | 01:42 | If you do have a worksheet with
many, many different cells with data
| | 01:45 | validation and sometimes they are ranges that
are not necessarily in sync with columns and rows,
| | 01:51 | sometimes what you'll want to do after
clicking on a cell is say, I want to know
| | 01:56 | which other cells have
the same rule as this one?
| | 02:00 | Again, logic usually rules here,
but another approach to this is to click the
| | 02:05 | Home tab and then go to Find & Select,
but this time choose Go To Special.
| | 02:13 | And here's a choice called
Data validation at the bottom.
| | 02:17 | There are two choices here.
| | 02:19 | By clicking Same, what we're saying is,
let's highlight all the other cells in
| | 02:25 | this worksheet that have the same data
validation criteria as the current cell.
| | 02:31 | Now, many times this is obvious, but not
always. Click OK here, and sure enough,
| | 02:35 | we are expecting all of these cells
here have the same data validation criteria
| | 02:41 | as the cell in question.
| | 02:43 | And from there we could then go to Data >
Data Validation and look at the rule in place.
| | 02:51 | These cells here work off of a
pick list over from column M, and cell D1 is not
| | 02:57 | part of the list, as it sometimes could be.
| | 02:59 | Two ways to check out where data
validation cells exist in a worksheet and where
| | 03:04 | necessary, to see which other cells
have the same data validation criteria as
| | 03:10 | the current cell does.
| | Collapse this transcript |
|
|
ConclusionGoodbye| 00:00 | After viewing this course, you might
want to visit lynda.com and view other
| | 00:04 | Excel offerings as well.
| | 00:06 | Simply click on Software, go to the
letter E, and choose Excel, and there is an
| | 00:10 | ever-expanding list of other Excel courses.
| | 00:14 | You might want to check out my Excel 2010
Power Shortcuts Course or Dates and Time Course.
| | 00:20 | You I might want to check out Bob
Flisser's Essential Training or Real-World
| | 00:24 | Projects with Curt Frye, and be sure to check
back here from time to time as the list grows.
| | 00:30 | Thank you for watching.
| | Collapse this transcript |
|
|
