Spreadsheet Validation Stages

 

Data Integrity App

Documentation of the spreadsheet

There should be a general description of the spreadsheet explaining its purpose, general layout, input types and data validation rules if required (some spreadsheet might be self-explaining).

This description can be documented in the spreadsheet itself (e.g. in a dedicated sheet), in a SOP or in the validation file.

 

Next to the general description, a full print-out of the spreadsheet where all formulas are shown (Formulas tab > Show Formulas) should be kept in the validation file.

When VBA 1 macros are used, the VBA code should also be printed and kept in the validation file.

If matrix-formulas (array-formulas) are used, this must be indicated. An individual printout of each matrix formula is necessary

All print-outs shall clearly identify the spreadsheet name or identification and version number. When a new version of the spreadsheet is being validated, a summary of the changes since the previous version should be given.

The version of Microsoft Excel used for the creation and validation of the spreadsheet should be traceable (either by the documentation of the spreadsheet or by the change log of the IT department), and any known incompatibilities with older or newer versions should be documented.

 

The documentation of the spreadsheet can be considered as the URS.

In order to properly document the spreadsheet, formulas shall be printed and entered into the validation document (see example below).

Source; Validation of Computerised Systems Annex 1 – Validation of Excel Spreadsheets PA/PH/OMCL (08) 87 R6

 

Validation of the calculations of the spreadsheet

All calculations are to be verified with a system completely independent from the self-developed spreadsheet.

One validation method is to compare the results obtained by the spreadsheet with results obtained by commercial software or with a calculator, using the same dataset as input.

Another validation method is to compare the results obtained by the spreadsheet with published reference data (e.g. physicochemical data of substances).

If the spreadsheet will be used on computers running different versions of Excel it is required to perform the validation of the functionality using each of those different versions as some newer Excel functions are not retro-compatible with older versions of Excel.

Source; Validation of Computerised Systems Annex 1 – Validation of Excel Spreadsheets PA/PH/OMCL (08) 87 R6

 

Validation of the calculations by using commercial software or published data

A dataset as close to real values as possible must be chosen. Excel calculations are compared to the results given by commercial software or by published data, which are considered as validated (see example in the image below).

The commercial software provides the coefficient of correlation, R 2 and the coefficients of the calibration curve.

If no discrepancy occurs, the validation of this part of the calculation is considered as fulfilled. If a discrepancy is observed, a check and revision of the formulas must be performed (and the whole validation re-performed).

Source; Validation of Computerised Systems Annex 1 – Validation of Excel Spreadsheets PA/PH/OMCL (08) 87 R6

 

 Validation of the calculations with a calculator (manual calculation)

Using the printed formulas from the spreadsheet, all concentrations are calculated using a calculator (see next image) and compared with the results given by the spreadsheet.

As an alternative, the PC calculator can be used and documented.

If no discrepancy occurs, the validation of this part of the calculation is considered fulfilled. If a discrepancy is observed, both the revision of the formulas and the manual calculations should be repeated (and the whole validation re-performed).

Moreover, calculations in paragraph 4.2.1 and 4.2.2 should be re-performed with other datasets including exceptional situations, for example: OOS results, missing data, or nonsense data.

Calculations should also be validated under these conditions, as applicable (data not shown).

Source; Validation of Computerised Systems Annex 1 – Validation of Excel Spreadsheets PA/PH/OMCL (08) 87 R6

 

Validation of the protections

The following points shall be verified and documented:

-  Access rights to the spreadsheet (e.g. on the network share) are correct: the file cannot be modified or deleted by users.

-  The different sheets within the spreadsheet are properly protected: only input cells can be edited, all other cells are locked.

-  A password (if applicable) is needed to remove sheet protection and workbook protection.

At this stage, the spreadsheet is considered as validated and its status is issued and filed.

Source; Validation of Computerised Systems Annex 1 – Validation of Excel Spreadsheets PA/PH/OMCL (08) 87 R6

“Trust but Verify “ Ronald Reagan

 

Across the internet, there are millions of resources are available which provide information about Everything.

 

If you found all content under one roof then it will save your time, effort & you will more concentrated on your important activity.

Data Integrity App

 

Our Data integrity app will helpful for understanding what Data integrity & CSV really means & How 21 CFR Part 11, EU Annex 11 & other regulatory guidelines affects in pharmaceutical Industry.

 

Data Integrity App Include 

- Basic Data Integrity Concepts

- ERES & Its Requirement

- CSV & Its best practices 

- Mock Inspection and General Q&A

- Checklist for inspection

- Inspection Readiness

- Useful SOP’s

- Stay Regulatory Compliant.

 

“Stay One Step Ahead in Pharma IT Compliance” 

Data Integrity App Link:

https://play.google.com/store/apps/details?id=com.innovativeapps.dataintegrity

 

Try our "Data Integrity" app which helps you to better understand current regulatory agencies thinking on Data Integrity & CSV.