The pharmaceutical industry relies on Microsoft Excel very much for several GMP related activities such as calculating assay results, generating calibration curves, storing stability data and performing various other types of GMP related calculations.
制药行业在多项 GMP 相关活动中高度依赖 Microsoft Excel，例如含量测定结果计算、绘制校准曲线、稳定性数据存储，以及执行各类其他 GMP 相关计算。
Although Excel provides flexibility and speed but it can also introduce compliance risks, if not appropriately validated.
尽管 Excel 灵活高效，但如果未经过适当验证，也会带来合规风险。
Excel Sheet Validation Excel 表格验证
Regulatory authorities including the USFDA, MHRA and World Health Organization (WHO) expect that organizations using Microsoft Excel to create spreadsheets in GMP facilities classify these spreadsheets as a GxP relevant computerized system and subsequently validate these spreadsheets for accuracy, dependability and data integrity.
包括美国 FDA、英国药品和医疗产品监管署（MHRA）及世界卫生组织（WHO）在内的监管机构要求：在 GMP 环境中使用 Excel 创建表格的企业，应将此类表格归为 GxP 相关计算机化系统，并对其进行验证，确保准确性、可靠性与数据完整性。
This article provides an overview of what it means to validate a spreadsheet using Excel, why it is important for pharmaceutical companies and how to validate an excel spreadsheet in a manner that is effective for pharmaceutical operations.
本文概述了 Excel 表格验证的含义、对制药企业的重要性，以及如何采用适合制药运营的有效方式完成 Excel 表格验证。
What Is Excel Sheet Validation? 什么是 Excel 表格验证？
Validation of Excel sheets is the procedure used to confirm the functionality of an Excel sheet meets the expectations for accurate calculations without errors and comply with regulatory requirements for quality system.
Excel 表格验证是用于确认表格功能满足无差错准确计算要求、并符合质量体系法规要求的流程。
Validation provides documented proof for an Excel sheet that it correctly completes calculations, protected from unauthorized changes to its functions, provides accurate data input and traceability and holds to the requirements set forth in 21 CFR Part 11 and Annex 11 regarding electronic data and data integrity.
验证为 Excel 表格提供书面证据，证明其可正确完成计算、功能免受未经授权修改、数据录入准确且可追溯，并满足 21 CFR Part 11 与附录 11 中关于电子数据和数据完整性的要求。
Simply stated, Excel sheet validation indicates that the numbers you calculate are correct, reproducible and secured from being manipulated.
简单来说，Excel 表格验证意味着：你计算出的数值是正确、可重现、且不会被随意篡改的。
The Importance of Validating Excel 验证 Excel 的重要性
Excel is a powerful tool, however Excel can be a source of error if not properly used.
Excel 是强大的工具，但使用不当也会成为错误来源。
For example, a small formula error In Excel could produce incorrect results for the pharmaceutical industry, this could lead to several issues with a product release, a validated essay or compliance with regulatory guidelines.
例如，Excel 中一个微小的公式错误就可能产生错误结果，在制药行业中可能导致产品放行、已验证分析方法或法规合规性等一系列问题。
Regulatory Expectations 法规要求
USFDA 21 CFR Part 11 - This regulatory requirement states that any computer system developed to generate, maintain or utilize GMP data must be validated to ensure it produces accurate, consistent and reliable information.
美国 FDA 21 CFR Part 11：该法规要求，任何用于生成、保存或使用 GMP 数据的计算机系统都必须经过验证，确保产生准确、一致、可靠的信息。
EU-GMP Annex 11 - This regulatory guidance states that businesses must validate any spreadsheet that generates and stores GxP data.
欧盟 GMP 附录 11：该法规指南要求企业必须对生成和存储 GxP 数据的所有表格进行验证。
WHO TRS 1019 Annex 4 – This regulatory guidance states that you must validate any software being used for recording, calculating or controlling your data.
WHO TRS 1019 附录 4：该法规指南要求，必须对用于记录、计算或控制数据的任何软件进行验证。
In summary, any Excel sheet being used to create, calculate or report GxP data must be validated.
总而言之，所有用于创建、计算或报告 GxP 数据的 Excel 表格都必须验证。
When to Validate an Excel Spreadsheet 何时需要验证 Excel 表格
Whenever an Excel spreadsheet is used in one of these ways, validation should be done.
当 Excel 表格用于以下用途时，必须进行验证：
- Analytical Assay, Dissolution, Content uniformity results
- Calibration and developing standard curves
- Stability trending and expiration projections
- Equipment qualification data
- Cleaning validation data calculations
- Batch manufacturing, packaging calculations for product records
- Records impacting the decision to release products or quality of the product
Spreadsheets used for non GxP administrative purposes like scheduling, HR tracking etc. do not require validation.
用于非 GxP 行政用途（如排班、人事跟踪等）的表格无需验证。
Validation Approach 验证思路
Excel validation follows the same process used for all other computerized system validations that includePlan → Build → Test → Authorize → Maintain.
Excel 验证与其他计算机化系统验证流程一致，包括：策划 → 建立 → 测试 → 批准 → 维护。
Step 1: Validation Planning 步骤 1：验证策划
In first step of validation, we have to create a validation plan or protocol that defines:
验证第一步需制定验证方案，明确：
- Purpose and Scope of the Excel Spreadsheet
- Description of the Intended Use of the Excel Spreadsheet
- Roles and Responsibilities Associated with Excel Spreadsheet Validation
- List of Deliverables Related to Validation of Excel Spreadsheet
- Acceptance Criteria for Validation of Excel Spreadsheet
- Change Control and Periodic Review Requirements for Excel Spreadsheet
The validation protocol is a document that communicate to all stakeholder holders how the process of developing, testing and maintaining the Excel spreadsheets will be carried out.
验证方案是向所有相关方说明 Excel 表格开发、测试与维护流程如何执行的文件。
Step 2: User Requirement Specification (URS)
步骤 2：用户需求标准（URS）
The URS provides descriptions of what the spreadsheet needs to achieve. The URS should include:
URS 描述表格需要实现的功能，应包含：
- Data input, calculation and output
- Reports the expected accuracy and range.
- Describes data protection and access control requirements.
- Describes audit trails or version control.
- Describes format and layout expectations.
For example: 例如
The excel sheet will calculate an assay percentage automatically based on the weight of sample, its dilution, its potency and its area ratio.
该 Excel 表格将根据样品称样量、稀释倍数、效价与峰面积比值自动计算含量百分比。
Step 3: Design and Development 步骤 3：设计与开发
Based on the user requirement specification (URS), the first step is to create an Excel spreadsheet.
基于用户需求标准（URS），首先创建 Excel 表格。
The following are examples of good design principles for the spreadsheet:
表格良好设计原则示例：
- Using cell references rather than entering hardcoded numeric values.
- Implementing data validation to prevent invalid data entry.
- Locking and securing the cells that have formulas in them.
- Labeling inputs, calculations and output areas separately.
- Using the same units of measure and decimal format throughout the spreadsheet.
- Incorporating the spreadsheet version number and date in the sheet header.
A well-designed Excel spreadsheet will limit the user’s potential for error and make it easier to validate data.
设计良好的 Excel 表格可降低用户出错概率，便于数据验证。
Create the Calculation Sheet as follows.
按以下要求创建计算表：
Prepare the Excel calculation sheet in “Microsoft Excel”
使用 Microsoft Excel 制作计算表
Prepare separate calculation sheet for individual tests, which can be common for all products.
为每个检验项目单独制作计算表，可适用于所有产品。
The typical calculation sheet should include：name of the organization, name of the test, name of product, batch No, AR No, and wherever applicable,standard preparation (Weight of the standard, dilutions, potency of standard)Average standard area /absorbance, labelled amount, Average weight, conversion factor (if applicable)Sample preparation (Weight of sample, dilutions) and sample area /absorbance.[In case of Related Substances, include retention time, relative retention time, name of the impurity etc]
典型计算表应包含公司名称、检验项目名称、产品名称、批号、分析报告号（如适用），标准品配制（标准品称样量、稀释倍数、标准品效价）标准品平均峰面积 / 吸光度、标示量、平均重量、换算系数（如适用）样品配制（样品称样量、稀释倍数）、样品峰面积 / 吸光度。【有关物质项下需包括：保留时间、相对保留时间、杂质名称等】
For dilutions of the standard solution, use prefix “S” and serial numbers.
标准品溶液稀释使用前缀 S + 序号。
For dilutions of the sample solution, use prefix “T” and serial numbers.
溶液稀释使用前缀 T + 序号。
Consider the maximum possible dilutions of the solutions and give provision for it.
考虑溶液最大可能稀释度，并预留相应位置。
Type the formula correctly in the cell where the result will appear.
在结果单元格中正确录入公式。
Fill the grey color in the cells that need to be filled while calculating the results.
将计算时需手动录入的单元格标为灰色。
Protect the excel sheet except for the cells that need to be filled while calculating the results.
保护 Excel 表格，仅开放计算所需录入单元格。
Protection of excel calculation sheet shall be done by Assistant Manager QA.
Excel 计算表保护由 QA 助理经理执行。
Step 4: Verification of Formulas and Logic 步骤 4：公式与逻辑核对
Each formula in the spreadsheet must be verified manually to check for correct logic and mathematical calculations.For each formula:
表格中每个公式都必须人工核对，确认逻辑与数学计算正确。对每个公式：
- Check the function syntax and logic (for example, SUM, AVERAGE, IF, ROUND).检查函数语法与逻辑（如 SUM、AVERAGE、IF、ROUND）
- Check the relative and absolute references used correctly.
- Verify results from the spreadsheet using a calculator.
- Check that there are no rounding issues or errors and confirm the number of significant digits.
All formulas and calculations must provide expectations for results within the range of the input.
所有公式与计算结果必须在输入值范围内符合预期。
Step 5: Conduct Functional Testing 步骤 5：功能测试
Functional testing tests the functional operation of the worksheet. This includes:
功能测试用于验证工作表功能运行情况，包括：
- Testing of results on extreme value inputs
- Testing of error messages if invalid values are entered into a worksheet.
- Testing of automated results based upon entered data into the worksheet.
- Testing of protected and locked cells (usually used for formula/accounting based testing) in the workbook.工作簿中受保护 / 锁定单元格测试（多用于公式 / 计算类）
- Testing using typical copying and moving of cells and ensuring that no formula is affected; i.e., copying of formulas should create duplicate formulas.常规复制、移动单元格测试，确保公式不受影响；即公式复制后应保持一致
- Testing of print layout and report formatting of worksheet.
Records of all functional testing activities should be documented in a testing protocol.The test protocol will provide documentation of all functional test procedures, which include input data, expected results, and actual outcomes, for the testing performed.
所有功能测试活动均应记录在测试方案中。测试方案记录所有功能测试过程，包括输入数据、预期结果、实际结果。
Step 6: Security and Access Control 步骤 6：安全与权限控制
To maintain data integrity, the Excel spreadsheet must be protected against unauthorized modifications.Recommended access and security controls are:
为保证数据完整性，Excel 表格必须防止未经授权修改。建议的权限与安全控制措施：
- All calculation cells must be locked, the worksheet must be password protected.
- Valid Excel workbooks must be stored on a network or dedicated server, not on an individual local hard drive.已验证 Excel 文件必须存储在网络或专用服务器，而非个人本地硬盘
- Only those users with authorized user rights will have editing access to the workbook.
- Version Control - Each new version of the Excel workbook must be validated.版本控制 ——Excel 文件每一新版本都必须验证
- File audits - If the Excel workbook files are stored in a controlled environment such as SharePoint or LIMS, audit trail logging capability should be enabled.文件审计 ——若 Excel 文件存储在受控环境（如 SharePoint、LIMS），应启用审计追踪功能
If the spreadsheet is used for GMP-critical data, it is highly recommended that the worksheets be maintained in a read-only and/or controlled Document Management System (DMS).
若表格用于 GMP 关键数据，强烈建议在只读 / 受控文档管理系统（DMS）中管理。
Procedure for Protecting an Excel Calculation Sheet.Select the cells to be filled in for calculation. Right click on the selected cell and click on Format Cells and select the Protection tab in the window opened. Uncheck the box next to Locked.
Excel 计算表保护步骤1: 选中计算时需要录入的单元格 → 右键设置单元格格式 → 保护 → 取消勾选 “锁定”。
Important: After protecting the calculation sheet, only the cells that were left unlocked during this procedure can be edited.
重要：保护表格后，仅上述未锁定单元格可编辑。
- 2. From the Review menu click on Protect Sheet option. Enter your password and press OK.You will then be prompted to confirm your password by re-entering it in the Confirm Password box; make sure to enter the same case-sensitive password here.
- 点击【审阅】→【保护工作表】→ 输入密码 → 确定。按提示再次输入密码确认，注意密码区分大小写。
- 3.Do the same process for all cells that will need to be filled in during calculations and fill these with a grey background color.
- 对所有计算需录入单元格执行相同操作，并将其背景色设为灰色。
Step 7: Documentation 步骤 7：文件记录
Validation must be fully documented and should include:
验证必须完整文件化，包括：
- Validation Protocol/Plan 验证方案 / 计划
- User Requirement Specifications (URS) 用户需求标准（URS）
- Risk Assessment (if applicable) 风险评估（如适用）
- Verification & Testing Results 核对与测试结果
- Validation Summary Report (VSR) 验证总结报告（VSR）
- Controlled Access to Approved Master Copy (Protected/Version-Controlled)
- Standard Operating Procedure (SOP) for Spreadsheet Creation, Validation and Change Control
The Validation Summary Report should provide a summary of all test results, any deviations and final approval for the validated item (s).
验证总结报告应汇总所有测试结果、偏差及验证项目的最终批准。
Risk Assessment for Spreadsheets 表格风险评估
The following guidelines outline how to perform risk assessments on Excel spreadsheets.
以下指南概述如何对 Excel 表格进行风险评估。
All Excel spreadsheets do not need an equal level of validation and a risk-based strategy should be used to know the requirement of spreadsheet validation.
并非所有 Excel 表格都需要同等验证程度，应采用基于风险的策略确定验证要求。
- High Risk - Assay calculation sheet for product release - Full validation (URS, test scripts, security, audit trail)高风险 —— 产品放行含量计算表 → 完整验证（URS、测试脚本、安全、审计追踪）
- Medium Risk - Calibration record tracking sheet - Partial validation
- Low Risk - Administrative or training tracker - No validation required
The higher the risk of affecting product quality or compliance, the more extensive the validation will be required for the spreadsheet.
对产品质量或合规性影响风险越高，表格验证要求越严格。
Change Control and Periodic Review 变更控制与定期回顾
Any changes (e.g. modifications to formulas, layouts, or the addition of other fields) identified with respect to an excel spreadsheet need to result in the assessment of impact and partially or completely re-validated through the use of a controlled change process.
Excel 表格任何变更（如公式修改、布局调整、新增字段等）均需进行影响评估，并通过受控变更流程进行部分或全部重新验证。
Periodic Review 定期回顾
Excel spreadsheets need to be reviewed periodically (typically 1 – 2 years) to ensure that:
Excel 表格需定期回顾（通常 1–2 年），确保：
- The Logic of the Excel spreadsheet still meets the current requirements;
- There are no unauthorized changes to the Excel spreadsheet;
- Current formulas, references, and protections are still intact/maintained.
Common Deficiencies Observed by Auditors 审计官常见缺陷观察
Auditors have noted the following deficiencies as issues that are common for inspectors (GMP).
审计官在 GMP 检查中常见以下缺陷：
- GMP calculations are completed on unverified excel spreadsheets.
- There is no version control or change tracking.
- Formula mistakes are made, and the lack of proper validation resulted in the formula mistake not being detected.
- The calculation spreadsheets have unlocked cells which allow for the unauthorized changing and manipulation of data.
- The validation process has not been documented and not reviewed.
- Spreadsheets are saved on employee computers without any back-up or control.
All of the above points create potential data integrity problems and can potentially generate regulatory citations.
以上所有问题都会造成数据完整性风险，并可能导致法规缺陷项。
Example Structure of a Validated Excel Sheet
已验证 Excel 表示例结构
Well-designed validated Excel spreadsheets include a header (name, version, date, approval, and purpose).
设计良好的已验证 Excel 表格包括：表头（名称、版本、日期、批准、用途）
- An input section (user entry cells are clearly marked white/yellow).
- A calculation area (formulas are protected/hidden with a gray background).
- An output section (results), and a footer (signature, date, remarks).
These elements help reduce errors and improve the user experience, as well as facilitate validation of an Excel spreadsheet.
这些结构有助于减少错误、提升用户体验，并便于 Excel 表格验证。
Validation of Excel Calculation Sheets is important to ensure accuracy, integrity and compliance in pharmaceutical Manufacturing and Testing.
Excel 计算表验证对确保制药生产与检验的准确性、完整性与合规性至关重要。
GMP critical Excel calculation sheets should be considered like any other computer-based system.
GMP 关键 Excel 计算表应视同其他计算机化系统管理。
Validation, documentation and control is critical to ensure that all Excel calculation sheets are verified and tracked throughout the life cycle.
验证、文件化与控制是确保所有 Excel 计算表在全生命周期内得到确认与追踪的关键。
Pharmaceutical Companies may use a structured process to validate Excel calculation sheets, including; User Requirement Specification (or URS) preparation, verify all formulas, functional testing, documentation, change control, etc.
制药企业可采用结构化流程验证 Excel 计算表，包括：制定用户需求标准（URS）、核对所有公式、功能测试、文件记录、变更控制等。
By using this structured approach, pharmaceutical companies can rely on the use of Excel for accurate calculations without losing their ability to maintain compliance to the regulations.
通过该结构化方法，企业可在依赖 Excel 进行准确计算的同时，持续满足法规合规要求。
Validated Excel calculation sheets are not only a method of preventing costly mistakes in today's data-driven GMP environment but validated Excel calculation sheets will also provide an additional layer of regulatory confidence and increased operational efficiency for all users.
在当前数据驱动的 GMP 环境中，已验证 Excel 计算表不仅可避免代价高昂的错误，还能为企业提供额外的法规信心，并提升所有使用者的运行效率。
注：关注公众号，公众号已经有"ima 数据库”了，可在众号内点击“数据库”获取二维码，扫码加入ima数据库进行提问呦
