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

This is under construction.

Click this link for the course's start

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

1. Create a theme color that matches your company's color palette
2. Set the Input style's color to a distinct color of your liking.
3. Apply the Input style to all entry cells
4. Apply worksheet protection (no password) with only Select unlocked cells checked.
5. 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

1. Read Change a theme and make it the default in Excel
2. Read Apply, create, or remove a cell style
3. Read Protect a worksheet
4. Complete the Lab
5. Complete the Quiz.

Quiz

1. An Excel Style can provide:
  1. Number formats
  2. Cell coloring
  3. Cell locking
  4. Any combination of the above at the same time
2. Excel styles can be combined:

- 1. TRUE
  2. FALSE

1. Excel styles get their color from:

- 1. Color themes
  2. Colors we specify
  3. Both a. and b.

1. Cell locking takes effect:

- 1. Immediately
  2. When worksheet protection is turned on
  3. When workbook protection is turned on

1. Worksheet protection requires a password

- 1. TRUE
  2. 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.

Entry Validation

Introduction

Self-Documenting Formulas