Categorization and Validation
Uploaded by
ScribdTranslationsCategorization and Validation
Uploaded by
ScribdTranslationsWhich spreadsheets should I validate?
Just like for computer systems, not all spreadsheets require validation, just
those that have some impact on BPx (good practices, where x can be manufacturing, distribution, laboratory and
etc.).
Logo, the best option is to draft a document to determine the need to validate or not a spreadsheet. With
the questions below, if any answer to the questions is 'YES', the electronic spreadsheet must be validated for having
impact on BPx.
1. The electronic spreadsheet generates, calculates, manages, and/or stores data or records that are regulated by
BPx?
The spreadsheet is used to generate, calculate, manage, and/or store data or records.
will they be submitted to a regulatory body or support a regulatory submission/registration?
The spreadsheet has a direct impact on the BPx processes or systems (e.g., installations, areas)
classified, calibration, maintenance), utilities (e.g., gas, water), production, process control,
cleaning, sterilization, quality control laboratory, materials system, handling and
storage, packaging, labeling or other regulatory activities?
4. The spreadsheet impacts/evaluates the identity, purity, characterization, sterilization, and/or status of
quality of a medication?
Where to start?
Well, just like computerized systems, according to GAMP, the electronic spreadsheet can be classified in the
categories below:
Category 1. The product on which the application (spreadsheets, templates, etc.) is built must belong to
category 1. The categories for spreadsheets and other applications for the end user vary from category 3 to 5.
Category 3. A spreadsheet that simply uses its original functions (average, standard deviations,
simple arithmetic operations and/or relationships between cells) to perform calculations, there being no
configuration, only acting as a calculator. When the arithmetic functions are used
spreadsheet, the calculations must be fully explained. This includes verifying that the formula used
was used appropriately and that the results obtained are correct. Such verification can be
documented through the review and approval of the spreadsheet by a second person. It is not necessary to
additional verification, since it is not necessary to challenge the accuracy of the calculations.
Category 4. A dotpo template spreadsheet, with more complex functions in which the user inputs
a piece of data that is automatically sent to another cell where specific calculations are performed, since
that the model is configured by the user before its use. There are also spreadsheets with functions
statistics, custom functions, and relationships with other tables.
Category 5. A spreadsheet that uses customized macros or other more sophisticated operations such as
source code editing of the spreadsheet, via VBA.
According to GAMP, some basic items must be considered for validation:
1. The calculations must be verified;
Will the model spreadsheet be on a single desktop or will it be available via a link?
3. Ensure that users are using the correct and up-to-date version of the spreadsheet;
4. A version control must be established through a change control;
5. How will access to the data by users and developers be controlled? Ideally, the spreadsheet
have the fields that contain formulas locked;
6. How will the functionality be configured? All formulas, macros, VBA, etc. must be described for
the understanding at the moment of validation;
7. Will the entered data be stored or just printed?
8. If the spreadsheet contains data retention, an audit trail is required.
What documents should I prepare?
As in the validation of computerized systems, you can choose to develop a Validation Plan, Analysis of
Risk, a Functional Specification and the QI and QO test documents and the Final Validation Report.
The Functional Specification must contain at least the requirements below:
1. The spreadsheet must be password protected, and only the fields for data entry should be.
to be released;
2. Name and summer of spreadsheet review;
3. All cells in the spreadsheet must be locked (preferably differentiated by another color), except for the
fields where data is manually entered;
4. Date and time of printing;
5. Number of pages;
6. History (sheet emissions/revisions);
7. Descriptions of the formulas that will be used;
8. Fields for the author's and reviewer's signature, in case it is printed;
9. Audit trail activated if the data is saved.
In the Risk Analysis, at least the risks related to must be evaluated:
1. Cells with unblocked formulas;
2. Sheet protection;
3. Errors in the formulas;
4. Protection of the VBA code;
5. Access control;
6. Electronic or paper registration;
7. Backup and restore.
Testing documents must include at least:
1. If the results of all formulas are correct (they must be checked manually);
2. Verification of all cells that contain formulas and whether they are in accordance with the specified;
3. Version of the spreadsheet software;
4. Check if it meets all the requirements of the functional specification;
5. Check if the critical cells and the workbook are locked;
6. Check if the document is set to 'read-only'; if data needs to be saved, check if the trail
The audit is enabled.
After validation, the original spreadsheet must be archived in the controlled directory for use, with the fields of
calculation blocked, allowing only the inclusion of values in open fields. The password must be
responsibility of the spreadsheet administrator.
The spreadsheet is only valid if the printout contains the drive path of
available storage location in the footer.
Backup and Storage
The folders where the spreadsheets are stored after validation must be secure locations with access control.
users, and the backup of spreadsheets should be carried out according to the backup and restore procedure
in force at the company for the network drives.
Access to the usage drive must be restricted to the users in the area that use it.
Worksheet Protection
The work structure and all tabs must be password protected, preventing any changes.
Modification. The spreadsheet must be modified/saved for the last time, reflecting the validation date, which does not
It may be changed again until the next review, except if it is a spreadsheet that is necessary for
data inclusion.
