Have you ever set up a spreadsheet only to have someone you’ve shared it with (or even you!) overwrite your carefully crafted formulas and formatting?
Accidents happen. The key to preventing this particular accident happening is to use a combination of worksheet protection and cell locking.
Most worksheets include cells whose content shouldn’t be changed (typically those cells contain formulas but they could contain other data) and cells whose content can be changed (non-calculated, manually-entered month-end sales revenue for example).
In the screenshot below, the cells with the light-green background contain values that need to be changed and the cells with the light-orange background need to be protected as they contain formulas.
Locking a cell prevents the contents of that cell from being changed. Turning off cell locking means the cell contents can be changed. If only it was that simple because unless you enable Sheet Protection, the locked/unlocked state of a cell is irrelevant. Confused? Hopefully by watching my short video you’ll be a little less confused.
Twice in the past few weeks the same question has come up on the Managing List Based Data course that I teach. “how do I prevent data from being edited but allow it to be sorted (or filtered)?”
Again something that should be simple. The “allow users to sort when Sheet Protection is enabled” checkbox doesn’t work. Well it does but only if you disable cell locking for the cells in the sort range – and that brings with it another set of problems!
So once you’ve got to grips with the basics of cell locking and worksheet protection, check out the “Bonus Tip” in the second half of the video – How to protect data but allow it to be sorted (BONUS TIP)(https://youtu.be/P4A4C1GTIDI?t=365)
Links to the video and individual sections below…
Worksheet Protection basics – explanation and demo