Spreadsheet Validation

  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...

  Data Integrity App Good Practices for Spreadsheet When setting up a new spreadsheet, following the good practices below will reduce the risk of accidental modifications of the template and erroneous data input: -  All calculating cells shall be locked (Format Cells > Protection > Locked) in order to protect cells containing calculations against unintended modification, except those used for data input. - Cells used for data input can be identified by a specific colour. -  Data validation rules (Data tab > Data Validation) can be applied to data input cells to prevent the introduction of aberrant values. Input messages and Error alert messages can be used to inform the end user of the expected data type and acceptable range. Cells used for presenting the results of the calculations (output) can be identified by a specific color. When the results are tested against acceptance criteria it is recommended using conditional formatting (Home tab > Conditi...

Data Integrity App   Security of Spread sheet Only the latest validated version of the spreadsheet is being used and to maintain the validated state of the spreadsheet, All validated Excel spreadsheets should be stored with read-only access rights for the end users (e.g., on a protected network share). Only responsible persons should have write access to the network share. End users should have no right to modify a validated spreadsheet, add a non-validated spreadsheet to the share, or save data on the share. End users should only have the right to fill in the (permitted) cells and to print the data or save a copy to a data repository if needed. Installation shall be documented, e.g. in the validation file, in a system log book or on a QA form. The name of the spreadsheet, unique identification, localization, and the person responsible for the spreadsheet shall be documented. The records shall also include verification, regular verification and other issues such as ...

Data Integrity App   Spreadsheet review policy Review the following procedures and records. 1.         Create a spreadsheet inventory. 2.        Identify critical spreadsheets for review. 3.        Rank each spreadsheet's risk level. 4.        Develop a baseline for each spreadsheet 5.        Evaluate policies and procedures for spreadsheet use. 6.        Evaluate controls that protect spreadsheet integrity. 7.        Evaluate spreadsheet validation practices and procedures. “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 imp...

  Data Integrity App FDA Spreadsheet Comments . ·          Nearly all of the spreadsheets in the laboratory showed rounding-off errors and none used the Excel rounding function, when needed to avoid rounding-off discrepancies between the original and check calculations by the second analyst. ·          Formulas in the analytical procedures did not follow the Excel equations. ·          Conversion factors were not expressed in the analytical procedures. ·          The formulas used by the check analyst for manual calculations were not the same as those used in the Excel spreadsheet validation. ·          The pre-determined specifications or limits were not shown in the spreadsheets. ·          Spreadsheets were not clearly documented. ...

Data Integrity App   Technical Controls of spreadsheet In order to meet specific regulations, for example audit trails within 21 CFR Part 11 [5], it may be necessary to add additional technical controls to Excel. These controls can normally be added in one of two ways. • Custom developed macros and functionality provided either on a spreadsheet by spreadsheet basis, or as a generic module which is added in the same format to multiple spreadsheets. These customized developments will generally be classed as GAMP category 5 software [8], and as such merit a rigorous validation effort due to the risks associated with customized application and coding. • Third party software packages that add additional technical controls to the Excel environment. These off the shelf software packages will generally be classed as GAMP category 3 / 4 software8, and as such require specific validation. When either of these “add-on” options are implemented, the project should be planned such th...