Time
6 hours 3 minutes
Difficulty
Beginner
CEU/CPE
6

Video Transcription

00:00
this lesson is going to about be about creating drop down lists and how to validate to data in a cell.
00:08
And this function is very useful if you're creating some kind of template or a table in which you were, some other people have toe enter day town a regular basis. So let's look at one example.
00:23
For example, you're just monitoring the
00:26
the temperature maximum daily temperature in a certain period of time. So you have day of the week and maximum daily temperature. And let's add another column, which would be, for example,
00:41
ah week number
00:45
so it can be from 1 to 52 you know,
00:50
regularly because one year has 52 weeks
00:55
and, um
00:56
so we want
00:58
us to prevent
01:00
somebody entering here, for example, 53 or entering here just number four instead off Thursday or Wednesday or whatever.
01:11
So we're going to delete this for a moment
01:17
and let's do first about week number
01:22
and, uh, let's go to date a validation.
01:27
And so we have been You know, when you click on this, we get to dissolve box, and we have here
01:34
settings and, uh
01:37
ah,
01:38
this tells us that
01:42
it can be a whole number decimal list, date, time, text or custom.
01:49
And it can be
01:52
Ah, let's a whole number
01:56
and let's say minimum is one
01:59
and maximum is 52.
02:02
So this way
02:06
we have limited what can be entered in this cell
02:08
between one and 52. Inclusive thes two values
02:14
Speak. Okay,
02:15
Now let's try to enter here something that is not possible. So, for example, zero. We got them error message, which says this value doesn't measure the data values the Dacian restriction defined for this self.
02:27
So
02:29
let's cancel,
02:30
and we can enter something else here. But also what we can do
02:35
is we can customize this message
02:38
and ah ah so we can click of warning
02:44
and we can click their message.
02:46
Ah, week number
02:51
can be
02:53
between one and
02:57
52.
03:00
And let's talk. Okay, so if we try to enter here like zero
03:06
week number
03:07
can be between one and 52 they're going to continue. No. Yes. Now cancel. We say cancel and that's it.
03:15
Now
03:16
here we have, ah, slightly different situation because we have a day of the week. It can be Monday, too,
03:25
said Sunday,
03:28
or someday to Saturday depending on how you
03:30
look at the which days, starting day of the week.
03:35
And, um
03:37
then it, uh
03:38
you have to create the list.
03:40
So I have created list here. We have Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday.
03:47
So I'm going to go here in data validation like undated validation.
03:53
And, uh, I'm just going to beckon settings. And now I'm going to choose list here,
04:00
and I'm going to click on source
04:03
and it's going to be like this
04:08
and I'm just going to sit on.
04:11
Okay,
04:12
so now you get the drop down list here
04:15
and you can enter on Lee items from this list
04:20
Now, in case of week
04:23
number, we could have done the same thing, but then this list would had
04:27
52 entries
04:29
and the it wouldn't fit on a screen, So it makes no sense.
04:34
So if we click your and maybe
04:39
zoom in a little bit so you can see a bit better.
04:43
So let's say it was weak 48 it was Wednesday,
04:46
and now we have maximum daily temperature. Now, this can be a negative number of positive number, but what we cannot allow here is for text to be entered.
04:58
So let's go to date a validation again.
05:01
And ah,
05:03
we, uh,
05:05
can save can be a decimal number
05:09
because it can be negative. It can be positive.
05:13
And let's say that daily temperature, maximum daily temperature off. I don't know, minus 50
05:23
maybe doesn't make sense. But let's say that minus 60 is the minimum
05:28
and the maximum is maybe 60. So you cannot enter the daily temperatures. I don't know, 100 degrees. I'm talking about Celsius because it's ah,
05:39
it's not happening.
05:40
So let's take OK and hear if somebody wants to enter 75
05:45
degrees Celsius
05:47
well again, get the message off course, we can customize it, but it really doesn't matter for this
05:54
Ah
05:57
example. So you get this and of course, you can copy these cell before you entered anything and you can create
06:04
Ah, full table for the M in Ukraine, created for for quite a lot of number of cells and the formatting, the validation will copy with cells. So you can, uh, you can create your template or table in such a way that
06:23
people simply don't enter the wrong data.

Up Next

Intro to Excel

This basic Excel training course will give you knowledge of Excel and the ability to use this software to do elementary calculations, file manipulation, data manipulation, to create charts, pivot tables and templates.

Instructed By

Instructor Profile Image
Milan Cetic
IT Security Consultant
Instructor