Operations & Systems

Audit a Business Spreadsheet Formula: Trace the Inputs Before Trusting the Output

A workbook shows room for two new projects. The team plans around that number. Nobody checks whether the total includes the last person.

Start with the cell that supports the decision. Write the rule it should implement. Then follow its inputs until someone can explain each number.

This spreadsheet formula audit checklist reviews one calculation in an existing workbook. The worksheet is a proposed operating method. The capacity model below is fictional. It demonstrates a range error without claiming to detect every possible defect.

The review ends with evidence: the formula examined, the inputs accepted, the result reproduced, and the questions still open.

Define the Decision Before Opening the Formula

Choose a narrow question. For this example: how many uncommitted service hours remain in next week's plan?

That question differs from how much work the team can promise a customer. Available hours alone do not establish skill fit, scheduling overlap, or delivery authority. Keep the calculation's meaning smaller than the decision it informs.

Write the intended rule in ordinary language. Take scheduled hours for the included people. Subtract unavailable hours. Subtract hours already committed. Add the remaining hours across those people.

Name the planning period and the person who owns each input. Record when commitments were last checked. A mathematically correct total can still describe an outdated plan.

An operator dashboard can display the result. This review follows the calculation behind that display. It does not redesign the dashboard or choose the team's business measures.

Before investigating, preserve a review copy of the workbook. Record its identity and the version examined. Keep the original observed result available for comparison.

Build a Small Fictional Capacity Workbook

Use a worksheet named Capacity. Each row represents one fictional contributor for one planning week. These records contain no customer or employee data.

For this example, all inputs are accepted planning assumptions. Unavailable hours and committed hours are separate deductions. Neither deduction overlaps the other.

Row A: Contributor B: Scheduled hours C: Unavailable hours D: Committed hours E: Remaining hours
2 Person A 40 10 22 8
3 Person B 32 8 20 4
4 Person C 24 6 12 6

The intended row calculation is =B2-C2-D2 in E2, with corresponding references in E3 and E4. The accepted roster contains all three contributors.

Put the remaining-hours total in E6. Deliberately enter the incorrect formula =SUM(E2:E3). It includes Person A and Person B. It leaves Person C outside the range.

The observed total is twelve hours. The intended total is eighteen hours. The omission changes the answer even though the included rows contain reasonable values.

In F6, use =E6/6 as a provisional count of six-hour project blocks. The incorrect total produces two blocks. The corrected total produces three. Six hours is a fictional planning assumption, not a delivery estimate or promise.

Keep that downstream interpretation provisional. Three mathematical blocks do not prove that three projects fit the calendar.

Trace the Inputs in Excel Desktop

Use Excel for Microsoft 365 on Windows for this procedure. Microsoft's formula-relationship documentation covers this desktop edition and Excel 2024. It defines precedents as referenced cells and dependents as formulas referencing a selected cell.

Before tracing, open File, then Options, then Advanced. Under Display options for this workbook, select the review workbook. Set For objects, show to All. Microsoft lists this visibility setting before the tracing procedure.

  1. Select E6 in the review copy.
  2. Open Formulas, then Formula Auditing, then Trace Precedents.
  3. Repeat Trace Precedents to inspect another input level.
  4. Select E2, then choose Trace Dependents.
  5. Compare the traced cells with the written calculation rule.

Microsoft documents that another workbook must be open for tracing its references. Named constants, PivotTable reports, and certain object references are untraceable. Closed-workbook dependent formulas also have tracing limits. Consult the documented limitations when the diagram appears incomplete.

For this fictional formula, inspect the literal E2:E3 range. Compare it with the accepted three-person roster. The review question is whether E4 belongs in the total.

Tracing describes relationships in the implementation. The business rule supplies the expected relationships. An omitted input needs comparison with that rule; an arrow diagram cannot decide who belongs in next week's plan.

Follow F6 as a dependent as well. Record the downstream decision label before accepting a correction.

Check Each Input Against Its Evidence

Use the roster and planning records to challenge the workbook. Do not use the workbook as its own source of truth.

For B2, ask where forty scheduled hours came from. For C2, ask whether ten unavailable hours cover the same week. For D2, ask whether twenty-two committed hours exclude the unavailable time.

In a real review, locate the team's authorized planning record. Note its identifier and cutoff in the worksheet. Avoid copying private records into a broadly shared review document.

Give every input one of three outcomes: supported, disputed, or unavailable. Record a reason for disputed entries. Assign someone to resolve unavailable evidence.

Check the population separately from the arithmetic. Person C's valid row cannot affect E6 while the total excludes it. An accurate input outside the calculation still leaves the result wrong for the stated rule.

Check units before adding rows. The example uses hours for one week throughout. A monthly allowance or a count of working days needs an agreed conversion before it belongs in the calculation. Record that conversion beside the input.

Keep negative remaining hours visible during review. Under this proposed rule, a negative value indicates that deductions exceed scheduled hours. Ask whether the inputs overlap or the plan exceeds its stated capacity. Do not remove the row to make the total easier to explain. Assign the discrepancy to its owner.

This discipline also applies to Google Sheets automation. A receiving workflow needs an explicit input boundary. This article's tracing procedure concerns Excel desktop, rather than establishing equivalent behavior in Sheets.

Inspect Constants, Copied Formulas, and External References

Read the complete formula before accepting the displayed result. Inventory its ranges, operators, constants, and references to other worksheets or workbooks.

For the fictional row formulas, compare E2, E3, and E4 side by side. Check whether each subtracts the same row's unavailable and committed hours. A visually tidy column is insufficient review evidence.

Examine copied formulas at the first row, last row, and any transition between sections. Check the intended movement of relative references. Check whether fixed references remain attached to the intended assumption.

The six-hour divisor in F6 deserves its own question. Who selected six? Does it include preparation and review? Can it be used for this service type? Record the answer before treating blocks as a delivery plan.

If the workbook uses an external reference, identify the source file and the exact input it supplies. Ask whether that source represents the accepted planning cutoff. Leave the result unresolved when the required source cannot be examined.

Keep constants visible in the review record even when you choose not to relocate them. Moving a number to an assumptions tab can improve readability, but relocation alone does not validate it.

Give Blanks and Error Handling Explicit Meaning

Imagine D4 is blank instead of twelve. The reviewer now lacks a confirmed commitment value for Person C. Do not silently interpret that blank as evidence of no commitments.

Ask the owner whether the blank means zero, missing entry, or a contributor outside the plan. Record the answer. If the input remains unknown, label the capacity result provisional.

Next inspect any error-handling expression around the calculation. Write down its fallback value and the business meaning assigned to that value. Ask whether the fallback could resemble a valid operational result.

A displayed zero should have a defined meaning in the review. It might mean confirmed zero capacity. It might also be a chosen substitute for unavailable evidence. Those states require different decisions.

Test proposed corrections in the review copy. Include a known zero input, an intentionally missing input, and an ordinary populated input. Record the observed result for each case rather than assuming the display is enough.

Do not change live error-handling rules during the investigation. First agree how incomplete information should reach the person making the decision.

Copy This Formula Review Worksheet

Use one worksheet per decision-critical output. Expand the evidence rows when the calculation crosses multiple sources.

Field Fictional entry or review instruction
Workbook identity Capacity review copy, planning week recorded in its heading
Formula location Capacity!E6
Intended rule Sum remaining hours for Persons A, B, and C
Formula observed =SUM(E2:E3)
Input evidence Accepted roster and three planning rows at the stated cutoff
Observed result 12 hours
Expected result 18 hours under the stated assumptions
Related output F6 divides E6 by the assumed six hours per block
Finding Person C is absent from the total range
Proposed correction =SUM(E2:E4)
Independent check 96 scheduled minus 24 unavailable minus 54 committed equals 18
Reviewer Named reviewer, review date, and examined version
Unresolved questions Skill fit, calendar overlap, and approval of the six-hour assumption
Disposition Arithmetic corrected in review copy; operational decision pending

Replace fictional entries with references to retained evidence. Include the actual before-and-after formula text. A note saying “fixed the range” leaves the next reviewer guessing what changed.

Keep the reviewer's observation separate from the owner's approval. Someone can confirm arithmetic without having authority to accept a new commitment.

Correct the Range and Reproduce the Result Independently

In the review copy, change E6 to =SUM(E2:E4). The row results now add to eighteen: eight plus four plus six.

Cross-check through a different arrangement. Scheduled hours total ninety-six. Unavailable hours total twenty-four. Committed hours total fifty-four. Ninety-six minus twenty-four minus fifty-four equals eighteen.

This second path avoids reusing the original two-row sum. It still relies on the same accepted inputs. It does not independently validate the roster or the commitment records.

F6 now produces three six-hour blocks. Record the changed downstream result. Keep the scheduling and authority questions open until their owners answer them.

Revisit any report that consumed the incorrect twelve-hour total. Identify its period and recipient before deciding whether a correction is needed. Preserve the original observation alongside the revised result.

For reporting automation, carry the accepted calculation version into the report record. A corrected workbook should have a traceable relationship to the output people used.

Close the review with a bounded statement: E6 matches the stated three-person rule in the examined copy. Name any checks you did not perform.

Trust the result within the evidence you checked.

Sources

Topic map: Spreadsheets, CSV files, and data handoffs

Articles in this cluster, then the 3 clusters that share the most topics with it.

This cluster: Spreadsheets, CSV files, and data handoffs

Neighbor: Business reporting and statistics

Neighbor: Records, access, and continuity

Neighbor: APIs, webhooks, and automation