Locked lesson.
About this lesson
Learn about the setup and weaknesses of Excel’s security systems.
Lesson versions
Multiple versions of this lesson are available, choose the appropriate version for you:
Exercise files
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.
Security Considerations.xlsx12.6 KB Security Considerations - Completed.xlsx
12.6 KB Security Considerations - Extra Practice.xlsx
18.9 KB
Quick reference
Topic
Setup and weaknesses of Excel’s security model.
When to use
Once you’ve built a file you’re proud of, you’ll want to protect its integrity. Unfortunately, the protection in Excel is best used to protect from accidental damage only, as it’s not all that strong.
Instructions
Triggering protection
- Go to the Review tab > Protect Sheet > Click OK (No password)
- Try to edit any cell and you’ll be told you can’t
- Go to the Review tab > Unprotect Sheet (No password was set, so it will)
Allowing editing
- Select B7:B10, right click and choose Format Cells
- On the Protection tab, uncheck “Locked” and click OK
- Go to the Review tab > Protect Sheet > Click OK (No password)
- You can edit any cell in B7:B10, but no others
- Unprotect the worksheet
Hiding formulas
- Select B11, right click it, choose Format Cells > Protection
- Check the Hidden checkbox and click OK
- Go to the Review tab > Protect Sheet > Click OK (No password)
- Select B11 and look in the formula bar… no formula!
- Change B8 to 25000 and notice the formula still works
Using passwords
- Unprotect the sheet, then re-protect it entering 1234 as the password
- Confirm the password and verify that the protection works
- Unprotect the worksheet and enter the password required to unlock it
Key takeaways
- By default, every cell on an Excel worksheet is “locked” when you apply protection
- You need to unlock cells you want the user to edit before you protect a worksheet
- Excel’s password security is very weak, and can easily be hacked
- Excel’s password security is best used to protect from accidental damage, not willful damage
- 00:04 Let's talk about security in Excel.
- 00:07 By default every single cell in Excel is what we actually consider locked.
- 00:13 So if we go to our Review tab
- 00:16 and we go to Protect Sheet
- 00:19 we can actually protect our worksheet. I'm not going to bother using a password.
- 00:23 Right now the default setting is to select locked cells and select unlocked cells.
- 00:27 And when we say OK you can see that we can move around still just we always could
- 00:32 but if I try and go and change a value it's going to come back and tell me it's on a protected sheet and can't be done.
- 00:38 This a good thing because
- 00:40 unless we've actually specifically told Excel that we want a cell unlocked
- 00:45 it will always be protected when we actually protect our worksheet.
- 00:49 Now, I'm going to unprotect the sheet and show you how this works a little bit differently.
- 00:53 What we're going to do is we're going to unlock this block of cells.
- 00:57 So if we right click and we say Format Cells
- 01:01 you'll notice that the protection has it as locked, so we'll uncheck that and we'll say OK.
- 01:08 Now the difference is, when we go and protect the sheet this time...
- 01:13 it allows us to select both locked and unlocked cells still... and we'll say OK... and here's the difference:
- 01:19 When we try and type something in this cell, it says that it's protected.
- 01:23 But when we go over to this cell and we type something in there, it'll let us type it in and
- 01:28 actually complete it. So that's one way we can use protection.
- 01:33 If I go back and unprotect the sheet, I can modify it again too.
- 01:36 I'm going to make a different change to the gross revenue cell.
- 01:39 You can see that we have the formula showing right in there for the sum of B7:B10.
- 01:45 I'm going to right click on this one, click Format Cells
- 01:48 I'm going to leave it locked, but this time I'm going to set this one to hidden and say OK.
- 01:54 And now we'll go back and protect the worksheet once again and say OK...
- 02:01 and that this point notice what happened to my formula...
- 02:04 it disappeared.
- 02:05 So the logic is still there, it still works so I can go in and modify this cell here and it's still working
- 02:13 but it hides the formulas away so you can't actually see them, so it can help protect your business logic a little bit.
- 02:19 So here's the thing about sheet protection in Excel:
- 02:24 Usually when I go and actually protect a worksheet I don't even bother using a password. And there's a very simple reason for that.
- 02:30 While the sheet protection features inside Excel are absolutely fantastic for preventing accidental damage,
- 02:37 as a security measure they're actually extremely weak.
- 02:40 Any guru can actually rip away the sheet protection inside an Excel file in less than
- 02:44 30 seconds without even adding any extra software.
- 02:49 Now, that doesn't mean it shouldn't be used, as I say, it's great for protecting from accidental damage.
- 02:53 But if you want to actually secure your workbook so that users can't do anything to them the challenge is this:
- 02:59 Once they're into the file you can't.
- 03:02 But you can actually prevent them from getting inter file to begin with
- 03:06 and the way we do that is we go to File menu.
- 03:09 We can actually go and protect our workbook using encryption with a password.
- 03:14 And when you do that it will come up and ask you for the password.
- 03:17 So 1234 we can set, and then it will ask you to confirm it: 1234.
- 03:22 Now, you would never use a password like 1234 because it's an extremely weak password and that can be hacked.
- 03:28 So you always want to remember when you're working with this kind of security:
- 03:33 You want to have something that is at least nine characters long, preferably a mixture of upper case lowercase letters,
- 03:39 not consisting of words from a dictionary
- 03:41 they should actually probably be in patterns of words that you wouldn't normally find together,
- 03:45 and mix in some numbers. Something to make a nice strong password because
- 03:49 at the end of the day, hacking is impossible to completely prevent but the longer your password is
- 03:54 the better the security algorithm will be.
- 03:57 Now, when we close this and we go back to reopen it again... let's go back and open our recent file.
- 04:06 It will now ask me for the password and if I put in the wrong password: 12345...
- 04:12 It won't let me in.
- 04:13 So that's the way we actually work with real security in Excel as we prevent the user from ever getting in in the first place.
Lesson notes are only available for subscribers.