Find numbers typed over formulas in an Excel report

Why these survive review

A reviewer reads the values. An overtyped 1,54,000 in the gross margin column looks exactly like a calculated 1,54,000, and it stays correct until sales for that branch change next month. The rate typed as 0.18 works until someone changes the input rate cell and expects every row to follow. The +25,000 was probably a sensible correction on the day it was made, with an email explaining it that nobody can now find.

All three pass a totals check in the month they are created, which is why they are found late.

In Excel: three built-in checks

Constants where formulas belong. Select the formula columns (for example D2:G200), press F5, choose Special > Constants and tick only Numbers. Excel selects every typed number in the range. In a column that should be entirely calculated, each selected cell is a finding. Fill them with a colour before clicking anywhere else, or the selection is lost.

A permanent highlight. For a report reused every month, add conditional formatting to the formula columns with the rule:

=NOT(ISFORMULA(D2))

Apply it to D2:G200 with D2 as the active cell. Anyone who overtypes a formula now turns the cell orange, and the next reviewer sees it at a glance.

Formulas that differ from their neighbours. Excel's error checking marks a cell whose formula does not match the pattern of the cells around it with a small green triangle ("Inconsistent formula"). This is controlled under File > Options > Formulas > Error checking rules; make sure that rule is switched on. To read formulas side by side, =FORMULATEXT(E5) in a spare column shows the formula as text, or press Ctrl+` to toggle formula view for the whole sheet.

An audit script for a whole workbook

Checking a monthly pack of fifteen sheets by hand is slow. The script below builds a synthetic five-branch P&L with the three problems planted in it, then audits it by reading formulas, not values. It uses the openpyxl package, which pandas also uses to read and write .xlsx files; install it with pip install openpyxl if needed. The Rent lookup refers to a sheet that is not created, which does not matter because openpyxl reads formula text and does not calculate.

import re
from collections import Counter
from openpyxl import Workbook, load_workbook

# Build a synthetic branch P&L sheet with three typical problems planted in it.
wb = Workbook()
ws = wb.active
ws.title = "PnL"
ws.append(["Branch", "Sales", "COGS", "Gross margin", "GST @18%", "Rent", "Net"])
rows = [("Noida", 820000, 540000), ("Ghaziabad", 610000, 402000), ("Meerut", 455000, 301000),
        ("Agra", 390000, 260000), ("Lucknow", 700000, 468000)]
for i, (b, s, c) in enumerate(rows, start=2):
    ws.append([b, s, c, f"=B{i}-C{i}", f"=B{i}*$J$1", f"=VLOOKUP(A{i},Rent!A:B,2,0)",
               f"=D{i}-F{i}"])
ws["J1"] = 0.18                      # the rate, in one labelled input cell
ws["D4"] = 154000                    # 1. a formula overtyped with a number
ws["E5"] = "=B5*0.18"                # 2. the rate typed into the formula
ws["G6"] = "=D6-F6+25000"            # 3. a "temporary" adjustment nobody removed
wb.save("pnl.xlsx")

# Audit: read formulas, not cached values.
ws = load_workbook("pnl.xlsx")["PnL"]
last = ws.max_row

def shape(formula, row):
    """Make a formula comparable across rows: B7-C7 and B8-C8 both become B{r}-C{r}."""
    return re.sub(r"(?<![$\d])([A-Z]{1,3})" + str(row) + r"\b", r"\1{r}", formula)

def has_typed_number(formula):
    """True if a number sits next to + - * / ^ once cell references are removed."""
    no_refs = re.sub(r"\$?[A-Z]{1,3}\$?\d+", "ref", formula)
    return re.search(r"[-+*/^]\s*\d|\d\s*[-+*/^]", no_refs) is not None

findings = []
for col in range(2, ws.max_column + 1):
    cells = [ws.cell(r, col) for r in range(2, last + 1)]
    formulas = [c for c in cells if isinstance(c.value, str) and c.value.startswith("=")]
    if len(formulas) < len(cells) / 2:
        continue                                      # an input column; constants are expected
    usual, _ = Counter(shape(c.value, c.row) for c in formulas).most_common(1)[0]
    for c in cells:
        v = c.value
        if not (isinstance(v, str) and v.startswith("=")):
            findings.append((c.coordinate, repr(v), "constant in a formula column"))
        elif shape(v, c.row) != usual:
            findings.append((c.coordinate, v, f"differs from the column's usual {usual}"))
        elif has_typed_number(v):
            findings.append((c.coordinate, v, "number typed inside formula"))

for f in findings:
    print(*f, sep=" | ")
assert [f[0] for f in findings] == ["D4", "E5", "G6"]

# Same check when the whole column shares the bad habit, so no row looks unusual.
bad_everywhere = ["=B%d*0.18" % r for r in range(2, 7)]
hits = [f for f in bad_everywhere if has_typed_number(f)]
print("column-wide literal:", len(hits), "of", len(bad_everywhere), "cells")
assert len(hits) == 5

It reports D4, E5 and G6 and nothing else.

How the audit decides what is unusual

Only formula columns are checked. If most cells in a column are formulas, the column is meant to be calculated, and constants in it are suspicious. The Sales and COGS columns are inputs, so their numbers are skipped.

Each formula is reduced to a shape. =B7-C7 and =B8-C8 both become =B{r}-C{r}, because their references point at their own row. The most common shape in the column is the expected one; E5's =B{r}*0.18 and G6's =D{r}-F{r}+25000 do not match it. Absolute references such as $J$1 are left alone, since they are supposed to be identical in every row.

Typed numbers next to operators are flagged. The comparison with neighbours cannot help when a whole column shares the same bad habit, for example if every GST cell says *0.18. The second check removes cell references and looks for a digit beside + - * / ^. It deliberately ignores numbers used as function arguments, such as the 2 and 0 in VLOOKUP(A2, Rent!A:B, 2, 0), which are column positions and match types rather than business assumptions.

Build the report so this is harder to do

Put every assumption, including GST rate, target growth and exchange rate, on one labelled inputs sheet, and give each a name through Formulas > Define Name, so formulas read =B5*GST_rate. Protect formula columns and leave only input cells editable. Keep an adjustments column with a reason and the approver's name instead of editing formulas. Then run the audit before the pack goes out each month, and treat an empty findings list as part of sign-off.

Reviewing someone else's workbook calmly, and fixing its structure rather than its symptoms, is a large part of an analyst's first year. It is practised with inherited, imperfect files in our Data Analyst programme. For a related sign-off step on budget packs, see budget vs actual variance on an April–March year.