Excel Cell Styles and Worksheet Protection

Cell styles and worksheets


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.

Format Cells Dialog Box


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!


This article was written by jefflenning


  1. amy zhang

    awesome tips on how to integrate both the protection and input cells. I am sure I can use it for my future accounting tasks!

  2. Hannah Wiser

    I loved learning about how to highlight input cells! Very valuble. I will definitely be using this going forward.

  3. Tom J

    Great idea, but the modification to the Input cell style does not save. When I use the same style in any other workbook I have to go through the steps to modify the style again. What am I missing?

    1. jefflenning Post author

      Style customizations are stored in each workbook rather than as a global Excel setting. A couple workarounds include setting up a macro or use the style merge to import an existing style into a new workbook.

      1. Tom J

        OK, thanks

        Also the webinars were outstanding.

        1. jefflenning Post author

          Thanks :-)

  4. Monique` O'Dell

    I love that Excel has the ability to lock cells to prevent errors!

  5. Cheryl

    One computer makes entry of numbers in Excel2010 frustrating, in that we have to enter a decimal after the number. ie 8, 80, 800 are all the same after entering unless we put 80. or 800.
    The same file opened on other computers do not require the decimal when entering numbers. Is there something I can do to fix this? it wasn’t there before. I think it might be in the cell style but cannot see it easily.

    1. jefflenning Post author

      It sounds like that computer has the “Automatically insert a decimal point” Excel option turned on. To turn it off, open Excel Options, click Advanced, and then you can uncheck the box. Hope this helps!

  6. Marco

    Thanks Jeff. I will be using this more now.

  7. Lee Ann Webster

    Thank you. I plan to incorporate this into my workbooks.

  8. harley sherman

    Wish I had thought of input format and cell protection on a project a few years back. Would have saved me time and aggravation.

  9. Maryann Losier

    Very useful info.

  10. Karen

    Very helpful – thanks, Jeff.

Leave a Reply

Your email address will not be published. Required fields are marked *

By submitting this form, you accept the Mollom privacy policy.