SOP for Preparation and Validation of Excel Spreadsheet


To define the procedure for Preparation and Validation of Excel Spreadsheets used for calculation in Quality control department.

  1. SCOPE:

This SOP is applicable for preparation and validation of excel spreadsheet in Q.C. laboratory at manufacturing facility of XXX.

    1. IT Department: Responsible for password protection and read-only access of excel spreadsheet.
      1. Quality Control: Responsible for preparation and validation of excel spreadsheets carried out as per SOP.
      2. Quality Assurance: Head Quality Assurance shall be responsible to approve SOP and ensure its proper implementation.
      1. To prevent general access to the files, a file open password should be required to access the file in read-only status.  Analyst is authorized to use this password.
      2. To prevent unauthorized or accidental modification to the worksheets, a password must be required to allow access to locked cells.  All calculation cells must be locked.  I.T. department head has this password.
      3. Only one version of the approved files must exist for use.
      1. Prepare the Excel Spreadsheet for calculation using computer system located in the Quality control laboratory based on the information available in the test procedure of current specification. 
      2. Log on to the computer and open a new Excel file.
      3. Define the page setting as A4 paper size and add Header – Footer for the work sheet.
      4. Header should contain name of company, with location, name of department, logo on the right section of the sheet and Calculation Sheet at the center section of the sheet. Mention version number of file in the right side of the header. 
      5. Footer should contain the path of the file at the right section of the sheet, where the file (spreadsheet) is saved.
      1. Upper part of the sheet should have the information related to the product. e.g. Name of product, batch No., A.R. No., stage & date of analysis / calculation.
      2. Lower part of the sheet should have the information about the remarks, analyst and checked by with sign & date.
      3. Middle part of the sheet should have the information that is required for calculation, where entry to be filled from Raw Data Sheet. All calculation cells are included in this part of sheet. e.g. Batch No. & potency of working standard, weight taken for solution preparation, peak area, results and specification as applicable.
      4. Add formula for respective calculation in the corresponding cells.
      5. Fill the yellow color into the cells, in which variable data is to be entered. 
      6. Do the conditional formatting to the cells as applicable based on the current specification.
      7. Name the worksheet as per test item. e.g. Assay, Dissolution, Related substances, Relative standard deviation etc.
      8. Numbering system of the excel spreadsheet will be follow as:


AA : Company Name

QC : Quality Control

XXXX : Product Type (i.e., Tablet, Capsule, Pallets…)

AAAA : Test Name (i.e., DISSO for Dissolution, RS for Related                                          Substance, Assay, UOD for Uniformity of dosage unit

YY : Serial number of particular excel calculation sheet

ZZ : Revision Number

  1. If common sheet is to be used for assay, disso, UOD test etc… one excel sheet shall be used for tablets, capsules, pellets etc….
  2. Select all the cells to be locked in a worksheet and protect them using a password, except the cell filled with yellow color. The cells filled with yellow color should be unlocked. All calculation cells must be locked.
  3. Protect all the worksheets of spreadsheet with a password. 
  4. Save as spreadsheet with name of product as file name in the following 

path using file open and modification password for excel spreadsheet first time validation.

i.e. \\\Excel Calculation sheet validation\For Validation \paracetamol\paracetamol tablet_dissolutiont

  1. After save the sheet on the server, the sheet shall be verified for its usage and details shall be recorded in the annexure I & II for the verification.
  2. After complete verification by IT person, the Excel sheet shall be transfer from folder : i.e. \\\Excel Calculation sheet validation\For Validation \paracetamol\paracetamol tablet_dissolutiont to i.e. \\\Excel Calculation sheet validation\For Routine Use \paracetamol\paracetamol tablet_dissolutiont by changing the master password by the  IT person for routine use.
  3. The Excel sheet for routine use are only applicable if the following path display on the footer of the sheet.

i.e. \\\Excel Calculation sheet validation\For Routine Use \paracetamol\paracetamol tablet_dissolutiont

    1. Before using the prepared spreadsheet for the first time it should be verified and protected to prevent unauthorized or accidental modification to the work sheets and spreadsheet. Head IT or his designee has the entire password; only file open password (open as read only) will be shared with analyst / user.
    2. Executive along with Department Head will perform the verification of the spreadsheet.
    3. Analyst / user will enter the variable data of any released batch of the product in the respective cells and take the print of spreadsheet.
    4. Analyst / user will also perform the manual calculation using hand held calculator on a paper sheet (Hard copy) for the final values and also shall verify & record the RSD of the replicate values entered in the Excel sheet of the same batch of the product. 
    5. Quality Control head will verify both the sheets i.e. Excel spreadsheet & manual paper sheet.
    6. For excel spreadsheet, he will also verify all the conditional statements and formulas for locked cells and make necessary correction if any.  
    7. After complete verification IT Head/Designee will change all the passwords uses for protection and unauthorized modification to the work sheet and spreadsheet and save the file after approval of Head QA.
    8. Only file open password (open as read only) will be shared with analyst for future use of verified spreadsheet.
    9. Record the verification of Excel spreadsheet as per the Annexure – I & II.
    10. Submit the data to IT department for save the validated spreadsheet for routine use in below path.

i.e. \\\Excel Calculation sheet validation\For Routine Use \paracetamol\paracetamol tablet_dissolutiont

    1. After completion of analysis, analyst / user feed the variable data from raw data sheet in Excel spreadsheet as per following sequence:
    2. Switch on the computer and access the path in which the excel spreadsheets are saved.
    3. Open the relevant excel spreadsheet by double click on the following in a sequence.

i.e. \\\Excel Calculation sheet validation\For Routine Use \paracetamol\paracetamol tablet_dissolutiont

  1. After clicking on the relevant Excel spreadsheet small icon will appear on the screen “filename.xls is protected” and ask for password. Type the password and press enter.
  2. Type the variable data in the cell, which are filled with yellow color and are unlocked. Fill all other data in the spreadsheet.
  3. Check the font color of the data; it should be black for all the cells. Check the fill color of the data, it should be yellow for variable cells and green for results obtained. Compare all the result with specification. In case of Out of Specification / Limit inform Department Head.
  4. Take the printout of the worksheet in the spreadsheet. Close the excel spreadsheet without saving the changes. Analyst/user should check the details and sign all the worksheets.
    1. Modification of excel spreadsheet to be authorized by Head QA/or his designee 
    2. Revision of the excel spread sheet shall be through change control procedure with proper justification and approval from head QA,
    3. Previous version of the excel spread sheet shall be archived in the folder as absolute and IT shall change the password to restrict the usage of the previous version of excel spread sheet. The list of excel spreadsheets shall be maintained along with its version no. as per AnnexureII.
    4. The list of all passwords is kept under control of IT Department Head. 

Trainer:   Head – Information Technology 

Trainees:  Officer / Executive IT, QC & QA


Controlled Copy 1 :  Head of Department – Quality Assurance

Controlled Copy 2 :  Head of Department – Information Technology 

Controlled Copy 3 :  Head of Department – Quality Control

Original Copy:   Quality Assurance

  1. ANNEXURE(S) :

Annexure I         :  Verification of Excel spreadsheet

Annexure II         :  List of Excel spreadsheets




1 thought on “SOP for Preparation and Validation of Excel Spreadsheet”

  1. Thanks for your whole team, i am really appreciated to giving the most important information and i learn lot and grow my knowledge with your sharing kind of sop’s. kindly share me below annexures
    Annexure I : Verification of Excel spreadsheet

    Annexure II : List of Excel spreadsheets

Leave a Reply