Add a proper formula auditing tool to better trace precedents and dependents
Third party tools already exist to properly audit formulas but a built-in auditing tool which would allow the user to quickly understand how a formula is built would be very helpful
Gary Knott commented
Yes, this is still my number 1 bugbear in Excel. Surely MS can implement better functionality as offered by various add-ins such as MART. As a bare minimum, the dialog box must be made bigger and resizable... ideally auto size.
A quick and easy first step would be to allow resizing the GoTo window that is shown when tracing precedents or dependents on other sheets. This is a disaster when the file and/or sheet names are long ... or when you have a long list of precedents or dependents. Better again would be to display a dialogue box more like the name manager (with different columns for file name, sheet name, refers to, and other relevant fields).
Cristian Nunez commented
I worked at an investment bank and we used S&P's CapitalIQ, whose excel add-on has the best formula auditing tool I've ever used. I held on to my CapIQ subscription as long as possible to hold on to this add-in. I wish it would be native to Excel!!
Gary Knott commented
I totally agree. My favourite tool is the "Formula Explorer" in the add-in "Spreadsheet Studio". This allows you to jump to precendent parts of a formula at a mouse click. Unfortunately the add-in only works up to Excel 2010. Please add something like this to MS Excel! It's awesome.
Arixcel explorer is excellent! I wish MS would incorporate.
we use http://www.arixcel.com/ and for £6.50 best tool ever for looking at tracing formulas even works with OFFSETS so you don't have to either work out which cell the formula is referring to or copy paste it into the goto window F5
Gareth Hayter commented
@Levi - Thanks for suggesting Spreadsheet Studio. I've just had a look at the website and this looks like a tool that I'll find very useful.
The Explode add in was another really useful third party tool for tracing precedents and dependents, but this is no longer being developed.
Levi Bailey commented
This is a great suggestion!
I'm not sure whether naming some of these tools would be help or potentially be a legal impediment to Microsoft so generally won't do it. I am aware that Joe D from the Excel team previously created and is just giving away Spreadsheet Studio - this has useful auditing tools (not specifically what Vincent is saying but related) - maybe there is hope of working something out?