Methods to Minimize Spreadsheet Errors: Part 2 - Data Validation

Quick reference

• 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.