Learn Excel: How to Protect your Workbook

If you wish to learn Excel inside-out, I'd recommend to go over some key principles that will let you understand first and foremost whether a spreadsheet is the most appropriate tool for your job, and then how to turn it into a dynamic, shared piece of work.

When it comes to the latter, I'd encourage you to work collaboratively, share ownership and suggest review to maximize results and turn your Excel spreadsheets into powerful, recyclable tools, as opposed to one-off, isolated pieces of information. Especially if it takes so much effort to create or analyse a spreadsheet, why do it all over again next time? After nearly two years at Filtered, and being involved with spreadsheets every day, this is possibly the best advice I can give to anyone looking to improve their Microsoft Excel knowledge.

However, when many people are involved, also come risks. For example, it only takes a user who doesn’t understand how a spreadsheet works to overtype a key formula with a fixed number. And let's be honest - quite often you could overtype one of your own formula accidentally yourself. The result? Pretty simple. None will be able to rely on the spreadsheet giving the right answers.

To avoid this, I recommend you to lock certain cells in your spreadsheet to prevent them being changed. Logically, you would need to lock every single cell that contains a formula. However protecting an Excel workbook involves several steps.

• First of all you have to make sure the right cells are locked and unlocked.
• Then, for locking to take effect, you have to individually protect each sheet of your workbook that contains at least one locked cell.
• Finally, to prevent users invalidating your spreadsheet, for instance by deleting or moving whole sheets, you need to protect the structure of the overall workbook.

Let's take a closer look.

1. Locking cells

Design

Before you consider locking the cells in your worksheet, you have to ensure their design is correct. You will need to lock every single cell that contains a formula, and ensure that any cells where users input data are unlocked. However this is only possible if you have avoided mixing formula and data in your cells. As soon as you have a single cell that mixes data with a formula, then you can’t lock that cell, because a user might need to edit the data. But you have to lock the cell to protect your formula. You can’t do both.

In this example, we want to calculate how much tax we need to pay for a set of invoices:

We have been careful to keep our data and formulas separate. If we had not used a separate cell to hold the tax rate, and simply entered our formula as A4*20%, we couldn’t have locked the cell to protect the formula while still allowing the user to change the tax rate if necessary.

Locking

Having got the design right, we can now sort out the locking and unlocking of individual cells. You might be surprised to find that all the cells, in all the sheets in your workbook, start off as locked. So, although we can happily type different values into cell B1, if we right-click on the cell, choose Format Cells, and the Protection tab, we can see that this is the case:

In fact, it is exactly right that this is the case. If our aim is safety, it’s very important that we start off with all our cells locked rather than unlocked. If they start off as unlocked, and we forget to lock one or more cells as we create our worksheet, this could be disastrous as we would have cells containing formulas that we believe to be locked, and therefore ‘safe’, but which are actually vulnerable.

It’s much better that all cells start off as locked and we have to remember to unlock the data cells. If we forget to change the locking in this situation, it will just mean that our user won’t be able to change the data until we, or they, turn off locking or protection. Sounds fairly safe to me.

Note that we can also set the contents of the cells to Hidden if we don’t want our users to be able to see the cell formulas when the worksheet is protected.

2. Protecting Sheets

So, if all cells are locked, why can we happily change any of them? The dialog above explains that locking has no effect until the sheet is protected using the Protect Sheet option on the Review ribbon tab.

Therefore, we start off by selecting each of our data cells, or blocks of data cells, and using the right-click, Format Cells > Protection tab > Locked option to turn off locking. As well as turning off locking, we also need to give our users some indication of which cells are intended for data input and which are locked. We can either use one of the other formatting options in the Format Cells dialog or the appropriate Cell Style:

We have unlocked our 5 data input cells and applied the Input Cell Style. As we have said, before this changes the behaviour of our cells, we need to go to the Review ribbon tab > Changes group > Protect Sheet option:

You can choose from a range of actions that you want the user to be able to use, or to prohibit, including whether the user can even select locked or unlocked cells. If you turn on sheet protection without setting a password, then the user can simply unprotect the sheet. However, even this can be useful, it means that in normal use the worksheet can remain protected to avoid accidental overwriting. Protection can be very useful for avoiding accidental errors but, even if you do use a password, sheet protection passwords may not be sufficiently secure to rely on locking and protection being completely unbreakable.

All the sheets in our workbook need to be individually protected this way to lock any of the cells they contain.

Once we have left some cells locked and protected our sheet, if a user tries to edit a locked cell, they will see the following message:

Protect Workbook

Our spreadsheet is still not fully protected. A user could still Insert, Delete, Rename or Move sheets for example, which could compromise our spreadsheet:

To protect the structure of our workbook we can go to the REVIEW ribbon tab, Changes group, Protect Workbook option:

The same comments about passwords and password strength will apply to the workbook password as to the worksheet password.

3. Protecting an Entire Workbook

So, we've learnt to protect a spreadsheet to try and ensure that incorrect or accidental changes don’t invalidate our testing procedures and controls. We might also need to prevent people accessing a confidential spreadsheet at all.

We can set a workbook open password when saving a file. Use File ribbon tab > Save As and then click on the Tools button and choose General Options:

You can set passwords to open and/or modify the workbook. Or just set it so that whenever it is opened it will recommend that it be opened Read-only: 

Finally, even if it sounds silly, make sure you remember your password. And possibly avoid using '123' or your first name!

​ILFM Members can get a huge discount off Filtered.com Excel training (£40 Inc Vat instead of £75) Click here to find out more.