Standard Operating Procedure

Title: Guideline for the Validation of Excel Spreadsheets

Department Validation/Technical Services Document no VAL-170

Prepared by: Date: Supersedes:
Checked by: Date: Date Issued:
Approved by: Date: Review Date:

1 Purpose
The purpose of this document is to provide guidelines for a suitable approach for the qualification of Excel
spreadsheets used in direct, or in-direct, GxP related activities at all GMP facilities.

2 Scope
The procedure applies to the initial qualification and re-qualification of Excel spreadsheets, designed and used
for making quality based decisions in GxP environments at the GMP facility. These areas include but are not
limited to:
 Quality Operations
 Manufacturing Operations
 Development
 Planning
 Regulatory Affairs
The scope does not include those spreadsheets which are single use only (prepared for a specific, one off
tasks) or those which are used for organisation of personal, or departmental work plans. These should be
verified at each time of use as appropriate.

3 References
 Good Automated manufacturing Practice Guide (GAMP), Version 4, ISPE
 David Harrison and David A Howard, A Pragmatic Approach to the Validation of Excel Spreadsheets,
Pharma IT Journal, Vol1 No.4 October 2007

4 Introduction
Spreadsheets can be used to record and manipulate (change, delete, add) GxP data and as such need to be
managed to ensure the continued integrity and security of that data. While some are no more than fancy
calculators others are embedded with special features and use intricate logic in the form of Macros that are in
effect computer programs.
Spreadsheets can be classified into GAMP categories depending on use and contents of the spreadsheet.
This will impact the level of validation effort that is applicable just as with other computerized systems.
Spreadsheets which fall into GAMP Category 3 guidelines (Table 1) do not require full validation activities but
should be held under appropriate security conditions.

Copyright©www.gmpsop.com. All rights reserved

Unauthorized copying, publishing, transmission and distribution of any part of the content by electronic means are
strictly prohibited. Page 1 of 11
Standard Operating Procedure
Title: Guideline for the Validation of Excel Spreadsheets
5.2 Spreadsheet Application Development
The Spreadsheet Application Developer will devise the spreadsheet application parameters, such as the way
the table looks, data formats, data sorting parameters, the addition of fields to the spreadsheet application,
reports, formulas and interfaces with other programs and instrumentation.

5.2.1 Raw Data Considerations

The Spreadsheet Application Developer must define how data will be entered.
Data can be inputted directly from instruments (or data files) into a spreadsheet application or data can be
entered manually. For automated operations, raw data will be input directly from the instrument to the
spreadsheet application.
If data sets are entered manually, all entered data must be reviewed by a second individual to verify the
correctness of the entered (transcribed) data. The initials of the reviewer and the date of review should be
indicated.
If data sets are entered automatically through an electronic interface, then the transfer link between the
spreadsheet application and the source of the data must be validated.
Similarly, data from a spreadsheet must not be exported to a validated computer system/application unless the
transfer link has been validated and the importation approved via change control for the receiving system.

5.2.2 Formula Development

Each formula described in the Requirements Documents must be embedded in the spreadsheet application.
The formulas must be checked for errors during development.
Diagnostic tools that allow error checking to be carried out during development are sometimes available with
spreadsheet programs.
Examples of error detection tools include:
 Cell errors – error messages that are created when cell rules are violated
 Auditing tools - Cells which are referred to by a formula in another cell may be audited for alignment,
with the spreadsheet displaying tracer arrows between cells
 Error tracer - when a formula returns an error, another auditing tool can be used to track it back to its
source
5.2.3 Rearranging Columns and Rows
Spreadsheets are also used to sort and rearrange data for reports and presentations. The source of the data
may be from another program/database, or an instrument system such as report data from a chromatographic
computer system. The procedures to be used for subsequent sorting or rearranging of the data via the
spreadsheet application should be described.
5.2.4 Macro Programming
Frequently performed tasks may be automated by recording them as macros and playing them back whenever
necessary. Macros may be associated with toolbar buttons, keystrokes and menu entries. Manual sorting and
rearranging of data are actions that might be automated, but if the macro is recorded, the actions must be
carefully documented during development and tested during the qualification process.
5.2.5 Report Creation
It is the responsibility of the developer to create/modify the spreadsheet application to report the results of
calculations performed by the spreadsheet application.
The Spreadsheet Application Report shall contain:
 The method (analytical if for a lab) used during creation of data

Copyright©www.gmpsop.com. All rights reserved

Unauthorized copying, publishing, transmission and distribution of any part of the content by electronic means are
strictly prohibited. Page 3 of 11
Standard Operating Procedure
Title: Guideline for the Validation of Excel Spreadsheets

5.3.2 Operation/ Perfomance

The Operational/ Performance Qualification shall be written to document the instructions for the testing. The
Operation Qualification shall include the following tests:
 Calculation Test to verify that correct formulas are entered
 Logical Values Test to verify Boolean algebra formulas.
 Range/Precision Test to verify calculations will still be accurate when extremely large or small values
are entered
 “Challenge condition” testing for the program and application (e.g., invalid inputs, stress testing)
 Boundary and limit checking, failure analysis and confirmation of appropriate error trapping.
Acceptance criteria shall be established for each test and written into the Operation Qualification Protocol.
Typical tests with associated acceptance criteria are:

5.3.2.1 Calculation Test

These are performed to verify that correct formulas are entered. Enter sample values in all fields that are
included in the input range of all mathematical functions. Restrict the input to whole numbers at this time
for aggregate functions (sum, max, etc.) use different values for each field. Document the step, include a
printout of the spreadsheet application indicating the test values, and compare the printout to that
obtained from a calculator.
Acceptance Criteria
Calculated values from the spreadsheet application must be identical to those obtained from a calculator
(preferably with printout capabilities).

5.3.2.2 Logical Values Test

These are performed to verify Boolean algebra formulas. Print the spreadsheet application cells
containing Boolean (logical values) and calculate the results manually. Print the results from the
spreadsheet application calculation.
Acceptance Criteria
For logical values (e.g. Pass/Fail or True/False) verify the displayed value is the same as the expected
value, calculated manually.

5.3.2.3 Range/Precision Test

These are performed to verify calculations will still be accurate when extremely large or small values are
entered. Enter extremely large values at the correct precision (e.g. enter 99.99 for 0.01 precision). For
aggregate functions, enter 0 in one or more fields with non-zero values in other fields. Duplicate the
formula calculations using a calculator and compare the results/printouts. Repeat this test with extremely
small values, and report the results.
Acceptance Criteria:
Calculated values from the spreadsheet application must be identical to those calculated manually using
a calculator (with correct precision and consistent with agreed Rounding principle).

Copyright©www.gmpsop.com. All rights reserved

Unauthorized copying, publishing, transmission and distribution of any part of the content by electronic means are
strictly prohibited. Page 5 of 11
Standard Operating Procedure
Title: Guideline for the Validation of Excel Spreadsheets
6 Usage of Spreadsheet Applications

6.1 Availability of Spreadsheet Application:

The Spreadsheet applications shall be protected from unauthorized modification of the master copy. For
example, the spreadsheet application shall be available to authorized users as “read only”.
Authorized users will be given access after they have been trained on the use of the spreadsheet application
(which shall include proper GMP handling of the files and documentation practices).
Where networks are employed for spreadsheet application use, spreadsheet applications will typically be
stored in a network directory that allows access by authorized users. In this instance, the storage of a copy of
the spreadsheet application on the hard drive of the local computer shall be forbidden by policy.

6.2 Training of Users

Users shall be trained to use the spreadsheet application. As appropriate, training objectives shall include
learning:
 How to use the spreadsheet application, including how to print the Spreadsheet Application Report
 The procedure for storing Spreadsheet Application Reports (if applicable)
Training may be conducted earlier based on a preliminary review of the Final Report.

6.3 Storage of Completed Electronic Files:

When data have been entered into the spreadsheet application, the calculated results become a new
electronic file.
If site policy requires storage of an electronic copy of the spreadsheet application file, the file should be stored
under a new name in a designated area of the LAN server.
 Files should be named using a convention agreed between the user and site Quality Assurance
 The file name of the completed spreadsheet application electronic report should be recorded in notebook
or other similar documentation system as a cross-reference.
 During review of notebook or other similar documentation system data, the reviewer should assure that
the electronic file has been stored correctly.

7 Re-qualification of Spreadsheet Application Formulas

Spreadsheet applications in the library will be reviewed / verified every 3 years to address the validity of the
validation documentation which includes formula integrity. Reviews will be managed via the Validation
Manager program.
Re-qualification may also be triggered by the installation of an upgrade of the spreadsheet program (Change
Control System), or by changes in the LAN (e.g., system crashes).

8 Change Control:
If changes are required in a spreadsheet application, it is the responsibility of the Department Manager to
manage the change using the site Change Control procedures.

Copyright©www.gmpsop.com. All rights reserved

Unauthorized copying, publishing, transmission and distribution of any part of the content by electronic means are
strictly prohibited. Page 7 of 11
Standard Operating Procedure
Title: Guideline for the Validation of Excel Spreadsheets

Spreadsheet A printed results report arising from calculations completed by a spreadsheet application
Application Repo

Spreadsheet An individual, who is sufficiently familiar with spreadsheet software and the task(s) to be
Application automated, to carry out the development of the spreadsheet application. This individual
Developer may be qualified by means of either work experience or training, or both.

Spreadsheet A person with the skills in spreadsheet development, the knowledge of the spreadsheet
Application program, and experience in developing spreadsheet applications for use in their
Specialist department.

Spreadsheet Application Software, typically Microsoft Excel.

Program

Version Number A number assigned by a software developer to identify a particular program at a particul
stage, both before and after public release. Successive public releases of a program are
assigned increasingly higher numbers. Version numbers usually include decimal fractio
Major changes are usually marked by a change in the whole number, whereas, with min
changes only the number after the decimal increases.

Copyright©www.gmpsop.com. All rights reserved

Unauthorized copying, publishing, transmission and distribution of any part of the content by electronic means are
strictly prohibited. Page 9 of 11