Security Considerations

Quick reference

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