SOP Title: Preparation and validation of Excel Calculation Sheet
- Objective:
To lay down the procedure for Excel Sheet Validation in Quality Control Department.
- Scope:
This procedure is applicable for ascertain the content of sample in Quality Control Department.
- Responsibility:
- Quality Control Department: To prepare and review the SOP. To follow the procedures laid down in the excel sheet for release of Sampling, in Quality Control Department as per this SOP.
- Quality Assurance Department: To review and approve the SOP and Annexures.
- Accountability:
Head Quality Control Department, Head Quality Assurance Department
- Procedure:
| 5.1 | Preparation of Excel Sheet :- | |
| 5.1.1 | Prepare the excel calculation sheet in “Microsoft Excel”. | |
| 5.1.2 | Prepare separate calculation sheet for individual tests, which can be common for all products or it can be product wise. | |
| 5.1.3 | The Header of the excel sheet shall consist of Name of the Organisation, Name of the Department, Product Name, Batch No., Stage, Test, A.R. No. and Date of analysis etc. | |
| 5.1.4 | Wherever applicable but not limited, Main body of the Excel sheet shall generally consist of standard weight, sample weight, dilutions, average weight, standard purity, sample/standard absorbance/area, label claim, final results and calculation formula etc., | |
| 5.1.5 | The Footer of the excel sheet consist of Analysed By & Date, Checked By & Date and Name of the Excel sheet with version Number etc. | |
| 5.1.6 | In case of Related Substances, include retention time, relative retention time and name of the impurity etc. | |
| 5.1.7 | For dilutions of standard solution, show weight and diluted to specific volume and the maximum possible dilution in excel sheet. | |
| 5.1.8 | For dilutions of sample solution, show weight and diluted to specific volume by considering maximum possible dilutions. | |
| 5.1.9 | Put the calculation formula correctly in the cell where results will appear. | |
| 5.1.10 | Calculation formula shall be referred from STP/AWR. | |
| 5.1.11 | Prior to excel validation, calculation formula shall be verified by supervisor. | |
| 5.1.12 | Results shall be rounded off up to the decimal points. Number of decimal points for results shall be decided by referring product specification. | |
| 5.1.13 | Protect the full excel sheet except the cells that needs to be filled while calculating the results. | |
| Note: After protection of the calculation sheet, the cells that are unlocked are open for changes like variables such as area, absorbance, weight, dilution and potency of working standard / reference standard etc. | ||
| 5.1.14 | To protect excel sheet select go to “Review” on the Tool menu of excel and then click Protect Sheet. Type an alphanumeric password and click “OK”, and then retype the password in the Confirm Password dialog box. | |
| 5.1.15 | Password shall be of eight characters having alphanumerical in nature and one special character.
For example:- abc@1234 |
|
| 5.1.16 | Password should be known to Administrator/Head QC/IT only. | |
| 5.1.17 | Master excel calculation sheet shall be stored in separate folder on the computer of administrator. | |
| 5.1.18 | Only Administrator shall have the rights to create new master Excel calculation sheet. | |
| 5.1.19 | Only Administrator shall have the rights to modify/delete master Excel calculation sheet after consultation with Head QC. | |
| 5.1.20 | Fill the unprotected cells that need to be filled while calculating the results. | |
| 5.1.21 | Give the input cells a color to make the spreadsheet more clear and easier to understand for users. | |
| 5.1.22 | Prepare a separate excel calculation sheet for respective tests. | |
| 5.1.23 | Right click option should be deactivated for user. | |
| 5.2 | Nomenclature of Excel calculation Sheet :- | |
| 5.2.1 | Each and every Excel sheet should have unique identification name as mentioned below | |
| 5.2.2 | “XXXX_YY_ZZ_AA_NN”
Where, XXXX-Product Name/Material Name YY- Dosage Form (e.g. Tablet/Capsule/Cream/RM) ZZ-Strength/Type AA-Test Name NN- Version Number For Example:- Paracetamol Tablets 500 mg Assay test should be named as “Paracetamol_Tab_500mg_Assay_00” |
|
| 5.3 | Validation of Excel calculation Sheet | |
| 5.3.1 | Prepare validation protocol for excel calculation sheet. | |
| 5.3.2 | Perform calculations using excel calculation sheet and compare the results with same sets of calculations performed using scientific calculator up to pre-determined decimal places. | |
| 5.3.3 | To validate excel sheet, first perform calculations by putting required values in excel sheet and take the printout of excel sheet. | |
| 5.3.4 | Then perform same calculations by using scientific calculator. All calculations should be reported on Annexure. | |
| 5.3.5 | Calculations done by Excel sheet and by calculator shall be compared. | |
| 5.3.6 | Acceptance criteria: Excel Sheet Calculation results should match with the scientific calculator results. | |
| 5.4 | ||
| 5.4.1 | Prove that entry in protected cells cannot be made. Screen shot shall be attached with validation documents. To verify the protected cell, double click on the locked excel the display will read as: The cell or chart you are trying to change is protected and therefore read-only. | |
| 5.4.2 | Prove that, to unprotect the calculation sheet, password is required. Take screen shot. To unprotect the protected calculation sheet click on review on Toolbar select unprotect sheet, enter the password and select ok. | |
| 5.4.3 | On input of wrong password, system should display warning message of incorrect password applied. | |
| 5.4.4 | Prove that, entry of alphabets, special character and zero in input cells which are directly involve in calculation results shows invalid results. | |
| 5.4.5 | Attempt to change Formula/values/text in protected cell. System should disallow change in protected cell. | |
| 5.4.6 | Attempt to Delete/ Add Row or column in the protected worksheet. System should disallow any deletion/addition of row or column. | |
| 5.4.7 | Verify the spreadsheet calculations by entering expected values and extreme values and verify the behaviour of the spreadsheet. | |
| 5.5 | Revalidation of Excel sheet | |
| 5.5.1 | Excel sheet shall be revalidated in case of change in any formula OR any change in design and templates which will have direct impact on the results.
Revalidation shall also be done once in 2 years irrespective of no change. |
|
| 5.5.2 | Any changes in validated calculation sheet shall be made through change control. | |
| 5.5.3 | Version number of validated calculation sheet shall be updated after changes. | |
| 5.5.4 | Re-Validation is not required for validated calculation sheet in case of typo graphical correction to be made, which will not have impact on the calculation results. However, impact assessment of changes shall be done through change control. | |
| 5.5.5 | Maintain list of excel sheet, Product Name, Test Name, version No., validation done on and due date for validation. | |
| 5.5.6 | Re-validation of Excel Sheet should be done within 30 days from the due date. | |
| 5. 6 | Calibration of Calculator:- | |
| 5.6.1 | New calculator shall be calibrated before use. | |
| 5.6.2 | Before starting calibration of the calculator, check the operation of the calculator as per manufacturer’s manual. | |
| 5.6.3 | Calibrate the calculator for the functions like Addition (+), Subtraction (-), Multiplication (X), Division (÷) and Square Root (√). | |
| 5.6.4 | Verify the functions calculated using calculator with manual calculations. | |
| 5.6.5 | Also verify some complex functions like Multiplication (X), Division (÷) and Square Root (√) by using Logarithmic tables and Antilog tables. | |
| 5.6.6 | Acceptance criteria: Calculator values should match with the manual calculation values. | |
| 5.6.7 | Re-calibrate the calculator in case of major repairing. | |
- Definitions / Abbreviations:
- Definitions:
- Abbreviations:
| Abbreviation | Expansion |
| SOP | Standard Operating Procedure |
| QC | Quality Control |
| QA | Quality Assurance |
| No. | Number |
| AWR | Analytical Work Report |
| STP | Standard Testing Procedure |
*Note – Ready to use SOP available in “DOWNLOAD” Section.