This week we've talked about locking down, versioning and controlling the distribution of an Excel template as the cornerstone of developing, testing, and validating spreadsheets for processing data in a regulated environment or in any critical application.

So what does it mean to "lock down" an Excel template? Password protect it? Mark it read-only? Hide it in a maze of similarly named directories? Today we're going to look at how to lock down functionality while enabling configuration and data entry.

Was this email forwarded to you?

Subscribe Here!

---

​

Before we start let's restate our goal here. We want to create a template where we restrict changes to the functionality, formatting, labels, etc. while allowing the user to enter data and set any parameters necessary for the data analysis.

1. Set cell-level locking

Before you can apply restrictions to what can be done to the cells in a worksheet, you've got to select which cells will be 'locked' and which will be 'unlocked' when the protections are applied.

In current versions of Excel, worksheet protection defaults to "locking" every cell on a worksheet - which is not what we want, since no-one would be able to enter any data. So we go through the workbook and choose all the data entry and configuration cells, and unlock them. Believe it or not this setting is kept with the cell’s formatting... I know, I know.

Make sure everything else is locked, including formulas, labels, and blank cells that aren’t meant to contain anything.

Now, setting whether a cell gets locked or unlocked doesn't actually do anything... you need to protect the worksheet first before anything is actually locked.

2. Protect each worksheet

Once the data entry and configuration areas have been unlocked, then you can protect each worksheet. This will present you with a checklist of options. Don’t just choose Excel’s defaults - go through the checklist and decide on exactly what you need the users to be able to do. There are consequences to each choice that is made here, so test out your use case and make sure the protected sheet behaves as expected!

​

You'll need to repeat this process for every worksheet in your template. If you're going to lock and unlock a bunch of worksheets in a big template it can get a little tedious - when I'm developing I often set up a little macro to go through and lock or unlock all the worksheets in one go.

3. Protect the workbook structure.

Finally, you should protect the workbook structure. This restricts the user from adding, deleting, un/hiding, or renaming worksheets. It also stops them from moving worksheets around, but we’re not so concerned about that.

​

Now we've got a template where most of the functionality of the worksheet is protected from accidental change.

4. Passwords and other protection options

Whether you want to use a password to protect the worksheet or structure depends on your use case and who its going out to - it’s probably a good idea just to dissuade anyone from ‘just making a quick fix here’. If you do add a password, do keep a record of it somewhere, and it might be a good idea to add an unprotected version to your backups just in case.

There’s also an option to encrypt and password protect the whole spreadsheet - but it you're looking for security you’re probably better off using the external infrastructure (e.g. shared drive, EDMS, etc.) to apply access restrictions and role-based permissions to the file itself.

Finally, there's an alternative method of locking down cells in Excel: the “Allow Edit Ranges” feature. This let's you define Windows group rights for who gets to edit which ranges, but only works on Windows computers that exist on an enterprise domain. And while it lets you name ranges, it doesn't offer up previously named ranges to protect... which doesn't make much sense to me.

You could use this feature to enable specific users to edit the template (or without the domain feature just set up passwords for specific ranges), but I think it’s of limited use for data entry instances. Unless of course you already have domain-level groups specified for e.g. operators, QC and QA roles... anyway it's there if you want to get creative!

​

And that's about it for today. If you need help figuring any of this out, well, there are so many Excel tutorials online you should be able to find some in-depth exploration of each of the options and how they interact. Or if you want to see a video of me walking through these activities using a relevant example in Excel, let me know!

Until next time, thanks for reading!

– Brendan

p.s. Enjoy this message? Read more at the Hyland Quality Systems website.