---
title: "Find hidden errors in a tender pricing workbook"
description: "Test formula coverage, fixed-value replacements, external dependencies and rounding with known results. Build a defect record before changing the buyer’s file."
canonical: "https://zephior.com/insights/validate-a-tender-pricing-workbook"
last-updated: 2026-09-05
---

# Find hidden errors in a tender pricing workbook

> Test formula coverage, fixed-value replacements, external dependencies and rounding with known results. Build a defect record before changing the buyer’s file.

By [Tony Kim](https://zephior.com/authors/tony-kim). Published 2026-09-05; updated 2026-09-05. 17 minute read.

## Definition

A pricing-workbook validation is a documented test of whether the submitted file calculates the required price from its approved inputs. The reviewer records expected and observed results, traces discrepancies to cells or dependencies, and assigns each finding a permitted remedy. Its deliverable is a test report tied to an exact workbook version, including unresolved limits. It does not set the bid price or authorize alterations to the buyer’s template.

## Problem

A total can be wrong without displaying an error. A range may stop one row early, a pasted number can replace a formula, or an old linked value can remain visible after its source disappears. Checking that the total looks plausible will miss these faults. Even reproducing the total can fail if the reviewer copies the same faulty formula. The review needs a separate expectation and tests that make the file reveal which inputs it actually uses.

## Point of view

Start from the price the buyer says will be evaluated, then work backwards through the calculation. Establish the expected contribution of each input before touching a diagnostic copy. A workbook that passes ordinary data entry still needs tests for omitted rows, inactive branches, stale results and the recipient’s opening conditions. Stop release when a material result remains unexplained. Knowing how to repair a formula does not confer permission to repair the issued form.

## Name the output before checking cells

Obtain the untouched buyer workbook, the completed candidate and every instruction that changes its calculation. Identify which output the buyer uses: a base-term total, an evaluation basket, a lot-specific figure or a value including options. A dashboard labelled “total” is insufficient evidence. Record its sheet and cell address, currency, tax basis and included periods. Where the instructions and formula disagree, retain both readings until the buyer resolves the conflict.

Keep a short test cover sheet outside the submission workbook. It should identify the candidate version or checksum, application and version, calculation settings, access to linked sources, reviewer and test date. Separate the diagnostic copy from the release location. Label experimental inputs visibly in the test record, not through unauthorized additions to the buyer form. A second reviewer must be able to reconstruct the opening conditions without relying on the first reviewer’s laptop.

The UK AQuA Book distinguishes checking implementation against its specification from checking fitness for use. Applied here, those are two different questions: does the formula execute the stated calculation, and is that calculation the one the tender requires? This guide develops the former through workbook tests and keeps the latter as a required boundary. It cannot settle an unclear commercial instruction by making the arithmetic consistent.

**Four records that prevent a test from becoming an accidental bid change**

| Record | What it identifies | What it cannot authorize |
| --- | --- | --- |
| Issued baseline | Buyer file and applicable amendments | A bidder-designed replacement formula |
| Approved inputs | Current values and their entry locations | A new commercial price |
| Diagnostic copy | Tests, changed inputs and observed results | Submission of the test values |
| Release report | Exact final file and completed checks | Unresolved changes to buyer instructions |

## Look for valid formulas that calculate the wrong thing

Compare formula-bearing cells in the issued and completed files. Investigate a formula replaced by a constant even when its displayed result is unchanged. Inspect the first and last row of each repeated block, transitions between years, optional lines and subtotal boundaries. A pattern break is a review lead, not proof of error: the first contract year may legitimately include a one-off fee. Explain the difference against the instructions before recommending a change.

Follow the evaluated output back through intermediate totals and named ranges. Check where each name points and whether similarly named sheet-level and workbook-level references have different meanings. Review absolute and relative references where rates are copied across periods. A fixed reference to last year’s rate can produce a plausible number in every year. An error wrapper returning an empty string or zero deserves inspection because it can conceal a failed lookup beneath an apparently clean output.

Microsoft’s formula-error documentation explains that built-in rules do not guarantee an error-free worksheet. Use those warnings as one source of findings. If inspection is restricted by protection, record the uninspected path and request an authorized explanation or test method. Do not bypass the protection. Seeing a result change under a test gives evidence about that case; it does not reveal every hidden branch.

Check input types separately from their appearance. A rate that looks numeric may be text, and an identifier with leading zeros may correctly be text. Microsoft documents that SUM ignores text in referenced cells. For a money range, reconcile the expected numeric entries against the entries actually counted by the calculation. Do not convert every cell to numbers: that can corrupt references, codes and intentionally textual instructions.

## A missing exit fee produces no spreadsheet error

Consider the fictional Harthill equipment-service bid. Its made-up instructions require four components in the evaluated total, all in EUR excluding tax. Row 8 prices 18 sites for 24 months at EUR 125 per site-month. Rows 9 to 11 contain setup, training and exit fees. The reviewer calculates the expected total from these components outside the workbook before examining its Summary cell.

The line values are correct, but the summary uses SUM(F8:F10). It returns EUR 62,100 because row 11 is outside the range. No broken-reference error appears. The expected EUR 64,900 is not an estimate or a market price; it follows only from this invented test specification. In a live bid, the reviewer would first need evidence that the exit fee belongs in the evaluated total.

Now change only the exit fee from EUR 2,800 to EUR 2,801 in the diagnostic copy. The expected total rises by EUR 1; the observed total does not move. Restore the baseline. Changing the monthly rate from EUR 125 to EUR 126 should increase the total by 18 × 24 = EUR 432, and it does. These two tests distinguish an excluded exit path from a workbook that is not recalculating at all.

The reviewer logs the omitted row, the controlling instruction and both observed results. If the range was already present in the issued template, the bidder asks the buyer for a corrected file or explicit treatment. If the bidder introduced the range error and restoring the original formula is permitted, the team makes that controlled correction. Either route requires a fresh test of the release candidate.

**Fictional Harthill reference calculation, EUR excluding tax**

| Component | Independent calculation | Expected amount | Included by defective summary |
| --- | --- | --- | --- |
| Recurring service, row 8 | 18 × 24 × 125 | 54,000 | Yes |
| Setup, row 9 | Fixed fee | 4,200 | Yes |
| Training, row 10 | 6 × 650 | 3,900 | Yes |
| Exit, row 11 | Fixed fee | 2,800 | No |
| Evaluated total | 54,000 + 4,200 + 3,900 + 2,800 | 64,900 | Workbook shows 62,100 |

## Write the expected change before entering a test value

Choose tests that can separate competing explanations. For a purely linear quantity-times-rate line, adding one unit should add one rate to the extended amount. For a fixed setup fee, changing the number of service months should leave the setup component unchanged. For a tiered price, the result depends on whether the whole quantity or only the excess uses the next tier. Read that rule before choosing a threshold test. A generic instruction to “double everything and expect double the total” fails when fixed fees, caps or minimum charges exist.

Cover price-bearing paths, not an arbitrary percentage of cells. Test the first and last item in a range, one representative from each genuinely equivalent formula family, each separate branch and every material exception. Record the basis for treating repeated rows as equivalent. A thousand copied formulas with one special final-year clause are not one uniform family. Full cell inspection may be necessary where the cost of a missed exception is high.

Include legitimate no-change tests. An unevaluated optional service should not enter the base total if that is the stated rule. A change in one lot should not alter another independent lot. These checks can expose accidental cross-references that a grand-total comparison misses. Two errors may also cancel: a missing EUR 2,800 fee and an unrelated EUR 2,800 overstatement leave the headline total unchanged. Reconcile components and dependencies as well as the final sum.

Use blank and zero tests only when their meaning is established. A blank rate might be an unanswered required field, not a zero-price offer. Keep deliberate invalid-input tests in the diagnostic copy and record the expected rejection or warning. A spreadsheet accepting the entry does not make that commercial response permissible. After each test, restore the baseline and verify restoration before the next test starts.

**Examples of tests with an explicit expected relationship**

| Test condition | Expectation to establish first | Finding if the expectation fails |
| --- | --- | --- |
| Increase one linear rate | Quantity × rate change reaches the intended subtotal | Wrong reference, missing path or stale calculation |
| Change an independent lot | Other lot totals remain unchanged | Unintended cross-lot dependency |
| Cross a pricing threshold | Published tier rule governs each side | Boundary or lookup defect |
| Change an excluded option | Base evaluated total remains fixed | Option included contrary to the stated rule |
| Restore the original input | Baseline outputs return | Residual test state or uncontrolled dependency |

## Separate recalculation from refreshing the source data

Recalculating formulas and fetching new external values are different operations. A workbook can recompute perfectly using an obsolete exchange rate or an earlier supplier quote. Record the approved source version before any refresh, and confirm whether that source must remain fixed for this offer. “Latest” is not automatically “approved.” Do not open unknown connections or enable macros merely to remove a warning; use the organization’s security process and the buyer’s requirements.

Microsoft notes that desktop calculation settings affect all open workbooks. Record the calculation mode in the actual test session, use the supported recalculation procedure and confirm completion before comparing outputs. Where the file relies on code, queries or specialized functions, document which dependencies were executed and which were unavailable. A library that reads stored cell results without evaluating formulas can help inspect structure, but cannot prove recalculation.

Inspect links beyond obvious cell formulas. Defined names, objects and charts can carry external references. Microsoft’s workbook-link guidance also warns that breaking a link converts dependent formulas to their current values. Treat that as a calculation change, not a cosmetic clean-up. A buyer-required dependency needs an approved delivery arrangement; a bidder-only working link needs a permitted replacement or removal followed by revalidation.

Check whether filtering changes a price-bearing subtotal. In Excel, SUBTOTAL with function number 9 includes manually hidden rows, while 109 excludes them; filtered-out rows are excluded in both cases. If the evaluated total is meant to include every required line, compare its behavior with that requirement. Preserve the observed filter state in the report. Do not switch functions simply because one produces the preferred price.

## Explain the cents instead of forcing the totals to agree

A rounding difference needs a rule, not a balancing entry. Suppose a fictional test uses three units at EUR 12.344. Rounding the unit price to EUR 12.34 before multiplication gives EUR 37.02. Multiplying the unrounded rate first and rounding the line gives EUR 37.03. Neither sequence can be chosen from the size of the difference. Find the buyer’s prescribed precision and the stage at which rounding applies.

Inspect stored values alongside their displayed format. Microsoft explains that Excel usually calculates with stored values, and that enabling precision as displayed permanently alters them. Do not use that setting to make an unexplained discrepancy disappear. Preserve the file, identify the required calculation and use a separately authorized correction if one is needed. A tolerance in the internal test report is not permission to submit a different contractual amount.

Check dimensions before tracing increasingly complicated formulas. Sites × months × EUR per site-month produces EUR. Multiplying an annual rate by months without conversion produces the wrong amount even if every reference is intact. Keep tax, currency conversion and indexation as approved inputs or explicit rules; this workbook test does not decide their legal or commercial treatment. Trace a mismatch to the earliest differing intermediate result and give its unit, not just its cell address.

Record both absolute and relative differences where useful, but classify findings by consequence. A one-cent difference can prevent an exact-match upload field from reconciling. A small percentage difference may conceal an entire excluded service line. Conversely, a display-only difference with identical permitted underlying values may require an explanation rather than a formula change. The release reviewer needs the reason for the disposition.

## Close the finding on a new candidate, not on a screenshot

A useful defect record states file identity, relevant instruction, cell or dependency, baseline inputs, test action, expected result, observed result, price effect and owner. Attach only the evidence needed to reproduce it. For a buyer question, use neutral illustrative values where allowed so the clarification does not disclose your rates or margin. Keep the commercial impact in the restricted bid record.

Classify the cause before routing it. An unauthorized bidder paste belongs with the workbook owner. An unclear inclusion rule belongs in clarification. A calculation that depends on an unavailable permitted application needs an environment decision. A wrong expected result requires correction of the test. Do not report every mismatch as a buyer defect or assume that the reviewer’s separate calculation must be right.

After an approved remedy, create a new release candidate from the controlled working file, restore all approved inputs and rerun the failing test, affected downstream outputs and independent total checks. Compare the restored input set against the approved source in full. Reopening a previous diagnostic copy and trusting that every changed value was put back is an avoidable risk.

Inspect the native file and any required PDF separately. The government guidance on sharing spreadsheets highlights hidden data and document properties; for a tender, apply that concern without deleting buyer-owned hidden structures or replacing the required workbook with CSV. Remove only permitted bidder residue, then retest. A PDF can confirm visible pages but cannot prove that formulas, validations or dependencies survived.

The final report should state which outputs were tested, which branches or dependencies remain unverified, and whether unresolved findings block release. Record the approved file identity after the last save. If the file changes afterward, the earlier report describes the earlier file. Commercial authorization and portal submission remain separate decisions made by the responsible people.

**Disposition of a workbook finding**

| Cause | Required next step | Closure evidence |
| --- | --- | --- |
| Bidder entry or permitted-formula mistake | Controlled correction within allowed edits | New candidate passes the failed and dependent tests |
| Issued formula conflicts with instructions | Buyer clarification or replacement file | Authoritative treatment and retested calculation |
| Unclear expected result | Resolve the governing rule | Revised expectation tied to that rule |
| Unavailable dependency | Approve a supported delivery method | Recipient-like opening test succeeds |
| Material unexplained difference | Hold release and assign an owner | Cause and authorized resolution, not a forced balancing value |

## Useful outcomes

- A reviewer can reproduce the expected evaluated total without copying the workbook’s total formula.
- Each tested input has a stated effect on the relevant output, including legitimate no-change cases.
- Formula replacements, missing ranges and unresolved external dependencies have specific findings.
- Differences caused by units or rounding are separated from calculation defects.
- The released file contains approved values, not diagnostic inputs, and matches the final test report.

## Workflow

1. **Identify the calculation under review.** Record the issued template, completed candidate, amendments, permitted input areas and exact evaluated output. Preserve the originals and work in a clearly labelled diagnostic copy.
2. **Build an expectation independently.** Derive a small reference calculation from buyer instructions and approved inputs. Keep units, periods, inclusion rules and rounding explicit rather than importing the candidate’s formulas.
3. **Inspect the calculation paths.** Compare formulas and cell types with the issued file. Follow named ranges, branches, hidden areas and external dependencies feeding the evaluated outputs.
4. **Run changes with known consequences.** In the diagnostic copy, alter one permitted input at a time. Write the expected result first, recalculate in the specified application and record observed changes before restoring the baseline.
5. **Resolve findings under the right authority.** Separate data-entry mistakes, unsupported test expectations, environment differences and buyer defects. Obtain the appropriate correction or clarification and repeat affected tests plus total reconciliation.
6. **Verify the file that will leave the team.** Reopen the release candidate without access to unapproved working dependencies. Confirm approved inputs, native calculations and required outputs, then record the tested file identity and remaining restrictions.

## Key decisions

- Which cell or permitted calculation determines the evaluated price?
- Is the expected output supported by instructions, or merely by the reviewer’s preferred formula?
- Which rows, options and periods should affect this output?
- Can the receiving environment calculate the same result without a private rate file?
- Does a discrepancy originate in bidder entry, the issued template or the test itself?
- Who may authorize the remedy, and which downstream checks must be repeated?

## Risks

- Two compensating errors can leave the grand total unchanged.
- A warning-free sheet can contain valid formulas implementing the wrong calculation.
- A diagnostic change can accidentally survive into the final submission.
- Refreshing an external source can replace approved prices with newer, unapproved ones.
- Breaking links or changing workbook precision can destroy information needed for review.
- An unauthorized template repair can make a technically correct workbook unsuitable for submission.

## Metrics

- Price-bearing input paths tested against a documented expectation
- Material formula changes explained relative to the issued template
- Independent subtotals reconciled at the required precision
- External dependencies with an approved release treatment
- Open calculation defects or untested branches at release

## Frequently asked questions

### Is Excel’s error check enough for a tender pricing workbook?

No. A valid formula can omit a required row or use the wrong period without producing an error. Combine inspection with an independent expected calculation and controlled input changes. Record untested areas rather than treating a clean warning panel as proof.

### Can an automated checker validate the file?

It can inspect formula differences, cell types and selected relationships when it supports the file correctly. Distinguish those checks from executing calculations in the required application. Test the checker against known defects and never let it silently rewrite the submission template.

### What if the workbook is protected?

Use permitted inspection and input tests, document what protection prevents you from examining, and request an authorized test method when the gap matters. Do not bypass protection or infer that protected formulas must be correct.

### Should we break external links before sending the workbook?

First establish what each link contributes and what the buyer requires. Breaking links can freeze values and remove calculation logic. Any permitted removal or replacement needs a preserved baseline, approved values and another calculation test.

### Does a matching grand total prove that the lines are right?

No. Omissions and overstatements can cancel, and money can sit in the wrong lot or period. Check the components and the effect of individual inputs as well as the headline total.

### Can we submit a corrected buyer formula with an explanatory note?

Only where the tender’s instructions or an authoritative clarification permit that treatment. A note does not itself grant permission. Preserve the defect evidence and obtain the required decision before modifying the submitted calculation.

### What should the final validation report identify?

The exact file, governing calculation, application conditions, approved input set, expected and observed results, resolved defects, untested areas and release decision. Keep diagnostic work separate from the candidate that will be uploaded.


## Primary sources

- [AQuA Book: verification, validation and analytical assurance](https://www.gov.uk/guidance/the-aqua-book), UK Government
- [Detect formula errors in Excel](https://support.microsoft.com/en-us/excel/detect-formula-errors-in-excel), Microsoft
- [SUM function and treatment of referenced text](https://support.microsoft.com/en-us/excel/functions/sum-function), Microsoft
- [Calculation mode, iteration and precision in Excel](https://support.microsoft.com/en-us/excel/change-formula-recalculation-iteration-or-precision-in-excel), Microsoft
- [Manage workbook links and their calculation effects](https://support.microsoft.com/en-us/excel/manage-workbook-links), Microsoft
- [SUBTOTAL function: filtered and manually hidden rows](https://support.microsoft.com/en-us/excel/functions/subtotal-function), Microsoft
- [Creating and sharing spreadsheets](https://www.gov.uk/guidance/creating-and-sharing-spreadsheets), UK Government


## Related articles

- [How to populate a tender pricing schedule safely](https://zephior.com/insights/populate-a-tender-pricing-schedule)
- [How to clarify an ambiguous tender pricing template](https://zephior.com/insights/clarify-an-ambiguous-pricing-template)
- [How to price a tender when volumes are unknown](https://zephior.com/insights/price-a-tender-with-unknown-volumes)
- [How to validate every statistic in a proposal](https://zephior.com/insights/validate-proposal-statistics-before-submission)