Excel spreadsheets are an indispensable part of a validation professional’s repertoire, and it is essential that they be validated like any other piece of equipment. On June 24 at IVT’s Statistics in Validation Conference, Raul Soto, M.Sc. gave a talk entitled “MS Excel’s Statistical Capabilities,” and he made the comparison that Batman’s tool belt is to crime-fighting as Excel is to validation. With Mr. Soto’s presentation and ambitious analogy in mind, your editors were inspired to go over the 3 Faces of Spreadsheet Validation.
First, lock all cells that are not input cells. This ensures formulae, column headers, data order, etc. do not become corrupted. It’s so easy to have a stray keystroke render a spreadsheet unusable, and it is conversely as difficult to trace accidental keystrokes to repair the mistake. Don’t let it happen—lock your cells.
This may seem to go without saying, but well-meaning tinkering has compromised too many spreadsheets. Prevent this by making all spreadsheets read-only and password protected. Another good idea is to record all cell formulae separately so that they can be checked manually and are available if they become corrupt.
Don’t allow users to add or remove worksheets. At best they are introducing extraneous and confusing data. At worst they are corrupting the functioning of your spreadsheet.
Above all else: Make sure validation documentation is clear. Too many spreadsheets become un-validatable because the trail of documented changes is untraceable. Spreadsheet validation procedures should always include outlines of how the spreadsheet is to be validated, what documents need to be produced, and the roles and responsibilities of all who will touch the spreadsheet. Another essential document, the change control procedure may be a separate procedure document or part of the main spreadsheets validation procedure.
Documenting all changes made to a spreadsheet is of, course, a rudimentary part of change control; however, keep track of each iteration with separate version numbers. This ensures that each version is easily referenced and tracked in future documentation and correspondence. In the same vein, never delete older spreadsheet versions. They will be useful when tracking changes, mistakes, etc. and will be essential when showing changes to regulatory authorities.
To make sure no corruptions have crept into your spreadsheets, always revalidate them. This includes things as simple as checking cell formulae against the saved-separately originals and manually verifying them in a full, new validation process.
Last, maintain an inventory of validated spreadsheets. You should know which validated spreadsheets are being used, where they are being used, and for what. Your inventory document should contain:
- Spreadsheet name and number
- Current version
- Area(s) where used
Testing is an on-going process in spreadsheet validation, and Excel has many tools built in to ensure your spreadsheets and data are validated—use them. For instance, make use of Excel’s data validation tool to automatically reject data that are outside valid ranges. This will easily alert you of any anomalies. When testing a spreadsheet, it is essential both negative tests and positive test are run for thorough validation.
Positive testing ensures all formula produce correct results for various input data sets. Use at least two sets, one that is known to produce a pass decision and one that is know to induce a fail decision. Also include borderline data sets to ensure your formulae’s precision. Last verify your spreadsheet’s results against manual calculations.
Negative testing uses incorrect inputs known to give bad answers. This ensures the spreadsheet is a robust, active document that is able to accommodate incorrect inputs without crashing, producing false outputs, or even bypassing security altogether!
Examples of negative testing include:
- Leaving an input field blank to ensure input fields cannot be left blank.
- Entering alphabetic or special characters onto a numeric field
- Entering a decimal onto an integer field
- Entering negative numbers into a positive numbers field
- Entering nonsense
- Entering more characters into a field than are allowed
- Entering a date outside the upper and lower bounds for dates
Deliverables for spreadsheet validation can, and should, be adapted to a project’s circumstances, but typical deliverables include:
- A validation plan
- Requirements Specifications
- Design Specifications
- A Traceability matrix
- A validation report
- Procedures and governance
In closing, validation of an Excel spreadsheet should be treated no differently than any other system, but special care must be taken for spreadsheets and their peculiarities.
Get our latest updates directly to your inbox by subscribing to our newsletter.