Why Audit Spreadsheet Formulas?
A spreadsheet can display a neat column of values while calculating some of them incorrectly. A formula may refer to the wrong row, a copied formula may shift a reference that should stay fixed, or a typed number may sit among calculated values. These problems can be hard to notice because the resulting values may look reasonable.
Formula auditing means checking both how a value was produced and whether the calculation matches the intended rule. This matters when cleaning data: an error in a conversion or review flag can change which records appear ready for analysis. A sensible audit compares formulas with one another, checks the cell references, and independently recomputes selected results.
How Formula References Behave
A formula can contain references to other cells. For example, if cell B2 contains a duration in seconds, the formula =B2/60 in C2 divides that value by 60 to express it in minutes. When this formula is copied down one row, a relative reference to B2 normally shifts to B3, giving =B3/60. This is useful when each row should be calculated from its own input.
A relative reference changes position when a formula is copied. An absolute reference, written with dollar signs such as $F$1, stays fixed. A mixed reference locks either the column or the row. These distinctions matter when a formula uses a shared value, such as a threshold stored in one cell. If F1 contains the review threshold, a formula such as =IF(C2>$F$1,"Review","OK") should use C2 for the current row and keep $F$1 fixed as the formula is filled down.
A Basic Audit: Follow the Reference Pattern
Suppose a hypothetical clinic spreadsheet is being cleaned so that appointment durations recorded in seconds are converted to minutes. The conversion rule is minutes = seconds divided by 60. The seconds are in column B and the cleaned minutes are intended for column C. For rows 2 through 5, the intended formulas are =B2/60, =B3/60, =B4/60, and =B5/60.
Worked Example: Finding a Shifted Row Reference
The seconds recorded in B2 through B5 are 420, 510, 360, and 600. The formulas shown in C2 through C5 are =B2/60, =B3/60, =B3/60, and =B5/60. Check the column for an inconsistent reference and calculate its effect.
Step 1: Identify the pattern. Each row in column C should use the value from column B in the same row. Thus C2 should use B2, C3 should use B3, C4 should use B4, and C5 should use B5. The formula in C4 breaks that pattern: it uses B3.
Step 2: Compare the results. The entered formula in C4 calculates 510/60 = 8.5 minutes. But the value in B4 is 360 seconds, so the intended result is 360/60 = 6 minutes. The formula therefore assigns row 4 a value 2.5 minutes too high.
Step 3: Correct and verify. Replace the formula in C4 with =B4/60. Its result is 360/60 = 6 minutes, as required by the conversion rule. The other formulas match their rows: 420/60 = 7, 510/60 = 8.5, and 600/60 = 10 minutes.
The error is visible by comparing formula structure, not just the displayed numbers. A useful check is to inspect formulas at the top, middle, and bottom of a calculated column, then examine every row that looks inconsistent or has an unexpected result.
Relative and Fixed References in a Copied Formula
Not every reference should change when a formula is filled down. If each converted duration is compared with a shared threshold, the current-row value must change but the threshold reference must remain fixed. Suppose F1 contains 8, the threshold in minutes. The formula in D2 can be =IF(C2>$F$1,"Review","OK"). When copied down, it should become =IF(C3>$F$1,"Review","OK"), then =IF(C4>$F$1,"Review","OK"), and so on. The row-specific reference changes; the absolute reference to F1 does not.
This comparison marks a record “Review” when its duration is greater than 8 minutes and “OK” otherwise. In particular, a duration exactly equal to 8 minutes is “OK,” because it does not satisfy the strict greater-than condition. The rule should be stated before the formula is checked; otherwise, a reviewer cannot tell whether the formula implements the intended decision.
Worked Example: Checking a Review-Flag Formula
The cleaned durations in C2 through C5 are 7, 8.5, 6, and 10 minutes, and F1 contains the threshold 8. The intended formula in column D is =IF(C2>$F$1,"Review","OK"), copied down. Suppose D4 instead contains =IF(C3>$F$1,"Review","OK"). Find the error and the correct flags.
Step 1: Apply the rule to each row. For row 2, 7 is not greater than 8, so the flag is “OK.” For row 3, 8.5 is greater than 8, so the flag is “Review.” For row 4, 6 is not greater than 8, so the flag is “OK.” For row 5, 10 is greater than 8, so the flag is “Review.”
Step 2: Check the formula in D4. D4 refers to C3, not C4. Since C3 is 8.5, the formula returns “Review.” That result is wrong for row 4, whose duration is 6 minutes. The correct formula, =IF(C4>$F$1,"Review","OK"), returns “OK.”
Step 3: Verify the shared reference. In each row, the reference to column C should follow the row number. The reference $F$1 should stay fixed. If it were copied down as F1, F2, F3, and F4, the formulas below the first row could compare durations with blank or unrelated cells instead of with the threshold.
Checking only whether a formula contains the right column names is not sufficient. Both the row-specific reference and the fixed threshold reference must point to the intended cells.
Look for Hard-Coded Values
A hard-coded value is typed directly into a cell rather than calculated by a formula. Hard-coded values are not automatically wrong: raw observations and fixed inputs such as a threshold may appropriately be entered as values. The concern is an unexplained value inside a column that is supposed to be calculated. Such a value can break the calculation pattern even when it happens to equal the correct result.
To check, select cells in the calculated column and inspect the formula bar or the spreadsheet’s formula view. A formula such as =B4/60 identifies how the value was obtained. A plain number such as 6 does not. Compare the cell with the surrounding formulas, then recalculate the expected result from the source data.
Worked Example: Finding a Plausible Hard-Coded Value
The seconds in B2 through B5 are 420, 510, 360, and 600. Cells C2, C3, and C5 contain the formulas =B2/60, =B3/60, and =B5/60. C4 displays 6, but selecting it shows that the cell contains the number 6 rather than a formula. Decide whether this is a problem.
Check the arithmetic. The intended conversion for row 4 is 360/60 = 6 minutes. So the displayed value is numerically correct for the current source value.
Check how it was produced. Unlike the surrounding cells, C4 does not calculate from B4. If B4 is later corrected from 360 to 420 seconds, C4 will still display 6, while a formula =B4/60 would produce 420/60 = 7 minutes. The value is therefore a hidden break in the calculation, even though its present display is plausible.
Correct the cell. Enter =B4/60 in C4 and verify that the result is 6. If the cell was intentionally meant to contain a fixed value rather than a calculated value, that intent must be clear to someone reviewing the sheet; it should not be mistaken for part of the formula column.
A Practical Audit Sequence
A reliable audit starts with the purpose of the formula, not with clicking through cells at random. State what each calculated column should do, identify its input cells, and then compare both the formula pattern and the computed results. The following sequence keeps those checks distinct.
For example, “convert seconds to minutes by dividing by 60,” or “mark a duration for review only when it exceeds 8 minutes.”
Check whether each row uses its own input and whether shared inputs, such as a threshold, are fixed where required.
Inspect cells in calculated columns for typed values or blanks that interrupt the expected pattern.
Use the source value and stated rule to check results, including boundary cases such as a value exactly equal to a threshold.
After fixing an error, verify that the formula refers to the intended cells and that its displayed output matches an independent calculation.
Why These Checks Matter
A spreadsheet formula error can silently affect a cleaned value or a category assigned to a record. If a wrong duration is used in a later summary, or a wrong flag determines which rows are reviewed, the consequences can continue into later analysis. A formula audit does not prove that the original data are accurate or that the cleaning rule is appropriate. It checks whether the spreadsheet implements the stated rule consistently and correctly.
Independent recomputation is particularly useful because two formulas can be consistently wrong. For example, if every conversion formula divides by 100 instead of 60, the column may have a perfectly regular reference pattern. Comparing selected outputs with the stated conversion rule would reveal the error. Formula-pattern checks and arithmetic checks answer different questions, so neither replaces the other.
Formula auditing is most effective when the sheet’s intended rules are explicit and the relevant inputs are identifiable. When a formula or cleaning choice is corrected, keep the correction distinguishable from the original data and make the reason understandable to the next person reviewing the work. The central habit is simple: check the formula, check its references, and check its result.
Check Your Understanding
Answer each question using the stated formula rule and cell references.
- Cells B2 and B3 contain 300 and 480 seconds. C2 contains =B2/60, but C3 contains =B2/60. What formula should be in C3, and what value should it return in minutes?
- A cell in a calculated column displays 12, but the formula bar shows only the number 12. What should you check before deciding whether this is an error?
- F1 contains a threshold of 8. A formula in D2 is =IF(C2>$F$1,"Review","OK"). When copied to row 3, which reference should change and which should remain fixed?
- Under the rule “Review if the duration is greater than 8 minutes,” what flag should a duration of exactly 8 minutes receive? Explain using the comparison in the rule.
- Why might checking that every formula in a column has the same reference pattern fail to reveal a mistaken conversion rule?