Locked lesson.

Upgrade

About this lesson

Understand how to restrict what end users type into a cell using Data Validation, with examples of lists and whole numbers.

Exercise files

Download this lesson’s related exercise files.

Data Validation.xlsx
8.5 KB Data Validation.xlsx
8.8 KB

Quick reference

Data Validation

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

Overview

  • Only allows certain entries in a cell
  • Can use lists similar to drop down boxes
  • Difficult to identify
  • Does not update automatically

Example 1 - List

  • 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 ‘List’.
  • With ‘List’ selected, the dialog box prompts for a source for the list.  In the illustration, the entries have been selected from the data in cell references from the worksheet

Example 2 – Whole Number

  • Instead of selecting ‘List’ change the selection in the ‘Allow’ drop down box to be ‘’Whole Number’
  • In the ‘Data’ dropdown list change select ‘Greater than’
  • In the ‘Minimum’ box type ‘0’
  • With the ‘Show input message when cell is selected’ checked, if the end user selects the data validated cell the message typed in here will appear.  This can make data inputs in a model much simpler as end users are ‘spoon fed’ with a pop-up box detailing what to do. A message can be typed in here such as the one shown above “Hint - Enter a positive whole number”
  • The third tab selects what to do if invalid data is entered in the cell.  This alerts the end user when an invalid entry has been made.  A message can be typed in here such as the one shown above “Incorrect entry - Enter a positive whole number”

Login to download
  • 00:04 We've looked at range names, and we've also looked at hyperlinks.
  • 00:09 Another useful Excel feature in financial modeling is data validation.
  • 00:15 Data validation is the ability to only make certain data valid in a cell.
  • 00:21 That is other data will be rejected if you try and type it in.
  • 00:25 You can have a drop-down box,
  • 00:27 I showed you that with the list when we were talking about inputs earlier.
  • 00:30 But they can be difficult to identify, and they don't update automatically.
  • 00:35 Let me explain with some Excel examples.
  • 00:39 Right, let's start on familiar ground, first of all with a recap on lists.
  • 00:44 So data validation is found on the Data tab under Data Validation,
  • 00:50 and it comes up here, keyboard shortcut, Alt+D+L.
  • 00:55 By default, all cells in Excel allow any value, but that can be changed.
  • 01:00 You can make it just a whole number.
  • 01:02 To do that, you can specify between a minimum and
  • 01:05 a maximum, which can be cell references, not between, equal to, etc., etc.
  • 01:09 More on that in a minute.
  • 01:11 We can go for decimals.
  • 01:12 Now, a decimal is just all numbers, not just whole numbers.
  • 01:16 We can go for lists, which is the one I'm going to look at.
  • 01:18 And several.
  • 01:19 Let's look at List.
  • 01:21 The source is here.
  • 01:24 I'm going to click OK, and I have a dropdown box now for my list.
  • 01:29 So I'm going to choose my colors.
  • 01:30 Red, orange, yellow, green, blue, indigo, violent maybe.
  • 01:37 Actually, I'm gonna choose violent but shouldn't that be violet?
  • 01:44 There, done.
  • 01:45 But do you notice that violent still exists.
  • 01:49 Now if I try and select it from the list, now it changes but it's not reactive.
  • 01:53 It's proactive, you've got to go in and actually change it again.
  • 01:57 So that's one of the drawbacks of data validation.
  • 02:01 Another drawback with data validation is that you can't see that's a list unless
  • 02:05 you click on it, that can be an issue.
  • 02:08 Now a way you can get around that is to press the F5 function key or
  • 02:12 Ctrl+G to bring up the Go To dialog box.
  • 02:17 What you do is you click on Special.
  • 02:20 And from here you can go to Data validation and
  • 02:22 you can either choose All or the Same as the cell you're currently in.
  • 02:27 Well, I'm in a cell that isn't data validated, so if I click OK, it finds it.
  • 02:32 And it will highlight all the others too.
  • 02:34 So that's a way you can find it on a particular sheet if you have a problem.
  • 02:38 Other examples of data validation, I can choose to make it a positive whole number.
  • 02:43 So Alt+D+L is a keyboard shortcut,
  • 02:46 Whole number, and it's got to be greater than 0.
  • 02:53 How difficult is that?
  • 02:54 Click OK.
  • 02:55 If I put -3 in now, the value doesn't match the data
  • 03:00 validation restrictions defined for this cell.
  • 03:03 Retry.
  • 03:05 Put in 5, it's happy.
  • 03:07 Now if you don't like those error messages, you can go and edit them.
  • 03:10 You go Alt+D+L.
  • 03:11 You can put an Input Message in that says Hint, Put a positive whole number in.
  • 03:23 And I might also put this, Ctrl+C to copy and paste it to here, Ctrl+V.
  • 03:29 And just put Invalid Entry and click OK.
  • 03:35 Now, when I click on that cell, do you see a hint comes up?
  • 03:39 Put a positive whole number in.
  • 03:41 That's brilliant for model documentation.
  • 03:44 I use this all the time.
  • 03:46 Because if you're clicking around on a cell, and you suddenly see something.
  • 03:51 Gotta put a positive whole number in.
  • 03:53 You can't help but read it.
  • 03:54 So as long as you keep it short, it's gonna make it nice and simple for
  • 03:58 people to understand what it is their suppose to be doing.
  • 04:00 This is especially going to when you've got costs, when people don't know whether
  • 04:04 costs should be put in as positive or negative numbers.
  • 04:07 So I tend to validate the cells which will only accept positive number and
  • 04:11 say cost should be entered as positive numbers, makes it easier.
  • 04:15 If I actually put -17 in now and press Enter,
  • 04:17 I get my custom error message, put a valid whole number in.
  • 04:23 You can put numbskull at the end or whatever else you want to.
  • 04:26 But it depends if you want to keep your job.
  • 04:29 So there you go, that's two examples quickly on data validation.
  • 04:33 You may notice I've got two more here, and what I'm going to do is come back in
  • 04:38 the next session, and complete this exercise, looking at how to put in
  • 04:42 text length of two to four characters, and creating an even number only.

Lesson notes are only available for subscribers.

Gift this course