Excel Formula Troubleshooting Tips and Tools

What’s your favourite way to troubleshoot Excel formulas? Here are a few tips for current versions of Excel, and a preview of new features that are coming soon, for Excel 365.

Use the Formula Bar

If there’s a problem with a simple Excel formula, I usually click in the formula bar, so Excel colour codes the precedent cells on the worksheet.

Sometimes the wrong cell is in the formula, so I can drag the coloured borders, and choose the correct cell instead.

Video: Audit Excel Formulas

This short video has a few more formula auditing tips, from my Audit Excel Formulas page. There’s another video further down, with new features that are coming soon, for Excel 365.

Go to Precedent Cells

For a formula that’s more complicated, you can go to the precedent cells, with this keyboard shortcut:

Ctrl + [

That shortcut will even take you to a formula cell on a different worksheet, but only if it’s the first reference in the formula.

Formula Troubleshooting Tool

For bigger formula auditing projects, you can use an Excel macro, to see where formulas have been copied down, across, or both.

In the screen shot below, I used a macro, written by Dermot Balson, which applies fill patterns to the formula cells.

Fill lines go down, across, or crosshatch, to show the direction in which the formulas were copied on the worksheet.
Solid coloured cells contain the original formulas

This colour coding makes it easy to spot cells where a formula is missing or different. That can give you a quick start in your troubleshooting!

And don’t worry – the colour coding is done on a new sheet, so it doesn’t mess up your worksheet!

New Excel Formula Tools

If you’re using Excel 365, there are new features coming soon, including improvements to the formula bar.

In current versions of Excel, you can select part of a formula in the formula bar, then press the F9 key, to evaluate that part of the formula.
In the upcoming updates, you’ll be able to click on an argument name in the formula tooltip, and see the values from that range of cells.

In this video, Mike Tholfsen, from Microsoft, shows 8 new Excel features, with the formula bar feature shown at the 6:28 mark.

Get the Excel Files

–1) Audit Tips: To get the formula auditing tips workbook, go to the How to Audit Excel Formulas page on my Contextures site.The zipped file is in xlsx format, and does not contain any macros.

–2) Formula Cell Colour: To get Dermot’s colour coding macro, and read more about it, go to the Formula Cell Colour Code Macro page on my Contextures site. The zipped file is in xlsm format, and contains the macros to colour coded the formulas. Be sure to enable macros when you open the workbook, if you want to test the macro.

_________________