The 10-Minute Model Check Before You Hit Send

Every analyst has a version of this story. The model was right. The analysis was right. But the file that went out had a broken external link, or a #REF! quietly swallowed by an IFERROR, or a tab still named "Sheet4 (2)" sitting next to the DCF. Nobody remembers that your IRR bridge was elegant. They remember the error dialog that popped up when the client opened the file.

Model errors cost credibility slowly. File hygiene errors cost it instantly, because they're visible to people who will never check your math.

Here's the pre-send check I run, in the order I run it. Ten minutes on a big model, three on a small one.

1. Break the external links, or know why you haven't

Data tab, Edit Links. If the dialog is grayed out, you're clean. If it isn't, every entry is a file dependency traveling with your workbook. The recipient won't have those files, and Excel will greet them with an update prompt at best and a wall of #REF! at worst.

For anything leaving the building: break the links (which freezes them to values) or rebuild the pull properly inside the workbook. For internal files, at minimum know what each link is and confirm the source still exists where the link thinks it does. "I didn't know there was a link to the old comps file" is not a sentence you want to say out loud.

2. Hunt the errors that are hiding from you

Searching for visible errors is easy: Ctrl+F, search #REF! with Look in set to Values, then repeat for #VALUE! and #DIV/0!.

The dangerous ones are wrapped. =IFERROR(complex_thing, 0) displays a clean zero while the complex thing has been broken for three versions. So run a second search with Look in set to Formulas for IFERROR and eyeball each hit: is this handling a legitimate edge case, or is it a muzzle on an error somebody didn't want to fix on a Friday? IFERROR in a lookup that can genuinely miss is engineering. IFERROR around a division in your returns calc is a confession.

3. Confirm the model actually calculates

Press F9 and watch the status bar. If you see "Calculate," the workbook has been switched to manual calculation at some point (often by a macro, sometimes by a colleague, occasionally by Excel itself after a crash), and some of the numbers on screen may be stale. Formulas tab, Calculation Options, back to Automatic, then Ctrl+Alt+F9 to force a full rebuild and confirm nothing moves that shouldn't.

While you're there: any circular reference warning in the status bar gets resolved now, not explained later. An intentional circularity (interest on average debt is the classic) should be documented on the sheet with the iteration settings it needs. An unintentional one is a bug wearing a trench coat.

4. Clean the Name Manager

Formulas tab, Name Manager. Every model that has lived through a few copy-paste generations accumulates corpses in here: named ranges pointing at #REF!, names scoped to sheets that no longer exist, links to workbooks from two deals ago. Sort by value, delete everything broken. This is also where phantom external links hide after you think you've broken them all.

5. Check what you're actually shipping

Unhide everything (right-click any tab, Unhide, repeat until empty; for many sheets a two-line VBA loop is faster) and look at what's there. Old scenario tabs, a pasted board page from last quarter, someone's scratch sheet with an unflattering sensitivity on it. Decide deliberately: delete, or hide with intent. Hidden is not secure. Anything in the file goes with the file, and "very hidden" in VBA is a speed bump, not a wall.

Then check the file's metadata footprint: File, Info, Inspect Workbook. Comments, hidden properties, the author names of everyone who has ever touched the file. Takes thirty seconds and occasionally saves you from shipping a reviewer's margin note to the client it was about.

6. The courtesy pass

Small things that signal care, or its absence:

Set every tab's cursor to A1 and scroll position to top-left, then save with the summary tab active. The file opens for the recipient the way a document should open, at the beginning. (Ctrl+Home on each tab; on a 40-tab model this is tedious by hand, which is why it never gets done.)

Print preview the tabs that matter. Half of all models still get printed or PDF'd by someone senior, and a page break through the middle of your sensitivity table reads as sloppiness even though nobody ever set up print areas on purpose.

Zoom levels consistent. Tab names real words. Gridlines and headings set the way your shop expects. None of it changes a number; all of it changes how the numbers are received.

7. One last hardcode scan

Select the projection columns, F5, Special, Constants, Numbers. Highlighted cells outside your input rows are typed numbers where formulas should be. Go To Special can't see constants buried inside formulas (that's a deeper scan, covered in its own post), but the surface pass catches the worst offenders in under a minute.

Making it automatic

The honest problem with checklists is that they work right up until the night you're too tired to run one, which is precisely the night you need it. That failure mode is why we built the Audit tool in ExcelSurge the way we did: one click runs this entire list (links, masked errors, circularities, stale names, hardcodes, calculation state) across every tab, hands back a prioritized findings list, and rolls it up into a workbook health score. One click covers the discipline you'd apply on your best day, on your worst one.

Checklist or tool, the principle is the same. The math earns you credibility over months. The file protects it in the ten seconds after someone opens the attachment. Spend the ten minutes.