Excel sheet validation
What is Excel sheet validation. Should we validate the calculations (arithmetic operations) in excel sheet validation
All Replies
Viewing 0 reply threads
Viewing 0 reply threads
- You must be logged in to reply to this topic.
Excel sheet validation is not about verifying the arithmetic operations of addition, substraction, multiplication etc. These are validated /verifed sufficienttly by the vendor ( say Microsoft). But we use Excel sheet for various calculations in QC – impurities, assay with formulae appplied, and putting in values for different parameters like weight, volume, dilution in the cells. These Excel sheets are saved, for again using for calculation of results for different samples. In this situation, the fields and the formulaes need to be protected / locked, it should not be changed by people using the excel sheets. This is where Excel sheet validation comes in – it involves developing the analytical work sheet and calculation template in the Excel sheet, securing the templates and protecting the template. The accuracy of the formulae and template created shall be verified by comparing the results generated with the Excel sheet with results calculated using a separate tool (like a validated calculator) and documented. Other cells in the Excel sheet shall be locked for any user inputs than where it is required. The Master Excel file with the formulae and template shall be saved in predefined folders, following a naming convention for the files and the folders. This involve version identification, defining a folder structure for master Excel sheets, maintaining a log / list of the Master Excel sheets, defining folder structure for saving operational Excel working sheets (where speific parameters are calculated for each sample),  securely saving the Excel worksheets. An audit trail for the excel file may be implemented which will indicate what has changed. A periodic review of the validated master Excel sheets shall also be performed and documented to ensure there is no inadvertent, unauthorised changes. Input data validation rules shall be applied for input cells to avoid aberrant entries. The Excel sheet validation involve verifying all the control features implemented – cell protection, password protection, template protection, formulae protection, formulae calculation accuracy, naming convention and folder structure etc. and documenting the same.
Refer the following documents also: