Locked lesson.
About this lesson
The proper steps to take to protect the integrity of the model.
Exercise files
Download this lesson’s related exercise files.
Security and Protection.xlsx7.5 KB Security and Protection - Solution.xlsx
8 KB
Quick reference
Security and Protection
Discover the how to secure and protect an Excel workbook.
When to use
Security and Protection of an excel workbook is an important part of creating a Financial Model. It will help to prevent unwanted parties gaining access to confidential information. It will also protect the model from any unwanted modifications.
Instructions
Overview
- Protect Workbook
- Structure of workbook cannot be changed, e.g. hiding / moving / deleting of sheets
- Protect Worksheet
- Locked cells cannot be accessed or modified
- Lock Cells
- Cannot be accessed / modified if worksheet is protected
Example
- To protect a cell in excel you will need to protect the sheet first. To protect a sheet:
- Go to the ‘Review’ tab
- Select ‘Protect Sheet’ – This will lock all of the cells on that sheet
- To unlock cells so they can be edited, you will need to unprotect the sheet first to unlock the cells that aren’t required to be locked
- CTRL + 1 will bring up the Format Cells dialog box (as shown below)
- Select the ‘Potection’ tab and untick the ‘Locked’ checkbox
- To protect a worksheet, you will need to protect the workbook – this will prevent any of the worksheets from being deleted.
- Go to the ‘Review’ tab
- Select ‘Protect Workbook’
- 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.