Locked lesson.
About this lesson
Understand how to restrict what end users type into a cell using Data Validation, with examples of text length and custom validation.
Exercise files
Download this lesson’s related exercise files.
Data Validation Part 2.xlsx8.8 KB Data Validation Part 2 - Solution.xlsx
8.8 KB
Quick reference
Data Validation Part 2
Data Validation explained.
When to use
This feature restricts what end users may type into a cell. Similarly to styles: many aren’t aware of this function in Excel, but once you use this functionality and understand what it can do for you, you never go back to whatever it was you were doing before.
Instructions
Example 1 – Text Length
- To access data validation, from any cell in Excel:
- On the Data tab of the Ribbon, go to the Data Tools group and click the Data Validation icon (ALT + A + V + V)
- Change the selection in the ‘Allow’ drop down box to be ‘Text Length’.
- Enter the ‘Minimum’ and ‘Maximum’ characters to be 2 and 4 as shown in the example
- Click ‘OK’
- This will now only allow the users to type in text between 2 and 4 characters long
Example 2 – Custom Validation with the use of a formula
- Change the selection in the ‘Allow’ drop down box to be ‘Custom’
- In the ‘Formula’ box’ type the formula =MOD(E10,2)=0
- This formula will ensure that only an even number can be typed into that cell
Login to download
- 00:04 More on data validation.
- 00:06 As promised, let's look at the last two data validation examples from our file.
- 00:13 The first one is for Cell E8.
- 00:16 And we've got to make it so that the only inputs allowed here
- 00:20 All those were the text lengths between two and four characters.
- 00:24 This is surprisingly simple.
- 00:26 Alt + DL, you'll recall the keyboard shortcut for data validation.
- 00:31 And all you have to do is find the right allowance,
- 00:34 it's actually text length, minimum of two, maximum of four.
- 00:39 Whenever you see these buttons, this can act as cell reference.
- 00:42 For instance instead should you wish.
- 00:44 Click OK.
- 00:45 If I type in Liam, that's four characters.
- 00:48 It's okay.
- 00:49 But if I type in Liam with a space, that's not.
- 00:53 That's one of the reasons we use drop down boxes,
- 00:56 because people often will put spaces afterwords if they're touch typists.
- 01:00 This is a way to sort of To prevent that.
- 01:02 And also to stop problems with forms if characters become too long.
- 01:08 This is what data validation does.
- 01:11 It stops you having to write more complicated calculations for
- 01:15 formulas later.
- 01:16 If you've got a formula that's gonna cause a problem if the input value is zero.
- 01:21 Why not prevent someone from putting a zero in in the first place?
- 01:25 That's the whole idea.
- 01:27 Now, the even number only at the end,
- 01:30 well, we've actually looked at even numbers once before here.
- 01:34 You may recall I said I was going to talk about the MOD function properly,
- 01:37 and I will.
- 01:38 What MOD does is it looks at the remainder after you divide a number.
- 01:43 So 3 divided by 2 is 1 remainder 1.
- 01:46 So the mod of 3,2 is what?
- 01:50 Its just it's the remainder.
- 01:52 So an even number is that the remainder is zero after dividing by 2 and
- 01:56 that's what I am going to work on here.
- 01:58 So 'Alt' 'D' 'L'.
- 02:01 I'm going to go here.
- 02:03 Well I can't go whole number,
- 02:04 because there isn't something here that allows me to go for equal, doesn't work.
- 02:08 So I'm gonna have to go down to the bottom, custom.
- 02:12 And I'm going to put in here equals the amount of what I want.
- 02:15 Well I'm in Sally ten so it's going to be ten.
- 02:19 And, it is divided by two, has to be zero.
- 02:25 So there's no remainder.
- 02:27 Then it will work.
- 02:28 If I click okay, if I put 17 in here, it won't work.
- 02:34 If I put 1.999 It won't work.
- 02:39 If I put two in, it's happy.
- 02:41 If I put 26 in, it's happy.
- 02:44 How simple is that?
- 02:47 What you should try and do is when you're actually building a model,
- 02:51 not only should you be considering likely text, units and actually formatting it.
- 02:57 You should be considering.
- 02:58 Should I put any data validation in as I go?
- 03:01 I'm defining it there and then.
- 03:03 It'll make formulas that are dependent on these inputs so
- 03:07 much simpler as a consequence.
- 03:09 It really is easy.
- 03:12 Practice with it, it's very useful.
- 03:15 And unlike Conditional formatting data violation does move if
- 03:19 you start inserting rows and columns.
- 03:21 So it's not so much of a problem.
- 03:24 So I've now got data validation sorted out.
- 03:27 I've got conditional formatting.
- 03:30 I'm in a position now where I start thinking,
- 03:32 okay I've got to create a template working here.
- 03:34 Perhaps now I can consider the model integrity, I think about checks.
- 03:39 And that's what we're going to look at next time.
Lesson notes are only available for subscribers.