Ultimate Excel Course #6: Validating and Protecting Excel Data | Alan Murray | Skillshare

Ultimate Excel Course #6: Validating and Protecting Excel Data

Alan Murray, IT Trainer, Coffee Lover and Lifelong learner

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
9 Lessons (41m)
    • 1. Introduction

    • 2. Validating the the Number of Characters

    • 3. Creating Date Validation Rules

    • 4. Drop Down Lists for Easy Data Entry

    • 5. Dependent Lists to Break Up Large Lists

    • 6. Custom Data Validation Messages

    • 7. Encrypt a File from Unauthorised Access

    • 8. Protect the Structure of a Workbook

    • 9. Protect Elements of a Worksheet


About This Class


This class is #6 in a series called the Ultimate Excel Course.

In this class you will learn how to effectively validate data entry, and to protect important data on a spreadsheet in Excel.

These Excel skills are often overlooked as users want to learn how to create charts, PivotTables and powerful formulas. Yet without, clean and accurate data none of those Excel features will work correctly.

Although it is the responsibility of the user to correctly enter data and to avoid editing the wrong cell, mistakes happen. So if we can limit, or possibly prevent them from happening. It is important.

In this class we will look at some Data Validation rules that you can apply right away. We will also cover a clever technique to create dependent drop down lists (a commonly asked question on my training).

We then look at protecting parts of a spreadsheet from accidental editing. We lock down specific Excel features, but ensure the user is able to do what they need to.

The Excel files used during the class are provided so that you can follow along. You can then take on the project exercises to test your skills in action.

Please post any questions in the discussion area and I will get back to you.

Grab a coffee, enroll and let's do this.

Please note that this is class #6 in a series called the Ultimate Excel Course.

You can find direct links to the other classes in the series below.

Class #1 - Excel Formulas Made Easy - Get up to Speed with Excel Formulas Fast

Class #2 - Essential Skills for Working with Large Spreadsheets

Class #3 - Logical Functions - The Decision Making Formulas of Excel

Class #4 - Conditional Formatting: Make your Data Come to Life

Class #5 - Lookup Functions - The Powerful VLOOKUP Functions and Beyond

Class #6 - Validating and Protecting Excel Data

Class #7 - Advanced Formulas for Analysing Data

Class #8 - Excel Charts for Simple and Effective Data Presentation

Class #9 - Cleaning and Preparing Data Ready for Analysis

Class #10 - Formatting your Data Ranges as Tables for Superior Management

Class #11 - Master PivotTables for Powerful Analysis and Reporting

Class #12 - Get & Transform Data with Power Query

Class #13 - Introduction to the Data Model and PowerPivot


1. Introduction: hello and welcome to number six in the Siris of the ultimate Excel course, where we look at how to validate and protect your Excel data. Now this is a very overlooked element of working of spreadsheets in my workers. A trainer. I'm asked for veal, a cup and pivot tables. Charts sort in filtering all the time, and that's fantastic. They're amazing. Everyone loves a formula. Everyone loves a chart. But if you can't get your data accurate, all of that is meaningless. It would be like giving a chef bad ingredients or a Formula One driver. Poor car. He can't give them the tools. It's meaningless. You might as well go home. So this is very important on in this course. We can expect to see some good examples off how we conduce just that. So we're gonna look at some examples off how to validate data entry look of validating the length of text. We will also look at how to do some typical date validations to ensure that typing in the crypt format on meeting certain rules as well. We'll also look at Crichton drop down lists, which is easily the most common reason people are aware off data validation as a feature everyone loves drop down list. It makes a life possibly quicker, definitely Asia and definitely more accurate. But we're gonna take things to a more advanced level. I really like using the word advanced too much, but it's a really cool technique. We're going to look at creating dependent drop damn ists. So if you've got a large drop dentist of options, somebody chooses an item from one list on that will determine what options are shown in the second list so I could choose a country from one list, and then the next drop down list would only show me cities from that country rather than the city's from around the world. So by using this technique, we can really break up what would otherwise be a large, ridiculous list. And we'll need a formula far that it's a pretty cool technique, great example of a certain functions. Well, and then we look at protecting difference. Bridget elements. So where we're looking to protect parts off a sheet, I like to look down. Certain sales are certain features, or if you want to protect the entire thing, I put it onto a shared drive. Stop people even opening it. Full encryption. These are covered in this course. So grab a coffee. What are you waiting for? In role now and start learning these important techniques today. 2. Validating the the Number of Characters: Hello and welcome to this video on the first of our examples off validating data entry in Excel. And in this example, we want to see how to validate, detect length. So in column A off this spreadsheet we're capturing the order Applied D and the idea is always exactly five characters in length. So we would like to correct a validation role to ensure that whatever somebody's type in meets that criteria. So the first thing we need to do is highlight Column A, which I've already done. And then we click on data and data validation that will open the data validation window and then within here we can specify our criteria. So from the allow drop down this from going to select text length so we don't do that. And then it will ask me for the criteria they want to check. Your data is between seven things equal to no equal to less than now. I want equal to I'm simply going to type five. It has to be exactly equal to five characters by click OK on. The validation role is set, so let's see in action I do my control down arrow to shoot to the bottom. And let's imagine I have a new order to come in and let's start by typing one that is five characters long. So 1109 Western 89 and press enter. And it's completely happy because it meets the criteria. We've always tied 11 nine. Then it's no happy. I get this era saying about the data validation Restrictions have been defined. Divorced type on a little bit more in that 110956 Once again, not exactly five characters, so not allowed that, as a first example off creating these data validation rules to ensure accurate data entry. 3. Creating Date Validation Rules: Hello and welcome to this video where we look at validating the entry off dates. So in column B, we captured the date of the order, and we would like to ensure the date entered must be over today's day or date. In the past, you cannot specify a future order, so we're going to provide that limitation. So let's crack on column B and data data validation button. And then we've been here. We're going to specify what to allow, and it's going to be the entry off some kind of tight. So I specify that. And then once again, we get to choose the criteria that the logic everyone to use now. This time, I'm going to choose less than or equal to. So the date that somebody into must be before all the same as two days. Date on in the end date. Uh, field here. I'm going to start equals today. Opening closing, Bracket said. It's wonderful function that calculates what today's date is, so there's a matter when people use this in the future. This will always know what today's date is and compare their entry against that. If I click OK and just zoom back out for the moment, so we get to see this in action. Let's go back to the bottom off the sheet. And if I type the date that is in the past, said the 9th February of this year is in the past, it will allow it. But if a type of date that's in the future, for example, the 10th of September 2018 is in the future at the time of this recording, it is restricted. So once again, I'm not allowed to do that. So these two validation rules have looked at so far with text lengthened date and possibly anything else you do that may not be perfect. Know somebody can still type the wrong date that we're putting certain restrictions in Teoh limit mistakes as much as we can because of the importance off the data. 4. Drop Down Lists for Easy Data Entry: Hello and welcome to this video where we look at creating a drop down list using data validation. This is the main reason how most people come across the data validation tool is when they want to learn how to create a drop down list. And it's a fantastic way of validating the entry of data on also helping the user actually enter it. No speed them up a little, make life a little bit more user friendly. They don't have to fret about miss typing or misspelling any words. They simply pick from an alphabetized list. So for this example, I want to use the country column someone to select column D here to sit and whole column out simplicity and want to put a drop down list off the country's in it. I know that in this list we have 21 countries that we are imagining that we operate him and I want those available. And there's another sheet at the bottom here called lists, where I have already listed in alphabetical order. Those 21 countries and I want that in my drop down. So after selecting column D, we will click data and data validation and we've been here shoes list from the drop down, off what to allow, and then it will put me for the source for the list. So click in that source box. Then I need to click on the sheet at the bottom who lists and then select the country's So Argentina, down to Venezuela. Stay on that sheet once you've selected it, so you'll see it right. Lists a two to a 22 and then we can simply click OK, and that will take you back to the original sheet with the drop down lists. So here we all. And if we give this a test run at the bottom off the sheet in each of these cells, we now have this drop down, and I can select a country like Belgium. Nice and easy on a drop down list is great. People can still type, though they top you. Kate. It's possibly quickly type that news. The list. That's fine. But if they were to make a mistake and accidentally put U K K, then the validation comes in and that's the really important bit off this toe valid it validating these entries, ensuring they're accurate. The list is Is there a lovely bonus for potentially making their life that quicker? And Asia Now, I just wanted to quickly mention very quickly mentioned that in that data validation window we selected at these at cells on this other sheet because the countries were there and I wanted it to be dynamic in the future where we may expand or reduce the countries that we operate in, it has the potential to change the very good chance it will change. But there are some drop down this you want that are probably never going to change a drop down list, for example, that say yes and no or open and closed well, up and down now for those kind of list. If I just imagine I want one here for a couple of seconds are highlights themselves State of validation. Those weaken simply. Just type them in or choose list. And in that source box, just put Yukun comma closed and I could go on it could put a comma, and I just keep putting the entries that I want. I'm imagining a what one for the words open and closed. I click OK on then, in those cells that are highlighted. We just have a little drop down for open and closed. And I didn't need to have that data on the sheet somewhere. For that to work has the validation. I don't need them in sheets and reflective work. And I don't need to be dynamics. It was simply is it It was easier just to type them into that box. So that is the data validation tool being used to create drop dentist. A fantastic way of capturing a text data. 5. Dependent Lists to Break Up Large Lists: Hello and welcome to this video on creating it dependent drop down lists. So in column E where we have to enter the product category, we have a drop down list. I have put a drop down. This didn't they're using the same technique is the previous video and using the category list from the list sheet. There are only eight categories, and that is done. What I would like, though, is in column where we enter the product name. I want to drop down, listing their off products, which is dependent in the category that was selected in column B now on the list Street. Each of the categories has their own list. This has already been done already been set up s so we don't have to waste out time doing it. And also what's very important here it If I highlight the beverages list again, there is a named range set up for it, so that is called beverages. If a highlight condiments that is called condiments. If our highlights confections that is called confections and so one through to seafood. It's very important as well, with the name given to these lists exactly matches what it's called in categories. So we've the list is named beverages. It must be called beverages in this list for the technique we're about to do. No. We're going to use the data validation tall like before for the drop down. But it needs to be dependent on the list before it on. We're going to do that with a function called indirect, very useful function. Andi, you might be new to dysfunctions. Let me just give you a quick insight before we actually use it in the data validation told . So I'm just going to type into one of these cells here. I'm not actually gonna finish it in here. I just want to give us an inside. So this is what it is in direct. It's one of the look up functions off Excel, and its job is to return the reference specified by a text. A ring? That's what it's gonna do. So the text string will be unnamed range on the, uh, the option chosen from the list. Convert the text selected from the list into a reference to named range. I hope that makes sense. It can be a little bit weird at first if I just completely indirect function there. It prompts for two questions. We're just going to provide the 1st 1 which is the reference text. The second is the different types of reference. We're gonna worry about that. We're going to stick with everyone referencing. And that's for another day, I think a bit more intense, looking in direct. Okay, let's get this done. It's jumped to the order sheet Select column F where the product name will go. Data, data validation. I shall choose list from that dropped down and then it will prompt a source. I would be against heart equals indirect open bracket. So I wanted to show it in the cell of the ship first because I knew we weren't going to get anything come upon a screen when we're in this window. But remember asks for the reference text, and I'm going to type in E one. So I'm referencing column a cause. That's where the selection of the Caterp Iwas referencing the one because the first one in that list No, it may seem silly because the one is a header, but because I've selected the entire column f I have to slept anyone. If I had selected from F two down to the bottom here I would write E to. It has to be in relations what we've selected so in direct the one by click. OK, you might get this message. Send the source currently evaluated to an era. We'll just continue. There's no era. There's no real area going on here. If I moved to the bottom off this list and see this in action, if I was to select a category like Dairy, the next drop down list is a twist off dairy products you can see that's clearly not 77 items in there, which I know is to complete list of products. And if I was to choose maybe seafood, we get a list off seafood, and it's probably quite clear that it's seafood with things like a crab meat and stuff coming up there on that. That is that we now have a list. Adopt them is dependent on the selections from another drop down list, a pretty core technique, especially when the dealing with very big lists you don't want to give somebody a drop down list of 500 items. If he can use a proceeding this to shorten that, then that's a good way to go 6. Custom Data Validation Messages: hello and welcome to this lesson where we will look at using the input message on the error alert to be able to communicate their users a little bit better than with the standard data validation messages. So for this example, we have that validation that we set up in column a where we specified that they must type exactly five characters. So if I can go back and just select column I and going to data and data validation, we have that criteria that we set up earlier, that the text length must be equal to five. No, at the top we have these two extra taps the input message on the air alert. So let's start with the import message now. This one is often not used because it's not usually that necessary. But this message appears, assumes the user clicks the cell, so it's a description for what the user needs to do. And normally the user already knows this unless there's some important criteria that you feel is necessary to state in advance. But to demonstrate it here if I put a title, Andi, I will just type help other than input message, and I'll type something simple at such as Please Santa, exactly five characters. And then we have the era alert. Now this one is far more useful. There's always the potential for an error. So the fact that you can speak directly to the user and explain in more simple terms and more specific terms is very useful. I'm going to put a title in here. I will type era and then an error message such as you did not enter exactly five characters . So a very simple example here, Onda. If I click OK and then test this out by looking towards the bottom off that list. So there is the input message assumes I click. What looks like a comment appears to tell me what is necessary to do. As I mentioned. A lot of people will admit that because it is often not that necessary for that to appear. But more importantly, if I make a mistake by just typing 12 free and press enter, there is the air alert. Something not not only stops may, but also tell me what it is I did wrong because the user might not be, it might be like clear to them them? I don't know exactly. They may have typed information in the wrong format or the wrong number of characters are used in legal character. You don't know what the criteria is where they might not. So I only got the opportunity to retry or cancel their and I'm going to cancel it. And I would like to demonstrate again a slight variation on their era alert. We have units sold in column height here. I'm going to select column hatch and go into data validation. And I'm going to set up some validation criteria where I'm going to say that it must be a whole number. You can put in decimals less than or equal to, and I'm going to type 50. It must be a whole number less than or equal to 50. And I'll click OK to set that and I'm just gonna go back into data validation. Actually, I didn't intend to kick okay there, and I'm going to look at the two tabs at the top again this time error alert. And I want to mention that there are different styles. So I used to stop style with a default in that previous example. But We also have warning and information. Now the one of the bottom information is very lenient. There is no stopping that will provide the air alert as it will provide. That message told the user what's going on? But they may be dead, but that's it. There's an O K button and it doesn't stop you in the slightest. Where was to stop option? It really stops you. But warning in the middle is what I would like to demonstrate right now where we have this concept me validation The message will appear and stop me half stop me. And so you did this. That will give me the option off clicking Yes and overriding it or no to back out. So the user has control. They warned that not stopped. Let me put a title warning on in a message you entered how you value greater than 50. Was this intentional? And I shall click OK and then give it a test run. So if I type number 23 we're okay If I type number 65 the alert is shown the error alert but it's different to before it's a warning message. I have the option to yes to continue no to back out. And it was a big mistake. If I click. Yes, the 65 is entered. It does not completely prevent May from putting that day. Tareen. So it's a great way of checking for errors, potential miss types, but not fully stopping the use of having control of Internet data because they may know best. So this lesson was all about those messages that we can use to fine tune our validation. We have the input message and in the air alert on the versatility of choosing whether you want full prevention or the warning or even just some information. 7. Encrypt a File from Unauthorised Access: Hello and welcome to this lesson on how to encrypt your excel file. So how we can prevent unauthorized access to this entire file on people will not be able to open it. This could be a great idea when your spreadsheet is sitting on at some kind of shared drive or shared device on the people could have access to it, which you don't wish to. Now, to do this, we can click on file at the top and you get protect workbook in this info section that I'm taken to. And it's different types of protection that you can apply an excel, and we're going to see some of those over the coming videos. But here on this is just outside of the encryption, the techniques you can do within Windows itself or other programs. But within excel, we can click encrypt with password that will prompt us for the password with some unusual speak underneath about the dictator of passwords I was gonna put one in. I'm going to type Excel. Please don't think that is my my normal password and I'll click. OK, that would prompt me to repeat that password and I will click OK, again on that yellow kind of backing appears on there just to indicate that a password is required. It has now been set that no, it's visual yellow at Survive Back out. And if I was to save and close this file and then look at reopening it, So click save, I shall defile close and then file open crypto file and there is the password front. So I know this password. I could just type that in that will give me access, but without that password, we do not have access to the content off this spreadsheet by putting the end that will take me back in an open a file as usual. Now, how can you remove that password? Well, we were just retrace our steps. So if I click file, protect workbook encrypted password just remove the symbols indicating the characters that password but deleting them. Click OK, and then we can go and save it again as usual. And that password has bean removed 8. Protect the Structure of a Workbook: hello and welcome to this lesson on protecting the workbook. So there are different types of protection in Excel, and the next one that is worth looking at is protecting the structure off. This book said a specific type of protection is to stop people doing things that are kind of sheet level because that is the structure off the workbook. So they cannot insert sheet moving, copy them, renamed them on hide them. You know all these kind of structural elements. So, for example, we have this offices tablet of bottom off at this fire at the moment on, the data on this tab is being used by a real cut formula on this. This example specially, we don't need to worry about that stuff. Whatever did cover Veto Cup in that one of the previous I courses off this off this ultimate excel course, but it has been used on. Although its importance is being used by formula. Nobody needs to interact with that on a day to day basis. So that is your typical sheets that people would right, mouse click on and hide. And now, depending on your interpretation and your requirement off protection for most people that act alone is protection because if it's not there, you can't damage it. It also means is not getting in the way. If you've got lots and lots of worksheets, there are various benefits to re, and that is a element off protection. However, anybody can easily get to and then hide it if they know how to. And there are other things other structural elements of the workbook he may want to shut down, such as the insertion of sheets, the movement of sheets. You may want to close down these as well. Normally, those things are quite harmless. So what? The protection is not as common as the one we're gonna talk about in the next lesson, but it depends what you using excel far. Just because it's not as mainstream, Mrs Popping next one doesn't mean it's not useful for you, and especially if you have got Matt Crows. Something recover later in this course, they may work on the sheets of this book and require the order so it can be important that people do not change this. So how do we apply it? We click on a review, among other ways. You can do it is in other ways on We have protect workbook on the far right off the ribbon . Now this is an area of excel has been changing quite a bit over recent years. You can see at the moment of the time of recording got his words legacy on some of the features nearby as marks of begin to change and upgrade. Some of these features the click on Protect Workbook You see it's called Protect Structure and Windows. But Windows is not an option anymore is that has been removed as its 2013 of thinking excel . But I took that out, so the only option we have now is structure. So with the option of a password, it is optional. Now you might find that strange what I'm doing. My training, a costume training. Little people can sometimes struggle to get their head around this. At first they hear the word protection. They think about security encryption passwords, and it's fair enough we could put a passport in here. But most people don't bother. This is normally just used as a basic protection to prevent mistakes and accidents as opposed to some kind of security feature. It is a very basic protection as well. I wouldn't rely on this kind of stuff of sensitive data. It's a basic end user protection. To stop issues much like locking the door of a padlock is a very basic protection in the grand scheme of things. So if we are, I'm not gonna bother apartment. Let's just not bother, but we could put one in. It tells me it's optional. Let me prove that fact. If I click OK, the protection is done. If I right mouse, click on the Sheikh tablet of bottom, I can no longer unhygienic into rename Move. Copy any off the structural elements off a workbook, even the little plus sign it in search. New sheets is disabled or if I go to the home had. And if I click, insert in the far right insert sheet is disabled there as well. So no way cannot bring in workbook structure when it's gonna bring in sheets, do things to ship, saying that I did not do a password. So if I click review and click Protect Workbook that will un protect itself and now I can go and do what I wish and to repeat what I said moments ago. Some people hear that and think that it's not protected. That is it. Well, it depends what you mean by protection. They're put in a little bottle of water, protects it, but it doesn't stop. You get into it very easily. It depends what level you want on which just really trying to prevent mistakes. Most of the time, if it was security of probably wouldn't send it all or think of some higher level protection to do as opposed to this one. But that will help keep things in place in your workbook. So it works reliably every day. 9. Protect Elements of a Worksheet: hello and welcome to this lesson where we look at protecting the worksheet off the different types of protection we have looked at. In this course, this is the most popular one by quite a distance is very useful to protect elements off a sheet. So you heard me right there. We can protect different elements of this sheet, said a typical behavior. I think, for most people, would be to protect cells that contained formulas. And we have them here. We have Colin Day that's got a four meter in on. We have condom A that's got formulas in, and that's the kind of thing that most people want to protect. So that day today, as people use it, including themselves, they don't accidentally mess up that formula or do something to sail with formula. Where was other cells? They need to access so they can't do much about that? There's gonna make sure they do. They do what I need to do, correct me because they need to be able to touch interactive it. But we do get a lot more options than that. We are able to disable certain functionality that sort in filter in in certain rose if we choose to and just really try and make this work seat as bulletproof as possible for when people are using it. So in this example, I want to protect only the formulas now, by default s so I can't speak for your spreadsheets, but typically by default, every single cell off a worksheet has a setting applied, called locked. Now that setting doesn't do anything until you protect the sheet. And we can do that by clicking, review and protect sheet. But I don't want to do that right now because I don't want to protect everything. Live a press that right now if I click on add, it gives me some options or talk about these in the second, Let me click OK on. The whole sheet has been disabled. I can type or do anything in any cell, but I only want to protect the ones we formulas that I want to protect these other ones. So I just click un protects sheet for a moment, and what we're going to do first is right mouse click on this gonna square in the top left corner, the one that select a bruising or sail off the sheet click format cells on when the format cell's window opens, there is a protection tab in the top, right? The last tab off that dialogue and in here is that setting called Locked, which I was speaking about, and Exhale describes itself underneath. Lock in sales doesn't do anything until you protect the shape andan. Tell what you had to do that now we're going to UnTech. That box said it does no protect to sheet protect order cells. So if you click OK, nothing really happened yet. We haven't protected anything. Exhales starts to complain about the cells formulas, the fact that we have unlocked them. It's quite strange behavior, they believe, but we're now going to look them. I'm now going to select the sales that contain the formulas, so I'm gonna highlight D six to East 16 right mouse click one of those highlighted cells and former cells. I'm back in the protection tab so that I could decide to look those once only those ones everything else unlocked. Now we also have an option here for hidden. So if I was to choose that, you can see the full were at the top in the formula bar despite even being locked when it's done. But if I choose hidden, they won't even be able to see the formula there. Let me demonstrate that if a tick hidden as well and click OK so we can see at a moment because nothing's protected. But now we're that we have decided what to unlock and what to look. We can click, review, protect sheet and then we have this window it once again prompter a password just like in previous lesson with workbook protection. And once again, most people don't worry about this because they're just trying to prevent mistakes as opposed to film security. But obviously we could top a password and you're welcome to do so, and it would ask you to repeat it. What you don't want to do is forget that passport underneath. We have some checked boxes features that we can enable if we choose. At the moment they are disabled, like formatting cells in certain rose. Deleting columns sort in a filtering, some quite major features of excel, so you can take those boxes. They will become available. Otherwise they are not. So I'm gonna leave it out. Here's I'm said. I don't really need that. This is just a form to complete. I shall click OK, and the protection is applied. So I cannot put my name in that name box in Cell C free because that cell was unlocked and I can use this office dropped down and the formulas will work. But if I click on and try and change a formula, I am prevented. In doing so, try and drag it somewhere unknown, allowed to. They are safe and sound. We could have even disabled people clicking on them. But I decided, you know, stop. Let's not do. They might confuse people. Now I mentioned in the build up the hidden setting. If I click a cell, the formula up in the formula bar, you cannot see the formula. So that's what I meant. I can't even see what a formula. It's kind of you see the rate of pay their given here. At worst, it's protected now. I did not know actually before I say it was about to say there, but click the home tab on. Also, the inserts have a great examples that are examples on other taps as well. Look how gray that Lebanese majority of the features and inset on also home are disabled because I decided to not for themselves not inset rose, etcetera, so they are not available to be done now. What did not put a password on this protection? So if I click review liking quick un protect sheet on, I'm straight back into it so that I can change the formula when it needs changing, vowed, stressing about remembering or recording passwords. Obviously, anybody can do that. Anybody can do that, so it's no secure to use that word. But it is safe for day to day work in which what most people go far at the password would have predicted. Prevented me on that is worksheet protection at really useful feature for preserving their the integrity of your data and its accuracy, which is vital in Excel s. So it's a very popular feature to do, and it's encouraged to do so, especially for things that you're formulas and depend what you use. Excel far other parts of it that to keep it safe, keep it reliable, keeping accurate, very important overlooked part all of except