Security Considerations

Quick reference

• 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.