Security Considerations

Quick reference

• 00:04 Let's talk about security in Excel.
• 00:07 By default, every single cell in an Excel workbook is actually locked but
• 00:12 unprotected, and this is by design.
• 00:14 Because what we really want is, when you go to the Review tab, up here, and
• 00:19 you choose to protect your worksheet and say OK.
• 00:23 If we haven't made any changes, we really want everything to be protected.
• 00:27 And you can see right now that as soon as I go and try and type something in a cell,
• 00:30 it comes back and tells me that this is on a protected worksheet.
• 00:33 So this is the behavior we would expect to see.
• 00:36 We certainly wouldn't want to go and say, of all of these cells on the worksheet
• 00:40 let's protect all these ones, much better to go with make sure that I have to
• 00:43 explicitly unprotect them if I want people to edit them.
• 00:47 Let's go and change this behavior right now.
• 00:49 I'll unprotect the sheet, I'm gonna go and select all these screen cells here from
• 00:53 B9 to B12, right click, and we'll format cells.
• 00:58 On the protection tab, you'll notice that these cells are locked, and
• 01:02 this again is the default state.
• 01:04 So I'm gonna uncheck that box and say, OK.
• 01:08 Now, this isn't gonna make any change until I go and protect the worksheet.
• 01:12 But before I do, I also want to take a look at gross revenues.
• 01:15 I'm gonna right-click on this one and say, Format Cells.
• 01:20 Notice that gross revenues has a formula in it, SUM B9 to B12.
• 01:24 It is locked, and I want it to stay that way.
• 01:26 I certainly don't want anybody to overwrite my formula.
• 01:28 But I'd also like to mark it as hidden.
• 01:31 And let's see what happens now when we go and protect the worksheet.
• 01:35 We'll choose OK, and at this point you'll notice that the formula disappears.
• 01:44 I can't edit it because it's locked.
• 01:46 But if I come back up to one of my other cells and I say,
• 01:49 hey, I'd like to make this 35,000.
• 01:52 It's now allowing me to edit and the formula still works.
• 01:55 So this is a way that we can actually protect a little bit of the intellectual
• 01:59 property that we have with our formula so that they don't show the calculations.
• 02:03 However, how secure is it?
• 02:05 Well, the reality is, you don't have to protect or
• 02:09 unprotect with no password, although in 99% of cases I do.
• 02:14 You could type in a password of 1234, and then say, OK, and
• 02:17 it would ask you to confirm that.
• 02:20 And then the user would have to type in that password when they want to go and
• 02:23 unprotect the sheet.
• 02:25 The reality is, I rarely use a password for this, and
• 02:28 it's because if you actually run into someone who knows visual basic for
• 02:32 applications, the programming language inside a cell,
• 02:35 you can actually find a brute force password hacker on the Internet.
• 02:39 This is very unfortunate, but
• 02:41 it means that this is not a super secure platform in many ways.
• 02:45 We shouldn't be using the protect sheet feature
• 02:48 to really protect our intellectual property.
• 02:50 I'd like to encourage people to still use, I mean, hey, Excel is a good,
• 02:54 solid program anyway.
• 02:56 But the reality is, the protection features inside Excel's workbook
• 03:00 are actually meant to protect against accidental damage.
• 03:03 If you have someone nefarious in your organization, you shouldn't be giving them
• 03:06 access to the critical information in the first place, that's the big issues.
• 03:10 So, you don't want them in your Excel workbook.
• 03:11 But put it in a protected directory so that people can't get to it.
• 03:14 Don't rely on sheet protection to make this work.
• 03:17 But where sheet protection works really well,
• 03:19 is when you protect things without your password.
• 03:21 And you say to your users, listen, it's protected so
• 03:24 you can't accidentally rewrite the formulas but.
• 03:26 But if you need to make a modification, you can unprotect it, you can do the work.
• 03:29 Just let me know why, and then reprotect it when you're done.
• 03:33 So, worksheet protection is a fantastic tool when used the right way.
• 03:37 The other thing that I wanna show you really quickly, when we protect the sheet,
• 03:41 I can also tell people that they can't select the locked cells.
• 03:44 They can only select the unlocked cells.
• 03:46 And the great thing about this, if they try and
• 03:48 click on any other cell on the worksheet, it won't let them.
• 03:51 If they come down here, and we go and say let's go down arrow, you'll notice that
• 03:56 it's moved to the next block of cells that I've unprotected as well.
• 03:59 So we can actually prevent people from even selecting cells if we want.
• 04:02 But again, remember, I'm protecting my sheets without a password,
• 04:05 to prevent from accidental damage.
• 04:07 I'm gonna prevent the nefarious users from even getting at this workbook
• 04:12 by saving it in a proper location on my network or in my SharePoint site.

Lesson notes are only available for subscribers.