OR WAIT 15 SECS
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.
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%.1
Records generated by M&S calculations are electronic records and therefore must comply with FDA's 21 CFR Part 11.2 Requirements of the rule and its implementation in laboratories have been discussed in depth elsewhere.3
The primary requirements of 21 CFR Part 11 are:
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
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.
Bringing spreadsheet programs and macros into compliance requires three major steps:
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:
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:
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:
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:
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.
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:
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:
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.
Panko RR. What we know about spreadsheet errors.
Journal of End User Computing
1998; 10 (2):15-21. Also available at URL:
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.