I recently had a multipage Excel spreadsheet/workbook dumped in my lap. Are there any tools to aid in deciphering spreadsheets? At the moment I click in a little cell to see what's there, then I click in some other little cell that it references, then I click in 3 other cells that it uses and fairly quickly I'm lost in a maze of twisty little cells, all alike.
I need to extract the "business logic" from the sheets in some hopefully non-laborious manner.
It's quite a hard problem, in that formulae aren't just business logic, they're also there just to get Excel to understand what you're trying to do, e.g. a simple look-up from a list.
Although I can't recommend a specific tool, I find working from a copy, and moving things around can often help. For example, moving the "result" or "total" type formulae in column A, the cells that are referenced by the result in B, any cells that these reference in C and so on. You'll then have a kind of tree structure. It may also be beneficial to move the look-ups next to the formulae they're used in, or put them all on the same sheet.
This is far from perfect, but it's a little more structured than just poking about.
In Excel, go to the Tools menu, open the Formula Auditing sub-menu, and use the Track Precedents menu item there, it will show you arrows leading into the cell you're currently at. Of course, you need to go to the source of these and repeat the process to get more information.
It's not an ideal tool, but it might give you more information than simply clicking around.
In addition to tools->auditing->trace precedents, you can use ctl+` (grave accent) to toggle formula display.
In addition to the auditing tools, the simplest tool and the one I use most is to select a cell and hit F2 to have all the cell references in the formula and the referenced cells highlighted with colour coding.
Well, I didn't find my magic spreadsheet bullet here, but I'll mark as 'answer' the one which was the most interesting idea and one I might not have thought of myself.
Thanks.