Locked lesson.
About this lesson
Learn about the setup and weaknesses of Excel’s security systems.
Exercise files
Download this lesson’s related exercise files.
Security Considerations - Begin.xlsx30.3 KB Security Considerations - Complete.xlsx
30.3 KB
Quick reference
Security Considerations
Setup and weaknesses of Excel’s security model.
When to use
Once you’ve built a file you’re proud of, you’ll want to protect its integrity. Unfortunately, the protection in Excel is best used to protect from accidental damage only, as it’s not all that strong.
Instructions
Triggering protection
- Go to the Review tab > Protect Sheet > Click OK (No password)
- Try to edit any cell and you’ll be told you can’t
- Go to the Review tab > Unprotect Sheet (No password was set, so it will)
Allowing editing
- Select B7:B10, right-click and choose Format Cells
- On the Protection tab, uncheck “Locked” and click OK
- Go to the Review tab > Protect Sheet > Click OK (No password)
- You can edit any cell in B7:B10, but no others
- Unprotect the worksheet
Hiding formulas
- Select B11, right-click it, choose Format Cells > Protection
- Check the Hidden checkbox and click OK
- Go to the Review tab > Protect Sheet > Click OK (No password)
- Select B11 and look in the formula bar… no formula!
- Change B8 to 25000 and notice the formula still works
Using Passwords
- Unprotect the sheet, then re-protect it entering 1234 as the password
- Confirm the password and verify that the protection works
- Unprotect the worksheet and enter the password required to unlock it
Key Takeaways
- By default, every cell on an Excel worksheet is “locked” when you apply protection
- You need to unlock cells you want the user to edit before you protect a worksheet
- Excel’s password security is very weak, and can easily be hacked
- Excel’s password security is best used to protect from accidental damage, not willful damage
- 00:03 So I have this workbook which is plainly set up as a template so
- 00:07 I can send it to someone else, have them modify the data in the green cells,
- 00:11 send it back to me and do my work.
- 00:14 Unfortunately, the problem that I have is that there's nothing stopping someone
- 00:18 typing over a formula and blowing up the whole thing.
- 00:21 So let's take a look at how we can actually fix this.
- 00:25 What I'm going to do is I'm going to use Excel's sheet protection features to make
- 00:28 this happen.
- 00:29 So I'm going to go to Review, and I'm going to choose to protect worksheet, and
- 00:33 for right now, I'm not going to bother using a password, and
- 00:36 I'm just going to say, okay.
- 00:38 And at that point, I prevented a user from being able to modify this cell but
- 00:42 unfortunately, I've also prevented them from being able to modify any other cells,
- 00:46 which is not good.
- 00:48 So let me just go and unprotect this worksheet again and
- 00:51 show you the security settings that actually go in play here.
- 00:55 So we're going to go right click and Format Cells, and
- 00:58 we're going to go into the Protection box.
- 01:01 And you'll notice that by default, every cell is locked, but not hidden.
- 01:07 But these only take effect when you protect the worksheet.
- 01:11 And this is a good thing, because the last thing we want to be having to do in here
- 01:14 is go through all of the columns and rows to figure out which one should be locked.
- 01:18 It's better to be locked by default.
- 01:20 So if we want a user to be able to interact with it when we protect
- 01:24 the sheet, we need to unlock it and say, okay.
- 01:28 Now, while we're here, let's also go down to our gross revenue cells, right-click,
- 01:33 format cells, and this one we're going to leave locked because of the formula, but
- 01:37 I'm also going to hide it to show you what happens here.
- 01:40 I'm going to say, okay, and now what I'm going to do is protect my sheet and
- 01:45 we'll say, okay.
- 01:48 Now, what I want you to notice here is that this formula is locked but
- 01:53 not hidden, notice the formula is visible.
- 01:57 This one is locked and hidden, notice the formula is not visible at all.
- 02:04 If I try and type into the cell, it's locked.
- 02:07 These guys here though, are not.
- 02:10 So I can actually type anything I want and notice they're not hidden so
- 02:14 I can see the values in the cells.
- 02:16 So that's actually working out quite nicely.
- 02:18 Now, what I'd like to do is unprotect the worksheet, and
- 02:21 I'd like to deploy this across my entire workbook.
- 02:24 And this is where styles come in really useful.
- 02:27 So let me right click on my DE value style,
- 02:30 I'm going to turn on protection as a feature, go into format and
- 02:34 make sure that all of the cells for my DE or data entry values are unlocked.
- 02:41 We'll say okay, and okay.
- 02:43 I also went to the effort of setting up another style called, formulas.
- 02:48 And if I go and modify this,
- 02:49 you'll notice that it doesn't do anything as far as the formatting goes.
- 02:53 This has the formula style, as does this and this, but
- 02:56 I did set the protection to, locked.
- 03:00 I'm going to go to format, I'm also going to set these as hidden.
- 03:03 I'm going to say, okay, and, okay.
- 03:06 What I'm going to do now is go back to the review tab and protect the sheet.
- 03:11 We'll say, okay, and now what we can see,
- 03:14 the formula on this one is hidden, as is this one, as is this one.
- 03:18 Those are all protected and yet these ones down here are available.
- 03:23 So that's a nice quick way to use styles to deploy your protection throughout your
- 03:28 workbook.
- 03:29 I want to show you one more little feature here that I think is quite useful, so
- 03:32 unprotect and re-protect again.
- 03:35 What I'm going to do is I just want to call out some of the different options
- 03:38 that we have here, and there's a lot of them that are fairly self-explanatory, but
- 03:42 if you're going to use pivot tables and pivot charts,
- 03:44 you will want to definitely turn this on to allow users to interact with them.
- 03:48 You may also need the auto filter, so just be aware of that.
- 03:51 And if you're going to use slicers or timelines,
- 03:54 you need to have your edit objects and possibly edit scenarios involved.
- 03:58 Now, I don't have any of those in play here, so I'm going to uncheck those, but
- 04:02 I'm also going to uncheck this one, select blocked cells.
- 04:06 I'm only going to allow my users to select unlocked cells.
- 04:10 Now, I'm going to say, okay, and now I can't even select the other cells
- 04:14 including the formulas, so nobody can see what they are.
- 04:19 So that's actually a pretty cool setup there.
- 04:22 Now, some things I want you to keep in mind about protection on these worksheets.
- 04:26 The reason why I don't use a password is unfortunately,
- 04:30 somebody with the skill can hack this.
- 04:33 Now, I don't want to freak you out because let's face it, the reality is that most of
- 04:37 the time we're dealing with people inside our organization anyway.
- 04:40 And if they're trying to do willful damage,
- 04:42 they can actually delete your file.
- 04:44 But I want you to be aware if you're emailing this to someone thinking this is
- 04:48 the ultimate level of protection, it is definitely not.
- 04:50 I can remove any level of protection inside Excel,
- 04:53 please don't send your files to me for
- 04:55 this purpose by just simply using a VBA macro that you can find on the Internet.
- 04:59 So be aware and guide yourself accordingly on this, it is still a fantastic program,
- 05:04 but you need to know that this protection levels inside Excel here is really about
- 05:09 protecting against accidental damage, not willful damage.
Lesson notes are only available for subscribers.