Locked lesson.
About this lesson
Data Validation helps us control what information is going into the spreadsheet.
Exercise files
Download this lesson’s related exercise files.
Exercise - Methods to Minimize Spreadsheet Errors - Part 2.xlsx11.5 KB Exercise Solution - Methods to Minimize Spreadsheet Errors - Part 2.xlsx
12.7 KB 2.06 methods-to-minimize-spreadsheet-errors-part2 - Exercise.docx
43.4 KB 2.06 methods-to-minimize-spreadsheet-errors-part2 - Exercise solution.docx
143.9 KB 02-06-Methods to Minimize Spreadsheet Errors-Part2-Start.xlsx
2.9 MB 02-06-Methods to Minimize Spreadsheet Errors-Part2-Complete.xlsx
2.9 MB
Quick reference
Methods to Minimize Spreadsheet Errors: Part 2 - Data Validation
Control cell inputs using Data Validation.
When to use
We use Data Validation whenever we want to control what users are entering into cells by limiting input to a list of values we've specified.
Instructions
Data Validation helps us control what information is going into the spreadsheet. We can add a drop-down list to each cell and provide others with a limited list of options to select from. This ensures that the correct information is input into the cell each time.
For example, if we have a spreadsheet that logs pizza orders, we need to ensure that every time an order is added to the spreadsheet, the pizza name is entered correctly. We can set up a data validation drop-down list to ensure the information is entered correctly each time.
Create a unique list of values
We need to start by creating a unique list of values to use in our data validation list. If we do not do this, we will have duplicate pizza names in the list.
Use UNIQUE (Microsoft 365, 2019 and 2021)
- Click in a cell outside of the dataset or on another worksheet.
- Type =UNIQUE
- Select the cell range that contains the pizza names.
- Press Enter.
Use the Duplicate Remover Tool
If we are using an older version of Excel and do not have access to the UNIQUE function, we can use the duplicate remover tool instead.
- Select the column that contains the pizza names.
- Press CTRL+C to Copy.
- On another worksheet, press CTRL+V to Paste.
- Select the new column.
- From the Data tab, in the Data Tools group, click Remove Duplicates.
Create a Data Validation Drop-down List
Now we have a unique list of values, we can use these in our data validation list.
- Click in the cell where you want the data validation list to go.
- From the Data tab, in the Data Tools group, click Data Validation.
- From the Settings tab, in the Allows area, choose List.
- In the Source field, select the column or cell range that contains the list of unique pizza names.
- Click OK.
Hints & tips
- To apply a data validation list to all cells in the column, highlight the column prior to setting up the data validation list.
- To hide the list of unique values, select the column, right-click, and choose Hide.
- 00:04 One of the very best ways to minimize errors in your spreadsheet is to
- 00:09 add data validation to really control what's going into the cells.
- 00:14 So let me show you an example of what I mean and
- 00:17 the type of consequences that incorrect data can have.
- 00:20 Now, once again, we're taking a look at the sales information for
- 00:25 Marco's Pizza Place.
- 00:26 And notice in the last column in column K we have the pizza names.
- 00:32 Now, these pizza names are not unique pizza names,
- 00:35 because these are order numbers.
- 00:38 And sometimes people order the same pizza, so
- 00:41 we might have repeats of the same pizza name throughout column K.
- 00:45 Now, it might be that either myself or
- 00:47 somebody else who has access to this spreadsheet
- 00:51 adds a new order to the bottom where the pizza name isn't spelt correctly.
- 00:55 For example, the second pizza name in this list is the Classic Deluxe Pizza.
- 01:01 And this is making me really hungry walking through this example.
- 01:04 Now maybe instead of spelling the word deluxe correctly,
- 01:08 when I enter this pizza name, I miss the e off of the word deluxe.
- 01:12 So what consequence is this is going to have to my dataset?
- 01:16 Well, if we add some filters onto the top of our columns,
- 01:20 click the pizza name dropdown.
- 01:23 If I take a look through my list of pizzas,
- 01:26 you can see that I now effectively have two pizzas that are exactly the same.
- 01:30 So I have one called Classic Deluxe Pizza spelled correctly, and
- 01:34 one called Classic Deluxe Pizza spelled incorrectly.
- 01:38 Now because these are the same pizza, this means that it's going to throw
- 01:43 off not only my filtering, but also any analysis that I might do after this.
- 01:48 So if I create a pivot table where I'm analyzing the amount of sales
- 01:53 by pizza name, I'm going to have Pizza Deluxe with an e and
- 01:57 Pizza Delux without an e listed twice in my table.
- 02:01 The same thing if I've created charts or pivot charts or dashboards.
- 02:05 So a way that we can get around this is to really control what people are inputting
- 02:10 into the cells so that they get it right every single time.
- 02:14 And we can do this by adding data validation drop down this onto our cells.
- 02:19 So let's Ctrl+Z just to put that pizza name back to how it was, and
- 02:24 take a look at data validation.
- 02:26 Now effectively, what I'm aiming for
- 02:28 here is I want to be able to click in the Pizza Name column.
- 02:32 I have a little dropdown arrow just here, which I can click and then select a pizza
- 02:38 from the dropdown list, ensuring that it inputs correctly every single time.
- 02:43 Now, to build our data validation dropdown list,
- 02:47 we first need a unique list of all of the pizzas that we sell.
- 02:52 Now, as I mentioned previously, these pizza names repeat throughout
- 02:56 this dataset, because sometimes people order the same pizza.
- 03:00 So I need to first get a unique list of all of these values.
- 03:04 Now, something else that I want to be able to do which is going to make my life a lot
- 03:08 easier.
- 03:09 Is if we go to the bottom of this data set, Ctrl+Down Arrow,
- 03:14 I want to be able to add new orders onto the bottom here,
- 03:18 which might contain new pizza names.
- 03:22 So maybe once a month, we have a special pizza that we sell to customers.
- 03:27 So I want to set up my data validation so that if there is a new pizza name
- 03:32 added in here, it automatically updates my data validation dropdown.
- 03:37 Now the way that we can do this is to put our data into a table before we begin.
- 03:42 So Ctrl up arrow, let's go to the top.
- 03:45 I'm going to click in my data and press the Ctrl+T keyboard shortcut.
- 03:51 You can see it's selected my dataset.
- 03:54 Yes, my table has headers.
- 03:56 Let's click on OK to put that into a table.
- 03:59 And remember,
- 04:00 you can click the Table Styles dropdown if you don't like the default formatting.
- 04:04 Now, I'm going to give my table a name, because if you take a look on
- 04:08 the Table Design ribbon in the Table Name area, it just says Table2.
- 04:13 So that is the generic name that Excel gives your tables when you create them.
- 04:18 So I'm going to make this more meaningful and
- 04:22 we're just going to call it, let's call it PizzaSales.
- 04:27 Now with these names, you can't have a space in between multiple words.
- 04:30 So either has to be one word like I've done here, or
- 04:33 you could separate the words with an underscore.
- 04:36 Let's press Enter.
- 04:37 So now I'm going to create my unique list.
- 04:40 I'm going to show you two different ways that you can do this,
- 04:44 because one of the methods is only available in the later versions of Excel.
- 04:48 So let's explore that one, first of all.
- 04:51 Now, if you're using Excel for Microsoft 365, Excel 2019,
- 04:58 or Excel 2021, then you're going to have access to
- 05:03 one of the brand new dynamic functions called Unique.
- 05:08 And this has very swiftly become one of my most used functions in Excel.
- 05:13 It is so useful for extracting unique values from data.
- 05:18 So let's take a look at the arguments.
- 05:19 We have three of them.
- 05:21 The last two are optional, because they're in those square brackets.
- 05:25 Now in this example,
- 05:26 all we need to do is provide the array to get our formula to extract a unique list.
- 05:32 And the array is basically a fancy way of saying the range of cells.
- 05:36 So all I need to do here is select my Pizza Name column.
- 05:40 Now, I could go in and select the cell range, but
- 05:42 because I've put my data into a table.
- 05:44 I can now just hover my mouse over that heading area until I get that little black
- 05:49 down arrow and click once, and it's going to pick up that column.
- 05:54 Also notice, instead of using cell references, so
- 05:58 K1 to K200, it's now using table references instead.
- 06:03 So it's telling me that this is the Pizza Sales table and
- 06:06 I've selected the Pizza Name column, which is perfect.
- 06:09 Let's close the bracket, hit Enter, and
- 06:12 it's going to extract that unique list of pizzas.
- 06:16 And the cool thing is just so you can see how this works,
- 06:19 if we jump to the bottom Ctrl+D+Down Arrow.
- 06:21 If I was to add a new order onto the bottom, and
- 06:25 I'm just going to add Test into the pizza name, the table auto expands,
- 06:30 and it's going to be automatically included in my unique list.
- 06:35 You can see there it is at the bottom.
- 06:37 So it just ensures that any new pizza names that get added will be included in
- 06:42 the data validation.
- 06:43 Now, I don't want test in there, so let's go down and delete that out.
- 06:48 Now, if you don't have one of the latest versions of Excel,
- 06:51 the other way that you could do this is to basically remove duplicates.
- 06:55 So what I could do here is select the pizzas, Ctrl+Shift+Down Arrow.
- 07:01 Ctrl+C to copy, Ctrl up arrow to jump to the top, and
- 07:05 then I'm just going to paste them in column Q, Ctrl+V.
- 07:10 I can then select the range and use the Remove Duplicates tool.
- 07:15 So Ctrl+Shift+Down Arrow again, up to Data.
- 07:19 And in the Data Tools group, we have the Remove Duplicates button.
- 07:23 So if I click this, it's going to look in the column that I have selected,
- 07:28 and it's going to remove all of those duplicate entries.
- 07:32 And I should find that this is exactly the same length as the unique list that I got
- 07:37 using the unique function.
- 07:39 So those are the two different methods you can use,
- 07:41 depending on the version of Excel that you have removed.
- 07:43 Duplicates is available in all of the older versions of Excel, so 2013 and 2016.
- 07:49 So now that we have a unique list, we can use this to build our data validation.
- 07:54 So let's click in cell K3,
- 07:57 Ctrl+Shift+Down Arrow to select all of the orders.
- 08:03 I'm now going to go back up to the Data tab and
- 08:06 I'm going to elsect Data Validation.
- 08:09 Now here, we want to create a dropdown list.
- 08:13 And my source is going to be my list of unique values.
- 08:17 Now, what I can do here is because I've built this using the unique function,
- 08:21 I don't have to actually select the entire range.
- 08:24 I can simply select the first cell, M3, and press the hash symbol.
- 08:29 Click on OK.
- 08:31 And now we should find that next to any of these,
- 08:33 we have a dropdown where we can go in and we can select the correct pizza.
- 08:38 And once again, if I Ctrl+Down Arrow to jump to the bottom,
- 08:44 if I was to add a new pizza name, so
- 08:47 let's add in Vegan Special Pizza, Ctrl+Up Arrow.
- 08:53 I should find that that is now available in the dropdown menu,
- 08:56 and I haven't had to go in and fiddle around changing my cell references.
- 09:01 Now, the final thing here with regards to this unique list,
- 09:04 you might not want to have this showing in your spreadsheet.
- 09:07 There's a couple of different ways that you could deal with this.
- 09:09 You could put it tucked away on its own worksheet.
- 09:12 Alternatively, you can simply right click and hide the column.
- 09:18 But that is how you can control what's been input into your cells using a data
- 09:22 validation dropdown.
Lesson notes are only available for subscribers.