0
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.

0
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:

0
shares

‘To the Cloud or not to the Cloud, that is the question.’

While it’s certainly a familiar question, with apologies to Hamlet, there is a question that tends to come first: “What is the Cloud in the first place?” Have you seen this on-line graphic lately of a young child who is just looking at you with the caption – ‘There is no cloud…’

Information Workers

The reality for most ‘information workers’ is that as long as your keyboard, mouse and monitor have something to plug into and data displays, all you really need to see is where that ‘something’ plugs into the wall so you can be sure you have a physical connection.

What’s on the other side of the wall – or even if there is strictly speaking no ‘other side’ – is not the users concern. You don’t need to have physical computing resources right next to you as long as the ‘virtual machine’ you are working on is properly provisioned and the latency (or ‘lag/response time’) isn’t large enough to be an issue.

Enterprise

The physical computing resources matter if you are doing a lab analysis with physical samples on instruments or using an automated line to produce product. In the land of data analysis, reporting and document production, the data needs to be available, not present.

From the enterprise perspective however, what is ‘present’ behind the wall is a true concern. Since compliance to regulations means ‘control,’ how the cloud supplies and protects those resources and the information they provide needs to be documented under a defined quality management system. While cloud vendors are glad to quote services and prices, they have not always been forthcoming about how things are done in their building.

Outsourcing

The move to the cloud is a physical move – of your data, applications and possibly compute and platforms – to somewhere else. The somewhere is physical while your usage and controls become logical and virtual. If that makes you feel uneasy – or if you prefer ‘risk averse’ – you are not alone.

Outsourcing is a concept that brings joy to financial / accounting types, but for those involved with compliance, quality and validation, it brings a new set of challenges and concerns. It should recall the words of Mr. Murphy – “Nothing is as easy as it looks, everything takes longer than you expect AND if anything can go wrong, it will – usually at the worst possible time.”

Current warning letters addressing data integrity have focused on site forensics – data in trash cans, bags of shredded records. When it comes to the cloud, there is no ‘there’ for you to access – unless there are the careful negotiations, detailed SLAs and rigorous audit/ follow-up required to give your enterprise the confidence it needs to move forward.

Do your current internal resources have the expertise and the flexibility to deal with a vendor you have to trust significantly? This is more complex than a contract manufacturer where you can review the SOPs for compliance, watch the process and then have the product independently tested.

GxP Compliance

The draw of the cloud is that everything is ‘out there’ – available ‘just by an e-mail’ – and not running up costs on your premises. What will happen if suddenly it isn’t ‘there?’ Whose fault will it be – oh, must be the vendor. Good idea – blame the vendor – but there’s many a wire between your ‘here ‘ and their ‘there.’

Will it be your communication vendor, some nameless third party supplier or cloud hardware, software or internal network failure? There are many ABCs in the cloud – SaaS, IaaS, PaaS, Haas, AaaS, ITaaS – and the list goes on. It is critical to have the proper support to be able to straighten all those letters out to spell ‘GxP compliance.’

Cloud Provider

Everyone wants their cloud provider to look like the image below. But that will take attention to detail, technical understanding and the ability to ask ‘the next questions’ needed to assure quality and compliance.

Those will include security at multiple levels and how are they going to maintain those perfect cables when the one in the middle breaks?

There are many items to be considered – here are some more:

  • What application(s) and data are going to be ‘sent to the cloud?’
  • What in-house processes / systems need to access that data?
  • Where will the data physically be held and what are the laws in that location if outside of the USA?
  • What are the backup provisions for the cloud providers’ servers and storage?
  • What is the security plan – including physical, logical and access controls?
  • How will your audit resources be granted access and under what ground rules?
  • Does the vendor provide a Quality Manual or Quality Management System document for review?
  • Does their contract include a ‘non-cookie cutter’ Service Level Agreement (SLA) that details your focus points?

About Azzur IT

Azzur_IT

Azzur IT is an Azzur Group Company providing a broad range of services, including IT Quality & Compliance auditing services; computer system validation (CSV); application selection, implementation, integration and project management services; IT infrastructure, operations & data center assessment; risk-based data integrity assessment; compliance remediation projects; SOX consulting; process improvement and change management.

www.azzur.com

0
shares

Similar articles:

0
shares

Changes to equipment and documentation is an integral part of the whole GMP process in any regulated facility. In this article we will discuss how equipment is designed in a GMP facility and how good documentation practices are an essential part of quality assurance and GMP.

The content of this article has been taken from module 7 of our eLearning module on Good Manufacturing Practices (cGMP) within the life sciences.

You can view this module in full by viewing the video below.

cGMP eLearning Module – Compelling, Engaging and Interactive

 

Equipment

All equipment is designed, constructed and located to suit their intended use and to facilitate easy maintenance and cleaning.

Equipment is installed in such a way as to prevent any risk of error or of contamination, and cleaned according to detailed and written procedures and stored only in a clean and dry condition.

Production equipment should be designed in such a way as not to present any hazard to the products.

The parts of the production equipment that come into contact with the product must not be reactive, additive or absorptive to such an extent that it will affect the quality of the product and thus present any hazard.

Any defective equipment should, if possible, be removed from production and quality control areas, or at least be clearly labelled as defective. When not in use, equipment should be covered to ensure it remains clean.

Balances

Balances and measuring equipment of an appropriate range and precision should be available for production and quality control operations.

All measuring devices are required to be calibrated and checked at defined intervals by appropriate methods, and adequate records of such tests should be maintained.

Utilities

Fixed piping should be clearly labelled to indicate the contents and where applicable, the direction of flow.

Water pipes used in production (e.g. Purified Water, Water for Injection) are sanitised according to written procedures that detail the action limits for microbiological contamination and the measures to be taken.

Electrical circuits should be identified, and a record maintained of the load on each circuit to prevent inadvertent overload.

Documentation

Good Documentation Practices are an essential part of quality assurance and GMP.

It is important for a manufacturer to get the documentation right in order to get the product right.

GMP Documentation e.g. Site Master File, Specifications, Batch Manufacturing Formulae, Batch Manufacturing Records, Processing, Labelling, Packaging, Testing Instructions, Standard Operating Procedures, Protocols, Technical Agreements, Records, Certificates of Analysis, Reports etc. should contain the following attributes of a good document:

They should be:

  • Attributable
  • Legible
  • Contemporaneous
  • Original
  • Accurate
  • Complete
  • Durable
  • Corroborated
  • Version Based
  • Accessible
  • And Authorized

Change Control

Change to GMP documentation, equipment, processes, systems, instrumentation, test methods, etc. are required to be controlled under a formal change control program.

This program must consist of Quality oversight to review the proposed changes, evaluate the potential impact of the change, determine any potential risk to product quality, and to establish the required level of supplemental validation/documentation required for the change.

In many instances, changes will also require submission to the Health Authority for approval of the change.

For example, changes impacting the submission documentation are subject to post approval change guidances according to the Health Authority regulations.

Change Control is a critical aspect of the GMP systems.

If you want to learn more about cGMP or if you want to evaluate our eLearning module for your company you can find more information here.

0
shares

TOP

Similar articles: