FormulaSurge
All articles

Article

Excel Formula Auditing: 8 Tools & Best Practices

Excel formula auditing best practices - Trace Precedents, Evaluate Formula, Watch Window, Error Checking, Inquire and a rule to spot hard-coded numbers.

  • formula auditing
  • trace precedents
  • evaluate formula
  • audit
3 min readBy
Excel Formula Auditing: 8 Tools & Best Practices

Most broken workbooks don't show an error. The total is just wrong, because a formula was typed over, a range stops one row short or a lookup points at the wrong column. Excel's built-in auditing tools find most of these in minutes, if you know which one to reach for. These are the eight we use, in the order we use them.

1. Show Formulas (Ctrl+`)

Press Ctrl+` (the key left of 1) to switch every cell to its formula. Scan each column: a formula column should show the same pattern all the way down. A number in the middle of a column of formulas is a typed-over value. Press Ctrl+` again to switch back.

2. Trace Precedents and Trace Dependents

Select a cell and choose Formulas > Trace Precedents to draw arrows to every cell it reads. A dashed arrow with a sheet icon means the source is on another sheet or in another file - double-click the arrow to jump there. Trace Dependents does the reverse: it shows what will change if you edit this cell. Remove Arrows clears them.

3. Evaluate Formula

For a long nested formula, Formulas > Evaluate Formula steps through it one part at a time and shows the result of each step. It is the fastest way to see which part of an IF, XLOOKUP or SUMIFS returns the wrong value.

4. Watch Window

Add key totals to Formulas > Watch Window and they stay visible while you edit other sheets. Change an input on one sheet and watch whether the summary moves the way you expect.

5. Error Checking

Formulas > Error Checking walks through every cell Excel flags: errors, formulas that differ from their neighbours, formulas that skip adjacent cells and numbers stored as text. Don't click "Ignore" by habit - the "inconsistent formula" flag is often the real bug.

6. Find hard-coded numbers with Go To Special or ISFORMULA

Select a formula area, press F5 > Special > Constants and tick only Numbers. Excel selects every typed number, so anything selected inside a calculated block needs a reason. To keep it visible, add a conditional formatting rule over the range:

=AND(NOT(ISFORMULA(A1)), ISNUMBER(A1))

Pair it with =FORMULATEXT(A1) in a helper column when you need to document or compare formulas row by row.

7. Inquire and Spreadsheet Compare

If your edition includes it, turn on Inquire under File > Options > Add-ins > COM Add-ins. Workbook Analysis lists hidden sheets, external links, formulas with errors and constants in one report. Compare Files shows every cell that changed between two versions - useful when last month's file worked and this month's doesn't.

8. Check names, links and hidden sheets

Open Formulas > Name Manager and filter for names with errors. Then check Data > Edit Links for links to other files, and right-click a sheet tab > Unhide for hidden sheets. Broken names and old links are where inherited workbooks hide the most surprises.

Formula auditing best practices checklist

  • One formula per column: the same formula from top to bottom, no exceptions.
  • No hard-coded numbers inside formulas - put rates and thresholds in labelled input cells.
  • Whole-column or table references, so new rows are never left out.
  • A check cell that ties the summary back to the source total, showing 0 when they match.
  • Inputs, calculations and outputs on separate, clearly named sheets.
  • No links to other files unless they are documented.

For a shorter routine to run before a report goes out, use our 10-minute spreadsheet audit checklist. If the workbook is large, inherited or feeds decisions, our spreadsheet audit and repair service starts with a paid Scoping Sprint: a written diagnosis and a fixed price before any build.

FS

Written by

Independent Excel practice · 10+ years of hands-on Excel work

Excel specialists in Power Query, Power Pivot, VBA and financial modeling. The patterns on this page are the same patterns we build with - fixed scope, fixed price, named owner. About FormulaSurge. Stuck on a formula? Get a free fix.

Free tool · 10 sec · runs in your browser

Workbook X-ray

Drop in an .xlsx and get an instant audit: #REF! errors, typed-over formulas, external links and more. Nothing is uploaded.

Open the tool

Related service

Need this built for your team?

See Spreadsheet Audit & Repair - fixed price in writing, built end-to-end. Not sure of the scope? A Scoping Sprint ($99 / ₹4,999) gives you an exact quote first.

Read more

Start here · Scoping Sprint

Know the exact price in 2 working days. $99, credited in full to your build.

Builds from $75, fixed in writing · Working draft in 5 days or your first payment back · Mutual NDA first if you want one

See a sample Sprint report →Who you'll work with: two partners, 10+ years on finance workbooks →

10+ years in ExcelNamed senior ownerFixed price, in writingDraft in 5 days or refundMutual NDA on request30-day warranty

Frequently asked

Questions readers ask most.

Where are the formula auditing tools in Excel?

On the Formulas tab, in the Formula Auditing group: Trace Precedents, Trace Dependents, Remove Arrows, Show Formulas, Error Checking, Evaluate Formula and Watch Window. They are in Excel for Windows and Mac; Excel for the web has a smaller set.

What is the difference between Trace Precedents and Trace Dependents?

Trace Precedents draws arrows to the cells a formula reads from. Trace Dependents draws arrows to the cells that read from the selected cell. Use precedents to check where a number comes from, and dependents to check what breaks if you change it.

How do I find hard-coded numbers that replaced formulas?

Use Go To Special (F5 > Special > Constants > Numbers) on a formula area, or add a conditional formatting rule with =AND(NOT(ISFORMULA(A1)),ISNUMBER(A1)) over the range. Any highlighted cell in a column that should be all formulas is a typed-over value.

Is the Inquire add-in available in every version of Excel?

No. Inquire and Spreadsheet Compare come with Microsoft 365 Apps for enterprise and some Office Professional Plus editions on Windows. If File > Options > Add-ins > COM Add-ins does not list Inquire, your edition does not include it.

How long does a proper formula audit take?

A quick self-check with the tools here takes 10 to 30 minutes on a small workbook. An inherited model with many sheets, links and macros needs a structured review, which is what our Scoping Sprint starts with.

Start with a Scoping Sprint

Want this built for your team?

Send your requirements, a sample workbook or a screenshot. We reply within one working day, and the Scoping Sprint ($99 / ₹4,999) gives you the exact price in 2 working days, credited in full to the build.

  • Written scope and fixed price before any build work starts
  • Working draft in 5 working days or your first payment back
  • Recorded walkthrough and 30-day warranty at handover
  • Mutual NDA on request
Replies within one working day, usually the same day

Calls 9am–7pm IST, Mon–Sat · How a project works

Built for teams in

Industries and cities this work is set up for

Reading norms

How every article on this blog is written.

Same six rules on every post - from the first 10-functions article to the latest CFO report pattern. The writing matches the work.

Excel workbook with a sales table and a rising chart15% OFF

Welcome offer

15% off your first project

15% off the build fee of your first project. Mention it when you book; the Scoping Sprint is still credited in full.

Start with the $99 Scoping Sprint: a fixed price for your build in 2 working days.

Not ready yet? · Free

Free 10-minute Excel audit

Seven checks on your workbook, results in writing within one working day.