You’ve probably had a moment in Excel when your numbers just didn’t add up. A common solution is to comb through every cell one by one or start your calculations all over again. At least, I thought those were the only options I had until I discovered that Excel has a dedicated toolkit for exactly this situation.

Head to the Formulas tab in Excel’s desktop app, not the web version. Between the Function Library and Calculation features, you’ll find a group called Formula Auditing. It’s easy to overlook, but inside are seven tools that can help you diagnose broken formulas and figure out exactly where things went wrong.

Trace Precedents and Trace Dependents

Find out where your numbers come from and where they go

These two features are the natural starting point. Trace Precedents draws visual arrows from every cell feeding data into your selected formula, so if your formula in G5 pulls values from B2, D3, and F4, you’ll see lines connecting them. Trace Dependents works in reverse: select a cell, click the button, and Excel shows every formula elsewhere in the sheet that relies on that cell’s value.

The color-coding makes both features intuitive. The blue arrows mean everything in that chain is error-free, while the red arrows point to the source of a problem. In most cases, you can simply follow the red line to find the issue.

If an arrow points to a worksheet icon instead of a cell, the reference lives on a different sheet or workbook, though that workbook needs to be open for Excel to trace it. You can also keep clicking either button to peel back additional layers and move deeper into the dependency chain each time.

When you’re done, click Remove Arrows to clear the lines from your sheet. If you want to be more selective, expand the dropdown menu and remove only precedent or dependent arrows.

Show Formulas

Switch from results to syntax to spot inconsistencies

This feature is another simple but useful one. When you click Show Formulas (or press Ctrl + `), every cell containing a formula displays the formula syntax instead of the calculated result. I find this especially useful for two things: quickly spotting inconsistencies across a row of formulas and catching cells where someone typed in a value directly instead of using a proper formula. For example, if one cell shows a plain number while the surrounding cells display a SUM formula, you’ll know something is off.

Once you’ve confirmed that the syntax checks out, click Show Formulas again or use the same shortcut to switch everything back to normal.

Error Checking

Built-in checks for broken references, circular logic, and obvious mistakes

The Error Checking menu contains three tools, each designed to tackle a different type of broken formula. The main Error Checking dialog works a bit like a spellchecker, scanning your entire sheet for common Excel formula errors like #DIV/0! or #REF! and walking you through them one by one. As it finds issues, you’ll get options to fix them, step through them, or dismiss them.

Trace Error is a more targeted feature. If a specific cell is already showing an error, Excel draws red arrows through the calculation chain to show exactly where the bad data entered the equation. Circular References, meanwhile, hunts down cells that reference themselves, either directly or through a chain of other cells, and lets you jump straight to the problematic reference to untangle it.

Evaluate Formula

Watch Excel break complex formulas down piece by piece

If there’s one tool in this toolbox that’s my favorite, it’s Evaluate Formula. This feature is probably the closest thing Excel has to a real step-by-step debugger. Select a cell containing XLOOKUPs, complex IF statements, nested functions, or other advanced formulas, click Evaluate Formula, and a dialog box opens to walk you through the calculation one step at a time.

Inside the dialog box, one section of the formula appears underlined. That underlined section is the part Excel is about to evaluate. Each time you click Evaluate, the selected portion resolves into a result displayed in italics, while Excel moves to the next step in the calculation. Continue clicking, and eventually, you’ll arrive at the final result.

When available, you can also use Step In to dive into a referenced cell or sub-formula and Step Out to return to the parent formula.

Watch Window

Keep important values visible without bouncing between sheets

The last tool in the Formula Auditing group is designed for large, multi-sheet workbooks. Watch Window opens a floating toolbar where you can add any cells you want to monitor, regardless of which sheet they’re on within the workbook. You can even keep tabs on cells from an entirely different workbook.

Each entry displays the cell’s address, current value, and formula in a single panel, and the Watch Window stays visible even as you move between sheets. So, if you’re adjusting inputs on Sheet 1 and need to see how a running total on Sheet 5 responds in real time, you won’t have to keep flipping back and forth. Instead, you can monitor everything from one place. You can also double-click any entry in the Watch Window to jump directly to that cell whenever you need to.

When Excel starts acting weird, start here

You probably won’t be able to untangle every broken formula using the Formula Auditing group alone. Even so, these tools can save you from spending hours clicking through cells and hoping you accidentally stumble across the problem. They exist for a reason: Excel was built to handle complexity, and complexity needs debugging tools to match.

The next time a workbook starts behaving strangely, head to the Formulas tab and open the Formula Auditing group. Instead of guessing where things went wrong, you can let Excel show you what it already knows.

OS

Windows, macOS

Supported Desktop Browsers

All via web app

Developer(s)

Microsoft

Free trial

One month

Price model

Subscription

iOS compatible

Yes

Microsoft Excel is a powerful spreadsheet application used for data organization, analysis, and visualization. It supports formulas, functions, pivot tables, and charts to process complex datasets efficiently. Widely used in business and education, Excel also integrates with other Microsoft 365 apps for collaboration, automation, and real-time data insights.