Articles

How do you audit a spreadsheet someone sends you?

Do not start by reading formulas. Rebuild the figures, put them side by side, and let the differences show you where to read.

The fastest reliable way to audit a spreadsheet you did not build is to rebuild its key figures independently, compare them one by one with a tolerance, and read the formulas only where the figures differ. In our worked case, 7 of 20 figures in a received summary do not match, and five root causes explain them. Each wrong figure looked plausible.

The case comes from the AI in Excel for Analysts course by Julian R. Sterling, author of The Real Estate Asset Manager.Get the DRM-free PDF, $19.99 →

Compare first

Brackenridge portfolio (fictional): received summary less your own. Blank means no difference.
MeasureAlder HouseBirch CourtCedar YardDunmore ParkPortfolio
Lettable area (sqm)-300-300
Passing rent (EUR k)-60.0-60.0
Vacancy rate (points)+0.57
WALT (years)-0.008+0.017
Figures flagged10123

A flag is one formula per figure: =IF(ABS(difference)>tolerance,1,0). Without a tolerance, rounding would flag everything. The count goes in your reply: 7 of 20.

Read the pattern of the gaps

From seven flags to five causes

Root causes found in the received summary.
FigureWhat the formula doesWhat it should do
Area of Dunmore ParkAdds the criterion use = LogisticsProperty only: the site has one office unit
Rent of Alder HouseSelects offices onlyEvery unit of the property, including the retail unit
Vacancy of Cedar YardDivides by the let areaDivides by the total lettable area
WALT of Dunmore ParkWeights by areaWeights by passing rent
WALT of the portfolioAverages the four property WALTsWeights every lease by its rent

The two other flags are portfolio totals that inherit the area and rent errors. Report causes, not cells: what the formula does, what it should do, which figures it affects and by how much.

Where an AI assistant helps

Give it the suspect formula as text, the layout of the data, the intended definition, and the two figures. Ask how the formula differs from the definition and which rows would explain the gap. Do not ask whether the model is correct: an assistant asked for a verdict tends to give a reassuring one. It can read a formula faster than you; it cannot know which definition your firm uses.

Before you write back

A difference says the two versions disagree, not who is right. Trace each gap to the rows. Your own figures deserve trust only if they pass their tests on a small sample and their totals tie by two routes.

The usual suspects

Five kinds of error account for most findings: a range too short, too long or drifting; a number typed over a formula; a logic error in a criterion, a base or a weight; a unit or sign error; and a stale input. All five errors of this case were logic errors, the kind that returns a plausible number.

Sources

  1. Julian R. Sterling, AI in Excel for Analysts (video course, 2026): running case Brackenridge portfolio
  2. Microsoft Support, function reference for SUMIFS, SUMPRODUCT, INDEX, MATCH and IFERROR
  3. ICAEW, Twenty principles for good spreadsheet practice; R. Panko, What We Know About Spreadsheet Errors

Every figure is computed by script from the course's running case, the Brackenridge portfolio, a fictional rent roll of 20 units, and checked on 19 November 2026. Function availability depends on your version of Excel. For education, not investment advice.

Formulas you can prove, step by step

26 short video lessons on using an AI assistant in Excel without being wrong: the five-part brief, SUMIFS, SUMPRODUCT, INDEX and MATCH, a mini sample with a known answer, checks that tie and the audit of a received model. You build a checked summary from a rent roll, with step files, exercises, a prompt kit and a certificate exam ($149).

Watch module 1 free →

Questions readers ask

What is the quickest way to audit a spreadsheet?

Rebuild the key figures independently, compare them with a tolerance, and read the formulas only where the figures differ.

How should spreadsheet errors be reported?

By root cause: what the formula does, what it should do, the figures affected and the size of the effect. A list of cell addresses does not help the author.

Can an AI assistant audit a spreadsheet?

It can read a suspect formula and say how it differs from a stated definition. It cannot know your definitions or see the data unless you provide them, and a request for a verdict gets a generic answer.

Read the whole case

This article is one calculation from The Real Estate Asset Manager. The book takes the same case from first principles to the decision, chapter by chapter, and every figure it prints is a live formula in the free companion workbooks.

See the course →Free companion files

Also on Amazon UK · Amazon Germany · Amazon France · Amazon Canada

Prefer a PDF? The same book is sold as a DRM-free PDF edition, searchable and printable. Get the PDF edition, $19.99

Also on this site

All 2164 articles →