Protecting your data in Excel

aat comment

Protecting an Excel workbook involves several steps.

First of all you have to make sure the right cells are locked and unlocked. Secondly, 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 by deleting or moving whole sheets for example, you need to protect the structure of the overall workbook.

Locking cells

Design

Before you consider locking the cells in your worksheet, ensure that you have designed it correctly. You’ll need to lock every single cell that contains a formula, and ensure that any cells where users input data are unlocked. And this is only possible if you have made sure that you have avoided mixing formula and data in any 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:

dch

Fig. 1 – Column C showing how the Tax payable values were calculated

We have been careful to keep our data and formulae separate. If we had not used a separate cell to hold the tax rate, and simply entered our formulae 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

Now that the design is right, we can 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:

pit

Fig.2 – Surprisingly, all cells start off as locked

This makes perfect sense. 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 formulae 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.

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 formulae when the worksheet is protected.

Protect sheet

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, FormatCells > 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:

iyt

Fig.3 – Using styles to show other users which cells are intended for data input

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:

plg

Fig.4 – Protect sheet dialog box

As you can see, you can choose from a range of actions that you want to allow the user to continue 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 in this way if we want any of the cells they contain to be locked. 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:

fdr

Fig.5 – Message displaying when a user tries to edit a locked cell

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:

refs

Fig.6 – Other users could still make dangerous changes to the workbook

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

nbv

Fig.7 – Protect Structure and Windows dialog

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

gres1

Fig.8 – The user no longer has the option to make changes to the workbook structure

Protecting an entire workbook

So far, we have been looking at protecting 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:

mki

Fig.9 – Protecting an entire workbook by choose General Options from the Tools dropdown in Save As

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:

lopn

Fig.10 – Creating a password to open and modify your workbook

Finally… don’t forget your passwords.

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.

Related articles