Alex has now located and removed all the errors within this worksheet.

To avoid further errors creeping in or formulas being accidentally changed,

he would now like to add some protection to

this workbook which will lock down the cells that contain the formulas.

What we're going to look at in this video are several methods of adding

protection to our workbooks to prevent unauthorized access or accidental damage.

Protection can be added at three different levels.

It can be added at the workbook level.

In other words, you can restrict access to the workbook itself.

It can be added at the structural level so you can prevent people from adding,

moving, or unhiding worksheets.

Or it can be added at the worksheet level itself,

where you can lock all the cells or just selected cells.

We're going to have a look at how to do all three.

To begin with, click on the File tab,

which should take you to the Info tab and you'll see a big button Protect Workbook.

And this gives you quick access to quite a lot of the options we've discussed,

including the Encrypt with a Password which will

allow you to add a password to prevent access to the workbook.

It only gives us one level of access though.

So, what we're going to do instead is we're going to click the Save As button.

And if you're in 2013 for example,

click the More options or the Browse button to bring up the traditional Save As dialog.

Now, once you're in this dialog,

tucked away next to the Save button is a little Tools option.

And when you click on that,

you'll see General Options.

And this allows us to add two levels of password protection.

You can add a password without which the user

will not be able to open the workbook at all.

Or if you're happy for them to open the workbook,

but only to view the contents,

you can add a password to modify as well.

And users without this password,

although they will be able to open the workbook,

they will not be able to actually change the contents.

Basically, it simply means the file is open read-only.

There's nothing to stop the users from saving it under a different name.

But it means that they cannot change the original workbook.

Now, obviously, once you've applied these passwords,

you will need to retype them again,

save and close the workbook,

and only when you try and reopen it will you see the passwords take effect.

But that is one way of protecting your workbook.

We're not going to go through your steps now,

but that is how you would go about doing it.

So, I'm just going to click Cancel now,

and then Back or Escape to come back into our workbook.

Now, the other two options available to us can be

accessed from the Review tab which is where we would typically go.

And you'll see a group called Protect and

the first option we're going to look at here is the Protect Workbook.

This allows you to protect the structure of the workbook.

We're going to click on the button,

and you'll see it offers us the option to put in a password. You don't have to.

But if you want to ensure that people can't remove this level of protection,

make sure you put in a nice strong password.

We're not going to worry about this now,

so we'll just say OK. Now,

if you come down to the tabs,

you will notice that the new sheet has been grayed out.

We cannot add a new sheet,

and we cannot move the sheets.

And when you right-click on any of the tabs,

you'll notice nearly all the options,

including Hide and Unhide,

have been grayed out.

So the structure of our workbook is locked.

And this is a very good idea when you have, for example,

3D cell references and you don't want the sheets being moved around,

or if you have a sheet that you want to remain hidden.

This is one way to ensure it does.

I'm just going to click back onto the Sales Dash now,

and to remove that protection,

simply come back and click on Protect Workbook.

If the password had been applied,

you would need to know that password in order to remove the protection.

But without it, it's very easy to just undo.

Now, probably the most useful protection,

however, is the Protect Sheet.

What this does is actually lock down the contents of the sheet.

So, we're going to click on the Protect Sheet button,

and it brings up quite a long list of options.

By default, users will still be able to select both locked and unlocked cells.

And we'll talk more about those in a moment.

But you'll notice here there are a whole lot of options that are not on by default,

which include allowing users to format columns,

insert hyperlinks, and so on.

So, you can actually customize what level of modification users can make.

And again, you can choose to add a password.

This time, we will.

We're going to keep it all lowercase.

And I'm just going to type pass and then I'll say OK,

and then I'm going to type pass again for verification and then click OK again.

And now it has actually locked the sheet.

I can still click on all the cells, as you can see,

but if I actually try and click Delete,

it will tell me that I can't make a change to that cell.

The one small problem here is there are

certain cells that I do want users to be able to change.

For example, they should be able to change

the Account Manager and they should be able to change the Year.

But with having locked everything,

that's not currently an option.

So, the next thing we're going to look at is how you can unlock certain cells,

that when you add protection,

those cells can still be modified.

So, I'm going to come back to Unprotect Sheet,

and I'm going to put in my password and say OK. And now protection has been lifted.

By default, all the cells in a worksheet are locked,

which means when you protect,

they cannot be edited.

But you can choose to unlock certain cells,

and that's what we're going to do now.

So, I'm going to select B29 and B32,

and then I'm going to right-click,

and I'm going to choose Format Cells.

And there are several ways you can get to this,

but that's often the easiest.

And if it doesn't automatically come to the Protection tab,

so it may have gone to the Number tab, simply click Protection.

And you will notice at the moment,

these cells are locked.

And like I said, that is the default.

But we're going to unlock these cells.

The Hidden option is by default turned off.

If you were to make cells hidden,

what that means is when the user clicks on them,

they can't actually see the value behind the cell.

So nothing will appear in the Formula bar.

We're not going to be looking at that now though.

So, we're just going to say OK,

and then once again,

I'm going to hit the Protect,

but this time I'm not going to allow the users to Select Locked Cells,

I'm going to untick that.

But I won't worry with the password.

And we're just going to say OK.

This time when I try and click on any of the locked cells,

it's not even allowing me to do that.

So I can't see what formulas are behind here.

I can't even click in the cell.

But if I come to one of the cells that are unlocked,

different story, now I can change them.

And again, of course I can also change my Year.

So what I've done here is allow the users to change

certain cells but locked down the ones that

contain the formulas so those remain protected.

Finally, we're going to look at the Allow Edit Ranges.

To do this, I'm going to have to remove my protection again,

and this time I'm going to click into H3.

This is our projected increase.

Now, unlike the other two values,

only certain people are allowed to change these.

But they're not allowed to change the rest of the workbook.

Now, to do this, what I can do is come to

Allow Edit Ranges and I can specify certain ranges to have their own password.

So, I'm going to click Allow Edit Ranges,

there are none at the moment.

So I'll click on New and I'm going to call this Projections.

It refers to cell H3, which is my seven percent.

And the password I'm going to add is,

again, going to be my lowercase "pass".

And then click OK. Re-enter the password and OK. And then I need to protect my sheet.

But this time, I will allow them to select

locked cells so they can actually get to that projected increase.

And I'm going to say OK. Now,

when I click on the seven percent,

if I try and change it, it will let me,

but I only if I have the password.

So I'm going to put my password in,

say OK, and now I can go and change it.

And I can keep changing it as many times as I like once I've typed that password in.

But when I reopen the workbook next time,

I would need to retype in the password to continue to being able to make those changes.

So now we've looked at the three ways in which we can add protection to our workbooks and

worksheets to help avoid unauthorized changes or accidental damage.

Workbook protection is a really good idea.

However, try not to overuse it and make it

impossible for users to make the changes they need to make.

Everything in moderation.

Practice Video: Protecting Workbooks and Worksheets

Excel Skills for Business: Intermediate II

Meet the Instructors