Overview

Starting with Excel 2007, Microsoft enhanced the Excel Cell Styles feature providing great built-in choices and flexibility. The purpose of this post is to briefly discuss the styles feature and demonstrate how it can be used in conjunction with worksheet protection.

Cell Styles

Think of a cell style as a comprehensive set of cell formats, which may include for example the fill color and borders.  Once the style is defined, you can apply the style to the cells, rather than format the cells directly. The beauty of using a style is that the definition of the style is centrally stored in the workbook. When you update the style definition the changes automatically flow out to all cells that use that style.

This is in sharp contrast to the typical way users format cells, which is to apply formatting to the cells directly.  The problem with the traditional approach is when you change your mind and want to update the formatting.  Let’s say you use a yellow fill throughout the workbook to identify user input cells.  If you change your mind about a given format, and want to change the fill from yellow to gray, then you would need to go through each worksheet in the workbook and update all of the individual cells. Depending on the number of sheets and cells involved, this could take a long time.  If, on the other hand, you applied the same cell style to all of those cells, and then changed your mind, you would simply modify the style definition. All cells that use that style would immediately be updated.

Microsoft provides many built-in styles, and you can also create your own, or modify the built-in styles. The built-in style I use most often is the Input cell style, which is sort of a peach color. I use this to highlight all of the input cells in the workbook. This makes it fast and easy for the user of the workbook to know exactly which cells require manual input. This promotes efficiency and reduces errors in the workbook over time.  How so? Well, the user doesn’t need to spend time reviewing all of the cells, trying to determine if they are formula cells or static cells or input cells. Highlighting input cells can also reduce errors in the workbook because it helps minimize the risk of a user accidentally typing over a formula. Of course, this risk could be further minimized with the use of worksheet protection. So, let’s chat about that now.

Worksheet Protection

Worksheet protection protects the cells within a worksheet. Once turned on, the locked attribute of all cells is enforced. What’s that? Yes, once worksheet protection is enabled, the locked attribute is enforced. Let’s unpack that a bit.

You know how you can lock or unlock a cell? There are of course several ways to do this, but for now, let me throw up a screenshot of the Format Cells dialog box, which is activated by going to the Format > Format Cells ribbon command.

There you can see the Locked checkbox on the Protection tab. This tells Excel whether or not to lock the cell.  By default, all cells in all sheets in all books are locked.  What!  Yes, by default, all cells, in all worksheets, in all workbooks are locked. However, the locked attribute is not enforced until worksheet protection is applied. See?

It is pretty easy to turn on worksheet protection, there are several ways, one of which is to click on the Review > Protect Sheet ribbon command.

Once you turn on worksheet protection, the locked attribute is enforced. A locked cell is unable to be edited by the user.

So, now for the big finish, and how this ties back to cell styles.

Styles and Protection

One common task is to unlock certain cells, namely input cells, and then turn on worksheet protection. This results in a worksheet that the user can enter data into, specifically into the unlocked input cells.  The user can’t mess up the formulas or other static values, because they reside in locked cells. Often, I’m asked if there is an automatic way to highlight the unlocked cells.  While there is some conditional formatting that could accomplish this, it is overkill.

In my opinion, the best approach is to highlight the input cells with the Input cell style. By default, the Input cell style does not unlock the cell, in fact, it doesn’t change the locked attribute at all. However, with a quick and easy step, we can modify the Input cell style definition so that when the style is applied it unlocks the cell at the same time. This is perfect, since you highlight the input cells using the Input cell style, and the cells get unlocked at the same time. Then, when you apply worksheet protection, the input cells are automatically unlocked allowing a user to make changes.

To modify the built-in Input cell style so that the locked attribute is applied, simply right-click the Input cell style icon from the Home > Cell Styles ribbon and select Modify… from the shortcut menu.  You’ll be presented with the Style dialog box shown below.

You’ll notice that, by default, the Protection box is unchecked. This means that the style will not override the existing cell protection status. It just leaves it alone. In our use, however, we want to define the protection status (unlocked) and apply it to the cell when we apply the style. Thus, we check the Protection check box and then click the Format button.  On the resulting Format Cells dialog box, we uncheck the Locked checkbox, as shown below

Then, we’ll return to the Style dialog, with the Protection box checked, as shown below.

With the built-in Input cell style now modified, once it is applied to input cells, they will be unlocked and highlighted. And, once we turn on worksheet protection, the cells that use the Input cell style will be identified and unlocked with no extra steps needed.

This is one of my favorite uses of cell styles, and I love how they can integrate so easily with worksheet protection.  Thanks Microsoft!