IT systems · Practical guide

Excel validation: limits, rounding and formula errors

A cell can display a rounded value while the formula continues calculating with more decimal places. The specification determines whether that is correct. Define when rounding occurs and how exact limits are handled before choosing test data.

Table of contents

At a glance

Which boundary tests belong in Excel validation?

Test values below, at and above each decision-relevant limit, as well as missing, invalid and exceptional inputs. Define when rounding occurs and which value determines the decision. Calculate expected results independently of the formula under test. Recalculating only familiar normal values leaves important failure paths untested.

Scope and Applicability

The tests relate to a specific work folder and its intended use. They neither replace the technical approval of an analytical calculation method nor the evaluation of recording controls throughout the entire process.

Sources: [1] [2]

Set inputs and expected values before testing

Empty entries and true zero values are not the same. Similarly, numbers entered as text, regional decimal separators, or an inserted row can affect calculations. Test such scenarios at points in the workflow where they may occur. An error message is only helpful if it actually prevents the misuse of invalid results.

The expected response must not be copied from the same unverified formula. Use a traceable, independent calculation, professionally approved reference values, or another appropriate comparison. Additionally, capture the relevant Excel version, calculation mode, and external references, if they influence the result.

Sources: [1] [2] [3]

Define business decision rules and alert indications separately

First, describe which number drives a quality decision. Is an unrounded measurement value compared against the limit, or a previously rounded value? Which unit applies? Is the upper limit inclusive? How are results below a detection limit handled? These questions must be professionally addressed before a formula can be considered valid.

The cell number format affects the displayed appearance, not automatically the stored value. For example, a displayed result of 10.0 could be based on a value above or below the specified limit. The deliberate application of a rounding function constitutes a calculation decision. Document the exact point at which rounding occurs and justify why this approach is appropriate for the intended process.

Capture, in addition to formulas, named ranges, hidden sheets, external references, macros, and import paths. A seemingly simple limit check may depend on a parameter sheet or a linked file. If this source is modified, the decision—without visible changes to the output sheet—may change. The released scope must take such dependencies into account.

Sources: [1] [2] [3]

Build a limit matrix with independent target values

Derate test values from the technical specification. For an upper inclusive limit, at least appropriate cases below, at, and above the limit are required. Impute missing values, text, negative values, and unusually large values, to the extent they are feasible or critical in practice. Selection must be justified; a universal number of test cases does not exist.

Calculate expected results independently of the Excel formula being evaluated. Anyone who copies the same formula text into a second cell will merely reproduce the same error. A traceable manual derivation or an independently assessed comparison calculation provides more credible validation. Ensure consistency in unit, rounding step, and comparison operator.

Internal work aid: Test values around the boundary of 10.0
Input in the exampleRule: unrounded value not more than 10.0Purpose of the case
9.999 Within the limit Value immediately below the limit
10.000 Within the limit Inclusion of the limit value
10.001 Outside the boundary The display must not conceal the finding
Leer No professional release decision Detect missing input
Text instead of number Reject entry or investigate and clarify systematically Handle a data type that is not permitted
Negative number Handle in accordance with defined specialist guidelines Do not use unverified values deemed implausible.

Sources: [1] [2] [3]

Example: Displayed 10.0 results in unauthorized batch release

A work folder displays measurement values with one decimal place. The technical instruction specifies that the unrounded value should be compared against the upper limit of 10.0. The value 10.001 appears as 10.0. An employee reviewing only the displayed value might interpret it as acceptable. According to the correct interpretation of this example, the result should be classified as "out of specification."

For the test, the same value is entered into the intended input cell. Separate verification of the stored value, applied formula, displayed value, and decision field is required. The report should clearly illustrate the selected rule. A change in presentation may improve readability, but does not replace the correct comparison logic. If the technical specification explicitly requires prior rounding, a different target value should be applied.

Expand the example to include a unit conversion. A factor error can generate a numerically plausible value that is factually incorrect. Therefore, jointly review the original unit, conversion factor, and unit of issue. Document the number of decimal places used in both calculation and reporting. The statement "Excel calculates correctly" is insufficient as justification for this technical decision.

Test levels from input through to output
Testing levelWhat to documentPotential error
Entrance Original value and unit Incorrect unit adopted or entered
Transformation Factor and Rounding Place Premature rounding or incorrect factor
Comparison Limit value and operator Smaller rather than smaller equal
Display Decimal places and labelling The display masks a limit excursion
Output Decision status and reference Result belongs to the wrong sample

Sources: [1] [2] [3]

Error handling and input pathways practically verify

Data validation can support input processes, but must be verified during actual workflow. Consider copying, pasting, importing, and macro usage, where applicable. A dropdown for manual entry does not automatically demonstrate that all other input methods comply with the same restrictions. Therefore, test all permitted workflows and restrict uncontrolled alternatives.

Evaluate how formula errors are handled. A function that converts any error into an empty cell or a zero may cause a technical issue to appear as a valid result. Define when a calculation is terminated, a value is marked as unresolved, or a technical review is triggered. Error indications and release-relevant status must be consistent.

Also verify manual calculation settings and external updates when they are in use. A report may display outdated results even though inputs have already been modified. Therefore, document the necessary calculation and update steps. The review should include a deliberately altered input, with a clearly expected impact.

Sources: [1] [2] [3]

Control approved formula base and subsequent modifications

Document the version of the Excel file used, the working workbook version, and the relevant settings. A form inventory can link critical calculations to their input parameters, rules, and test logic. It is not necessary to treat every decorative cell equally. The selection depends on which cells influence the professional judgment.

Sheet protection helps preserve locked cells from modifications. Microsoft explicitly states that it is not intended as a security feature. Therefore, additional evaluation of storage, access rights, template distribution, and handling of work copies should be conducted. The use of a protected formula in a freely exchangeable file does not prove that the approved template is always being used.

When the limit value, rounding rule, or common auxiliary function is changed, affected decisions must be identified. Recalculate the relevant edge cases and review historical outputs if the change impacts the interpretation of those outputs. A format change alone may not trigger additional review requirements; however, it should still be evaluated if it leads to ambiguous or misrepresentative values or statuses.

Sources: [1] [2] [3] [4]

Worksheet for recording the calculation rule and an independent expected result

Describe a critical calculation in a concise technical statement prior to documenting the formula text. Example: A value is taken from the input unit without prior rounding and compared against an embedded upper limit, followed by display with one decimal place. This rule clearly separates calculation from presentation.

Include an independently derived invoice for normal and edge cases. Document the accuracy of the input data and specify which information remains visible in the report. If an employee only sees rounded display values, they should still be able to understand the decision logic. Otherwise, a correct formula result may be misinterpreted.

Use this template even in the case of a subsequent change. A different threshold can leverage the same test setup but requires new setpoints. An alternative rounding rule, on the other hand, may alter the technical decision itself. Identify these distinctions and review dependent outputs, rather than simply entering the new value into an existing test file.

Sample filling example – internal work aid
Template fieldExample entryAssessment question
Entrance 10.001 in the specified unit. Has the stored value been copied without modification?
Business rule The unrounded value must be no more than 10.0. Is equality included and overlimitation excluded?
Display A decimal place yields visibly 10.0. Could the display hide a limit excursion?
Expected decision Outside of this professional guideline. Is the decision status independent of the display?
Failure scenario Empty or textual input. Is no seemingly valid zero generated?
Status Work folder, relevant settings and formula version. Is the actual verified status retrievable?

Sources: [1] [2] [3]

Next steps

  • Specify formula, unit, limits, and rounding sequence.
  • Define independent target values for normal, boundary, and out-of-specification cases.
  • Verify user inputs and result decisions in the real work folder.
  • Record formula specifications and deviations and approve for release.
Further reading:

Project experience and collaboration

Test the decision rule with carefully chosen boundary cases

A formula can calculate correctly yet produce the wrong decision if the display and evaluation rule disagree. With the process owners, we identify boundary cases and calculate expected values independently. We use them to test inputs, rounding and error handling throughout the workbook. For a narrow formula change, we identify the affected analyses. If the decision rule itself is unclear, we resolve it before testing the technology. The evidence should be explicable against the actual calculation.

Frequent Questions on Excel Thresholds and Rounding

Does a leaf protection exist?

No. It may complicate unintended changes and provides neither proof of formula correctness nor sufficient access security. These aspects require separate controls.

Sources: [1] [2] [4]

Must each cell be tested individually?

Not mandatory. Similar formulas can be justified for grouping; copied sections, differing formulas, and border cells require special attention.

Sources: [1] [2]

Are macroscopic features a prerequisite for a validation obligation?

No. Even a workbook without macros can perform quality-relevant calculations. What matters most is the intended use.

Sources: [1] [2]

Is commercial rounding always the correct rule?

No. The rounding rule follows professional guidance and the intended use. Define the timing, accuracy, and handling of edge cases. Only then can it be assessed whether the Excel function used is appropriate. A familiar built-in function is not a substitute for a professionally justified decision rule.

Can we test exclusively normal measurement values?

To ensure that critical edge cases remain unassessed, include values just below, at, and above the limit, as well as appropriate error and blank cases. Select cases based on the calculation logic and potential consequences. Document independently derived target values and verify that display, calculation, and decision logic consistently represent the same rule.

How do we differentiate formatting and rounding in a test?

Compare the recorded input, actually used calculation, and displayed result separately. A number format may hide decimal places without altering the value. A rounding function modifies a calculation step. The technical specification must explain which of these levels is decisive for the decision.

What should be included in an appropriate Excel test certificate?

Name the workbench version, inputs, relevant settings, independent setpoints and actual results. Link the reference to the technical regulation and evaluated deviations. Screenshots may support, but do not replace a traceable calculation and clear assignment to the verified cells or functions.

Sources and review date

The sources form the technical basis. Tables, checklists, and examples are derived methodological elements, not verbatim regulatory requirements. Manufacturer information must be referenced to the specific version and configuration in use. Targeted additional source cross-check on 18.10.2026: Microsoft "Protection and security in Excel" regarding the limits of worksheet protection.

  1. European Commission: EU GMP Annex 11, Computerised Systems (2011)§§ 1, 4.4, 4.7, 5 and 10: Requirements, boundary and error checks, data processing and changes. Current version as of EudraLex dated 14.10.2026; no universal project or deadline specification.
  2. Microsoft: Detect formula errors in ExcelSupplier source for product functionalities. Availability and behavior depend on version, edition, license, and configuration; no general compliance certification.
  3. Microsoft: More on data validation in ExcelSupplier source for product functionalities. Availability and behavior depend on version, edition, license, and configuration; no general compliance certification.
  4. Microsoft: Protection and security in ExcelBoundary of the sheet protector checked on 18.10.2026: Protection against cell migration is not a safety function.

Sources checked: .

Daniel Herrmann, Daniel Herrmann Consulting

Daniel Herrmann Consulting

So your team can safely plan the next step.

We support you in assessing the current situation, clarifying responsibilities, and implementing appropriate evidence within operations. The type of support that is most effective depends on your system, your team, and the specific objective.

  • Computer system validation →

    Bring your GxP systems into validated operation, from laboratory software, MES and eQMS to ERP. We define the scope, plan risk-based tests using GAMP 5 and hand the evidence to QA for release.

Send a project enquiry Customer testimonials →