Skip to content
Formula Audit XL

Print this page on A4 (Ctrl+P), or download the PDF. Back to the checklist page.

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.

Model: ____________________   Reviewer: ______________   Date: __________

1. Structure

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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

  1. 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.

  2. 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.

  3. 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

  1. 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.

  2. 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.

  3. 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

  1. 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.

  2. 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.

  3. 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.

  4. 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

  1. 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.

  2. 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.

  3. 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.