Excel Cell Styles and Worksheet Protection

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!

Posted in

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

32 Comments

  1. amy zhang on May 9, 2013 at 8:01 pm

    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 on December 29, 2014 at 1:13 pm

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

    • Cathy Allard-Climie on January 27, 2018 at 10:45 am

      Yes I agree I will definitely be using the highlight input cells style going forward!

  3. Tom J on January 2, 2016 at 5:40 am

    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?

    • jefflenning on January 2, 2016 at 9:08 am

      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.
      Thanks
      Jeff

      • Tom J on January 2, 2016 at 9:32 am

        OK, thanks

        Also the webinars were outstanding.

        • jefflenning on January 2, 2016 at 9:33 am

          Thanks 🙂

  4. Monique` O'Dell on February 4, 2016 at 8:19 pm

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

  5. Cheryl on April 13, 2016 at 7:56 am

    Jeff,
    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.

    • jefflenning on April 14, 2016 at 5:39 am

      Cheryl,
      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!
      Thanks
      Jeff

  6. Marco on May 24, 2016 at 1:24 pm

    Thanks Jeff. I will be using this more now.

  7. Lee Ann Webster on May 30, 2016 at 3:08 pm

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

  8. harley sherman on June 21, 2016 at 8:01 am

    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 on July 20, 2016 at 11:02 am

    Very useful info.

  10. Karen on July 30, 2016 at 9:18 am

    Very helpful – thanks, Jeff.

  11. Nick Grgas on December 2, 2016 at 6:29 am

    good tips

    • jefflenning on December 2, 2016 at 6:30 am

      Thanks 🙂

  12. Linda on February 18, 2017 at 9:01 pm

    I haven’t played around with cell styles very much but I use many of the shortcuts on the style bar like the comma placement, the currency no decimals. The shortcuts are great. I am glad to learn how the Input box style is used. It would be useful to share with the department so that we are consistent as a group when we create worksheets. Thus it will be easy for the next employee to follow as well.

  13. Donna Kinsman on April 28, 2017 at 9:36 am

    I haven’t used cell styles much but plan on giving them a try.

  14. Patrick Westrick on August 31, 2017 at 8:25 am

    Those are great tips for using cell styles!

  15. Kimberly Hake on September 18, 2017 at 10:51 am

    I will definitely start using the cell input style. This is a great way to easily identify the cells

  16. Jeremy Sparling on September 20, 2017 at 7:57 am

    I never knew about the “input” format. Love it! That will really help users of the sheets to know where to type.

  17. Gerard Cannito on January 12, 2018 at 4:24 pm

    I will be using cell styles going forward

  18. Stanley Runyon on May 10, 2018 at 4:00 pm

    I have been locking cells and protecting worksheets for quite some time. I just discovered hiding formulae. I lock the formula cells and format them “accounting green” to keep users from accidentally overwriting my formulae. I didn’t know about automating this with styles. This will save me tons of steps…I owe you a coffee or a beer when we eventually meet. Thanks!

  19. Carrie on May 15, 2018 at 2:01 pm

    Great idea to modify the input style so those cells are unlocked.

  20. Dorothy on May 21, 2018 at 12:10 pm

    Great information

  21. Sarah Moon on June 21, 2018 at 6:21 pm

    Automatically organizing using input style will be a huge help without trying to do it manually everytime!

  22. Samantha Hansen on September 11, 2018 at 12:53 pm

    This is super helpful! Thanks

  23. Son Nguyen on September 4, 2019 at 2:15 pm

    Very nice!

  24. Mike Clark on November 6, 2019 at 9:44 pm

    Plenty of options!

  25. Andrew Shockling on February 14, 2021 at 11:05 pm

    Cell styles could save me a lot of time in formatting.

  26. Pamela Beaule on August 23, 2022 at 6:34 am

    Is there a way to protect a worksheet, but allow the user to do a Data Refresh using Power Query? I have users that keep adding or data in the wrong worksheet and thus losing their information when the next refresh occurs.

Leave a Comment