All language subtitles for 3. Controlling Data Input

One of the golden rules that we discussed in the first lesson of this section was how we can control

data input because again, if you've spent a lot of time working on a spreadsheet and you've put together

some really nice complex formulas, if you send that out to a group of people who maybe don't have the

level of skill that you do when working with Excel, you might find that it comes back to you full of

errors and all that hard work we put in has been undone.

Or it might be that you send this out to other people and they start entering in invalid information

into the spreadsheet so we can put in some safeguards which will help keep our spreadsheets as error

free as possible.

So the two that I'm going to demonstrate to you in this lesson are data validation and also protection.

So let's take a look at the data that we're going to be using now.

This is just a very simple list of employees.

I have the employee ID, the employee name, the department they work in and then their salary in Column

D.

And what I have over on the right hand side are actually a couple of formulas now.

Currently, these formulas are showing me any errors.

That's okay.

That's going to be fixed in a moment.

But if we click on Cell I for, for example, take a look in the Formula Bar, you can see that I have

an index and match formula in there.

Now, don't worry if you have no clue what this is doing, we are going to cover index and match in

a couple of sections time.

What we're basically doing here is a look up.

So what I want to be able to do is just above here in Cell i3.

I want to be able to have the employee name and then have these index image formulas returned.

The department of that employee works in and also their salary.

So the formula is basically going to look at the information in Cell i3.

It's going to compare it in the table.

It's going to find the employee and then it's going to return the department and the salary for the

employee that we've selected.

Now, if I give this spreadsheet to a colleague and their manager says to them, find out for me, the

department and the salary for Laurie May.

So the employee comes up here, they type in Laurie Mae and hit enter and it doesn't bring anything

back.

Now, the reason why it's not bringing back the correct information is because the employee has spell

Laurie incorrectly.

It should be r i.

So instead of that employee going back to their manager and saying couldn't find any details or the

spreadsheet wasn't working, which could reflect badly on you, if you put the spreadsheet together,

you want to make it so it's impossible for them to select the wrong name.

And we can do that by using a data validation dropdown list.

So what we're going to do here is we're going to click Cancel E3.

Let's go up to the data tab and into the data tools group.

Now, if we click data validation and data validation again, it's going to open up this little dialog

box and we want to be on the Settings tab.

If we click the dropdown underneath allow, you can see that there are a few different options in this

menu.

Now, the one that we want is list.

We want to create a dropdown list of all the employee names so that other people can select them.

Once we've selected list, we then need to provide the source for our list.

So what are the names that are going into this list?

Now my names are listed in Column B in this table and they are all unique names.

Now I'm going to show you how you would do this if your list items weren't unique a bit later on.

But for now, let's not get too far ahead of ourselves.

These are all unique.

So in the source field, I'm simply going to select all of the employee names.

These are going to be in my dropdown list and click on OK.

Now notice that I get an in-cell dropdown, which is listing out all of those names.

So now if the same employee wants to find this information for Laurie, they can simply select them

from the menu.

And now these formulas are set up in such a way that they're going to look at the value in Cell 03 and

return the relevant information from the table.

So simply by adding a dropdown list, we're controlling what's input into this particular cell and ensuring

that our worksheet is a lot less prone to errors.

So dropdown this a fantastic way of controlling what's going into your spreadsheet now.

With all that said, what if I want to go a stage further?

Maybe I need to send this worksheet out to other people.

I want them to be able to select an employee from this list, but I don't want them to be able to edit

any of these formulas.

Currently, anybody can jump in here and start making changes to these index and match formulas.

But I really want to lock them down so that they don't get broken.

Now, before I do that, let's just make this a bit clearer, I'm just going to add an employee into

here and I'm going to use my format painter just to copy across that formatting.

So everything's nice and neat.

So what I'm going to do here is I'm basically going to lock down these two cells so that they can't

be edited and leave everything else on this work.

Sheets editable.

And this is what we call protecting cells.

Now, the thing that I always found slightly confusing about this is that it feels like it works a little

bit backwards.

The concept you have to understand is that by default, in Excel or in a work sheet, every cell is

locked.

Now this is the part which always used to confuse me because I used to think to myself, Well, if every

cell is locked, how can I go into every cell and just start typing?

Well, it's because the locking doesn't take effect until you protect the worksheet.

So the first step here is to select the cells that can be edited and then unlock them.

So it might be that I want this cell to be editable so that people can select employees from the dropdown,

and maybe I want the table information to be editable as well.

So I'm going to select this cell, I'm going to hold down control.

I'm just going to select all of the information in the table, right click the mouse and go into format

cells.

Remember, you can press the shortcut key of control one.

Now in here, we have a protection tab and notice.

By default, locked is checked, and that is because all cells are locked by default.

Now we want to unlock these cells, so let's remove that checkmark and click on OK.

Now, for all of this to take effect, I need to protect my worksheet.

So up to the Review tab into the Protect Group, and we want to select Protect Sheets.

Now I'm going to put a password on this worksheet, but notice underneath.

We have a whole list of basically exceptions to this rule, so I could choose all the things that I

want to allow users to do in this worksheet.

So currently, I'm only going to allow them to select locked and locked cells, meaning they can click

on the cell.

That is absolutely fine.

Now let's put a password on here.

And then we need to confirm that password.

So now what you should find is that I can still do things like select employees from this dropdown list.

I can make changes to cells over here because those are also unlocked.

But if I try and change these formulas, these two cells, I'm going to get this message.

The cell chart you're trying to change is on a protected sheet.

So effectively, I've protected both of those formulas from editing.

And again, this is something that is invaluable.

If you want to keep your spreadsheets as error free as possible, if you want to protect your sheet

once you've protected it, just click the Protect sheet button.