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!
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.
awesome tips on how to integrate both the protection and input cells. I am sure I can use it for my future accounting tasks!
I loved learning about how to highlight input cells! Very valuble. I will definitely be using this going forward.
Yes I agree I will definitely be using the highlight input cells style going forward!
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?
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
OK, thanks
Also the webinars were outstanding.
Thanks 🙂
I love that Excel has the ability to lock cells to prevent errors!
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.
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
Thanks Jeff. I will be using this more now.
Thank you. I plan to incorporate this into my workbooks.
Wish I had thought of input format and cell protection on a project a few years back. Would have saved me time and aggravation.
Very useful info.
Very helpful – thanks, Jeff.
good tips
Thanks 🙂
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.
I haven’t used cell styles much but plan on giving them a try.
Those are great tips for using cell styles!
I will definitely start using the cell input style. This is a great way to easily identify the cells
I never knew about the “input” format. Love it! That will really help users of the sheets to know where to type.
I will be using cell styles going forward
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!
Great idea to modify the input style so those cells are unlocked.
Great information
Automatically organizing using input style will be a huge help without trying to do it manually everytime!
This is super helpful! Thanks
Very nice!
Plenty of options!
Cell styles could save me a lot of time in formatting.
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.
How do you globally change the input cell style in a workbook – say you wanted to change it from peach to bright blue or yellow?