Excel Formula Auditing
A slightly off-topic post this one but it’s too good not to share. I say it’s too good, I only found out about this yesterday, maybe everyone knows about it already!
In Excel 2007 - apparently the functionality was introduced in Excel 2003! How out of date am I?! - there is a ‘Formula Auditing’ chunk on the Formulas Ribbon tab.
There are all sorts of goodies in here.
Trace Precedents allows you to find out the cells that make up the current cells data.
If you select a cell that is sourced from a formula and then click ‘Trace Precedents’ arrows appear indicating which cells the value is sourced from. Click ‘Trace Precedents’ again more arrows appear indicating where those source values are sourced from and so on and so forth. Below I’ve clicked ‘Trace Precedents’ four times to go back 4 levels from the selected cell J8.
You can go the other way too; Trace Dependents details all cells that that depend on that value. Selecting cell B3 and clicking ‘Trace Dependents’ four times traces all related dependencies.
It’s worth having a play with the other commands in the chunk too. Show Formulas, Error Checking, Evaluate formula and the Watch Window, together with the tracing functionality could really help debugging or reverse engineering complex spreadsheets. I just wish I knew about it all before now !