Mouse Training London Ltd: Excel- Protecting the formulas in your worksheet

Lock Cells

You would think that protecting formulas in an Excel worksheet would be easy to do. After all, they are a key element of any worksheet.

The reality is that it can be rather a frustrating process if you don't understand how Excel cell locking and worksheet protection works. It's an old, familiar story; the design dates back to the 1980's when Excel was first released and now it can't be changed because everyone has got used to it. The way you have to do it does seems illogical if you've never done it before. But then if you had done it before you wouldn't need to be reading this article, would you?

So, persevere with this, it does work but you need to suspend common sense and just get on with it. Let's spend a few minutes understanding how worksheet protection and cell locking works and then we can successfully and easily do simple things like protecting our formulas.

How does worksheet protection work?

The theory is that all the cells in an Excel worksheet are locked but the locking is not enabled until you protect the worksheet. You can demonstrate this on a new worksheet if you like.

Click the Format control in the Cells group on the Home tab and then choose Protect Sheet from the menu. Don't worry about entering a password or any of the other options, just click OK and the active sheet is protected. Now, try to type something into a cell, any cell. See, it doesn't happen—you can't make any entries at all.

Protecting the worksheet

All you get is the "The cell that you are trying to change is protected" message box. To turn off the worksheet protection repeat the previous process and choose Unprotect Sheet from the menu.

In practice, protecting the entire worksheet is overkill. You need to be able to make entries in some of the cells and you do this by unlocking them before you turn on the worksheet protection. Remembering, of course, that all cells are locked unless you set them otherwise.

So, the process is that you think about the cells on the worksheet that you wish to be left open for entry and these are the ones which you unlock. Then you protect the worksheet so that only the unlocked cells can be used.

You can not directly select and format a range of cells to be protected. This is what causes the frustration because this is what people expect to be able to do. Well, it can be done but you have to work backwards and it's not a one-step process. What would we do if we had a range of cells on a worksheet that we wanted to protect and we were quite happy that all the other cells would be open for entry?

We would proceed like this:

Select all the worksheet cells and unlock them.
Select the cells you wanted to protect and lock them.
Protect the worksheet.

Quite logical isn't it? Not. But it works. Now, not only is the general method of implementing cell locking and worksheet protection somewhat convoluted but some of the controls in the user interface seem to be designed to promote maximum confusion. Everything we need is in the Protection section of the Format menu.

The process of protecting and unprotecting worksheets is entirely straightforward as the menu has a toggle control which switches between Protect Sheet and Unprotect Sheet. That is a good example of how to design a toggle, the menu caption changes so that you can easily see whether your sheet is protected or not.

However, the process of locking and unlocking cells is not so obvious. Again, we have a toggle control to switch between the two different states but, sadly, the design is not too good as the menu caption does not change at all. Whether you are locking or unlocking your cells the menu caption always reads as Lock Cell.

I am a little disappointed in this as changing a menu caption is the simplest thing to do but by failing to do it the Excel designers have managed to bamboozle and confound countless innocent victims. It's a crying shame. The golden background to the padlock icon is the visual clue—if the padlock has a golden background then the cell is locked and if you click the control you are unlocking the cell.

Now you're going to tell me that the golden background of the icon is not always very distinct against the golden background of the active selection. And I am going to agree with you because the menu caption should really switch between Lock Cell and Unlock Cell just as the sheet protection setting does. But it doesn't. Sorry. I hope that this all made sense.

Selecting and Protecting Formulas

If you want to protect the formulas in your worksheet then you will need to be able to select your formulas easily without having to do it yourself. Click the Find & Select control on the extreme right hand side of the Home tab and choose Formulas from the menu.

Selecting formulas

All the formulas in the entire active worksheet are now selected. If you select a range first then only those cells containing formulas inside your current selection are selected.

Here is the step-by-step process for protecting all the formulas in the entire worksheet:

Select all the cells.
Unlock all the cells.
Select the formula cells.
Lock the formula cells.
Protect the worksheet.

It does seem rather a rigmarole but once you've done it a few times it's easy to remember. Each worksheet has to be done individually. You can always automate the entire process by recording a macro. Watch out for my future article where we shall record a macro to protect a single worksheet and then adapt it so that all of the worksheets in the workbook are protected.

If you want your documents to be secure then you should definitely consider entering a password when you protect your worksheet otherwise the worksheet protection can be easily turned off by anyone.

Here's the five steps in detail: