.jpg){kind=link}
- Published
- Updated
Locking formula cells in a clinical tracking spreadsheet can be useful. But a protected formula that references the wrong range or omits new rows can still produce an unreliable result that appears trustworthy to its users.
Preserve the meaning of data
Define which decisions the spreadsheet supports and which cells supply calculation inputs. Formula correctness, protection against accidental editing and version management are separate review questions. Test whether a newly added record enters the calculation. For critical outputs, an independent worked calculation provides more informative evidence than merely observing that cells are locked.
Worked example
For example, a site tracker excludes its last five rows from a total. Because the file is protected, users assume the number has been verified. The team compares it with a known expected total, corrects row-insertion behaviour and reviews affected decisions according to their significance. File protection alone would not have detected the calculation error.
Review checks
- Test added rows, deleted rows and blank input examples.
- Recalculate a critical output independently using a small worked example.
- Compare locked and editable cells with actual user tasks.
Acceptance and boundaries
Acceptance means real inputs enter the intended calculation correctly and unauthorised changes are controlled. Not every spreadsheet needs the same verification scope; choose an approach proportionate to its effect on study decisions. When copies multiply, recognising the current version also matters. If users routinely need to remove protection, address that requirement in the design.
Frequently asked questions
- Does a password prove the formula is correct?
- No. Protection can reduce accidental editing but does not establish the correctness of calculation logic.
- Should every cell be locked?
- Separate inputs from calculations. Protection that prevents legitimate work can create additional errors.