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

Similar Content