Spreadsheet Audit Checklist: Find Formula Errors, Broken Links, and Reporting Mistakes

Overview

Spreadsheet error checking is more than looking for cells that display an error message. A workbook can return plausible numbers while still using the wrong range, an outdated data import, an incorrect sign, or a hidden sheet that no longer supports the intended calculation. The purpose of an audit is to test whether the workbook is understandable, complete, reproducible, and fit for its intended decision.

Before starting, make a copy of the original file and name the working copy with the audit date. Record the workbook owner, purpose, reporting period, source files, and intended audience. If the workbook supports a recurring report, note the normal refresh steps. This context helps distinguish a genuine error from an intentional exception.

For a small workbook, a structured manual review may be sufficient. Larger financial models, KPI dashboards, and operational reports benefit from a second reviewer, documented checks, and a clear separation between raw data, calculations, and presentation outputs.

Printable spreadsheet audit checklist

• ☐ Create a dated backup and preserve the original version.
• ☐ Confirm the workbook’s purpose, owner, reporting period, and audience.
• ☐ List every input sheet, calculation sheet, output sheet, and external data source.
• ☐ Check that source data is complete, current, consistently formatted, and within the expected date range.
• ☐ Search formulas for errors, hard-coded values, unusual references, and inconsistent patterns.
• ☐ Review named ranges, tables, pivot sources, charts, filters, and conditional formatting.
• ☐ Inspect hidden sheets, hidden rows and columns, grouped sections, and protected cells.
• ☐ Test totals, subtotals, percentages, dates, units, and sign conventions.
• ☐ Check links to other workbooks, imported ranges, queries, and refresh instructions.
• ☐ Compare summary outputs with an independent calculation or prior approved report.
• ☐ Record each issue, its impact, its owner, and the required correction.
• ☐ Save a final reviewed copy and state the date or condition for the next audit.

Checklist by scenario

Before a recurring business report

Start with the reporting period. Confirm that dates, filters, and cut-off rules include the intended transactions and exclude duplicates or future records. Check whether the source file has been replaced since the previous report and whether column names or sheet locations changed. Refresh imports, pivot tables, and queries according to the workbook’s normal process, then compare key totals with the previous reporting cycle.

For a KPI dashboard, verify that every displayed metric has a clear definition, source, unit, and period. Review whether a blank value means zero, unavailable data, or an error. Check that charts use the intended range and that labels, legends, and filters do not imply a conclusion the underlying data cannot support. A KPI dashboard spreadsheet template can provide a useful structure, but its definitions and source mappings still need to be audited.

Before a budget, forecast, or financial model

Trace the flow from assumptions to outputs. Identify inputs such as volume, price, headcount, payment timing, tax assumptions, or cost rates, and confirm that each is labeled with units and a time period. Test whether changing one assumption changes only the outputs that should depend on it. Review formulas across periods for accidental omissions, copied references, and inconsistent absolute or relative references.

Recalculate selected outputs outside the workbook. For example, independently check revenue, gross margin, cash balance, break-even volume, or forecast growth using a small sample of rows. When reviewing a cash flow forecast spreadsheet or scenario analysis spreadsheet, confirm that scenarios change the intended assumptions rather than overwriting base-case values.

Before a decision or operational handoff

Review the workbook as a new user would. Can someone identify where to enter data, which cells are formulas, what each output means, and how to refresh the file? Test permissions and protection settings. Remove obsolete tabs or clearly label them as archived. If the spreadsheet supports vendor selection or prioritization, independently inspect the scoring weights and criteria; a weighted decision matrix spreadsheet is only as reliable as those assumptions.

What to double-check

In Excel, use formula auditing tools such as trace precedents, trace dependents, error checking, and the formula view where appropriate. Inspect defined names and workbook links separately from visible cells. In Google Sheets, review version history, named ranges, protected ranges, filter views, connected data, and import formulas. The exact menu names can vary by application version, so focus on the underlying check rather than a particular interface.

Pay special attention to formulas that suppress errors with functions such as IFERROR. Error suppression can make a report look clean while concealing missing or invalid data. Confirm that the fallback value is appropriate and that exceptions are visible somewhere in the process.

Common mistakes

• Auditing only visible output: A polished dashboard can conceal an incomplete source range or stale calculation. Trace important figures back to their inputs.
• Trusting totals without testing detail: Two errors can offset each other. Reconcile selected rows, categories, and periods instead of checking only the grand total.
• Sorting one column: Sorting part of a data set can separate values from their records. Select the full table or use a structured table before sorting.
• Ignoring blanks and zeros: A blank may represent missing data, while zero may represent a measured result. Define the distinction before interpreting metrics.
• Leaving hard-coded adjustments unexplained: Manual overrides may be valid, but they should include a note, date, owner, and reason.
• Checking formulas but not assumptions: A technically correct formula can still produce a poor decision when its input definition or time frame is wrong.
• Overwriting the evidence: Correct the working copy, but retain the original and the issue log so changes can be reviewed.

When to revisit

Run this audit before a seasonal planning cycle, recurring report distribution, budget approval, forecast update, or important operational handoff. Revisit the workbook whenever source data is replaced, a worksheet is redesigned, a formula is changed, a new scenario is added, or Excel or Google Sheets features alter the refresh or calculation workflow.

For a stable recurring report, set a lighter pre-publication check and a deeper periodic review. The pre-publication check can cover refresh status, reporting dates, key totals, visible errors, and output formatting. The deeper review should revisit formulas, links, hidden content, permissions, assumptions, and documentation.

Finish each audit with a short issue log containing the cell or range, problem, business impact, correction, reviewer, and status. Then save the reviewed workbook with a clear version name and write down the next trigger for review. If the file informs a sales forecast, break-even decision, project plan, or strategic plan, link the audit record to that process. Related tools such as the sales forecast spreadsheet template, break-even analysis spreadsheet template, and strategic planning spreadsheet template can be reviewed using the same input-to-output method.

Next action: choose one important workbook, make a dated copy, complete the checklist from top to bottom, and do not mark it ready until every unresolved issue has an owner and a documented decision.