EXCEL SHEET VALIDATION

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.

EXCEL SHEET VALIDATION

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

  1. Objective:
  2. Scope:
  3. Features of excel sheet:
  4. Methodology of validation:
  5. Acceptance criteria:

 All the values in the formula cells should match with the manual calculated value up to 1 decimal. 

Excel spreadsheet

ChecksResults
AllowedNot Allowed
Entry into a locked cell
Modification of Formula
Entry in Formula cell

Manual calculations

Excel sheet calculations – attach (Excel sheet printout)

Results comparison

ParametersCell No.Manual ResultExcel sheet resultDifferentResult
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 FormulaCorrection Required

Reason for change

Conclusion:

The excel sheet with control no.______________________________ stands validated.