1 product worth 45.55 in your cart
go to the checkout

8
shares

This article will present a simple method for validating MS Excel spreadsheets for GXP use. The goal of our validation strategy is to provide better testing and documentation of individual spreadsheets in less time. Once we establish this methodology, we can rapidly apply it to many spreadsheets to reach compliance faster.

Scope

This article will attempt to cover the most common examples of spreadsheet validation activities that we find. We will attempt to cover special or very complicated examples whenever possible, but it should be understood that anything not explicitly covered in this article can be added as needed to fit your existing validation requirements or for special cases. A good example is any spreadsheet that uses or is used with custom automation. We have validated spreadsheets that contained hundreds of formulas and custom macros, but did not require any user intervention at all because they were created, populated with data and saved entirely by external code. In this case, the focus of the validation was on validating formulas and macros, but security testing was limited to proving that users could not intercept or interrupt the operation at any time.

Assumptions

We are not going to make any assumptions about the existing SDLC (Software Development Life Cycle) that may be currently in use, any material presented here can be adapted to meet your existing standards and practices.
We are also going to assume that the spreadsheet that need validation has already been written, as this is what we find in the overwhelming majority of the cases presented to us.

Approach

The basics of our approach are as follows:

  • We define our basic documentation practices and methodology in a single document, the Spreadsheet Validation Master Plan. This document can be referred to for all individual spreadsheet validation projects, and will not need to be reviewed or approved for each validation effort.
  • We define the requirements for each sheet or chart in the workbook, then focus our testing on verifying these requirements.
  • A heavy emphasis is placed on defining and testing formulas, and also on the security for each sheet by limiting the parts of each sheet that the users are allowed to edit.

Methodology

To keep things simple, we define how we enter information into the requirements and design specification documents, then describe how we go about testing the spreadsheet in the test protocols. Keep in mind that input cells may be defined as one single cell or as a range of cells.

Requirements Specification

The requirements specification should include all the requirements that your spreadsheet must accomplish. This document should be kept simple and relatively non-technical so that anyone who reads the document will understand that the requirements are.

Starting with a common validation template:

  • List all sheets and charts in the workbook
  • What users should be allowed to enter into the sheet. These are your input cells. You may find it helpful here and for later use to label each input cell, or they can be labeled in the design specification document.
  • What formulas or calculations exist on the sheet.
  • What cells represent the output or final calculation of the sheet. This may also refer to a chart.
  • For each chart, define properties like the title, axis labels and units, and the datasets used to create the chart.

Design Specification

Now that you have defined your requirements for each sheet, you can create the detailed software design specification document. The purpose of this document is to describe how the requirements have been implemented. This document should include enough information so that a developer could create the entire software project based on the information contained within this document and from reading the requirements specification
We like to break each sheet into four sections: Inputs, Processing, Outputs and Security.

Inputs

Document the cells users are expected to enter or update data. In an automated system, you can also define the source or the input data or instructions. If any validation rules are used to enforce proper data entry, these should be documented here as well.

Processing

Processing is mostly about documenting the formulas that are used on the sheet. Any custom macros or code is also documented here.
The majority of errors that we find when validating sheets is in the formulas. The best way we have found to catch these errors is to define each formula using the actual names of the variables represented as input cells.

For example, it may be easy to document that the range of cells F10:F20 contains the formula “=(A10*$C$5)/($D$5*B10). However, it is difficult to verify if this formula is correct. We recommend writing the formulas out like this:

  • Cell $C$5: Volume (V)
  • Cell $D$5: Ideal Gas Constant (R)
  • Cells A10:A20 Pressure (P)
  • Cells B10:B20 Temperature (T)
  • F10:F20 Final Result (moles of gas, n)
  • n=(PV)/(RT)

This is the easiest and most effective way we have found to verify that formulas are correct / to catch errors in formulas.

The other kind of processing is the validation of macros and code that is used in the spreadsheet. Here, the design specification is a good place to copy the code and annotate as needed to describe the purpose of each macro or function.

Note: If you have good coding standards and user proper headers and comments in your code, this step may be already done for you.

Outputs

Outputs usually fall under one of the following three main categories:

1. The cell or range of cells that contain the final result of all previous calculations
2. Charts – many times these are printed and saved with external reports
3. Data that is copied into a final result sheet or exported to a separate file or database.

Security

This section can be a short statement, i.e. “All non-input cells should be locked to prevent changes.” You can also include additional security settings if you are using either custom code or a third party add-on to implement multiple levels of security to control who can edit certain cells, run a macro or function, etc.

Test Protocols

The testing of any spreadsheet should prove that the requirements were properly implemented according the design specifications.

Installation Qualification (IQ) testing is usually limited to making sure the file is in a location where users can access the file, unless the workbook is part of a larger automation project. Operational Qualification (OQ) testing is mostly about verifying formulas, macros, and also to test the security of each sheet to verify that all non-input cells are locked to prevent changes. The IQ and OQ can be combined into a single IOQ protocol as needed.

To start generating our test cases, Once again, we like to break testing down into Inputs, Processing, and Outputs, but security testing is easier to test in separate test cases.

Process & Output Testing

These are usually easier to combine when writing test cases. Once again, FOCUS ON TESTING THE FORMULAS!

  • Are formulas correct? There are several methods for verifying and testing formulas
  • Continue testing until all input cells have had data entered on changed, and that result has been verified. The key point is to look for formulas that are incorrect or charts that are not using the correct or complete set of data, or are pointing to the wrong columns of data.
  • Macros can be tested by entering a range of data and comparing the results with a hand calculator, or by visual inspection that the function performed as expected.
  • Charts can be tested by a combination of visual inspection or verification of the properties, including the dataset used as the basis for the charts.

Security Testing

The type and amount of security testing that you can do for spreadsheets is largely based on how security has been implemented. At a bare minimum, you should test that users are limited to entering data into the defined input cells only, and that they do not have the ability to alter any other part of the spreadsheet. Failure to do this can compromise the integrity of the validation effort and of any data or information generated by the workbook.

Conclusion

This methodology will result in a User/Functional Requirements Specification, a Software Design Specification, and an IOQ Protocol ready for approval and execution. Any deviations found during testing can be handled according to existing validation practices, and a summary report that shows that all the activities specified in the Validation Master Plan or SOP can be generated.

8
shares

  • maryacton

    Very nice article, its a good refresher course for me.

    Thanks you – Love the new design!

    Keep up the good work

  • maryacton

    Very nice article, its a good refresher course for me.

    Thanks you – Love the new design!

    Keep up the good work

  • John Kerr

    If you do a search for “Printing Formulas To Word” you’ll come across a page by CPearson. The macro will print all the formulas in a selected region in an Excel spreadsheet to a Word document. It saves so much time!
    To get the macro to run you need to “Make sure that you have enabled references to Word objects, from the Tools->References menu” in the VB Editor menu which can be confusing for novices to do.
    The problem we’ve had with validating Excel spreadsheets is getting the upper echelons to sign off the documents… they just don’t understand it! 😉

  • John Kerr

    If you do a search for “Printing Formulas To Word” you’ll come across a page by CPearson. The macro will print all the formulas in a selected region in an Excel spreadsheet to a Word document. It saves so much time!
    To get the macro to run you need to “Make sure that you have enabled references to Word objects, from the Tools->References menu” in the VB Editor menu which can be confusing for novices to do.
    The problem we’ve had with validating Excel spreadsheets is getting the upper echelons to sign off the documents… they just don’t understand it! 😉

  • http://www.plainsite.net/validation/validation.htm Val Dater

    The problem with the entire concept of validating an Excel spread sheet is that there is no audit trail that comes with an out of the box Excel spreadsheet. Which means there is no way to track if the data has been modified after the original entry, when the modification occurred, why it occurred and who did it. The Excel spread sheet maybe doing the calculations correctly but any auditor worth their salt would not be satisfied with a system carrying out a critical function that did not have an audit trail. There are some third party add-ons that install an audit trail into an excel spread sheet. I am not sure how good these are.

  • http://www.plainsite.net/validation/validation.htm Val Dater

    The problem with the entire concept of validating an Excel spread sheet is that there is no audit trail that comes with an out of the box Excel spreadsheet. Which means there is no way to track if the data has been modified after the original entry, when the modification occurred, why it occurred and who did it. The Excel spread sheet maybe doing the calculations correctly but any auditor worth their salt would not be satisfied with a system carrying out a critical function that did not have an audit trail. There are some third party add-ons that install an audit trail into an excel spread sheet. I am not sure how good these are.

  • davidj

    Excelent article! Very clear and straight forward.

    My only comment is although it is probably not a problem 99% of the time, be aware of rounding errors when comparing Excel calculations with a calculator, especially where there may be several to many iterations in a calculation! Also if displaying the result to only a few decimal places errors can creep in as Excel calculates to 18 dp (I think) and doing cumlulative calculations can again cause rounding errors. I know, because this has happened to me!

    I have also heard of audit trail addons, but I cant vouch for these either.

  • davidj

    Excelent article! Very clear and straight forward.

    My only comment is although it is probably not a problem 99% of the time, be aware of rounding errors when comparing Excel calculations with a calculator, especially where there may be several to many iterations in a calculation! Also if displaying the result to only a few decimal places errors can creep in as Excel calculates to 18 dp (I think) and doing cumlulative calculations can again cause rounding errors. I know, because this has happened to me!

    I have also heard of audit trail addons, but I cant vouch for these either.

  • http://www.spreadsheetvalidation.com Dave

    There are numerous add-ons to Excel which provide audit trails and the necessary features to enable 21CFRpart11 compliance. DaCS is probably the most widely used add-on in the Life Sciences arena, details can be found at http://www.spreadsheetvalidation.com

    One other consideration when validating spreadsheets is the time/costs required for the documentation/testing. If you have a large (ie more than about 5!) portfolio of GxP critical spreadsheets, then I would strongly recommend that the validation process is ‘genericised’ by means of standard document templates to allow rapid turnaround.

    There is a mini-series of papers available at http://www.spreadsheetvalidation.com describing a “Pragmatic Approach to the Validation of Excel Spreadsheets”. Using this approach we find that the majority of relatively simple spreadsheets (as typically used for data calculation/reporting etc), can be validated in 4-5 days, this includes full documentation and testing.

  • http://www.spreadsheetvalidation.com Dave

    There are numerous add-ons to Excel which provide audit trails and the necessary features to enable 21CFRpart11 compliance. DaCS is probably the most widely used add-on in the Life Sciences arena, details can be found at http://www.spreadsheetvalidation.com

    One other consideration when validating spreadsheets is the time/costs required for the documentation/testing. If you have a large (ie more than about 5!) portfolio of GxP critical spreadsheets, then I would strongly recommend that the validation process is ‘genericised’ by means of standard document templates to allow rapid turnaround.

    There is a mini-series of papers available at http://www.spreadsheetvalidation.com describing a “Pragmatic Approach to the Validation of Excel Spreadsheets”. Using this approach we find that the majority of relatively simple spreadsheets (as typically used for data calculation/reporting etc), can be validated in 4-5 days, this includes full documentation and testing.

  • alex

    What about the audit trail requirements testing of part 11 ?

  • alex

    What about the audit trail requirements testing of part 11 ?

  • abu rafey khan

    Really a good One.

  • abu rafey khan

    Really a good One.

  • Roberto Cassone

    Kindly note that the Off the box MS EXcel does actually have a form of audit trail. This is activated when the file is shared through the Share Workbook function or Protect and Share Workbook. Once saved in this form on the network new options appear on track changes in which a new sheet (hidden normally) appears where there will be the audit trail.

  • Roberto Cassone

    Kindly note that the Off the box MS EXcel does actually have a form of audit trail. This is activated when the file is shared through the Share Workbook function or Protect and Share Workbook. Once saved in this form on the network new options appear on track changes in which a new sheet (hidden normally) appears where there will be the audit trail.

  • Bhalchandra Farde

    Its realy good and inshort cover all regularty requirment.

Similar articles:

8
shares

Training of personnel on deviation reporting and deviation handling is essential to a successful implementation of deviation management. All personnel must have a clear understanding of the deviation concept on all level of operation.

From a cleaning employee to a top level manager, everyone in the organization must apply deviation management procedure or instructions.

In order to do that, it must be mandated that all employee will attend at least once per year training on how to identify and report deviations.

 

The Three Levels

It is important to keep record of all the training’s performed in order to demonstrate to auditors that all personnel in the organization are competent and can report, handle, and/or manage deviations in a swift and accurate manner.

There are three levels of training on deviation management:

  1. Level 1: Identifying deviations and reporting them
  2. Level 2: Deviation Handling and Investigating
  3. Level 3: Deviation’s Corrective and Preventive Action

Level 1: Identifying Deviations and Reporting Them

  • This level of training should be mandated for all employees.
  • In this level, the trainee must learn how to notice and identify a course of actions or results which might indicate that something deviated from the standard and approved procedures in the quality management system.
  • This particular skill is crucial to the deviation reporting.
  • The trainer must spend considerable time developing attention to details in trainees. This particular competency forms the basis on which the trainer will build the trainees knowledge of deviation management.
  • The second obstacle to overcome is to make the employees understand that deviation reporting isn’t the equivalent of mistakes reporting.
  • In my experience, at first, many employees will see deviations as a way for the top management to record their mistakes for performance evaluation and bonus estimation.
  • The trainer must explain that it is the opposite of that concept, deviation reporting is your chance to express your interest in preserving the quality of the product or the service you provide in your organization.
  • It asserts your loyalty to the a quality based product and/or service and your devotion to the company’s standards.
  • Level 2: Deviation Handling and Investigating

    • This level of training is exclusive for management staff, including both lower and upper management.
    • In this level of training, the trainer must focus on investigational techniques and root cause analysis.
    • The basis of the training is risk analysis. The trainer must be an expert in risk identification, assessment, evaluation, reduction, and communication.
    • Other essential techniques include listening skills, communication, team work, and meeting management. These skills must be honed by the trainees during the training and during normal working hours.
    • The trainer must emphasis that this level of training is a mere introduction into deviation handling and that practice makes perfect. The reason is no one can develop all the skills necessary for investigating and solving complex deviations in one training module. The experience of the person practicing those tasks is the ammunition that would help him or her succeed at his or her job.

    Level 3: Deviation’s Corrective and Preventive Action

    • This level of training is exclusive to quality assurance personnel and upper management (including other department’s managers).
    • The aim of this training is to develop the ability of trainees to find the most efficient and effective actions to correct and prevent the deviation from reoccurring.
    • As we mentioned in 3.1.8. there are several points to consider when taking corrective and preventive actions. These points must be included in the training.
    • The trainer can be someone with experience with the applied procedure for corrective and preventive action in the organization and it would be better if he or she is experienced in risk management as mentioned in level 2 training.
    • If the training is being conducted for the first or second time, the trainer must emphasis to upper management that the trainee will need time to develop the experience necessary for them to create systematic actions with little to no side effects.
    • This training must conclude that the personnel required to decide the corrective and preventive actions must convene periodically in order to discuss the effectiveness of their actions and see what they can improve in their decision approach.

    Optional Training Sessions

    There are several competencies which are essential to successful deviation management, time management is one, communication management is also an essential feature which is greatly needed for the continuous and effective follow-up of deviations throughout the organization.

8
shares

Similar articles:

8
shares

Not all suppliers are the same, for example when you buy a car or a house, time and effort is dedicated into finding the best provider with the best product in terms of quality and reliability.

Choosing a Vendor

The same methodology needs to be dedicated when choosing a vendor for your business. Not only will you be purchasing their products or services, but you will also be dealing with them on a regular basis hence your personal relationship most also be strong.

Regulated Environment

When working in a regulated environment one of the key questions that needs to be considered is will the product of service affect your product quality. If the answer is yes then it is paramount that you can trust your supplier/vendor and the quality of their product or service

Supplier Qualification Online Course

This module is taken from our course on Supplier Qualification.

 

About Us

Learnaboutgmp is a cloud based learning management system (LMS) designed specifically for life science and regulatory organizations. Our LMS contains the courses you need to help you achieve regulatory compliance. With new courses published every month and existing courses updated in line with regulatory changes we are your true continuous learning platform.

8
shares

TOP

Similar articles: