Journal of Chemical Health Risks

[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Development and Validation of Calculation Data Sheets to Ensure the

Accuracy and Security of Pharmaceutical Data Through Qa Procedures
(Qaps)
Author Names: Ankita Bhadoria1, Kuldeep Vinchurkar*2, Pankaj Kumar Pandey1, Manoj Kumar Likhariya1, Shreesh
Tiwari3, Juhi Bhadoria1
Lakshmi Narain College of Pharmacy (RCP), Indore, M.P.
Krishna School of Pharmacy and Research, (Formerly BITS Edu Campus), Vadodara, Gujrat
Chameli Devi Institute of Pharmacy, Indore, M.P.

(Received: 07 October 2023 Revised: 12 November Accepted: 06 December)

KEYWORDS ABSTRACT:
Quality, Data Introduction: Quality stands as an essential prerequisite in the evaluation of any product and especially true for
Data Integrity pharmaceuticals, for stringent standards of quality during their manufacturing process. In this consideration, the
Validation Food and Drug Administration introduced good manufacturing practices (GMP) and viewed validation as an integral
Spreadsheets part of cGMP. Validation studies have been conducted for a long time in industries. In recent times to better the
GMP quality of pharmaceutical products, industries emphasize quality assurance programs.
GDP ALCOA
Objectives: This research has introduced a simplified and efficient method for validating Excel spreadsheets. The
sheets are designed to fulfil the regulatory requirement, easy to use and developed itself by the users to assure the
accuracy, integrity and quality of their data. This sheet should be validated and strictly follow the GMP and FDA
21 CFR part 11 regulations.

Methods: All the sheets are designed in Excel- 2013 following the regulatory requirements. The data is collected
from the analytical laboratories following the standard procedures to perform the test.

Results: The sheets should validate and every action should be documented and all the sheets are assured to present
the quality data with complete maintenance of the security, accuracy and integrity of the data. By following these
principles, it is possible to fully validate and implement a standard spreadsheet in very less time.
Conclusions: The sheets provide the accuracy maintenance of data and calculations in order to protect the data from
any unauthorised access. The good documentation and manufacturing practices alongwith the regulatory authorities
made it important for getting regulatory compliance. Validation of process, equipment plays important role in
assuring the quality of product while the excel sheets ensure the data integrity and protection from any modification.
Therefore it necessary to validate the excel sheets as per the reference documents and to assure quality of the data.

1. Introduction activities from the quality control analytical methods of drug

substances and products to computerized systems for clinical
Quality stands as an essential prerequisite in the evaluation of trials, research, and process control. This term is derived from
any product and especially true for pharmaceuticals, for the word valid or validity meaning “legally defined”.
stringent standards of quality during their manufacturing [3]
According to the FDA, "Validation" is characterized as the
process. [1] systematic procedure of gathering and analyzing data in order
In this consideration, the Food and Drug Administration to establish scientific proof that equipment, utilities, or facilities
introduced good manufacturing practices (GMP) and viewed can consistently produce products of high quality. [4]
validation as an integral part of cGMP. Validation studies have
been conducted for a long time in industries. In recent times to COMPUTER SYSTEM VALIDATION
better the quality of pharmaceutical products, industries
CSV (Computer System Validation) is synonymous with
emphasize quality assurance programs. [2] software validation. According to the FDA, validation is
In the mid-1970s, Ted Byers and Bud Loftus were the two FDA described as the process of verifying, through examination and
officials, who first proposed the concept of validation. This presentation align with user requirements and intended
concept has broadened virtue to support a wide variety of

1588
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

functions, and that specific requirements have been QUALITY ASSURANCE

implemented accordingly. Quality Assurance (QA) is defined as all those planned actions
needed to provide adequate confidence that products or services
satisfy the given requirements of quality and be fit for use. It is
the total of the activities aimed at achieving the required
Examination Confirmation
standard. [8]
• Inspect the software to ensure that it meets the user
Components of Quality Assurance
requirements and that it will be suitable for the intended use.
• Setting up the system
Provision of objective evidence
• The Quality Manual
• Software requirements must be identified.
• Training
• Documentation of all validated test results.
• Standard Operating Procedure
User needs and intended uses
• The Quality Assurance manager
• Software examination to ensure the fulfillment of
user requirements • Auditing and checking compliance
Particular requirements implemented through software • Maintaining Quality Assurance
• Ensure that the standards can be met consistently. Functions of Quality Assurance
In the pharmaceutical sector, validation judgment is crucial to • Ensures validation and qualification of designated
ensuring adherence to pharmaceutical cGMP rules and formulations.
assisting businesses in maintaining consistent quality. • Qualifies batches for scaling up to production
Computer systems or information technology systems can still batches.
be validated using the same basic principles. Validation of • Assists in the design of validation protocols.
computer systems examines the efficacy and efficiency with • Establishes clinical programs for manufacturing bio
which the system achieves there intended goals. [5] batches, aiming for FDA pre-approval clearance.
They also provide custom support routine offering high-quality • Ensures the delivery of safe, effective, and high-
products to boost production process performance, scale back quality medicines to patients.
production prices, and improve product quality.
• Encompasses technical and managerial activities
Successful validation includes:
necessary to fulfill all quality functions.
• All standard supporting procedures are carried out
satisfactorily • Includes documentation, review of quality control
• Proper instructions laboratory tests, product performance.[9,10]
• Document management
QUALITY ASSURANCE FRAMEWORK
• Change control
Quality holds a significant place in the identity of the Institute
• Configuration management
and is one of the foundational elements of UNITAR's Six-Point
• Traceability specifications
Vision, which will guide its programming in the future. [11]
• Self-inspection and
The QAF serves by incorporating these elements, the QAF
• Deviation management ensures that the quality of UNITAR's training is upheld and
NEED OF CSV IN PHARMACEUTICAL INDUSTRY provides a mechanism for continuous improvement and
✓ Helps in controlling different phases of
knowledge exchange in relation to quality matters. The Quality
development, design, testing and routine of the software life
cycle. Assurance Committee (QAC), quality standards and
✓ Helps deliver accuracy, security, reliability and guidelines, and self-assessment and review processes not only
consistency to pharmaceutical industry. contribute to enhancing the quality of products and services
✓ Helps in finding time to time error, flaws and offered by the Institute but also offer valuable opportunities for
mistakes (software bugs) in order to minimize data integrity. improvement. Additionally, they serve as a point of reference
✓ Helps supports quality controls to ensure that the for external quality certification schemes and help facilitate the
process is followed correctly, to reduce manual error. Institute's journey towards accreditation. By implementing
✓ Helps improve product quality, to accelerate process these measures, UNITAR can ensure continuous enhancement
performance, and to support high quality of product. [6] of its offerings and align them with recognized quality
ELEMENTS OF VALIDATION benchmarks, both internally and externally. The framework in
• For quality assurance brief is shown in (Fig.1). [12]
• For time reduction
• For process optimization
• Better regulatory compliance
• Increased output
• Rapid automation [7]

1589
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

According to the World Health Organization (WHO),

validation is described as the comprehensive process of
gathering and assessing data across a product's entire lifecycle,
beginning from the process design phase and extending through
commercial production. The goal of this process is to furnish
scientific proof that a given process is capable of consistently
producing a product of high quality. [16] The types of validation
Fig.1 Quality Assurance Framework in pharmaceutical industries are given in (Fig.3).

DATA COLLECTION AND QUALITY ASSURANCE

Data management refers to the comprehensive system that
encompasses the gathering, purification, storage, oversight,
analysis, and presentation of registry data. The effectiveness of
this system plays a pivotal role in determining the data's
suitability for achieving the registry's objectives. The database
must conform to established quality standards, typically
aligned with its intended functions. Additionally, specific data
recipients might demand adherence to their individual
guidelines or benchmarks during the data collection or
validation processes. The framework of quality assurance
regarding data management is shown in (Fig.2). [13]

Decision Making
and Enforcement

Document Inspection of
Review local product
samples

Fig. 3 Types of Pharmaceutical Validation

Product Reporting Data Analysis
Testing and evaluation ‘V’ for Validation
Good Automated Manufacturing Practice
Fig.2 Data Management System (GAMP) recommends a Software Development Life Cycle
(SDLC) called the V-Model. The V-model as given in (Fig .4)
PHARMACEUTICAL VALIDATION in which left side gives the user demands while the right side
In pharmaceutical industries, validation is one of the vital parts gives the code modules.
of quality assurance system; it involves the regular study of The application of calculations within the field of pharmacy is
methods, equipments, facilities and processes for determining diverse and wide-ranging. It covers a spectrum of calculations
whether they produce consistent and constant results as per the carried out by pharmacists in both conventional and specialized
pre-determined standards. To maintain the quality of system at practice environments, spanning across operational and
every step and not just at the end is the main aim of doing research domains in industries, educational institutions, and
validation. The validation activities mainly include production government entities. [18
training, Standard Operating Procedures (SOPs), people,
Fundamentals of Pharmaceutical Calculations
facilities and processes involved in that system. A process is
Pharmaceutical calculations are the area of study that applies
said to be validated when it provides a high degree of assurance
the basic principles of mathematics to the preparation, safe
that uniform batches give specified results as predicted. [14]
and effective use of pharmaceuticals. To have a complete
The validated processes are helpful in understanding the
understanding of various types of calculations which are
Quality Management System (QMS) and also applicability of
involved in dispensing, it is desirable that the pharmacist should
manufacturing processes. As per the FDA's perspective,
have a thorough knowledge regarding the mandatory
ensuring the quality of a product involves meticulous and
calculations. [19]
methodical focus on several crucial elements, which
QUALITY OF ANALYTICAL DATA AND
encompass the choice of quality processes involving both in-
VALIDATION [20]
process and final product testing.[15]
• Instrumental Signals

1590
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• Calibration Methods ➢ Identification by Liquid Chromatography

➢ External Standard ➢ Identification by UV-Vis Spectrophotometry
➢ One Point Calibration • Assay of the Active Pharmaceutical Ingredient
➢ Internal Standard ➢ Assays Based on Liquid Chromatography
➢ Standard Addition ➢ Assays Based on UV Spectrophotometry
• Analytical Procedures • Chemical Tests for Pharmaceutical Preparations
• Validation ➢ Tests for related substances
➢ Specificity ➢ Uniformity of content
➢ Accuracy ➢ Dissolution
➢ Precision • Titration Calculations
➢ Detection Limit ➢ Titration of a pharmaceutical ingredient and calculation of
➢ Quantitation Limit purity (assay)
➢ Linearity and Range ➢ Preparation of primary standard (0.1 M potassium
➢ Robustness hydrogen phthalate)
➢ Test Methods in Ph. Eur. and USP ➢ Standardization
• System Suitability SPREADSHEETS
➢ Adjustment of Chromatographic Conditions The spreadsheet is a collective computer application for
• Chemical Analysis of Pharmaceutical Ingredients analyzing and storing data in a tabular format valid for many
➢ Pharmaceutical Ingredients, Production, and Control organizations. They have replaced paper-based documentation
➢ Impurities in Pharmaceutical Ingredients throughout the business world. Although earlier they were used
➢ Impurities in Pure Chemical Ingredients for accounting or bookkeeping tasks nowadays they are used in
➢ Impurities in Organic Multi-Chemical Ingredients multiple contexts where tabular lists are built, sorted, shared,
• Identification of Pharmaceutical Ingredients and stored. [21]
➢ IR Spectrophotometry
➢ UV-Vis Spectrophotometry EXCEL SHEETS/ SPREADSHEETS /DATASHEETS
➢ Thin-Layer Chromatography VALIDATION
➢ Melting Point In the laboratory, various types of equipment are used to
examine the quality of products. The greater challenge for
• Impurity Testing of Pharmaceutical Ingredients (Pure
analysts is the interpretation of the analysis data. Hence, Excel
Chemical Ingredients)
is the most popular software application for automatic
➢ Appearance of Solution
calculation and data visualization. The use of spreadsheets in
➢ Absorbance cGXP= GMP, GLP, and GDP must be controlled and validated.
➢ pH and Acidity or Alkalinity Spreadsheets are also used for testing acceptance criteria and as
➢ Related Substances a database view tool i.e. there additional application. [22]
➢ Residual Solvents These sheets should strictly follow Good Manufacturing
• Identification and Impurity Testing of Organic Multi- Practice and the FDA 21 CFR Part 11 electronic records and
Chemical Ingredients electronic signatures regulation. A comprehensive approach in
➢ Oxidizing Substances the development and validation of the spreadsheets is necessary
➢ Acid Value to validate the data. Spreadsheets used must be multipurpose
➢ Hydroxyl Value rather than single-purpose. [23]
➢ Iodine Value
Eg: A spreadsheet used for more than one product or a
➢ Peroxide Value spreadsheet producing multiple results for single data set.
➢ Saponification Value Like weight variation evaluation as per European
➢ Unsaponifiable Matter Pharmacopeia (EU), British Pharmacopeia (BP), and
➢ Other Tests Indian Pharmacopeia (IP). [24]
• Assay of Pharmaceutical Ingredients
➢ Aqueous Acid–Base Titration BENEFITS OF SPREADSHEETS VALIDATION
➢ Non-Aqueous Acid–Base Titration Validation is required by the FDA to ensure accuracy,
➢ Redox Titrations reliability, consistent intended performance, and the ability to
➢ Liquid Chromatography discern invalid or altered records. At Ofni Systems, we view
➢ UV-Vis Spectrophotometry validation as an opportunity to add value to your computer
systems. Validating your spreadsheet allows you to:
• Identification of the API
➢ Identification by IR Spectrophotometry • Submit data to regulatory organizations.

1591
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• Use data to make quality and GxP decisions. 2. The spreadsheet must be adaptable to produce results
• Control your data and define who has access to the even if values alter.
data. 3. The data that is utilised to calculate results should be
• Ensure your spreadsheet performs to specifications. readily visible in the spreadsheet.
• Query your data to better understand your business 4. It should be simple to validate and revalidate the
process. spreadsheet.
• Have documented evidence that your processes work 5. To prevent modifications or manipulation, the
as expected. spreadsheet cells holding important data should be secured. [27]
• Have increased confidence in your system. [24]
APPROACH TO SPECIFIC PART 11 REQUIREMENTS
DEVELOPMENT OF SPREADSHEETS Title 21 of the Code of Federal Regulations Part 11 (21 CFR
Designing of the spreadsheet should be: Part 11) is a set of regulations established by the U.S. Food and
• In an orderly manner Drug Administration (FDA) that specifically governs
electronic records and electronic signatures in the context of
• Data should be populated automatically, by a
pharmaceutical, biotechnology, and medical device industries.
system, or manually by an operator
These regulations outline the requirements for the use of
• It should provide a good summary concerning its electronic systems in capturing, storing, and managing data that
objective or purpose is subject to FDA regulations. Compliance with 21 CFR Part
• It should provide well-defined operations (e.g., limit 11 is crucial for companies operating in FDA-regulated
checks, transfer of data to other spreadsheets, online generation industries to ensure the integrity, confidentiality, and
of, use of macros), and calculations. authenticity of electronic data and to maintain regulatory
• Results of each individual product should be easily compliance. It covers aspects such as the security of electronic
traceable.[25] records, validation of computer systems, audit trails, electronic
signatures, and documentation practices as shown in (Fig.1.5)
KEY POINTS OF SPREADSHEETS .[28]
1. The spreadsheets can be used easily for other
products Electronic Validation
Records
2. The spreadsheet shall be flexible to give results if
21 CFR Part 11 Audit Trail
values are changed
Electronic
3. The spreadsheet should have clear visibility of data Signatures Record Copies
used for result calculations
4. The spreadsheet validation and revalidation should Record
Retention
be performed easily
5. The spreadsheet cells containing critical information Fig. 5 Criteria for 21 CFR Part 11
should be protected to avoid any changes or manipulation. [26]
OVERVIEW OF EXCEL SPREADSHEET
CREATION OF SPREADSHEETS Spreadsheet software of today can contain numerous
The spreadsheet's design should be: interconnected sheets, execute basic mathematical and
• Organized and structured arithmetic operations, and display data either graphically or as
• Data entry should be done either automatically or text and numbers. For typical economical and scientific
manually by the user activities, it offers integrated functions. In contrast, a
• Able to summarize a clear description of its goals and spreadsheet manipulates numerical data, text and the formula
objectives or the information. Spreadsheet can be useful in creating
• Able to include clearly defined procedures (such as budgets, data analysis, financial planning, and to perform the
limit checks, data transmission to other spreadsheets, online critical numerical operations. These sheets have tendency of
creation, and usage of macros), computations automatic recalculation, self feeded formulas by the user, or by
• Able to provide results that are simple to track for default included math functions, along with this changes in
each unique product. [27] already given command or formulas could also be seen
enhances the monitoring and error findings. [29]
OBJECTIVE OF SPREADSHEETS How to start Excel
1. The spreadsheets are simple to utilize for different • Go to START OPTION on the windows taskbar
goods • It can be find in the search bar as well

1592
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• Click on MS- Excel option, the window appears as

shown in (Fig.1.6). [30]
2. Objectives

NEED OF SPREADSHEET VALIDATION

During an FDA inspection of a QC analytical laboratory in a
pharmaceutical firm, several spreadsheet problems were
identified. These problems indicate a failure to adhere to
MS- Excel current good manufacturing practice (cGMP) and good
laboratory practice (GLP) regulations. Furthermore, the
laboratory lacked proper multi-user spreadsheet design,
validation, and documentation practices. Some specific
Search bar problems observed in the laboratory include as described in
(Fig.1.8). [33]
There are some more problems as follows:
• One specific problem identified in the QC analytical
laboratory was the presence of rounding-off errors in nearly all
the spreadsheets. Rounding-off errors can occur when
numerical values are truncated or approximated without
Fig. 6 MS - Excel considering the appropriate rounding rules.
• Proper excel equations are not followed to put
In the (Fig.1.7) given below the large window, labeled formulas into the sheets.
"Microsoft Excel" may take up the entire screen. This is • No expressions of conversion factors in the sheets.
referred to as the Application Window. The top line is called • Unmatched formulas used for manual calculations
the Title Bar and has three buttons (Minimize, Restore, and and excel calculations.
Close) to the right. These buttons are used to size the window • The standard limits were not mentioned in the
and close it. This title bar is standard in all Windows programs. spreadsheets.
[30]
The second line is called the Menu Bar. Notice that one • No clear documentation of spreadsheets.
character of each selection is highlighted or underlined. This For example, No clear indication of product statement
menu bar is also standard in all Windows programs. The next replacing it by number in a cell.
two lines contain buttons with text or images and are referred • No labels such as mg/g, mg/ml is provided for unit
to as the Standard and Formatting Toolbars. If you have a expressions.
mouse, these toolbars allow you to enhance your worksheet
• No provision to maintain data integrity and security.
without accessing the menu. Keep in mind that these may not
be in the exact same place as on the illustration above. All • Sheets are not protected from unauthorized changes
toolbars can be customized to display any buttons you desire. and access. [34]
[31]
The next line is the Formula Bar and displays the current Unique designing of these analytical spreadsheets by the user
cell address and contents. As you move from cell to cell, Excel itself can remove these errors. A modest confirmation
will keep track of the current cell address for you. The Formula document must have following points:
Bar can also be used to edit the text (contents) or formulas • Properly describe the programme, its use and
contained in the cell. [32] working.
• Entries of data should be color coded.
The Excel Screen
• Properly described calculation formulas.
Menu bar • Appropriate relationship between excel equations
and formulas.
Tool bar
• Macro functions must be listed. [35]
Title bar • Investigation sheets with projected and authentic
outcomes, contracted and studied, that have been confirmed by
Current Position
physical calculations.
• Properly secured and password protected.
• Installation date, version no. and system type.

Fig.7 MS - Excel Screen

1593
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• Conventions –
Consist of assuming data in
the sheets.
• Manuscript
[Link], expectations – Transparent
2. Absence of 3. Insufficient
Supnroea documentation of
rgdasnhieseetd Standard Documentation assumptions made in
Mdaatiantenance Operating
Immparoinpteern,ance Procedure Improper formulas.
unorganised Clear documentation • Data- This file
data instructions and contains raw data, such as
maintenance on the usage, compliance data records from which the
consistency model will execute
computations.
Fig. 8 Some specific problems of spreadsheet validation • Calculations –
Division of sheets into series
SPREADSHEET DESIGNING computation stages, and the
The framework of an Excel spreadsheet is almost identical to make one sheets with
that of an electronic programme. Spreadsheet formulas are different series and stages.
essentially a type of computer code. Alternative to generate the etc.
template file and then verifying for mistakes, check for
• Results – The end
problems throughout the different phases of the creation part should contain the final
process. A spreadsheet model is a collection of spreadsheets conclusion alongwith the
and custom sheets that are used to analyse and organize data or predictions done [22].
to solve a specific issue. This designing in appropriate format
• Ensures flexibility
is shown in (Table 1.1) [18]. The sheets shall be designed in
of model by reducing
such a way that they are easy to understand and to ease the
recalculations.
complexities of handling data generated from different
• Formula shall be
analytical procedures, validated excel spreadsheets containing Clear Visibility
3. written into different cells
relevant formulas are developed to ensure the process of data of formulas
instead of writing it in
handling will consistently produce the expected results. [36]
single cell, give cell
Table 1 Steps of Spreadsheet Designing
comments wherever
[Link] STEPS DESCRIPTION needed.
• Checks and
• Vertical
control measure shall be
calculation scheme should
included such as “IF and
be promoted. Control
4. ERROR conditions to avoid
• All the formulas Measures
mistakes such as #N/A,
Flexible should be written above its
1. #VALUE!, #REF!, #NUM!,
Designing respective cells [19].
#NAME?
• Proper color
formatting, border style and
STEPS IN PERFORMING VALIDATION
color should be done
The steps followed while performing the validation are as
appropriately [20].
follows:
• Documentation – Validation Planning
Must contain the title, scope,
author name, list, macros
and outlines.
Typical
2. • Menu – A one
Outline A comprehensive document consisting of actions done,
click consist of user
instructions such as drop approaches, schedule and validation activities.
down list, data entry location Validation procedure
etc.

1594
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

The following points should be considered while validating an

excel sheet as described in (Fig 1.9). Every worksheet taken
The validation procedure should be divided into an Installation into process must be fully documented consisting of all
Qualification (IQ) and Performance Qualification (PQ) [23]. formulas, macros, and user interface elements alongwith their
Installation Qualification copies in the validation documentation [25].
Operational Change Control

It consists of the points to establish the confidence that the

Changes to spreadsheet apps must be managed while in use.
results are in compliance with the regulations.
This modification management must be established from the
Performance Qualification stage in the creation life cycle where an individual begins to use
the programme and will continue throughout the application's
existence. [37]

Development of Requirements
PQ will systematically, strictly and constantly test the software
by testing all involved and resultant values, records, procedure, Proper Flexibility Testing
and control flow logic [24].
Proper use of excel auditing
Validation report
Performance Testing

User Instructions

The verification report should include complete validated Error messages

results, particularly the outcomes of the tests. When feasible,
evaluations should be reported quantitatively rather than as
Data Validation
"pass/fail." Qualified authority must assess and consent to the
report. Fig. 9 Elements of Validation Documentation

Validation Lifecycle
DATA
Data are quantitative or qualitative facts, figures and statistics
collected for reference or analysis.
Data may be captured or recorded:
It consists of preservation, controlling and maintenance of the • By manual recording, on paper or in an electronic
validated sheets. These controls include computerized systems system, of an observation or of an activity;
and it compliance with the regulations. Some controls to • By automatic recording, on paper or in an electronic
support the system includes system, using equipment that range from simple instruments
through to complex highly configurable computerised systems
• Preventive Maintenance
• Using a hybrid system where combinations of paper
• Environment Control and electronic records constitute the raw data
• Monitoring of performance • On other means of media such as photography,
• Retention imaging methods and technologies, etc. could be generated
• Recovery
manually, automatically or by hybrid system.

• Training to users, developers and administrators Configuration Management:

➢ Quality assurance for managing software addresses
Validation Documentation all documentation associated with the system and is applied
during all operational phases of software use, including the
development and maintenance phases. [37]

1595
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

DATA VALIDATION certain group of people in the pharmaceutical sector. The

Data validation is the activity where one decides whether or not process of integration requires that the data be user-defined.
a particular data set is fit for a given purpose. The decision is
based on testing observed data against prior expectations that a DOMAIN INTEGRITY (DI)
plausible dataset is assumed to satisfy. A straightforward Because the operational records of the pharmaceutical sector
formalization is to define it as a function from the collection of contain many different sets of regulations and processes, this
data sets that could have been observed to True or False. data may be streamlined and saved by using the DOI technique.

DATA INTEGRITY PHYSICAL INTEGRITY (PI)

➢ It is done to avoid intentional, accidental falsification or There are several ways in which different elements might
deletion of data to withstand data quality during regulatory impact the data integrity process and storage. One of the
inspections elements outside of one's control is one's wishes. Earthquake,
➢ Data integrity and security infractions are not only 21 flood, tsunami, tornado, and hailstorm are a few examples of
(CFR) Part 11 issues, but also severe cGMP violations. natural disasters that might interfere with how data integration
➢ It plays a very crucial role in USFDA audit. It is important is stored and retrieved. Data storage and protection can greatly
to back up the data containing valid reports from the entire benefit from the application of physical integrity and its
department. operating principles.
➢ It is important to ensure the data quality in order to offer a
quality product to fulfill the customers’ requirements. GOOD LABORATORY PRACTICE (GLP) ON DATA
➢ It is a critical aspect of data management and is INTEGRITY [OECD, 2021]
essential for ensuring that data is trustworthy and can be relied One of the fundamental purposes of the Principles of Good
upon for making informed decisions, conducting research, or Laboratory Practice (GLP) is to ensure the quality and integrity
supporting business operations. [38] of test data. This includes the increasing use of electronic data
capture, integration and automation of systems and other
TYPES OF DATA INTEGRITY technologies. However, the main purpose of the requirements
On the basis of mechanism and data integrity stream integrity of the Principles of GLP remains the same in having confidence
can be divided as shown in the (Fig 1.10). in the quality and the integrity of the data. [39]

1. LOGICAL INTEGRITY WORLD REGULATORY GUIDANCE ON DATA

INTEGRITY:
2. REFRENTIAL INTEGRITY • USFDA: 21-CFR: The Code of Federal Regulation
(CFR) is a codification of the general and permanent
3. USER-DEFINED INTEGRITY regulations that the executive departments and agencies of the
federal government have published in the Federal Register. The
4. DOMAIN INTEGRITY Food and Drug Administration's regulations are included under
Title 21 of the CFR. The CFR is updated once a year on or
5. PHYSICAL INTEGRITY around April 1st of each year for each title/volume.

Fig. 10 Types of Data Integrity • MHRA: The MHRA's guideline on GMP data
integrity requirements for the pharma sector is meant to
LOGICAL INTEGRITY (LI)
supplement the EU's current GMP standards for dosage forms
It is a crucial component of DI since it involves a physical
and active ingredients. The pharmaceutical quality system,
integrity procedure and safeguards data from human mistake,
which guarantees that medications are of the requisite quality,
piracy, and data hackers. In the pharmaceutical sector, logical
is fundamentally dependent on data integrity.
integrity is crucial for the preservation and processing of data
collected from the appropriate department. • TGA: The Therapeutic Goods Administration
(TGA), an Australian regulatory organization, specifies the
REFERENTIAL INTEGRITY (RI) need for data integrity as a deficit. A flaw in a procedure that
Integrity can take many different forms, including procedures might lead to a considerable risk of creating a user-harmful
and the application of laws related to data storage and retrieval. product.
Foreign keys are a crucial component of referential integrity. • WHO: Vital drugs and medical supplies, WHO
releases data integrity recommendations to safeguard patients
USER-DEFINED INTEGRITY (UDI) worldwide. In order to lessen instances of manufacturers A
Data acquired via post-marketing monitoring and information providing inadequate data or purposeful data fabrication, has
gathered from pharmacovigilance supervisors is specific to a

1596
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

WHO produced a guideline on worldwide best practices for

regulatory bodies and inspectors while we are creating a
medication and marketing it. Contemporaneous: Data should be recorded on
C
• EME: To guarantee the integrity of data generated time
during the testing, production, packaging, distribution, and Original: Data should preserved in its original form
monitoring of medicines, the European Medicines Agency O or a certified true copy
(EMA) has published new Good Manufacturing Practice
(GMP) guidelines. Effective data record management enables Accurate: Data should be free from error and as per
A the protocol
regulatory agencies and pharmaceutical producers to make
informed decisions by ensuring that the data produced are
Fig. 12 ALOCA
correct and consistent.
Attributable: All data should be attributable to the person who
DATA LIFECYCLE performed the original observation or data entry. When the
Data life cycle management is very much useful for any record has been completed, it is advised that you use permanent
enterprise or application where data is being used and ink. This method guarantees that the record remains readable.
processed for producing results. The lifecycle is the It should be persistent and readable in all scenarios.
development phase to the release and retention of the data. It Legible: The data of records involved in maintaining the data
integrity must have appropriate accessibility during the entire
plays an important role in easy flow processes including data
duration of action. Data should be recorded in a clear and
collection. It starts from creation, store, usability, sharing, and
readable manner.
archive and destroy in the system and applications The different
Contemporaneous: All the data are documented during the
steps of data lifecycle includes data approval, Transcription, entire course of action. It records important information from
Data processing, Data migration, Computerised system several sources into the proper useable data in chronological
transition, Data retention, Backup and Archive as shown in series.
(Fig.1.11) Original: The genuine or accurate copy of the raw data must
Data be given. Original data should be preserved and maintained
Approval throughout the study. [52]
Accurate: Data must include complete meaning and should be
Archive Transcription correct. Use an observation check for the purpose of basic
record collecting to confirm the correctness of the information.
[40]

Data 3. Methods
Backup
Processing
Validation Approach
Data Data A broad approach to spreadsheet validation has similarity to
Retention Migration that of the computerised system validation. The process of the
Computrised validation consists of same steps (user requirements, risk
System
Transcition assessments, provisions, design, challenging, reporting), the
project accountabilities, appropriate validation documents, and
Fig. 11 Data Lifecycle rules for the archival and change management processes.
Development Process
The V model is the most commonly adopted approach to
develop protocol for validation. The protocol is developed for
ALCOA various calculations and operations involved in pharmaceutical
"ALCOA" is an acronym used in the pharmaceutical and
industries. Some of the major tests includes IPQC test, Assays,
clinical research industries, as I mentioned earlier. It is used to
Molarity, Normality etc. The sheets and the process must be
ensure data integrity and accuracy in the recording of data
easy, reliable and adaptable in order to make it easy for the
during clinical trials and research studies. Each letter in the
acronym represents a fundamental principle that should be developer and user to make useful to maintain data integrity
followed when collecting and documenting data: The FDA uses and security throughout the operation.
ALCOA to define its expectations of electronic data as shown User Requirements
in [Fig.1.12]. The first step in the development of spreadsheet as per the User
Requirements Specifications Guidelines (URS) is to capture the
user’s spreadsheet demands.

Legible: Easy to understand, record and preserved

L in original form

1597
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

User Requirement Specification for Molarity and • Data entry is secured by error messages, dropdown
Normality determination spreadsheet: list and for time, date entries.
• Date and time should be electronically feeded with
URS‐1. The spreadsheet should be applicable for all
signatures of authorized personnel’s respectively.
parameters.
• Error messages should be there for invalid entries
URS‐2. The spreadsheet should be easy and quick to determine into the cell.
the given conditions. • The template is password‐protected (at sheet,
workbook and VBA code level).
URS-3. The format of spreadsheet should have Test name,
Sheet code, Standard taken with proper headings. • Defined location to save worksheet template and to
perform straightforward validation.
URS‐4. The spreadsheet should standard reference, calculation • All the information required for verification should
formulas and also the instructions wherever needed. be provided in the hardcopy and screenshots.
Development of Spreadsheet
URS‐5. The spreadsheet should use the Unique ID No. for
• The sheets are developed based on the User
specific compound in the form of dropdown list wherever
Requirement Specifications (URS).
applicable.
• The sheets should be designed systematically.
URS‐6. The spreadsheet should indicate invalid entries with • The user should include all the recommendations as
error messages and numerical value entries or any specific per URS step by step.
entries. • In case of Macro or VBA code recording should be
URS‐7. The spreadsheet input should only allow text, date, done by manual operations automatically.
time and signatures as well alongwith the approval details. • The code should be checked to ensure the correct cell
references.
Requirements concerning security, protection and GLP • At very first step user or the developer check the
compliance must also be taken into account. spreadsheet, cells, references and input values as per the user
URS‐8. The calculations in the spreadsheet should be protected requirement. This is done by the set of typical data.
against unauthorised modifications.
Review of codes
URS‐9. The sheet should have color coding system for input, • These codes should be evaluated twice by the
output cells and locking and unlocking system for formula developer and the experienced personnel who are accustomed
cells. with the planned use and functionality.
• In excel sheets it includes inspection of cell content,
URS‐10. After completion of the calculation, the spreadsheet
especially formulas, formula locations, cell numbers and
should be initialed, dated and put time electronically by the
user. ranges, syntax of additional functionality, and VBA code.
• The content and reference of the cell can be evaluated
URS‐11. Making changes to the spreadsheet by auditing the formula cells, dropdown list by zooming and
(modification/deletion) after signing off (initials/date) should scrolling to check mouse sensitivity.
not be possible. • Formula Auditing Tools in Excel by doing following:
URS‐12. The spreadsheet template and completed spreadsheets 1. Trace Precedents.
should be retained in a secure location in an electronic form 2. Remove Arrows.
with access control in the form of worksheet protection. 3. Trace Dependents.
4. Show Formulas.
URS‐13. The sheet should consist of the macro code and audit 5. Error Checking.
trail.
Trace Precedents
Designing of Excel sheet
• The “Trace Precedents” command can be seen in
The designing of the spreadsheet is related to the system
and version of the program and a worthy design ensures the “Formula Auditing” group under the “Formulas” tab to
the: trace precedents as shown in (Fig.13)
• Reduction of mistakes while operating the sheets.
• Easily understandable, adaptable and versatile.
• Easy verification of the formulas and cells to inhibit
unauthorised access.
Security, Error messages and Password

1598
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Fig.15 Trace Dependent Arrow

Show Formulas

• This command to display formulas written in the

excel sheet.
• The “Show Formulas” command can be seen in
the “Formula Auditing” group under the “Formulas” tab to
trace precedents as shown in (Fig.16).

Fig. 13 Trace Precedents Command

• To select the formula cell and click on the “Trace
Precedents” command.
• To check the formula’s precedents, press F2 to get
into edit mode after selecting the required cell so that
precedents cells.

Remove Arrows
• We can use the “Remove Arrows” command in
the “Formula Auditing” group under the “Formulas” tab to
remove these arrows as shown below in (Fig.14). Fig.16 Show Formula Command
Error Checking

• Select the cell where the formula or function is

written, then click “Error Checking.” from “Formula
Auditing” group as shown in (Fig.17).

Fig. 14 Remove Arrow Command

Trace Dependents
• This command traces the cell, dependent on the
selected cell.
• The “Trace Dependents” command can be seen in Fig.17 Error Checking Command
the “Formula Auditing” group under the “Formulas” tab to
5.5.6. VBA Code
trace precedents as shown in (Fig.15).
• The formula weight of the compound, to know how • To enter the code go to: Developer tab< Record
much weight of the compound is needed. Macro< Give Macro name, Shortcut key, Destination to
save and a short description of macro as shown in (Fig. 18)

1599
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• The Freeze Pane is included by: Home > View>

Freeze pane
• The color coding of cells was done containing
formulas.
• To view the formulas in Excel: Tools > Options >
Tab: View and check “Formulas” under Window options;
Formulas > Formula Auditing > Show Formulas
Security and Protection
Password Protection
• The protection of spreadsheet as well as worksheet
both is important in order to avoid the unauthorised access for
security purpose.
• To do any editing in the sheet the user needs
password to access the sheet, these passwords are unique to
individual sheets and one unique password is set to protect the
worksheet containing this spreadsheets.
• To Protect Worksheet: File > Info > Protect
Worksheet > Choose option as shown in (Fig. 19)

Fig.18 VBA Code Command

Qualification Process
The qualifying method's goal is to establish that the created
spreadsheet is fit for its intended use. It must be proved
throughout the entire procedure that the desired outcome - is, a
collection of processed data that has been subjected to
operations such as computations, rounding, formatting, and
regrouping for presentation as a table, chart, or report - is
achieved in an accurate and repeatable manner.
Installation Qualification (IQ)
A spreadsheet design is a vital document since it often contains
the whole source code (VBA code and Excel functions) and
may be used to run the programme.
Operational Qualification (OQ)
In the development phase user requirements are tested
systematically. It is not compulsory to perform all test but
some basic functions are checked. The entire test should be
performed with independent data sets.
Performance Qualification (PQ)
The main purpose of this last qualification stage is
demonstration of the spreadsheet by giving various commands
as per the user’s requirements. It is observed as the internal
acceptance test. It can be done regularly by the future users.
Fig.19 Worksheet Protection Window
Spreadsheet Accuracy Test
• To protect spreadsheet go to: Home > Format >
A. IPQC Test of TABLETS
Layout Protect Sheet
• The spreadsheet is divided into an input area
(yellow), an output area (green) and an area for verification of
constants and specifications

1600
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• As well as logical operators, Excel logical functions

return either TRUE or FALSE when their arguments are
evaluated.
• To insert it: Home > Formulas > Insert Functions

Table 2 Logical Formulas

Formula Formula
Function Description
Example Description
The formula
returns TRUE
if a value in
Returns
AND cell A2 is
TRUE if all =AND(A2
greater than or
of the >=10,
equal to 10,
arguments B2<5)
and a value in
evaluate to
B2 is less than
TRUE.
5, FALSE
otherwise.
The formula
returns TRUE
if A2 is
greater than or
Fig.20Spreadsheet Protection Window OR equal to 10 or
Cell Locking B2 is less than
Returns
=OR(A2> 5, or both
TRUE if any
• Cells containing formulas, sheet code, and Batch no. =10, conditions are
argument
and test name are locked. B2<5) met. If neither
evaluates to
• To Lock Cell go to Home > Format > Fromat Cells of the
TRUE.
> Protection as shown in (Fig.5.9) conditions it
met, the
formula
returns
FALSE.
The formula
returns TRUE
if either A2 is
XOR greater than or
equal to 10 or
Returns a B2 is less than
=XOR(A2
logical 5. If neither of
>=10,
Exclusive the conditions
B2<5)
Or of all is met or both
arguments. conditions are
Fig.21 Cell locking Window met, the
Logical Formula formula
returns
• Microsoft Excel provides 4 logical functions to work FALSE.
with the logical values. The functions are AND, OR, XOR and
NOT as given in (Table.5.1)
• These functions are used to compare formula or
several test conditions instead of just one.

1601
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Returns the
reversed
NOT logical value The formula
of its returns
argument. FALSE if a
I.e. If the =NOT(A2 value in cell
argument is >=10) A1 is greater
FALSE, than or equal
then TRUE to 10; TRUE
is returned otherwise.
and vice
versa.

Calculation Formula

• The formula used in these sheets as per the

calculation requirements.
• The formulas used in these sheets are based on their Fig. 22 Conditional Formatting Command
usage in the sheets.
• In the calculation and results worksheet, each • Now choose the color formatting for as per the user
calculation should be based on the results of the previous requirement as given in the (Fig.23)
calculation so that the calculations progress through the
worksheet from top to bottom.
• They are used to make calculation easy and simple to
understand

Conditional Formatting

• It consist of command where conditionally format

cells in a range that have duplicate text, unique text, and text
that is the same as text you specify.
• Select the cells you want to apply conditional
formatting to. Click the first cell in the range, and then drag to
the last cell. Fig.23 Cell Colour Format
• Click HOME > Conditional DATA VALIDATION
Formatting > Highlight Cells Rules > Text that Contains. In Drop Down list menu
the Text that Contains box, on the left, enter the text you want
• For more quicker and accurate data entry dropdown
highlighted.
list menu is useful as it can limit the entries people can make in
• Select the color format for the text, and click OK as
the cell.
shown in (Fig.22)
Steps to create a drop – down list

a. Select the cell you want to contain the lists .

b. On the ribbon, click DATA> Data Validation as
shown below in (Fig.24)

1602
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Data Input and Error messages

• The cells use the data validation functionality in

Excel (Data > Validation Data > Data Tools > Data
Validation).
• With this functionality, such as wrong entries of
alphabets, text, decimals and so on with their respective error
Fig.24 Data Validation Tab messages.
c. In the dialog box, set Allow to List as given in
(Fig.25) VBA Code
• The Office suite of applications has a rich set of
features. There are many different ways to author, format, and
manipulate documents, email, databases, forms, spreadsheets,
and presentations.
• The great power of VBA programming in Office is
that nearly every operation that you can perform with a mouse,
keyboard, or a dialog box can also be done by using VBA.
• Further, if it can be done once with VBA, it can be
done just as easily a hundred times. (In fact, the automation of
repetitive tasks is one of the most common uses of VBA in
Office.)
• Insert VBA code to Excel Workbook by following
this steps
1. Open your workbook in Excel.
2. Press Alt + F11 to open Visual Basic Editor (VBE).
3. The window appears after giving this command is
described in (Fig.27).

Fig.25 Validation Criteria

d. Put the data in list you want to see in drop down list as shown
in (Fig. 26)

Fig. 27 VBA Window

4. Right-click on your workbook name in the "Project-
VBAProject" pane (at the top left corner of the editor window)
and select Insert -> Module from the context menu as shown
in (Fig.28).

Fig.26 List Data

1603
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

3. Formula auditing can be done by using the watch

window as it allows the effect of change in one cell on the other.

Fig.30 Excel Auditing Tools

4. To set watch window: Click on the cell containing
formula> Watch Window>Add watch to the cell as shown
in (Fig.31).

Fig.28 VBA Project Explorer

Audit Trail
Audit trails are obligatory for computerized systems falling
under FDA/EU regulations. These logs are essential to Fig.31 Add Watch Window
document the inception, alteration, and removal of controlled Addition of Hyperlink
digital records. During system validation, the accuracy and
alignment of the audit trail with regulatory and organizational
• The hyperlinks are added to get information either
from another file, web page or image. It can be inserted in a
standards should be confirmed. Subsequent to successful
validation and system deployment, the significance of the audit particular cell of worksheet or the chart element.
trail must not be overlooked. • It can be done by using ‘Insert Hyperlink' dialog
box in Excel or by using Hyperlink function as shown in
To attain audit trail in spreadsheet: (Fig.32)
1. It is important to create audit trail to track changes
made to formula and data cells in the sheets.
2. To do this, go to the “Review” menu and select
“Track Changes” as shown in (Fig.29).
3. The required changes are done for being tracked and
can be assembled into the list in a new sheet.

Fig.32 Hyperlink Command

• To add a hyperlink to the current file, should be
selected from the list as shown in (Fig.33).

Fig.29 Track Changes Command

Excel Auditing Tool and Watch Window

1. Excel’s auditing tool is used to determine the errors
in formulas and data cells.
2. To do this, go to the “Formulas” tab and select
“Error Checking”, “Watch Window” as given in (Fig.30).

1604
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Fig.33 Hyperlink List

Validation Protocol
It is a documented plan consisting of all the experiments, their Confirmed Notes
designing and the procedure to carry out the validation. This Whether the signature
plan is a written procedure which consists of all the information 1. log book maintained
such as product description, testing parameters, production or not
process, planning and the acceptance criteria. Validation
Are the employees
protocol consists of two major points such as:
know about
• Procedure: It defines the steps involved in the
2.
[Link], GAMP
procedure of the validation. and 21CFR Part 11
• Form: It is the documentation of the any change or Is the all operating
modification done in the pre-approved validation protocols. 3. system update in the
company?
• Parts of Validation Protocol Is all the sheets used
• The important parts to be included in the protocol are 4. for calculation are
described in (Table 3): validated or not?
Table 3 Validation Protocol Parts Is all the sheets have
[Link] Parts Description 5. password protection to
open the sheets or not?
It shall be done by the Is all the sheets have
Protocol concerned departments and 6. color coding system or
1.
Approval taken for final approval from not?
quality assurance department. Is the system have raw
7.
It includes the objective to data captured
2. Purpose
made validation protocol. All the sheets are
It includes the specific 8. properly named and
3. Scope departments following this coded
protocol Is the original data
The reason to carry out 9. captured in readable
Reason of validation process either of the form or not?
4.
Validation new, process, method or the Is the sheets follow
equipment. 10. electronic signature
model or not?
Revalidation The need of revalidation such
5. All information
Criteria as change to any part or step.
11. recorded both in paper
The responsibilities of every and electronic form?
6. Responsibilities department should be All the sheets can be
mentioned appropriately. 12. run on the entire
All appropriate documents version or not?
Reference
7. (BMR, BPR, SOPs) should be The sheets have
Documents
mentioned. 13. acceptance criteria
Procedure/Devi Mention a stepwise procedure limits or not?
8. The sheets are
ations with required deviation.
provided with the
All the obtained results as per 14.
9. Conclusions VBA codes and Audit
acceptance criteria or not.
trails or not?
Report/ Report The report should be made
10. The sheets are
Approval with department approval
provided with error
15.
and warning messages
or not?

1605
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Sheets saved in
16. appropriate formats as
per SOPs or not?

4. Results
Validation Approach
In terms of duties, the spreadsheet designer is frequently
interchangeable with the researcher who is also in charge of a
certain test methods. It must be ensured in this scenario that the
programmer is constantly aware of his present job and that both
the creation and validation stages are clearly divided. If
modifications are required during the validation process, they
must be reported, assessed, and tested again. Version control
may be the core in handling changes, and essential
modifications may be done with fewer efforts than using CSV. Fig.34 Spreadsheet Layout
At last the approach of validating a spreadsheet is to assess the
Development of Spreadsheet
risk with short and systematic procedures in order to delete
• Testing of spreadsheet efficiency as per User
some steps of the process.
Requirement Specifications (URS)
Development Process
• The spreadsheets includes the formula auditing tools
The sheets should be designed as per the user requirements or
namely :
can be designed as a standard format for some common tests
1. Trace Precedents.
can be used as per the need. The key components of the sheets
2. Remove Arrows.
includes the password protection, color coding, locking and
3. Trace Dependents.
unlocking of the cell, limits as per the guidelines etc.
4. Show Formulas.
User Requirement Specifications
5. Error Checking.
This includes calculations, formulas, layout, security and
integrity of data, included in Functional Specifications. • The designed sheets must have these command as
6.4 Designing of Excel sheet described in (Fig.35)
To depict a sophisticated spreadsheet design the sheet is • The sheets were tested for the formulas feeded for the
divided into the colour coded area as shown in (Fig. 6.1) calculations.
where,
• First three rows (2, 3, and 4) contain heading as Test
name, Sheet code, and Standard reference.
• The next columns contains subheadings (G to P: ID
No., Vol. and Strength of solution required, Name of
compound, Formula Weight of compound, Weight of
Compound taken and Points to remember ).
• The heading and subheading cells are separated by
different colours.
• The designed sheets were easy to understand and
colour coded as well.
Input and output cells are separated and color coded as
well:
• The first three columns (G.H, I, J, and M to P) are
INPUT CELLS in YELLOW COLOUR.
• The next two columns (K and L) are OUTPUT
CELLS in BLUE COLOUR.
• The input cells are UNLOCKED CELLS while the
output cells are LOCKED CELLS.
• Formulas, Calculations and other parameters cannot Fig.35 Formula Auditing Tools
be changed by the users in locked cells.

1606
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Trace Precedents. • This could be one of the ways including one more
• Suppose we have the following formula in the L7 cell appropriate way to check precedents for the formula cell.
for calculating weight of the compound to make molar solution
as shown in (Fig.36) Remove Arrows

• The command was given the window appears and a

click on the remove arrow just disappears the arrow seen on the
sheet as given in (Fig.39).

Fig.36 Formula Auditing Cell

Fig.39 Remove Arrow Cells
• The arrows where trace precedents are highlighted
with blue dots appears, as shown below in (Fig.37) Trace Dependents
• Select the K7 cell and click on the “Trace
Dependents” command as shown in (Fig.40)
• In the (Fig. 20), we can see the arrow lines where
arrows indicate which cells are dependent on the cells i.e.K7
dependent on L7 cell.
• We will remove the arrow lines using the ‘Remove
Arrows’ command.

Fig.37 Trace Precedents Arrow

• The cells get bordered with various colors and written

in the same color and cell reference as shown in (Fig.38).

Fig.40 Trace Dependents Cells

Show Formulas
• In (Fig.41), the cells containing formulas in the entire
sheets are shown.
• The sheet shows all the formulas from the average
calculation to the every single function involved in the
calculation.
• The show formulas command allows to identify the
Fig.38 Formula Precedents Check
cells containing the type and cells involved in the formulas.
• We can see that H7 is written with blue in the formula
cell, and with the same color, the H7 cell is bordered.
• In the same way, I7cell has a green color and K7 cell
has a purple color.

1607
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Fig.41 Show Formula Cells

Error Checking
• Once the command is given the error checking starts Fig.43 VBA Code
and ends with a pop message as shown in (Fig.42).
Release for Qualification
As soon as the development documentation is completed sheet
and worksheet protection through password is installed and
transferred for the documentation of qualification process. The
recommendation of spreadsheet version is important. The
sheets are than released for qualification.
Installation Qualification
To guarantee that the spreadsheet can be used across the testing
facility, it may be important to verify that:
• The spreadsheet can be operated on multiple
computers (either identical or varied technology
configurations, user profiles, or local settings).
• The spreadsheet may be installed and operated on a
variety of software platforms (Excel, OS, and, if appropriate,
the data collection system).
• The sheets should be flexible so that they can be
saved on the server to make it useable for many users at same
time.

Operational Qualification (OQ)

The result of making wrong entries into the cell such as

mistakes in date format, text instead of numbers, incorrect
Fig.42 Error Checking Popup Message
decimal places, and real numbers instead of integers gives the
VBA Code
error message as described in (Table 5).
• The VBA codes should be should be properly
reviewed line by line for the consistency, correctness, reliability
and reproducibility as described in (Fig 43)

1608
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Table 5 Operational Qualification 1. Layout

Testing as
Test
Acceptance • The color coding of the input and output cells are
per URS Criteria shown in (Fig. 44).
The spreadsheet
should use the Unique The error INPUT CELL
ID No. for specific message “Please
compound in the form enters code from
URS‐5. of dropdown list the dropdown
wherever applicable. list”.
The spreadsheet
should indicate
invalid entries with The error
error messages and message “Please
numerical value enters numerical
entries or any specific value 2, 12,4,15
URS‐6 entries. only”.
The spreadsheet input
should only allow The error Fig.44 Test sheet Layout
text, date, time and message “Please • The freeze pane command can be shown in
signatures as well enter date and (Fig.6.12).
URS‐7 alongwith the time as
approval details. required”. Freeze Pane
The error
The calculations in message “The
the spreadsheet cell or chart you
should be protected are trying to
against unauthorised change is
modifications on protected and
clicking on the cell in therefore read‐
URS‐8 the sheet. only” appears.

Performance Qualification (OQ)

It can be done by observing the following as:

Fig.45Freeze Pane
• By testing the input values in order to check the
• The green and blue areas contain formulas in cells as
consistency by entering even the boundary values, all
given in (Fig. 46)
functional requirements and all the special and combination
values .
• By testing with real data been the major part of the
validation process by taking real data as the input one. This
input can be done manually or by importing the files. For
achieving the high level of confidence a wide variety of data is
used for spreadsheet testing.
• This step makes sheets accurate, reliable so that their
final copies can be tested for real data.
• The accuracy and reliability of the sheets decides
their usage and compatibility with the security purpose,
unauthorized access and many more.
• The qualification process is to ensure that the sheets
will fulfill the predetermined standards.

Spreadsheet Accuracy Test

A. IPQC Test of TABLETS

1609
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• Whenever unauthorized users try to manipulated data

in the sheet they will get an immediate warning to unprotect the
sheet as shown below in (Fig.49)

Fig.49 Warning Message

7.3 Logical Formulas
• It consist of two logical commands as shown in the
dialog box as given in (Fig. 50 & 51)
Fig.46 Formulas
Security and Protection
Password Protection
• Whenever unauthorized users try to manipulated data
in the sheet they will get an immediate warning to unprotect the
sheet.
• The window appears as shown in (Fig.47) to enter
password for opening of the sheet.

Fig.47 Worksheet Password Entry

• After the worksheet, the spreadsheet is also password

protected as shown in (Fig. 48)

Fig.50 Logical Formula Window

• Put commands in this box and Press ENTER

Fig. 51 Logical Formula Command

• O
nce the command is entered if both the command is “True” or
“Pass” result displayed will also be “True” or “Pass”
Fig. 48 Protected Spreadsheet Window
[Link] Cell Locking

1610
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• T
he formula description is described in (Table.6)
Examples of Sheets containing Logical Formulas:
There are some sheets containing logical formulas as shown in
(Fig. 6.19, 6.20 and 6.21)
➢ T
hickness, Hardness and Disintegration Sheet
=IF (AND (K8<1, K8>0.5),"PASS","FAIL")

Fig.54 SUM ROUND Formula

Table 6 Formula Descriptions

[Link] Formula Description

Returns “PASS” if K8
=IF (AND (K8<1,
1. contains value less than
K8>0.5),"PASS",
1, greater than 0.5 and
"FAIL")
FAIL otherwise.

2. =ROUND Returns MIN value from

(MIN (J18:J23), 2) J18 to J23 cell than
Fig.52 IF, AND Formula round it to 2 decimal
➢ Dissolution Sheet places.
=ROUND (MIN (J18:J23), 2)
3. =ROUND (SUM Returns MIN value from
(F8:F27), 2)/20 J18 to J23 cell than
round it to 2 decimal
places.

6.7.4 Calculation Formulas

• The formula inserted occupies one cell each for easy
and clear understanding of the calculation and formulas.
• The formulas should be clear, concise and
appropriately used.
• Some sheets having calculation formulas are shown
in (Fig. 6.22, 6.23, & 6.24)

Fig.53 ROUND Formula

➢ Weight Variation Sheet
=ROUND (SUM (F8:F27), 2)/20

1611
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Fig.55 Dissolution Calculation Sheet

Conditional Formatting

Examples of Sheets containing Conditional Formatting

• The “Pass” result will show green colour with dark

green text
• The “Fail” result will show red colour with dark red
text as shown in (Fig. 6.25, 6.26 and 6.27)

Fig.56 Friability Calculation Sheet

Fig.58 Thickness, Hardness and Disintegration Sheet

Fig. 57 Assay Calculation Sheet

Fig.59 Weight Variation Sheet

1612
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Fig.60 Friability Test Sheet

DATA VALIDATION
Drop Down list menu Fig.62 List of compounds
b. Weight Variation Sheet: It contains test name in drop down
• The dropdown list into the sheet was seen as shown
list as shown in (Fig. 6.30)
in (Fig.6.28)

Fig.61 Drop down list

Examples of Sheets containing Drop down List
a. Molarity Sheet: It contains compounds name in drop down Fig.63 Test Name List
list as shown in (Fig. 62) Data Input and Error messages
• The input of invalid data (e.g. alphanumerical data in
numerical fields, out‐of‐range values) can be intercepted (Table
7).

1613
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

• The VBA code coding can be obtain by clicking on

the module than on the desired code to run it as given in
Table 7 Data input and Error messages (Fig.6.32)

Fig.65 VBA Code

• Save your workbook as "Excel macro-enabled
workbook".
• Press Ctrl + S, then click the "No" button in the "The
following features cannot be saved in macro free workbook”
warning dialog as given in (Fig.6.33).

[Link] VBA Code

• Click on the VBA code to open the window showing
VBA codes, then click on the desired code to open the VBA
code command to do this click on: Project Explorer> VBA
Project Personal> Modules> Choose the desired module as
given in (Fig.64). Fig.66 Macro Workbook Warning Window
• The "Save as" dialog will open as shown in
(Fig.6.34). Choose "Excel macro-enabled workbook" from the
"Save as type" drop-down list and click the Save button.

Fig. 64 VBA Code List Fig.67 Macro book saving dialog box

1614
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

How to run VBA macros in Excel

When you want to run the VBA code that you added as
described in the section above: press Alt+F8 to open the
"Macro" dialog. Then select the wanted macro from the "Macro
Name" list and click the "Run" button as shown in (Fig.68).

Fig.70 Error Checking Message

• The formula auditing can also be done by using
watch window which shows how change into one cell brought
the change into other.
• The watch window can been in (Fig.6.38).

Fig.68 Macro Run dialog box

Audit Trail
• The changes done in the sheet are tracked in new
sheets as shown in the (Fig.69).

Fig. 71 Watch Window Command

Addition of Hyperlink
• The added hyperlink can be seen on the sheet as
shown in (Fig.6.39)
• One click on the hyperlink takes to the document or
the image, draft from where it is inserted.

Fig.69 Tracked Changes Sheet

Excel Auditing Tool and Watch Window
• The error checking can be done for the entire sheets
and the message is displayed as shown in (Fig.70)

Fig.72 Hyperlink Cell

1615
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Validation Protocol • The formula should be written in the corresponding

The protocol should be follow while performing the excel sheet cells for reference.
validation: • The conditional formatting should be done to assure
1. Protocol Approval: Protocol should be made and approved the acceptance criteria as well as the entry into the cell should
by the QA department. also be color coded.
2. Purpose: The objective of this protocol is to assure the • The sheets should be saved with the respective name
validation of excel sheets used for calculation purpose. such as assay, dissolution, disintegration etc.
9. Conclusion: The sheets should be saved in ready only mode
3. Scope: This protocol shall be followed by the QA personnel with authorized persons signature as shown in (Fig.6.40)
while doing the testing to assure product quality.

4. Reason of Validation: The validation of excel sheet was

carried out to maintain the data security, integrity and accuracy. Read Only Mode
5. Revalidation Criteria: The revalidation of excel sheet
depends upon the change in the

• Computer Version no.

• Software Update
• Calculation, Formulas, Acceptance Criteria, color
coding etc.
• Guidelines, Users and Procedures

6. Responsibilities: Following departments are responsible for

the validation as follow:

• IT Department: Handles the password protection

and any unauthorised excess.
• Quality Control: Prepares procedure for excel sheet
validation as per SOP.
• Quality Assurance: QA head is responsible for the Fig.73 Approved Final Sheet
approval and implementation of protocol properly. 10. Report/ Report Approval:
The sheets should validate and every action should be
7. Reference Document: All the documents taken for documented and sent to the appropriate departments and
references shall be properly checked and approved. personnel for its approval.

8. Procedure/Deviations: CONCLUSION
• To prevent unauthorised access to the files, a file This research has introduced a simplified and efficient method
must be password protected or should be in the read-only for validating Excel spreadsheets. This method combines
status. industry best practices into two comprehensive documents,
with more specific information to follow in upcoming articles.
• The cell containing formulas or involved in
By following these principles, it is possible to fully validate and
calculation must be locked to maintain data accuracy and
implement a standard spreadsheet in less than four days. This
integrity. approach offers a practical solution to the widespread issue of
compliance, minimizing risk through an affordable and
• The version of the operating systems shall be fixed to consistent process. It is adaptable to various quantities of
prevent any problem during the change or revalidation. spreadsheets and can be easily integrated into a company's
• The format of the sheets should be fixed and such that computer systems validation policies.
it the header contains the Product name, Sheet code, Test name,
Batch no. and company name , logo (if applicable) Spreadsheet creation often point out that the process of creating
• The middle part shall contain the calculation, where spreadsheets is more casual in nature, and only a small number
entry to be filled from Raw Data Sheet, formulas and the of organizations possess thorough guidelines for spreadsheet
development. While there are instructive pieces that emphasize
information needed to make results.
practices like modular design and including sections for
• The footer of the sheets must contain the in-out
assumptions, these might not hold as much significance as other
timing, start-end date alongwith the prepared, checked and
advancements, particularly the meticulous inspection of
approval information with the signature. individual cells' code following the development stage.

1616
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

Acknowledgments involve the installation and checkout phase, 10. Rockville (MD): Agency for Healthcare Research and
which requires shifting the application software from the Quality (US), Registries for Evaluating Patient Outcomes: A
development lab to the plant site. Upon installation on-site, User's Guide; Data collection and Quality Assurance, 2014; 3rd
functional testing with the process can commence. Before this edition
phase starts, a procedure for documented change control (defect
identification, review, software and document updates) must be 11. Chapter 19 Quality Assurance for Pharmaceuticals.
defined and implemented. Outputs encompass functional test Management Sciences for Health, 2012
results, change control reports, and a validated computer 12. Loo M. P. J. & Jonge E. Data Validation. 2020, Wiley Stats
system.
Ref Statistics: 1–7
The sheets provide the accuracy maintenance of data and
13. Heiko Brunner. Chapter 18 Validation of Excel
calculations in order to protect the data from any unauthorised Spreadsheet, Analytical Method Validation and Instrument
access. The good documentation and manufacturing practices
Performance Verification, 2004: 277-298
alongwith the regulatory authorities made it important for
getting regulatory compliance. Validation of process, 14. Ayalasomayajula U.L. [Link], Review on Pharmaceutical
equipment plays important role in assuring the quality of Calculations, Res. J. Pharm. Tech. 2016; 9 (11):2043-2047
product while the excel sheets ensure the data integrity and
protection from any modification. Therefore it necessary to 15. Howard C. Ansel, Pharmaceutical calculation. 2017; 15th
validate the excel sheets as per the reference documents and the edition
given guidelines to assure quality of the product as well as the
16. OECD Series on Principles of Good Laboratory Practice
generation of the quality data. The necessary steps required to
and Compliance Monitoring, Number 22 Advisory Document
validate an excel sheets are also shown here.
of the Working Party on Good Laboratory Practice on GLP
REFERENCES Data Integrity, 2021
1. Sarvani V. et al. Process validation: an essential process in
17. Ahmad [Link]. Importance of data integrity & its regulation
pharmaceutical industry, IJMCA. 2013; 3(2): 49-52.
in pharmaceutical industry, The Pharm. Inn. J., 2019; 8(1): 306-
2. Raul S, Padhy G.K , Mahapatra A , Charan S.A , An 313
Overview of Concept of Pharmaceutical Validation, RJPT.
18. Butler J.M. Quality Assurance and Validation, Advances
2014; 7(9): 1081-1090.
Topics in Forensic DNA Typing, 2016; Chapter 9: 167- 211.
3. Singh A, Singour P, Singh P, Computer system validation in
19. Staden, J. Koos F. Good Laboratory Practice in Analytical
the perspective of the pharmaceutical industry, JDDT. 2018;
Chemistry with Modern Laboratory Management. Proceeding.
8(6):359-365.
2020; 55(1): 23. doi: 10.3390/proceedings2020055023.
4. Butler M. John Chapter 7 - Quality Assurance and
20. Good Practices for Data Management and Integrity in
Validation. Advanced Topics in Forensic DNA Typing:
Regulated GMP/GDP Environments, Pharmaceutical
Methodology, 2012: 167-211
Inspection Co-Operation Scheme, July 2021.
5. Jadhav S, Waghchaure V, Swamini S, “Computer system
21. Potkany, M, Zavadsky, J., Hlawiczka, R., Gejdos, P. &
validation in pharmaceutical industry”, IJCRT. 2021; Vol.
Schmidtova J. Quality Management Practices in Manufacturing
9(6):h101-h109.
Enterprises in the Context of Their Performance. Journal of
6. Bera, S., & Mukherjee, I. Advances in solution methods for Competitiveness, 2022 14(2), 97–115.
optimisation of multiple quality characteristics in [Link]
manufacturing processes. Int. J. Pro. Qual. Manag. 2018; 24
22. Taylor et al. Research skills and the data spreadsheet: A
(4): 475–494. [Link]
research primer for low- and middle income countries Afr. J.
7. Hesham A and Patan IK. Computerized Systems Validation Emerg. Med. 2020; 10 (l2): S140–S144
(CSV) in Biopharmaceutical Industries, J. Pharma. Res. Oct
23. M.S. Durmuş et al. Enhanced V-Model. Informatica, 2018;
2020; 4(4): 2-15.
(42): 577–585
8. Nandhakumar. L, Dharmamoorthy.G, Ramesh.S and
24. Kumar R, Banyal R, Data Life Cycle Management in Big
Chandrasekaran. S, An overview of pharmaceutical validation:
Data Analytics, Procedia Computer Science, 2020 (173) 364–
Quality Assurance view point. IJRPC 2011, 1(4):1003-1014
371
9. A. Storey, R. Briggs, H. Jones and R. Russell. Chapter 4:
Quality Assurance, Research Gate, 2011

1617
 Journal of Chemical Health Risks
[Link]
JCHR (2023) 13(6), 1588-1618 | ISSN:2251-6727

25. Stig Pedersen-Bjergaard, Bente Gammelgaard, Trine 40. Goldberg, S. I., Niemierko, A., Shubina, M., & Turchin, A.
Grønhaug Halvorsen. Introduction to Pharmaceutical “Summary Page”: A novel tool that reduces omitted data in
Analytical Chemistry, 2019; 2nd edition: 1-505 research databases. BMC Med. Res. Meth. 2010; 10: 91-97

26. Higgins D, Johner C., Validation of Artificial Intelligence

Containing Products across the Regulated Healthcare
Industries, 2023; 57(4):797-809. doi: 10.1007/s43441-023-
00530-4.

27. Berhe, H. H. Application of Kaizen philosophy for

enhancing manufacturing industries' industries’ performance.
Int J. Qual. Reliab. 2021; 39 (1): 204–-235.

28. Patel KT and Chotai NP, J Young Pharm. 2011; 3(2): 138–
150

29. Kevin C. Martin and Dr. Arthur Perez GAMP 5 Quality

Risk Management Approach, The Official Magazine of ISPE
2008; 28(3): 1-7

30. AGIT, Guidelines for the Archiving of Electronic Raw

Data. 2018; (2): 1-20

31. Howard D. & Harrison D. A Pragmatic Approach to the

Validation of Excel Spreadsheets – Overview. Pharma IT
Journal, April 2007; 1 (2): 6-10

32. Kruck S.E. Testing spreadsheet accuracy theory,

Information and Software Technology, March 2006; 48(3):
204-213

33. David L. Christie J. Herald, Computer systems validation

and the software development process. Elsevier Science. ISA
Transactions. 1993; (32): 65-73

34. Potdar MA. Pharmaceutical Quality Assurance, Nirali

Prakashan, 2007; 2nd edition: 20-27

35. Tuan T. Phan, Technical Considerations for the Validation

of Electronic Spreadsheets for Complying with 21 CFR Part 11.
Pharma. Tech. 2003: 50-62

36. FDA, Guidance for Industry, General Principles of

Software Validation. 2002.

37. Brunner, H. Validation of Excel Spreadsheet. Analytical

Method Validation and Instrument Performance Verification.
2004: 277–298. doi:10.1002/0471463728.ch18

38. Saxer. Good Laboratory Practice (GLP).Guidelines for the

Archiving of Electronic Raw Data in a GLP Environment.
Qual. Assur. J 2003; 77, 262–269. DOI: 10.1002/qaj.244

39. Gliklich, R. E., & Dreyer, N. A. (Eds.). (2010). Registries

for evaluating patient outcomes: A user’s guide (2nd ed.,
AHRQ Publication No.10-EHC049). Rockville, MD: Agency
for Healthcare Research and Quality

1618