Using Macros and Spreadsheets in a Regulated Environment

Spreadsheet calculations are popular in all kinds of businesses. A macro is a set of commands that can be embedded in a document to add functionality to standard programs and to automated processes. Macros and spreadsheets (M&S) are also frequently used in regulated environments; for example, batch release in pharmaceutical manufacturing may be based on M&S calculations. Spreadsheet programs are not designed for regulated environments, and without preventive actions the risk that they will generate errors is relatively high. Raymond R. Panko from the University of Hawaii studied spreadsheet errors extensively. Over several surveys, he found that error rates can increase by as much as 30%.¹

Records generated by M&S calculations are electronic records and therefore must comply with FDA's 21 CFR Part 11.² Requirements of the rule and its implementation in laboratories have been discussed in depth elsewhere.³

The primary requirements of 21 CFR Part 11 are:

• use of validated software and computer systems

• user-independent, computer-generated, time-stamped audit trails

• system and data security, data integrity, and confidentiality through limited authorized system access

• binding electronic or handwritten signatures to electronic records

• secure retention of accurate and complete electronic records.

Compared to previous interpretations, FDA's final guidance on 21 CFR Part 11 narrowed its scope to records that are required by predicate rules and can have a high impact on product quality and patient safety.^4,5 Records generated by M&S calculations fall into this category, for example, if they are used for batch release in pharmaceutical manufacturing.

Figure 1. Automated Limit Checking on Custom Calculated Results in a Networked Data System8

Commercial spreadsheet programs such as Microsoft Excel do not have all of the necessary functionality for Part 11 compliance. For example, Excel does not generate a Part 11-compliant electronic audit trail. There are three possible ways to address this compliance problem.

Dedicated software. Perform the tasks using software that better enables users to comply with regulations. Many laboratory data systems offer dedicated custom calculators as an integral part of their data analysis functionality. Some custom calculators even have spreadsheet functionality compatible with Excel. This approach is used in some networked chromatography data systems, and it allows implementation of method-specific custom calculations. Applications range from simple calculations, such as relative impurity calculations and so-called "response-factor" statistics (for the evaluation of calibration precision), to more complex calculations. There are also advanced packages that allow calculations with custom values and perform dynamic limit checks based on user-defined acceptance criteria (Figure 1). This approach eliminates the necessity of manually transcribing numeric values between different computer applications and the risk of introducing precision errors while transcribing rounded numbers formatted for a specific report. Integrated custom calculators benefit from revision control, data integrity, access security, and audit trail functionality provided by commercial off-the-shelf (COTS) laboratory data packages. This reduces the scope of validation and qualification tasks for custom calculations to dedicated test cases for the laboratory data package.

Add-on software. Evaluate "add-on" software packages that better enable users of spreadsheet programs to comply with regulations.

Existing procedure improvement. Change the way spreadsheets are developed and used. Apply all available built-in functions of the spreadsheet program to increase compliance.

This article will focus on improving existing procedures to increase Part 11 compliance. Although it is important, we will not discuss M&S validation. We recommend using commonly described validation practices described elsewhere.^6,7

Current Compliance Problems

Before making recommendations, we will explain the problems associated with using macros and spreadsheets in a regulated environment.

Access Security. Almost everybody can access the programs. Spreadsheet software is installed on most personal computers and servers. Often, it is accessible to all users.

Personnel Qualification. Anybody can develop spreadsheet calculations and perform other spreadsheet tasks. Although spreadsheet calculations are considered software, developing these calculations does not require software development skills. Formulas can be added to spreadsheets using simple menu functions.

Change Control. Formulas and other functions of spreadsheets are easy to change and almost anyone can do so. Often, such changes are not documented, and no testing is done afterwards.

Validation. There is little awareness that spreadsheet calculations should be validated. This is especially true for "ad hoc" calculations where a user defines a formula and performs a calculation for a specific, one-time task.

Documented Evidence. Frequently, users of spreadsheet calculations are also the developers. Sometimes the developer is the only user. It may not be obvious why a process that only the developer will use needs documentation.

Change Control: Environments. Spreadsheets are developed on a specific computer configuration with a specific operating system and version of the spreadsheet program. However, they are distributed and used on computers with different configurations, operating systems, and spreadsheet versions.

Change Control: Files. Different versions of spreadsheets may be located in different areas of a computer system: a local PC, a server, or in different folders within these locations.

Regulatory Compliance. Typically, spreadsheet programs do not comply with all relevant regulations. In particular, 21 CFR Part 11 provides the most difficult compliance challenge.

Quality Management. The quality assurance department may not know that spreadsheets and macros are used for GxP work.

Quality and Compliance

Bringing spreadsheet programs and macros into compliance requires three major steps:

• providing the infrastructure and atmosphere and developing policies and procedures for development, use, and documentation of such programs

• validating spreadsheet calculations and macros following documented procedures

• using security and other functions built into the programs such as Excel.

Attitudes towards spreadsheets and macros should be changed to emphasize quality and compliance. This requires a company policy, a master plan, system inventories, and procedures for development and use of M&S calculations.

Inventory Control Example

Company Policy. A company should have a policy for development and use of macros and spreadsheets. This can be either part of a company's compliance or quality policy, or it can be a stand-alone document. The policy should be supported by management and communicated to all employees. It should include statements such as:

• Development and use of all macros and spreadsheets used in a GxP environment should follow standard operating procedures.

• All systems using M&S calculations should be registered in a database.

• All macros and spreadsheets should be validated following standard procedures.

• Changes should follow established change control procedures.

• Security and other spreadsheet program functions relevant to compliance should be applied.

• Macros and spreadsheet programs should be protected against accidental and incidental changes.

Compliance Master Plan. While the company policy dictates what needs to be done, a master plan guides implementation of the policy. The master plan should include examples that help elucidate the policy. The compliance master plan should include:

• a list of which spreadsheets must follow the policy

• templates for system inventory

• assignment of responsibilities

• a list of SOPs for implementation

• a list of generic requirements for macros and spreadsheets (for an example, see "Recommendations," below).

System Inventory. All M&S calculations should be registered in a database. Entries should include information on the spreadsheet program, the operating system, computer hardware, the application, and a list of authorized users (see "Inventory Control Example"). It is very important to update the information with any changes, for example if additional users are added.

Standardize Hardware and Software. Standardizing operating systems and spreadsheet programs (including their version) helps reduce:

• required user information for instal-lation and use

• validation effort

• support during installation and use

• errors due to incompatibility of versions.

For example, all users should switch from one version of an operating system and spreadsheet program to the next one within a given time frame. PC hardware is less critical, but many organizations also standardize on selected vendors and models for cost and support reasons.

Standardize Software Development. Development of macros and spreadsheets should be standardized as much as possible. The following are some of the items that should be standardized:

• design conventions, such as dropdown lists

• name conventions, such as rt_data_entry, std_results

• code conventions

• code annotations, such as comments in the header

• formulas for specific tasks

• colors for cells, such as using the same colors for entry fields (yellow) and alerts (green)

• spreadsheet annotations (such as file name, version)

• validation and testing.

Routine Use. Most errors made in M&S applications are not due to poor programming or insufficient testing but arise from improper use during routine work. There are multiple reasons this may occur.

• New users are working with the spreadsheets without training.

• Unauthorized users are working with the spreadsheets.

• Users input changes to the worksheet, and save the file under the same name.

• Users enter erroneous data.

Fortunately, these problems can be overcome or minimized. Because spreadsheet programs are so common and easy to use, users may think they don't need any training. While this may be true for the program, it is not always true for specific workbooks. Prior to using such workbooks, users should at least read instructions on what can go wrong when working with the worksheet.

Spreadsheets should be located on a server that limits access to authorized users. Whenever possible, store and retrieve macros on a secure server.

Procedures should be in place so that only original M&S can be used. This can be achieved through a combination of the following procedures:

• Allowing read-only access prevents the spreadsheet from being modified and saved under the same name in the same directory.

• Protecting cells that contain formulas is necessary to ensure accurate results.

• Displaying the file name and the directory on all spreadsheets (including printed outputs) alerts users if they are working in a spreadsheet that has been saved under the same name in a different directory or under a different name.

Minimizing or preventing incorrect data entry can be achieved through program design by using drop-down lists and implementing entry checks for data type and ranges.

Documentation. Correct and complete documentation is important for inspections. It also allows people who have not been involved in development and testing of the M&S to understand and review the procedures. The following documents should be available:

• company policy or quality plan for macros and spreadsheets

• company validation master plan

• with information on macros and spread-sheets

• SOPs for using macros and spreadsheets in a regulated environment, for validation of macros and spreadsheets, and for change control

• system inventory listing authorized users, locations of computer hardware, system software, spreadsheet program and version, spreadsheet file names and versions

• validation project plan

• requirement specifications

• design specifications

• design review reports

• code with annotations

• code review reports

• test plans and protocols

• installation reports

• change control reports

• history logs

• validation reports

• Part 11 assessment, gap analysis, risk assessment and active correction plan.

Implementation Summary

Although it is impossible to avoid all M&S errors, they can be minimized by following these recommendations. Although some spreadsheet programs don't have all the functions required for 21 CFR Part 11 compliance, a fully functioning quality system, a gap analysis, and an active correction plan can be of tremendous help in preparing for FDA inspections.

Recommendations

References

1.

Panko RR. What we know about spreadsheet errors.

Journal of End User Computing

1998; 10 (2):15-21. Also available at URL:

panko.cba.hawaii.edu/ssr/Mypapers/whatknow.htm

.

2. FDA. Code of Federal Regulations, Title 21, Food and Drugs, Part 11: Electronic records; electronic signatures; final rule. Federal Register 1997; 62 (54):13429-13466.

3. Implementing 21 CFR Part 11. Supplement to Biopharm International, February 2004.

4. FDA. Guidance for industry: Part 11, electronic records; electronic signatures - scope and application. (Draft February 2003, Final version August 2003). Available at URL: www.fda.gov/cder/guidance/5667fnl.pdf.

5. Winter W, Huber L. Part 11 is not going away: the new electronic records draft guidance. Biopharm International 2003; 16 (5):28-34.

6. ISPE. The good automated manufacturing practices (GAMP) guide for validation of automated systems in pharmaceutical manufacture, GAMP 4. Brussels: ISPE; 2001.

7. Huber L. Validation of computerized analytical and networked systems. Boca Raton (FL): Interpharm Press; 2002.

8. Agilent Technologies. Cerity networked data system for pharmaceutical QA/QC specifications A.02.01 and higher. Agilent publication 5988-8418EN. Palo Alto (CA): Agilent, 2003.