SOP on Validation of Microsoft Excel Calculations Worksheets

0
27511
1.0 Objective
To lay down a procedure for validation of Microsoft Excel worksheets to be used for analytical calculations.
2.0 Scope
This SOP is applicable for validation of Microsoft Excel worksheets to be used for analytical calculations in QC lab.
3.0 Responsibility
3.1 QC Executive or above responsible for design and validate the worksheet for different test.
3.2 Head-Concerned department is responsible to ensure the accuracy of validation worksheet.
4.0 Accountability
4.1 Department Head & QA Head shall be accountable for implementation of this SOP.
5.0 Abbreviations and Definitions
SOP                :           Standard Operating Procedure
QC                   :           Quality Control
IT                     :           Information & Technology
SP                   :           Specific
S.T.P No.        :           Standard Test Procedure Number
Spec. No.        :           Specification Number
A.R. No.         :           Analytical Reference Number
6.0 Procedure:
6.1 Control of Microsoft Excel Spread Sheet:
6.1.1 Each Worksheet shall be Product and Test Specific.
6.1.2 All worksheets used in the laboratory shall be validated with the help of IT department as following and approved by QA department.
                                                 Validation of excel Spreadsheet
Work sheet No. ________                                                                   Product_________
Path____________                                                                                       Test________
   
 
Sr. No.
     Test Parameter
          Activity
Acceptance Criteria
Observation (Yes/No)
1.
Validated Excel Sheet to be stored on a separated folder
Check availability of exclusive folder for validated excel sheet
Excel Sheet to be available on a separate folder
2.
Workbook Protection: The excel workbook should be operational by the authorized personal with right password. On supplying wrong password system should display warning message of incorrect password supplied.
Correct Password
System allows opening of workbook.
Incorrect Password
System disallows opening of workbook.
3.
Alteration:
a)
Changes or Modification in the protected cells
Attempt to change formula or test in the protected cells
System disallows change in protected cells
b)
Additional or deletion of the any row/coloum in the worksheet impacting on the operation of the formula
Additional or deletion of the any row/column in the worksheet.
System disallows additional / deletion of any row or column.
4.
Calculations
Calculations verified using calculator:
Calculator ID No.:
Difference between values obtained from excel sheet and calculator should not differ.
Result By calculator:
Result by excel sheet:
  • 6.1.3 QC Executive or above shall design and validate the worksheet for different tests an shall prepare a list heading “Validation Excel sheet index” as following:
                                                                Validated Excel Sheet Index
Sr. No.
     Product
        Test
Spreadsheet No.
Verified by
Remarks
  • 6.1.4 Header and Footer of each worksheet shall be given below:
q
  • 6.1.5 Each validated spreadsheet used for calculation shall be numbered as QC-AABBB-CC, where QC represents Quality Control, AA represents RM for raw material, FS for finished product & Stability Product, IM for intermediate products and MS for miscellaneous samples, BBB represents unique incremental no. and CC represents version no. starting from 00.
  • 6.1.6 Master Excel spreadsheet shall be stored in a separate folder on the computer of administrator.
  • 6.1.7 Executive or above shall act as administrator and only administrator shall have the rights to create new master Excel worksheets.
  • 6.1.8 The master workbook/worksheet modification and deletion rights shall also be there with the administrator only
  • 6.1.9 Master Excel worksheet shall be used for the purpose of calculations by the Laboratory Analysts from the computer terminals in the laboratory.
  • 6.2 Worksheet Protection :
    • 6.2.1 Each Excel spreadsheet shall be password protected.
    • 6.2.2 The password shall be of eight characters having alphanumerical in nature and one alphanumerical character in capital letter.
    • 6.2.3 The password of each workbook shall be maintained as give below format:
                                                           Table of Password of Spreadsheet
Sr. No.
Excel Sheet I.D. No.
Password to       open
Password caps lock character
Password to open
Verified by
6.2.4 Each Excel workbook shall opened as “read only” option.
6.2.5 The right click option shall be deactivated for user.
6.2.6 The Excel worksheet shall be operational in “read only” format and user shall not be able to save any data on the sheet and user shall be authorized to take print only via “Ctrl+ P” command.
6.2.7 Addition or deletion of the any row/column in the worksheet impacting on the operation of the formulae shall be restricted.
6.3 Validation Procedure:
6.3.1 Availability of validated Spreadsheet.
6.3.1.1 Acceptance criteria: Excel Sheet shall be available on a separate folder.
6.3.2 Wrong password.
6.3.2.1 An attempt shall be made to open the Excel workbook using a wrong password.
6.3.2.2 Acceptance criteria: The Excel workbook shall not open with the wrong password and the system shall display the message accordingly.
(Note – Screen print carrying the system message shall be printed and attached with spreadsheet validation document.)
6.3.3 Alteration:
6.3.3.1 An attempt shall be made to alter the contents of the protected cells, carrying the formula and fixed contents.
6.3..3.2 Acceptance criteria: The Excel workbook shall not allow alteration of contents in the protected cells and the system shall display a message accordingly.
(The screen print carrying the system message shall be printed and shall be attach with validation document)
6.3.3.3 Addition or Deletion of row/column
6.3.3.3.1 An attempt shall be made to add or delete row/column
6.3.3.3.2 Acceptance criteria: The Excel workbook shall not add or delete the row/column and the system shall display the message accordingly.
(Note: The screen print carrying the system message shall be printed and attached with spreadsheet validation document.)
6.3.4 Verification of calculations:
6.3.4.1 Each of the calculations shall be done by calculator as well as excel sheet and result shall be compared.
6.3.4.2 Results shall be rounded off up to decimal point.
6.3.4.3 Each of the calculations involved in the Excel worksheet shall be verified using a qualified calculator.
6.3.4.4 Calculations done by calculator shall be recorded.
6.3.4.5 Prior to verification of calculation, it should be ensure that the formula to be used is as per STP and should be verified as by QC Executive/nominee.
6.3.4.6 Acceptance criteria: There shall be no difference in calculated values using the Excel worksheet and calculator.
(Note-The Excel worksheet carrying the calculated results shall be printed and recorded observation.)
6.4 Revalidation of Excel Sheets:
6.4.1 Excel sheets shall be revalidate after every three years.
6.4.2 Excel sheets shall also be revalidate if:
6.4.2.1 Any change in the formula
6.4.2.2 Any change in design/template of Excel Sheet
7.0 Forms and Records (Annexures)
Attach Related Sheets
8.0 Distribution
Master copy              –       Quality Assurance
Controlled copies    –      Quality Control Lab
9.0 Reference:  
              If any
10.0 History
          Date
             Revision Number
                     Reason for Revision