Formula Audit XL
Excel Model Audit Checklist
34 checks for a financial model review. Tick each box when the check passes. "Excel" says how to check with built-in commands. "Formula Audit XL" says where the add-in does it.
1. Structure
-
01A cover sheet states the purpose, owner, version and key assumptions.
Excel: Open the first tab. If it does not tell a new reader what the model is for, add one before the review starts.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
-
02Inputs, calculations and outputs sit on separate, clearly named sheets or blocks.
Excel: Walk the sheet tabs and the top of each sheet. Each one should say what it holds.
Formula Audit XL: Quick Navigation (Ctrl+Shift+N) lists every sheet in one panel. Cell Map (Ctrl+Shift+M) shows where inputs and formulas sit.
-
03The calculation flows one way: from inputs to outputs, left to right, top to bottom.
Excel: Select sample cells and use Formulas > Trace Precedents. Arrows that point down or right back into a block are a warning sign.
Formula Audit XL: Explore Precedents (Ctrl+Q) and Explore Dependents (Ctrl+Shift+Q) show the full chain as a tree, across sheets.
-
04No hidden or very hidden sheet without a stated reason.
Excel: Right-click a sheet tab > Unhide lists hidden sheets. Very hidden sheets show only in the VBA editor (Alt+F11), in the sheet Visible property.
Formula Audit XL: Model Check (Ctrl+Shift+R) counts and names hidden and very hidden sheets. Quick Navigation marks them [H] and [VH] and can make them visible again.
-
05No hidden rows or columns that hold live numbers.
Excel: Look for gaps in the row numbers and column letters, then use Home > Format > Hide & Unhide.
Formula Audit XL: Utilities > Unhide makes every hidden row and column visible on the active sheet or the whole workbook.
2. Inputs and hardcodes
-
06Every assumption is entered once, in a labelled input cell.
Excel: Press F5 > Special > Constants > Numbers on each calculation sheet. Typed numbers outside the input area light up.
Formula Audit XL: Cell Map (Ctrl+Shift+M) gives typed numbers, unique formulas and formulas without references their own colours on the whole sheet.
-
07No numbers typed inside formulas, such as =B4*1.2 or =C10/12.
Excel: Press Ctrl+` to show formulas and read each row. There is no reliable Find pattern for this.
Formula Audit XL: Hardcoded Numbers (Ctrl+Shift+H) lists every numeric constant inside a formula. Constant Formulas (Ctrl+Shift+K) lists formulas with no cell reference at all.
-
08No typed value overwrites a formula inside a calculated row.
Excel: Use F5 > Special > Constants inside a block of formulas. One constant in a row of formulas is an overtype.
Formula Audit XL: Cell Map (Ctrl+Shift+M) gives a typed number its own colour, so it stands out in a row of copied formulas.
-
09Inputs look different from formulas, for example blue font for inputs.
Excel: Scan the input sheets. A reader must see at a glance which cells they may change.
Formula Audit XL: AutoColor (Ctrl+Shift+A for the sheet, Ctrl+Shift+U for a selection) applies finance-standard font colours by content.
-
10Input cells show their units and use data validation where a range is known.
Excel: Use Data > Data Validation > Circle Invalid Data to find inputs outside their allowed range.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
3. Formula consistency
-
11Each row (or column) uses one formula, copied across.
Excel: Press Ctrl+` and compare neighbouring cells. Excel’s green triangles flag only some breaks.
Formula Audit XL: Inconsistent Formulas (Ctrl+Shift+I) compares formulas in R1C1 form and lists every cell that breaks its row or column pattern.
-
12No formula is too long to read in one pass.
Excel: Click through the formula bar on key rows. Split long formulas into helper rows.
Formula Audit XL: Model Check (Ctrl+Shift+R) counts formulas longer than 200 characters on each sheet.
-
13Nesting stays shallow: no more than five levels of brackets.
Excel: Read the nested IF and LOOKUP chains in the formula bar. Replace deep IFs with a lookup table.
Formula Audit XL: Model Check counts formulas nested more than five bracket levels deep on each sheet.
-
14Absolute and relative references are anchored correctly before formulas are copied.
Excel: Press F4 to cycle a reference. Check that copied formulas still point at the right input row.
Formula Audit XL: Utilities > Make References Absolute or Relative rewrites the selected formulas. Inconsistent Formulas catches the result of a wrong anchor.
-
15No formula points at an empty cell.
Excel: Trace Precedents on key outputs and open each source cell.
Formula Audit XL: Explore Precedents (Ctrl+Q) lists every source cell of a formula as a tree, so an empty source is easy to spot.
-
16Signs follow one convention, for example costs always negative.
Excel: Check that subtotals add, not subtract, the lines above them, and that each total has the sign you expect.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one. Utilities > Switch Signs flips a selection of formulas when you fix one.
4. Links and references
-
17Every link to another workbook is listed and intended.
Excel: Use Data > Workbook Links (Data > Edit Links in older Excel).
Formula Audit XL: External Links (Ctrl+Shift+L) lists every external reference and can break one link or all of them.
-
18No external link is broken.
Excel: In Data > Edit Links, select each source and click Check Status.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one. External Links lists each source file path, so you know which ones to check.
-
19Every defined name is valid and used.
Excel: Open Formulas > Name Manager and sort by Value to find #REF! names. Unused names need a search of every formula.
Formula Audit XL: Named Ranges (Ctrl+Shift+J) flags broken, error and unused names and deletes them in the panel. The free Named Range Checker lists them in your browser.
-
20Cross-sheet references point at the right sheet and row.
Excel: Trace Precedents shows a dashed arrow to a sheet icon. Double-click it to open the list of sources.
Formula Audit XL: Explore Precedents follows references across sheets and named ranges. Cell Map marks cross-sheet and cross-workbook references.
-
21Every calculation feeds something: no dead cells.
Excel: Use Trace Dependents on suspect cells. Excel says when a cell has no dependents.
Formula Audit XL: Dead Cells (Ctrl+Shift+D) lists the cells on the active sheet that nothing references.
5. Errors
-
22No error values (#REF!, #DIV/0!, #N/A, #VALUE!) anywhere in the workbook.
Excel: Use F5 > Special > Formulas > Errors on every sheet, one sheet at a time.
Formula Audit XL: Error Checker (Ctrl+Shift+E) lists every error value across all sheets. Model Check counts them.
-
23IFERROR and IFNA do not hide real errors.
Excel: Use Ctrl+F, Within: Workbook, Look in: Formulas, and search for IFERROR. Check what each one replaces.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
-
24Check rows return zero: the balance sheet balances and cash ties.
Excel: Build one check row per statement and give it a conditional format that turns red when it is not zero.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
6. Circularity
-
25No unintended circular reference.
Excel: Use Formulas > Error Checking > Circular References. Excel shows one circular cell at a time.
Formula Audit XL: In the Excel task pane, Circular References shows a circular path of cells for each sheet that has one. The free Circular Reference Finder lists them in your browser.
-
26Iterative calculation is off, unless the model needs it and says so.
Excel: Open File > Options > Formulas and look at Enable iterative calculation. When it is on, Excel stops warning about circular references.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
-
27An intended circularity, such as interest on average cash, has a switch that breaks the loop.
Excel: Look for an input that sets the loop to zero, and test that the model still calculates with it off.
Formula Audit XL: In the Excel task pane, Circular References shows the cells of the loop, so you can confirm the switch is part of it.
7. Outputs and presentation
-
28Outputs link to calculations. No output is typed.
Excel: Click through the output and summary sheets and read the formula bar.
Formula Audit XL: Cell Map shows hardcoded numbers on output sheets at a glance.
-
29Key outputs reconcile to a source: actuals, the prior version or the data room.
Excel: Tie three or four headline numbers to their source and note the result.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
-
30Number formats are consistent: thousands, decimals, negatives in brackets, percentages.
Excel: Scan each output table at 100% zoom.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one. Utilities > Number Cycle and More / Fewer Decimals apply a consistent format when you fix one.
-
31Charts and summary tables read the current ranges.
Excel: Click each chart series and check the range in its =SERIES formula covers every period.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
8. Version control
-
32The file name carries a version number and a date, and only one file is current.
Excel: Check the folder. Archive older versions where nobody will edit them by mistake.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
-
33A change log records what changed, who changed it and when.
Excel: Keep a log sheet or a note next to the file and update it with every version.
Formula Audit XL: Manual check. Formula Audit XL does not automate this one.
-
34The new version is compared with the last reviewed one before sign-off.
Excel: Use Spreadsheet Compare or Inquire (in some Microsoft 365 and Office Professional Plus editions), or compare key outputs by hand.
Formula Audit XL: Model Check writes a _ModelCheck sheet you can keep with each version, so you can compare the counts between versions.
formulaudit.com/resources/excel-model-audit-checklist · Formula Audit XL, the Excel add-in for model audit and review.