Methods to Minimize Spreadsheet Errors: Part 3 - Protection

Locked lesson.

Upgrade

Quick reference

Methods to Minimize Spreadsheet Errors: Part 3 - Protection

Lockdown workbooks, worksheets, or specific cells to protect from unwanted changes. 

When to use

We protect workbooks, worksheets, or specific cells whenever we want to control how much access others have to change things in the spreadsheet. 

Instructions

Protect the Workbook

We can protect the entire workbook and all of the worksheets contained within. When we protect a workbook, others will have limited to no access to the workbook without a password. 

  • Open the workbook to be protected. 
  • Click the File tab. 
  • Click Info. 
  • Click the arrow next to Protect Workbook to see the protection options. 
Always Read-Only

Always Read-Only applies minimal protection. Others can still open the workbook but they will see a yellow warning message across the top that lets them know this workbook has been set to Read-Only. 

This can be bypassed simply by clicking Edit on the warning message or re-saving the file. Read-Only serves as a warning to others not to edit the file without actually locking them out of the file. 

Encrypt with Password

This option requires colleagues to type in a password to open and edit the file. Without the password, colleagues cannot access any part of the file.

Protect Current Sheet

Protect Current Sheet protects the current worksheet only. Colleagues will still have access to modify other worksheets in the workbook. 

Protect Workbook Structure

This option protects the workbook structure ONLY. The workbook structure relates to the tabs (worksheets). For example, if we protect the structure we can still edit cells on the worksheets but we cannot delete worksheets, insert new worksheets, change the tab color, etc. 

Add a Digital Signature

If we have access to the required third-party software, we can protect our workbook by adding a digital signature. 

Mark as Final

Mark as Final is another protection option that doesn't stop people from editing the spreadsheet but instead lets them know that this is the final version and should not be changed. 

When a workbook that has been marked as final is opened, a yellow warning message will appear at the top. Colleagues can choose to override this. 

Protect Specific Cells

Sometimes we don't necessarily need to lock down the entire workbook or worksheet, just specific cells. 

The thing we need to remember is that by default, every cell in Excel is locked. We ann type into cells because the locking doesn't take effect until we choose to protect the worksheet. 

If we only want to make certain cell ranges editable and lock down everything else, we need to unlock those cells before applying protection. 

  • Select the cells/ranges that need to remain editable. 
  • Press CTRL+1 to open the Format Cells dialog box. 
  • Click on the Protection tab. 
  • Remove the tick from the Locked box. 

Now we can protect the sheet to lock all cells except the unlocked ones. 

  • From the Review tab, in the Protect group, click Protect Sheet. 
  • Select from the list what you want to allow users to do. 
  • Add a password. 
  • Click OK. 

Hints & tips

  • To unprotect a worksheet, click on the Review tab and click Unprotect Sheet. 
Login to download
  • 00:04 Now another way that we can control what's going into our spreadsheet is to
  • 00:09 apply protection to worksheets, workbooks, or just specific cells.
  • 00:14 And if you wanted to just completely lock down this workbook and make it read only,
  • 00:19 you could very simply jump up to File, go into Info, and go to Protect Workbook.
  • 00:25 And you have a few different options that you could choose in here.
  • 00:28 If you want it to open as read only you could use that.
  • 00:31 You can protect it with a password if you wanted to so
  • 00:34 people need to know that password before they can get in.
  • 00:37 You can protect just the workbook structure, so
  • 00:39 that means that people can add things to the worksheets, but
  • 00:42 they can't change the actual structure of the workbooks.
  • 00:45 They can't do things like add new tabs, for example.
  • 00:49 You could even simply just mark your worksheet as final.
  • 00:52 Which doesn't effectively apply any protection, people can still edit
  • 00:56 the cells, but it does give a little warning message running across the top of
  • 01:01 the worksheet when someone opens it, this is the final version of this workbook, and
  • 01:06 they should proceed with caution if they're going to change anything.
  • 01:10 So a few different options when it comes to protecting the actual workbook itself.
  • 01:15 What I want to focus on is protecting specific cells and
  • 01:18 ranges of cells in a worksheet.
  • 01:21 So let's take a look at the example that we just completed.
  • 01:24 You can see here we have our pizza names, the price per unit, and
  • 01:28 the total plus tax, and these cells contain a formula.
  • 01:31 We also have the sales tax over here.
  • 01:35 So, maybe I want to lockdown this worksheet so
  • 01:39 that people can't change the formula, but they can change the pizza name,
  • 01:44 the price per unit, and the sales tax amount.
  • 01:48 Now when it comes to locking cells in Excel,
  • 01:51 this kind of works a little bit backwards, at least in my mind.
  • 01:55 Because one thing you have to remember is that
  • 01:59 by default every cell in Excel is locked.
  • 02:02 Now this is the part which always used to confuse me,
  • 02:06 because I used to say to myself, well, if every cell in a worksheet is locked,
  • 02:11 how come I can go into any cell I like and start inputting numbers.
  • 02:16 Well, it's because the locking doesn't take effect until
  • 02:19 you protect the worksheet.
  • 02:21 Now, we are going to protect this worksheet, but
  • 02:24 we want to unlock specific cells first of all that we want to be able to edit.
  • 02:29 So I'm going to select the pizza names and the price per unit,
  • 02:33 I'm going to hold down Ctrl and select where we have the sales tax amount.
  • 02:37 So I want these cells to be editable, but I want everything else to be locked.
  • 02:42 So what I need to do here is go into Format Cells, so we can right-click and
  • 02:46 select from the menu, or we can use the keyboard shortcut Ctrl 1.
  • 02:50 And notice we have a Protection tab at the top, and Locked is checked.
  • 02:56 So, this is what I mean by default, everything is locked.
  • 03:00 So, I want to unlock these cells, click on OK.
  • 03:04 And then I need to protect the worksheet,
  • 03:07 which means it's going to lock all of the cells that are locked by default, but
  • 03:11 keep open the ones that I have just deselected.
  • 03:14 So let's lock the worksheet, let's jump up to Review and Protect Sheet.
  • 03:20 Now this is where I can go in a stage further and
  • 03:23 specify things that I want to allow users to be able to do.
  • 03:26 So currently, they can Select Locked Cells and Unlocked Cells.
  • 03:31 So even though I'm going to lock this worksheet,
  • 03:34 they can still click and select a cell, they just can't edit the cell.
  • 03:38 I'm going to add a password, let's re-enter it.
  • 03:42 And now, if I try and change this formula column by typing something in,
  • 03:47 I'm going to get an error message, but
  • 03:50 I can still edit things like the sales tax cell because I unlocked them.
  • 03:55 So this is a great way of really safeguarding important
  • 04:01 cells that contain important formulas that you
  • 04:06 don't want to be changed from accidental errors.

Lesson notes are only available for subscribers.

Gift this course