Seven steps to (almost) protected Calculated Columns

Earlier I complained about how you can’t lock down Calculated Columns in Tables, while still allowing users to insert new rows. Dick gave me half a hack in the comments of that post, and I’ve come up with the other half to give you an almost-foolproof way to protect those precious Calculated Columns from your almost fools.

Step 1: Unprotect the entire rows that your Table occupies.

Step 2: Park a shape over the Calculated Column. Leave it’s protection as the default ‘Locked’. This will act as a shield once the sheet is protected.

Step 3: Format the shape so that it has no border, and a white fill that is 99% transparent. (If it’s 100% transparent, then Excel will let users click right through it, to the cells behind).

Step 4: Add some DV that stops users from changing the formula in the column, should they navigate around the shape shield via using the arrow keys. Dick’s formula below ensures that someone will have to spend an awful long time typing before they are allowed to overwrite the existing formula.

Step 5: Add a pop-up input message that politely warns them not to mess with your calculated column, should they manage to reach it using the arrow keys. This is still needed, because while they can’t change the formula, they can still clear it using the Delete key.

Step 6: Protect your worksheet in a manner that allows row insertions/deletions.

Step 7: Go vote for Zach Barresse’s UserVoice request for Microsoft to fix Tables so that you don’t have to use 6 hacks to work around their “feature”.

This works a treat: Try as they might, they can’t select the unprotected cells in the Calculated Column by clicking on them. If they click in the bordering cells and use the arrow keys to navigate behind the shield, they get warned off politely:

…and yet they can still insert new rows:

…and as you can see, the unprotected formula in the calculated column copies down just fine.

3 thoughts on “Seven steps to (almost) protected Calculated Columns

  1. Your solution made me giggle. Smart way of making sure that nobody messes around with the formulas :)

  2. Reads well, and I cannot make it work. Using Excel 2013. As soon as I unprotect the rows as in Step 1 all the drop-down options grey out and the sheet is effectively locked from any changes. Reversing steps (Ctrl Z) stops functioning.

    Step 2 is inoperable at this stage. It is not possible to “park a shape”.

    Perhaps I’m a fool, but please clarify the bit that is missing aroun Steps 1 and 2.

  3. I can’t reproduce that in Excel 2019. It seems odd that unprotecting rows would lock down a sheet though. Have you tried it on another machine? With no add-ins loaded? Those would be the first two things I would try.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.