Security and Protection

Quick reference

• 00:04 Next up, I want to talk about Security and Protection in Excel.
• 00:10 Now, let's just be clear here.
• 00:14 The security in Excel is not necessarily the best security in the world,
• 00:19 although since Excel 2013, it has been getting better and better.
• 00:25 In fact if you go to the Microsoft website you can find on one page that it says and
• 00:31 I quote, that the security features in Excel are for display purposes only.
• 00:37 Now, Let's just be clear here, I'm not trying to disparage Microsoft.
• 00:42 What actually they are saying is, it's a spreadsheet.
• 00:45 It's got some security features, but if you really require your data to
• 00:49 be confidential, don't give it to someone.
• 00:53 Don't give it someone, it can be cracked.
• 00:57 What am I talking about with the layers security and protection?
• 01:00 There's three levels in Excel,
• 01:03 I'm going to read the bullet points in the wrong order here.
• 01:06 Which you might think, will should have put them in the other way around then,
• 01:09 but let me explain.
• 01:11 Lock cells, is what you do on a particular cell.
• 01:14 We showed that when we were doing an assumption style a few sessions back.
• 01:19 It's only when we protect the worksheet that the cell
• 01:23 protection actually activates.
• 01:26 And protecting the workbook is what actually protects the worksheet.
• 01:30 So we need to be careful how we are putting these together, so
• 01:34 let me show you this with an Excel example.
• 01:39 Time for a brand new Excel workbook, freshly baked, straight out of the oven.
• 01:44 Good old book one, crisp and ready to show you the three tiers of security and
• 01:49 protection available to you in Excel,
• 01:52 namely Lock cells protect sheet or protect work book.
• 01:57 So let me have a sheet one here, and
• 02:00 it doesn't which cell I pick around them, control one, format cells it's locked.
• 02:05 That means if I click OK, I can't type in, wanna bet?
• 02:09 Seem to be doing fine, that's not what it means.
• 02:13 It means it will not you actually type in that cell if the sheet is protected,
• 02:18 you have to go to the next layer.
• 02:21 Let me demonstrate,
• 02:23 let me actually make this formated cell and put number 17 in it.
• 02:29 Control one, I'm going to unlock it and then protect the actual worksheet.
• 02:35 Now, I can protect the worksheet by going to format and down here,
• 02:39 the third from bottom on protect sheet here.
• 02:41 That would do it but I want to show you it's also on the review tab.
• 02:45 And why I want you this, because not only to protect sheet,
• 02:49 next to it is protect workbook.
• 02:51 So, you've got both in there available,
• 02:54 Alt-R brings up the review tab, PS for protect sheet, PW for protect workbook.
• 03:00 I quite like the old excel 2003,
• 03:02 keyboard shortcut as well which is Alt-TPP for protect sheet.
• 03:08 I used that joke earlier if you missed it you might have to look
• 03:11 at an earlier session.
• 03:13 If I protect the sheet, I'm just gonna go click, it prompts you for password.
• 03:18 Now, since Excel 2013, it has had a bit better security for putting in passwords.
• 03:26 But I find that you often forget what they are and
• 03:29 you're the one who's going to the Internet search engine looking for
• 03:33 free Excel password cracker to hunt out, how to actually edit your sheet again.
• 03:38 So, I don't really recommend password protecting your worksheet.
• 03:41 Because quite frankly, it's not fully secure anyway.
• 03:44 And for most people,
• 03:46 if it says you shouldn't edit this cell, they'll leave it well alone.
• 03:49 Regardless of whether there was a password on it or not.
• 03:51 So, I'll just click OK, that means now, I can't actually type in that cell.
• 03:57 But I can type here, there you go.
• 04:01 So protecting sheet has the effect of locking down cell,
• 04:07 but does it protect the sheet?
• 04:09 Well if I right click here and go Delete, It will permanently be deleted.
• 04:15 I won't be able to get it back Okay, it's gone.
• 04:20 Now, I don't know about you, but that's not my definition of protecting a sheet.
• 04:26 If you want to protect a sheet, so can't let, you've got to protect
• 04:30 the workbook again it prompts a password, if you want, I'm not going to bother.
• 04:34 Now, if I right-click here and hit Delete, I can't, it's not available to me.
• 04:40 Now, if you want to stop a workbook being deleted in File Explorer,
• 04:44 well good luck to you.
• 04:45 You'll have to talk to your IT department.
• 04:47 You can still delete a protected workbook in File Explorer.
• 04:51 So it's all about being at the next level up.
• 04:55 What you might want to think therefore when building a financial model
• 04:58 is your input sheets and calculations and outputs.
• 05:01 Which cells need protecting and which don't, and
• 05:04 then protect all the sheets as a result.
• 05:07 Just keep in mind the different levels of security and realize you need to go one
• 05:12 level up from what you think, that is to protect the cell, you protect the sheet.
• 05:17 To protect the sheet, you have to protect the Workbook.
• 05:21 That's the important message here.

Lesson notes are only available for subscribers.