Audit Excel Formulas: Highlight and Review All Formula Cells

Inherited an Excel file and can’t easily find the formulas, and some have errors? This is how to audit excel formulas so you can locate every formula fast, see what feeds it, see what it affects, and fix the issues before you start analysis.

The goal is simple: make formula cells easy to spot, then validate that they’re consistent.

Step 1: Audit Excel formulas with Go To Special

Excel Go To Special dialog showing Formulas selected to find all formula cells in a worksheet

Click anywhere inside the worksheet.
Go to Home > Find and Select > Go To Special.
Choose Formulas.
Keep all the checkboxes checked.
Click OK.

Excel will highlight every cell that contains a formula. In this example, you’ll see all formulas are in column D.

Step 2: Reveal what each formula does (Show Formulas)

Excel worksheet with Show Formulas enabled, displaying the underlying formulas instead of calculated results

Go to Formulas > Show Formulas.

This expands the cells so you can read the actual formulas. You’ll see something like B2 times C2 repeated down the column, row by row.

Use this as a quick toggle when you audit excel formulas:

Turn it on to read formulas.
Turn it off to see the results (values) again.

Step 3: Make formula cells pop with conditional formatting

Excel table showing Profit and Margin columns highlighted to visually identify formula cells in a worksheet

If you want any cell that has a formula to stand out visually, add a conditional formatting rule.

Select your full data set (the range you care about).
Go to Home > Conditional Formatting > New Rule.
Select Use a formula to determine which cells to format.
In the formula box, enter =ISFORMULA(A1).
Click Format.
Choose a fill color (yellow works well).
Click OK, then OK again.

That’s it. Every cell with a formula is now highlighted. This makes a big worksheet feel less like a scavenger hunt. It also makes it easier to audit excel formulas at a glance, even before you trace anything.

(Visual: add a screenshot of the Conditional Formatting rule dialog showing =ISFORMULA(A1) and the yellow fill.)

Use highlighting with Show Formulas for quick Excel Audits

Turn on Formulas > Show Formulas while the yellow rule is active. You get two wins at once: the formula cells are colored, and the exact formulas are readable in the grid.

Step 4: Trace what feeds your formulas (Trace Precedents)

Excel worksheet showing Trace Precedents highlighting January, February, and March values used to calculate the Q1 Total and Average

After you find a formula, the next question is, “What is this cell using?”

Click the formula cell you want to inspect (example: a Q1 total cell).
Go to Formulas > Trace Precedents.

You’ll see arrows and highlighted cells:

A blue box around source cells means they feed the selected formula.
An arrow shows the flow into the result cell.

Demo: trace precedents to a Q1 total

When you trace precedents on the Q1 total, you may see an arrow that starts in January and ends in Q1. January, February, and March show in a blue box. That means those months feed the Q1 total.

To clear the screen:

Go to Formulas > Remove Arrows (or Remove).

Demo: trace precedents to an Average

Now select the Average cell and run Trace Precedents again. You may see blue around January, February, and March, and the arrow ends in Average. You won’t see Q1 involved. That means January, February, and March feed the average, and the Q1 total is not being evaluated at all.

This tracing step audit in Excel is a big help when formula columns are far away, like column AA or BB. You don’t have to scroll around to guess what’s connected.

Step 5: See where your data gets used (Trace Dependents)

Excel worksheet showing Trace Dependents arrows from monthly sales values to the Q1 Total and Avg calculation cells

Precedents show inputs. Dependents show outputs.

Single cell example: January

Click the January cell.
Go to Formulas > Trace Dependents.

Excel will show arrows from January to the cells that use it. In this case, January is being used in both the Q1 total and the average.

Multi-cell example: January, February, March

Select January, February, and March together.
Go to Formulas > Trace Dependents.

Now Excel shows that all three are being used for Q1 total and the average.

When you’re cleaning up someone else’s workbook, this formula audit in Excel is a fast way to see what breaks if you change a number.

Step 6: Fix inconsistent formulas with Error Checking

Excel Error Checking dialog showing an Inconsistent Formula warning for a payroll calculation with options to copy, edit, or ignore the formula

Green in the upper left corner of a cell means something is going on. Click the warning icon. One common message is inconsistent formula.

That doesn’t always mean the value is wrong. It means the formula is different than the pattern around it.

Example:

The row above might be B2*C2.
The problem row might be equals forty times C3.

If column D should be the consistent calculation, that second formula needs to be fixed.

Go to Formulas > Error Checking > Error Checking.
Read the message that the formula differs from others in the area.
Choose Copy formula from above.

To step through each issue:

Click Next.
If it’s the same problem, click Copy formula from above again.
Repeat until you’re done.

Bulk fix when the whole column should match

If you know they’re all supposed to be the same, fix one cell, then fill it down:

After correcting the first (or last) one, select that corrected cell.
Double-click the fill handle to copy the formula down the column.

Then re-check the sheet. You should see fewer (or no) green flags. At this point, you’ve done a real audit excel formulas, and you can be more confident nothing breaks as you make changes.

Conclusion

Finding and formulas is only the start. Highlight them, trace what feeds them, trace what they affect, then run Error Checking to fix inconsistent formulas. Once the workbook is clean, your numbers hold up better when you edit inputs. Follow along for more Microsoft Excel and Microsoft 365 tutorials.

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.