EXCEL SHEET VALIDATION
The objective of this procedure is to establish the guideline for the validation of Microsoft Excel spreadsheets use for calculating the product quality parameters.
This procedure covers the validation procedure for validation of Microsoft Excel spreadsheets use for calculating the product quality parameters in QA.
Procedure
Spreadsheets used for simple calculations (e.g: % RSD, Average, Sum, etc.) need not be stored electronically and version control is not applicable to these spreadsheets.
Index of validated excel sheets in use should be maintained and updated from time to time.
Preparation of Excel spreadsheets
The template owner to design a template fulfilling the intended use
Data entry cells should be identified using “colour shading”.
After completion, the template owner shall calculate the results using the new excel spread sheet and compare the results obtained with those obtained by calculation.
Correctness for each calculation cell in the open template should be validated.
Template owner to submit soft copies of Excel spreadsheet to Manager-QA along with hard copies of all manual calculations demonstrating the correctness of results.
Manager-QA or designated person shall verify all the formulae in the excel sheet and if found correct shall protect all the cells except those required for data entry.
Manager-QA or designated person shall protect the sheet using the password to disallow any changes to the formulae or any entry in the fields except those required for data entry.
In case of any suggestions or/ and corrections to be made, Manager-QA /Supervisor to communicate with the spreadsheet owner. After rectification, Manager-QA / Supervisor to carry out re-verification.
When the excel spreadsheet is found satisfactory, the file shall be saved in the Master excel sheet folder with password protection.
Manager-QA / Supervisor to confirm that the template validation procedure calculates all test data correctly. This must get demonstrated by direct comparison of template calculated results with the results obtained for the same data using a second independent procedure (Viz. hand calculator).
Manager-QA to approve the template, calculation review sheet, and template validation report.
Version control
Template owner to allocate a unique identification number
A typical template identification number should be as follows
EXCEL/XX/YYY
Where,
EXCEL indicates the excel spreadsheet
XX represents the departmental code
YYY represents the serial number from 001
For instance, the file name for the first version of the first excel template should be “EXCEL/QA/001.XLS”.
When the new version of the validated spreadsheet comes into existence, the earlier version spreadsheet should be deleted.
Control and Archival
Manager-QA / Supervisor to follow the following steps during saving of spreadsheet as a template.
All cells of a template, except data entry cells must be “Locked” (Protected) to prevent unauthorized changes and invalidated formulas.
Each template should be password protected
Validation of Excel spreadsheet
Validation exercises should include:
- Verifying the cell protection for all the cells with formulae.
- Check the formulae with the test method.
- Compare the results obtained by using an excel sheet with those obtained using a calculator. In case the excel sheet contains cells with similar formulas each of these cells shall be independently checked.
- The result will be considered acceptable if they do not differ from the significant figure in the limit of expression.
- Record the observations in the excel sheet validation report (Attachment -I) and attach the printout of the excel sheet along with manual calculations to the report.
Use
User to use blank and current version template
User to enter the data and confirm the correctness of all the data entries made in the spreadsheet.
Re-validation
Manager-QA / Supervisor to assess the necessity for complete re-validation based on the extent of changes made.
Any changes made in the calculation formula will require revalidation.
Once the new version gets approved, the Manager-QA / Supervisor to save the new version – “Validation of Excel spreadsheet” for the spreadsheet.
Annexure– I
Excel Sheet Validation Report
- Objective:
- Scope:
- Features of excel sheet:
- Methodology of validation:
- Acceptance criteria:
All the values in the formula cells should match with the manual calculated value up to 1 decimal.
Excel spreadsheet
| Checks | Results | |
| Allowed | Not Allowed | |
| Entry into a locked cell | ||
| Modification of Formula | ||
| Entry in Formula cell | ||
Manual calculations
Excel sheet calculations – attach (Excel sheet printout)
Results comparison
| Parameters | Cell No. | Manual Result | Excel sheet result | Different | Result |
| Found satisfactory / Not satisfactory | |||||
| Found satisfactory / Not satisfactory |
Check of formula (Strike out whichever is not applicable)
Formula calculation in each cell has been verified: Found satisfactory / Not satisfactory.
Correction of cell
Following cells need correction
| Cell No. | Current Formula | Correction Required |
Reason for change
Conclusion:
The excel sheet with control no.______________________________ stands validated.
