Graphics To Protect Spreadsheet Cells

Q. I share several of my spreadsheets with colleagues. Each periodically enters data into various worksheets. Every now and then one of them accidentally enters data in the wrong cell, or, worse, in a misguided effort to improve one of my formulas, corrupts the entire worksheet. Do you have any ideas for protecting shared spreadsheets?

A. You’ve touched on a common and delicate issue because every office has someone who has just enough knowledge about computers to be dangerous to both himself and the office.

I’ve invited Justin D. Stein, a CPA with Arthur Andersen, to address this question because he frequently deals with it and he wrote an article that contains part of the solution ( “Yes, You Can Program in VBA,” JofA , Nov.00, page 47 ).

Stein advises: We provide visual alerts to warn users when a cell in a worksheet or template is special and thus needs exceptional handling and protection. This includes cells that contain formulas, those that allow inputs (be they amortization tables or interest calculations) and those with any sort of problem that will require special treatments. At my firm, we’ve designed graphics for those cells that serve as visual alerts. The goal is to create visuals that are instantly understood by users without additional guidance.

The best way to design the graphics is with Visual Basic for Applications (VBA)—a process that’s described in my JofA article.

One design identifies a cell as an input cell—that is, users are invited to enter new data into it. We format the cell with enough color contrast to catch the users’ eyes. For instance, we’ll use a light background and a dark font (such as a very light yellow with a blue font). A typical input cell looks like this:

In addition, we unlock the input cell so users can change the value of cells formatted with this standard even though the worksheet may be protected. To unlock a cell, right-click on it and then click on Format Cells, which brings up the screen below.

I’ll describe the locking process later.

We use another design, also with a lot of contrast, to identify a cell as containing a formula—so don’t touch it. A typical formula cell looks like this:

This color design, by the way, is used by Conditional Formatting to warn users when two cells aren’t equal.

The design for our calculation cells usually is formatted simply as black text on a white background:

However, we protect these cells so they can’t be modified.

The design choice is up to you. There are no universally accepted designs.

After settling on formats for each type of cell you want to identify (for example, input, calculation, formula ), create VBA routines that apply those formats to the cells of your choice. Assign each routine to a separate toolbar button. All these steps are described in my article.

Many times when designing complex models and what-if scenarios, you may find you need a separate worksheet in which you can maintain information you don’t necessarily want users to see.

For instance, if you are creating a file to calculate individual estimated income taxes, you will need a series of tables to calculate the tax based on taxable income and filing status. You need to maintain these tables within your file, but you don’t want users to change them. One way to accomplish this is to maintain the tables on a separate worksheet and, when you’re ready to release the file to users, hide it so they won’t be able to see it—although, of course, the software can “see” the tables and thus use them for calculations. You can hide worksheets by selecting the worksheet and clicking on Format, Sheet, Hide . It’s a good idea to protect all the cells on this type of worksheet since users can easily unhide this worksheet by choosing the hidden sheet from Format, Sheet, Unhide .

Once you’ve formatted your worksheets using these techniques, it’s time to protect them. Click on Tools, Protection, Protect Sheet and be sure that all check boxes are selected.

If you use a password to protect a worksheet, be sure you save it somewhere safe but accessible to your coworkers.

When a worksheet is protected, users can modify only cells that are specifically marked as not locked. You can unlock a cell by clearing the Locked check box in the Protection tab of the Format Cells dialog box. This means that users can’t delete, change, paste or otherwise corrupt any of those cells. A cell is locked only when the worksheet on which it is situated is locked. If a cell is locked but the worksheet isn’t, users may still modify its data.