How to Audit a Spreadsheet You Inherited: A 90-Minute Method

Someone leaves, and their model stays. It has a dozen tabs, a few colors that once meant something, and a total on the summary tab that everyone quotes. Nobody can tell you how it works. Your name is now on it.

You do not need to understand every formula before you use it. You need to know whether the numbers it produces can be trusted, and where they cannot. That is a different and much shorter job. This is the order I do it in. Ninety minutes for a model of ordinary size, longer for a monster, and you will know by the end whether to keep it, patch it, or rebuild it.

Minute 0 to 10: make a copy and map the tabs

Work on a copy. Save it with today's date in the name and do not change the original until you are done.

Then map the workbook before you read a single formula. List every tab, including hidden ones (right-click any tab, Unhide), and give each one a word: inputs, calculations, outputs, or unknown. Note which tabs feed which. Formulas, Trace Precedents on a summary cell draws a dashed arrow to a sheet icon when it pulls from other tabs, and double-clicking that arrow lists them; do that for the two or three figures people actually quote.

You are looking for three things. Tabs nothing references, which are usually old versions someone was afraid to delete. Tabs that are both input and calculation, where typed numbers and formulas sit in the same rows. And any sheet marked very hidden, which only appears through the Visual Basic editor or a file inspector. A very hidden sheet is not a problem by itself, but you need to know it exists.

Minute 10 to 25: find every hardcoded number

This is where inherited models often fail, and it is usually the check with the best return on time.

On each calculation tab, select the whole sheet, press Ctrl+G, choose Special, then Constants, then untick everything except Numbers. Excel selects every cell holding a typed number. Some belong there: an inputs block, a header year, a date. The ones that do not are typed numbers sitting in the middle of a formula row, where someone overwrote a calculation to make a month come out right.

Do the same for formulas that contain constants. A formula like =D14*1.035 hides a growth rate that nobody can see, change, or defend in a review. Formulas, Show Formulas (Ctrl+`) turns the sheet into text, and a quick read down each row will show you the ones with a literal in them. Note every one.

Two rules for what you find. A typed number inside a calculated row is a plug until proven otherwise. A constant inside a formula is an assumption that needs a cell of its own. You are not fixing anything yet. You are building a list.

Minute 25 to 40: check the totals and the pattern of each row

Totals go wrong in two ways, and both are quiet.

First, the short range. A row of twelve months with a total that sums eleven of them, because a column was inserted after the formula was written. Click each total and read the range it covers against the block beside it. Do it for columns too, where a subtotal was meant to cover ten cost lines and covers nine.

Second, the broken pattern. A calculated row should hold the same formula across every month, shifted one column at a time. Select the months in the row, starting with one you know is right so that it is the active cell, then press Ctrl+G, Special, Row differences. Excel selects every cell in the row that differs from the active cell. On a clean row, Excel reports that no cells were found. On an inherited model, expect a few hits per tab. Each one is either a legitimate exception, such as a quarter-end subtotal, or the hardcode you found in the last step wearing a formula's clothes.

While you are in each block, check the sign convention. Costs shown as negatives on one tab and positives on another is a common way a model adds when it should subtract.

Minute 40 to 55: trace the chain and look for circles

Pick the single most important output and walk it back to inputs. Trace Precedents, one level at a time, and write the chain down: summary EBITDA comes from the P&L tab, which comes from revenue and cost blocks, which come from the assumptions tab. If the chain crosses a tab you labeled unknown, that tab just became known.

Watch for three things on the way. Links to other workbooks (Data, Edit Links, which newer versions call Workbook Links, or search formulas for a square bracket). Excel usually warns on opening that automatic update of links has been disabled, but a workbook can be set not to ask. Anyone who clicks past the warning, or never sees it, keeps working from the values cached the last time the link updated, which for a file on someone's old laptop could be last year's. Named ranges that point at deleted cells (Formulas, Name Manager, look for #REF!). And circular references: if the status bar says "Circular References" or you see a cell listed under Formulas, Error Checking, Circular References, the model has a loop. Either it was built to iterate, which should be documented, or it is broken. Whether or not anything is flagged, check File, Options, Formulas, Enable iterative calculation. If it is switched on, Excel stops flagging loops, and a broken one can produce a plausible number every time you press F9.

Minute 55 to 75: test the inputs

Now change things. This is the part people skip, and it is the part that tells you whether the model is a model or a display.

Set one input to zero and read every output that should go to zero. If revenue units are zero and revenue still shows a figure, something is typed over. Double one input and confirm the outputs move by exactly what the arithmetic says, and that nothing else moves. Set the growth rate to a wild number and look for errors: #DIV/0!, #N/A, #VALUE! anywhere means an unguarded formula, and a wrapper like IFERROR that hides those errors is worse, because it hides them from you too.

Then put the inputs back. Compare your copy against the original with a simple check: on a blank tab, subtract the original's summary cells from the copy's. Every difference should be zero. If it is not, you changed something you did not mean to, and the audit taught you where the model is fragile.

Minute 75 to 90: decide

Write down what you found in three lists. Plugs and hidden assumptions, with cell addresses. Structural problems: short totals, broken patterns, external links, circular references. Things you could not explain in the time.

Then decide, and the decision is simpler than it feels.

Keep it as is if the lists are short and every item has an explanation you could give in a meeting. Patch it if the plugs are few and the structure is sound: move each constant to an input cell, fix each total, and leave a change log tab with the date and what you did. Rebuild it if you found a circular reference nobody planned, links to files you do not have, or more plugs than you can count on one hand. A model with that many patches is not saving you time; it is borrowing time from the next person, who will be you.

Whatever you decide, the ninety minutes were not wasted. You now know where the numbers come from, which is the only thing that separates a model you can defend from one you are quoting on faith.

What to keep after the audit

Three habits make the next handover easier. Inputs in one place, in one color, and nowhere else. Every constant in a cell with a label. And a notes tab that says what the model is for, what it is not for, and who last checked it. None of this takes an hour, and it is the difference between a model that outlives its author and one that dies with them.