The Prevent Defense in Excel

Sift Media
Share this content

Invariably, I will create and distribute an Excel template only to fall victim to the dreaded formula overwrite offense. It's frustrating when a vacation schedule is returned that doesn't foot, or an item is coded incorrectly in an expense report after significant time has been taken to try and make a template idiot proof. Then some "idiot" spoils the whole thing by hard-coding a number over a formula.

Use the "Protect Sheet" option to help ensure that unwanted changes are not made into your Excel templates.

To illustrate, I have downloaded a template from Microsoft Online. It has a Description in column A, two input columns (Budget in column B and Actual in column C), and two calculated fields (Difference ($) in column D and Difference (%) in column E).

I would like to protect this worksheet so that entries can only be made in the Budget and Actual columns as shown in the highlighted area below.

About admin


Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.