Security Considerations

Quick reference

• 00:04 >> In this video we're going to look at protection methods for your worksheets.
• 00:08 Once you've built a beautiful file and you're really proud of it,
• 00:10 you obviously want to protect it when you're gonna send it to another user.
• 00:14 So I'll show you what works and what doesn't work here.
• 00:16 The first thing that we wanna recognize is that
• 00:20 we can go into our Review tab and we can protect our worksheets.
• 00:25 So I'm not gonna bother putting a password on this right now, but
• 00:28 you'll notice that we can allow our user to select locked cells and unlocked cells.
• 00:32 We'll say OK.
• 00:33 This means that they can select anywhere on the worksheet, but when they try and
• 00:37 come in and change a cell, they get a nice little message that the cell or
• 00:41 worksheet is protected and the cells are therefore read-only.
• 00:44 So that's kind of nice.
• 00:47 I'm gonna unprotect the worksheet.
• 00:48 Notice I didn't use a password last time so it doesn't ask me for one.
• 00:52 I'm gonna make a small change to these cells here.
• 00:55 What I'm gonna do is I'm gonna go to Format Cells, and
• 00:59 on the Protection tab, I'm gonna uncheck the Locked box and say OK.
• 01:05 And now what I'll do again is protect the worksheet, again with no password.
• 01:09 And you'll notice that I can't make any changes anywhere else, but
• 01:13 in these ones now, I actually can.
• 01:16 Okay, so everything still works.
• 01:18 The formulas recalculate, so that's kinda cool.
• 01:21 One challenge with this that's a little frustrating is if you've used your styles,
• 01:26 which you should, the unfortunate part here is that
• 01:30 even though our protection options get set in here where we go back and
• 01:34 say let's Unlock these, this cell change or
• 01:38 this particular piece does not roll through your formatting for your styles.
• 01:41 You have to go back and
• 01:42 reapply the styles in order to get that particular setting working.
• 01:47 And that's a little frustrating because really, I mean when you make a change to
• 01:50 that cell style it should roll through the rest of the workbook, but protection for
• 01:53 some reason does not.
• 01:54 This happens in both Windows and in Mac Excel.
• 01:59 Now another thing I wanna show you here as well,
• 02:02 we also have the option to protect formulas.
• 02:04 And this is kind of a neat one.
• 02:05 So if we go and we right-click on this cell and say Format Cells,
• 02:09 I wanna leave this locked because I don't want a user touching this cell, but
• 02:14 I'm also gonna mark it as Hidden.
• 02:17 And what's gonna happen right now is nothing.
• 02:19 Nothing particularly happens until I go back and protect my worksheet.
• 02:24 When I do that and I say OK, look in the formula bar.
• 02:31 Where this one here is not hidden, this one is.
• 02:36 The cell value still shows but there's no formula showing up here, and
• 02:40 yet if I go back and
• 02:42 modify the cell that I can change, you'll notice that the formulas do update.
• 02:47 So that's kind of a neat thing there,
• 02:50 if you wanna hide your logic off on your formulas.
• 02:54 One thing that I do wanna mention though is that the password protection
• 02:58 inside Excel's object model here with the worksheets is actually very weak.
• 03:02 If somebody wants to get into this, they can actually hire a programmer very
• 03:05 cheaply to actually hack a password inside the workbooks.
• 03:09 So it's important to realize just using a password here of one, two, three, four.
• 03:14 It's important to realize that when we do go and protect these things,
• 03:18 we need to be aware that the proper protection is storing it
• 03:22 in the right folders inside our corporate drives that are properly protected.
• 03:26 If somebody gets our file and we have our worksheets protected,
• 03:30 that's not gonna be sufficient to actually prevent somebody who has the means and
• 03:34 the tools to be able to actually unlock these things.
• 03:37 Now, you'll notice at this point when I try to unprotect my worksheet,
• 03:40 and I've protected it, so I can't modify the cells outside
• 03:44 of the ones that I've allowed to be modified, so these are all good.
• 03:49 When I go to unprotect the worksheet now, it's gonna ask me for the password, and
• 03:53 if I put the wrong password in, which I will right now, it'll come back and
• 03:57 tell me it's an incorrect password.
• 03:58 If I put the correct password in, it will unlock and everything is good to go.
• 04:04 A final thing that I wanna mention with the protection options
• 04:08 is you do have the option to only allow people to select unlocked cells.
• 04:13 This would only allow them to select the green cells that I've unprotected here.
• 04:17 That's kind of a nice feature, and
• 04:18 you can also control some different things that they can do as well here.
• 04:21 But the nice piece about this one is if I've got a worksheet that's meant for
• 04:25 data entry, they can't even select.
• 04:27 I'm trying to click over here.
• 04:28 They can't select that cell at all, but
• 04:31 they can select any of these green ones here.
• 04:33 If I try and arrow up, up, it's gonna go through the unprotected cells.
• 04:36 So that's kinda cool.
• 04:38 The final thing I wanna mention is workbook protection.
• 04:41 When you go to do a Save on your file or
• 04:44 Save As, there is an option down on the bottom for Options.
• 04:49 If you click on this one, you get to provide the Password to open or
• 04:53 a Password to modify.
• 04:55 Open will prompt the user for a password right when they open the workbook, and
• 04:59 this is much, much harder to hack, so these ones are a little bit safer.
• 05:03 Regardless, you still should be storing your files in proper locations.
• 05:08 The other one that you can deal with is you can actually force somebody to open it
• 05:11 as read Read-only, and
• 05:12 you can give them a password they have to enter if they want to modify the workbook.
• 05:15 So a few different levels of protection that you can apply
• 05:20 that you may wanna actually use in your work.
• 05:23 So keep all those in mind as you move forward.

Lesson notes are only available for subscribers.