DEVELOPMENT PROCESS OF
EXCEL SHEETS
User Requirements
Before the spreadsheet is developed, the users should define what
they expect the spreadsheet to do,
which calculations have to be performed, the layout, etc. This is defined in the User Requirement
Specifications (URS), which would also include the Functional Specifications. For example, a (non-exhaustive) URS
for the gravimetric pipette control spreadsheet may look
like this:
URS-1. The spreadsheet should
be applicable for volumes from 0.01 to 10 mL URS-2. The spreadsheet should only accept
a sample number of 10
URS-3. The spreadsheet should calculate the mean, standard
deviation (SD) and relative SD (RSD=(SD/average)x100%)
of the 10 samples
URS-4. The spreadsheet should use the density of water between
20°C and 25°C in steps of 1°C
URS-5. The Inventory Number
of the pipette should
be visible on the screen and on the print-
out.
URS-6. The spreadsheet should indicate whether
the accuracy and RSD fulfil the acceptance
criteria according to the SOP
URS-7. The spreadsheet
input should only allow numerical values for the nominal volume
of the pipette, sample weight
and temperature
Requirements concerning security, protection and GLP compliance have also to be taken into account:
URS-8. The calculations in the spreadsheet should be protected
against unauthorized modifications
URS-9. After completion of the measurement, the
spreadsheet should be initiated and dated electronically by the user
URS-10. Making changes to the spreadsheet (modification/deletion) after signing off (initials/date) should not be possible
URS-11. The spreadsheet template and completed spreadsheets should be retained
in a secure location in an electronic
form with access control
The spreadsheet design corresponds to the system architecture of a software
program, and a good
design will:
· reduce the number of errors during
programming,
· make it easier to maintain, adapt or further
develop the spreadsheet and
· make it easier to verify the function during
validation.
To illustrate a structured layout, the sample
spreadsheet shown in Figure 1 is divided into
an area where data input occurs (columns A and B: inventory number, nominal value, sample weight, water temperature),
an area where the results are calculated and presented
(columns D–F: mean, SD, RSD, etc.) and an area containing constants
and criteria (columns H–L: water density at different temperatures, maximum Delta and RSD). The different
areas may be highlighted by different colours or shadings
The spreadsheet is password-protected, and data can only be entered
in the marked cells (column
B). Data entry is secured by error messages
and ranges.
It is important for the design of a spreadsheet that:
·
input and output
areas are separated;
·
calculations, constants and parameters cannot be changed
by the user (locked cells);
· a blank template
is used – the use of “master copies” with typical data should be avoided;
and
·
the template is password-protected
(at sheet, workbook and VBA code level).
At this stage, it should be defined where the productive template is
stored. The file paths for automatic
opening or saving of files and the security settings should be adapted
accordingly.
Such a design provides the basis for straightforward validation. All input data, constants
and calculation results
are shown on the spreadsheet, which allows stepwise
verification, e.g. with a calculator. All the information required to
verify the calculation is a printed hard copy or a corresponding
screenshot
The spreadsheet is password-protected, and data can only be entered
in the marked cells (column
B). Data entry is secured by error messages
and ranges.
It is important for the design of a spreadsheet that:
·
input and output
areas are separated;
·
calculations, constants and parameters cannot be changed
by the user (locked cells);
· a blank template
is used – the use of “master copies” with typical data should be avoided;
and
·
the template is password-protected
(at sheet, workbook and VBA code level).
At this stage, it should be defined where the productive template is
stored. The file paths for automatic
opening or saving of files and the security settings should be adapted
accordingly.
Such a design provides
the basis for straightforward validation. All input data, constants
and calculation results
are shown on the spreadsheet, which allows stepwise
verification, e.g. with a calculator. All the information required to
verify the calculation is a printed hard copy or a corresponding
screenshotSystematic testing of User Requirement Specifications
At this stage, the
developer tests whether the spreadsheet fulfils the URS. A simple set of typical data for the input values
is used and varied in order to simulate the situations that are
needed to test all URS.
Code review
It is highly recommended that this review should be conducted by two
persons – the developer and an
experienced reviewer who is familiar with the intended use and the applied
functions.
For spreadsheets, the code review
consists of checking
the content of cells, especially formulas, formula locations, cell numbers and ranges, syntax
of additional functionality, and VBA code
A spreadsheet template is a critical file since it usually contains
the complete source code (VBA code and spreadsheet functions) and can be used to execute the program. In terms of software development, the source code and the compiled version
are stored in the same file. Therefore, it is
important to make sure that the spreadsheet template is stored in a secure,
controlled environment. Servers
with the appropriate read/write/modify security settings can provide a reasonable
level of control and are therefore
the recommended location for storage of templates. Alternatively, the use of a DMS comprising tools such as version
control and backup function, as well as the possibility of access control can be considered.
It is self-evident that adequate backup measures have to be in place
so as to avoid any damage
to or loss of the template (e.g. if any of
the above measures should fail).
In order to ensure that the spreadsheet can be utilised throughout
the test facility, it may be necessary to check that
· the spreadsheet can be run on different
computers (identical or different hardware
configurations, user profiles, local settings) and
·
the spreadsheet can be installed and run using different software
versions.
Ability to run the spreadsheet on different computers
This is especially important when the spreadsheet is stored as a
template on a server and run by
different users on their desktop PCs. In many companies, the computers may have identical
or similar hardware,
but the configuration (properties or settings,
e.g. operating system
language) may be different depending on the use of the specific PC. For example,
a computer used for instrument control may run with US versions of the
software, whereas further data handling may be done in the office using a local version.
Special attention is required if normal use involves automatic
access to raw data files (e.g.
reading an export file created by an analytical instrument) and/or automatic or manual
saving to certain folders on the network.
It is important to test whether a spreadsheet is capable of running on different computer
hardware if that is part of its intended use. This would
require a test of the influence of user profiles, local settings, etc.
Ability to install and run the spreadsheet using different software
versions
It is important to test the spreadsheet on different software
configurations if they are actually
used in parallel. This does not need to be tested during installation qualification if it can be ensured that identical versions of the operating
system and spreadsheet software will be used.
Systematic tests of the initial user requirements have been
performed during the development
testing phase (usually by the software developer). It is not necessary to repeat every single test in this phase,
but typical cases with all the basic functions
should be included. It is recommended that all tests should be performed with different and independent
data sets.
Test for ruggedness
Choose input values that lead to unacceptable or impossible results.
Try to enter data in incorrect formats or in the wrong cells (text instead of numbers, values
out of range, wrong number
of decimal places,
or real numbers
instead of integers;
see Table 1). In these cases, the user requirements should
already specify what behaviour is required and
expected from the system. As many of these tests have already been included in development testing, the focus should be on issues arising
from the productive environment at that stage. This may include repeated tests with
real data in order to discover malfunctions due to loops,
or values that are not correctly re-set
at the end of the execution. Testing of security
aspects should also be included at this stage since the spreadsheet is now
installed in the productive environment.
Table 1: Examples of tests to evaluate the ruggedness
and security aspects of the spreadsheet
|
Test relating to
URS:
|
Test
|
Test result/acceptance criteria
|
|
...
|
|
|
|
URS-7
|
Enter
“q” as weight in the sample 1 field.
|
The error message
“Please enter a weight in grams!” appears.
|
|
URS-7
|
Enter
“q” as temperature in the temperature field.
|
The error message “Please enter a
temperature between 20 and 25°C!”
appears.
|
|
...
|
|
|
|
URS-8
|
Place
the cursor in cell E4 and press “Delete” to erase the
content of the cell.
|
The error
message “The cell
or chart you are trying to change is protected and
therefore read-only” appears.
|
|
...
|
|
|
|
URS-9
|
Enter
“A1” into the Pipette (Inventory
Number) field and press the “Enter/Save” button.
|
The error message “You
have to fill-out all the green input cells!” appears.
|
|
...
|
|
|
Table 2: Examples of tests
with selected input
data
|
Test relating to
URS:
|
Test
|
Test result/acceptance criteria
|
|
...
|
|
|
|
URS-3,
|
Enter “A1” in the Pipette
(Inventory Number)
|
Results: mean 10 g, SD 0.015, RSD
|
|
URS-6
|
field, “10” for
the Nominal Value
(ml) and
|
% 0.15, Accuracy ok, RSD ok
|
|
|
“22” for the
Temperature (°C).
|
|
|
|
Enter the following values for samples 1–10:
|
|
|
|
9.98, 9.98, 9.99, 9.99, 10, 10, 10.01,
10.01,
|
|
|
|
10.02, 10.02
|
|
|
URS-3,
|
Now enter the following values
for samples
|
Results: mean 10.078
g, SD 0.173,
|
|
URS-6
|
1–10:
|
RSD % 1.717, Accuracy ok, RSD out
|
|
|
9.95, 9.95, 9.99, 9.99, 10, 10, 10.05,
10.05,
|
of spec
|
|
|
10.4, 10.4
|
|
|
URS-3,
|
Now enter the following values
for samples
|
Results: mean 10.35
g, SD 0.053,
|
|
URS-6
|
1–10:
|
RSD % 0.512, Accuracy out of spec,
|
|
|
10.3, 10.3, 10.3, 10.3, 10.3, 10.4, 10.4,
|
RSD ok
|
|
|
10.4, 10.4, 10.4
|
|
|
URS-3,
|
Now enter the following
values for samples
|
Results: mean 10.00
g, SD 0.000,
|
|
URS-6
|
1–10:
|
RSD % 0.000, Accuracy ok, RSD ok
|
|
|
10.0, 10.0, 10.0, 10.0, 10.0, 10.0, 10.0,
|
|
|
|
10.0, 10.0, 10.0
|
|
|
...
|
|
|
Test with selected
input data
The goal of this step is to verify all possible and all special
cases (including boundary values),
even if they are very rare, pursuing a systematic approach (see Table 2). In contrast, the real data will typically be
of relatively high consistency and cover only
realistic cases, not necessarily the extremes. Therefore, the input data
has to be selected to cover all functional requirements and combinations of theoretical cases
for decision criteria
(branching, conditional instructions), as well as all extreme values or special
data points.
Test with real data
From the user’s point of view, this is the principal part of the
validation process – applying real measurement data as input data. Data input can be done manually or by importing a file, depending on the
intended use of the spreadsheet. Irrespective of whether this is a manual or an automated step, real data from previous
measurements is to be used.
It is important to use a sufficient number of different real data sets in order to obtain
a high level of confidence that the spreadsheet functions as intended.
