Advanced Excel Modeling - Styles and Protection
Post date: Jul 19, 2018 3:00:55 AM
For the professional modeler Excel's advanced features improve transparency and efficiency while reducing complexity and risk. They also provide greater flexibility, scalability and connectivity. These benefits grow in value as models become more complex and connected to other models, data sources and people.
In the previous section we discussed helping users get entries right with data validation. In this section we discuss helping users know where they can make entries and keeping them out of places they must not change
Lesson Objectives
At the end of this lesson you will be able to:
Explain what a Style is and how it can impact colors, fonts, number formats, and protection.
Explain how themes impact styles
Explain how cell locking and worksheet protection work together
Protect your model while allowing users to make data entries.
Stylish Protection
About Styles and Cell Locking
Formats and Colors
You may have noticed the color I've used in my examples. I use color to convey information while keeping the colors consistent to my brand pallet. Styles do more than just help make our models look more professional by harmonizing fonts, colors, and number formats. They facilitate protection as well.
These colors start with choosing a color theme from Excel's ribbon>Page Layout tab > Themes group > Colors dropdown. Once a color theme is created (if desired) and selected Excel's styles take on the theme's colors.
Some of Excel's styles provide cell colors. Some styles provide number formats. Some provide cell locking. Cell locking is central to Excel's protection scheme. Most styles have no impact on protection. Two do: Normal and Input.
Normal
Input
This is a spreadsheet's default style and it is locked.
This style was created specifically for user input and it is unlocked.
Cell Locking and Worksheet Protection
Cell locking has no effect until worksheet protection is turned on. To turn on protection use Excel's ribbon > Review tab > Protect group > Protect Sheet button.
Lab
Create a theme color that matches your company's color palette
Set the Input style's color to a distinct color of your liking.
Apply the Input style to all entry cells
Apply worksheet protection (no password) with only Select unlocked cells checked.
Test to make sure no one can change formulas while protection is turned on and at the same time users can make entries into input cells.
Assignments
Read Protect a worksheet
Complete the Lab
Complete the Quiz.
Quiz
An Excel Style can provide:
Number formats
Cell coloring
Cell locking
Any combination of the above at the same time
Excel styles can be combined:
TRUE
FALSE
Excel styles get their color from:
Color themes
Colors we specify
Both a. and b.
Cell locking takes effect:
Immediately
When worksheet protection is turned on
When workbook protection is turned on
Worksheet protection requires a password
TRUE
FALSE
Summary
Using Styles and worksheet protection is an easy way to guide users to where they can make changes and protect the integrity of our model.
When protection is turned on only those cells with the Input style applied (or with cell locking turned off using cell formatting) can be changed by the user. And when we turn on protection if we uncheck Select locked cells then users can only click into Input cells. Using the Input style on entry cells with protection does two helpful things for us:
It keeps users out of formula cells which we don't want them to change
It shows users where entries can be made
In my models I set the Input style to light gray with black letters and tell users that is where they can make changes.