Locked lesson.
About this lesson
Learn about the setup and weaknesses of Excel’s security systems.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Security Considerations.xlsx19.9 KB Security Considerations - Completed.xlsx
19.9 KB
Quick reference
Topic
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:04 Now let's talk about security in Excel.
- 00:07 By default,
- 00:08 every single cell in an Excel worksheet is actually locked but unprotected.
- 00:14 This is actually by design, because what we want is,
- 00:18 when you go the Review tab and choose to protect your worksheet, and say OK.
- 00:24 This set up means that nobody can actually make any modification to your worksheet.
- 00:31 I'm going to unprotect this now, and
- 00:32 show you what happens when we change this behavior.
- 00:35 If we right click on our cells, and say Format Cells, and choose to unchecked
- 00:41 the lock box then we protect our worksheet, say OK.
- 00:47 You'll notice that I can now change the values in the cells that were unprotected,
- 00:51 but I can't change anything else.
- 00:53 So this is why these cells are locked but not protected by default.
- 00:58 When you turn protection on, that becomes active.
- 01:02 We also have some other features that we can use with the same kinda thing.
- 01:07 If I right click here and
- 01:08 say Format Cells, you'll notice that I have something called Hidden.
- 01:13 And what hidden does is nothing, until the worksheet is protected.
- 01:18 When we go and protect our worksheet, we say OK.
- 01:21 If you look at the formula in the formula bar, you'll see that it's not there.
- 01:26 It is for the cells that I can select.
- 01:28 It is for the cells that I have not marked as hidden.
- 01:31 In this case, we've actually hidden away the formula so
- 01:34 that people can't see what it is.
- 01:35 Now, they can see that it updates.
- 01:38 As we change things it will work just nicely.
- 01:42 Now, one of the things that I like to encourage when I'm working with
- 01:46 these things is I actually will protect my worksheets.
- 01:49 And I'll protect them using no password at all or a very simple password.
- 01:54 And the reason being is because, for a common user,
- 01:57 it's kind of hard to get through these things if there's a password on it.
- 02:00 But for a VBA expert, somebody who knows the programming language inside that we
- 02:04 use to write macros, we can actually very, very easily find a brute
- 02:09 force password hacking software on the internet that'll actually do this, and
- 02:13 it's free of charge if you know where to look for it, which is unfortunate.
- 02:16 So spreadsheet protection is really about preventing accidental damage,
- 02:21 not willful damage.
- 02:23 So in this case, often times what I'll do is I'll protect my worksheet, and
- 02:27 I won't even use a password at all.
- 02:29 And this is because I might need my users to go in and make a modification to
- 02:33 a formula, so this will prevent them from doing something they shouldn't do like
- 02:37 overwriting this formula here, but if they need to get at it for
- 02:41 some reason, they can unprotect the worksheet and make changes to it.
- 02:45 If you don't feel the same way about that, that's no a problem.
- 02:48 What we can do is we can go and say Protect worksheet.
- 02:52 We can put in a password.
- 02:53 I'm gonna go 1234.
- 02:54 We'll say OK.
- 02:56 It'll ask me to confirm that password, 1234.
- 03:01 We'll say OK, and
- 03:03 now again, I can't make any modifications to the cells that are protected.
- 03:07 I can make modifications to the ones that aren't protected, and
- 03:10 when I go and say Unprotect it, it's gonna ask me for the password.
- 03:15 And if I get it wrong, 12345, It will let me know that, hey, this doesn't work.
- 03:22 So we can provide a small level of protection for what we actually have here.
- 03:28 So, the big key takeaways I want you to remember here, is that every cell on
- 03:31 an Excel worksheet is locked when you, by default, open it up.
- 03:35 And that locking only takes effect when you actually apply protection.
- 03:40 You need to unlock the worksheet cells that you want your user to be able to
- 03:44 work with before you actually activate protection on your workbook.
- 03:48 That's the biggest,
- 03:48 biggest thing to remember when you're working with these particular pieces.
- 03:53 I'd also say if you are desperately want to make sure that nobody can get access
- 03:57 to your data, for any reason, it shouldn't be in Excel because, unfortunately,
- 04:01 it is relatively easy to go and
- 04:03 hack that worksheet protection on the worksheets internally.
- 04:06 So it makes more sense to store them in a secure location where you're not gonna
- 04:10 have those problems.
- 04:12 Overall, however, we wouldn't wanna say that this is
- 04:14 a reason to not use Excel because it's absolutely not.
- 04:17 We just need to make sure that we actually look after our workbooks appropriately.
- 04:20 And don't leave them in places where they're actually exposed to nefarious
- 04:24 individuals.
Lesson notes are only available for subscribers.