Excel 2013 included some great new features for BI. However, one really cool feature that flew under the radar was Inquire. For all intents and purposes, this is formula tracing at the GYM, on steroids and the only the only runner in the race. It’s a great inclusion to understand the structure of the workbook including dependencies between books (including formula references), record the formulas and document just about anything in the book.
So let’s start with a quick recap of formula auditing. This is still available and shows the how a cell is derived. You typically activate the cell and then press the Trace Precedents (if you want to see what cells are used in the cells formula) or Trace Dependents (if you want to see what other cells have a formula which is dependent on the selected cell). These are in the formulas ribbon. We can see how this works in the following image where the dependencies of cell B7 are shown as B5 and B6 (note the blue line connecting the cells).
When the formula was a linked value from another book, a grid would show to indicate and external reference. In the prior image, the cells A1 and A2 refer to cells in another workbook (called book 2) and so tracing the Precedents would show the following.
Now let’s compare this to Inquire
Firstly, Inquire must be activate as an Excel add-in. Go to File à Options, then chose add-ins from the options pane, then manage click Go from the Excel Add-Ins dropdown.
Ensure that the Inquire add-in is checked. Then an Inquire ribbon tab should be present.
The ribbon gives us many options, but let’s focus on the structure of the work book. If we click the Workbook analysis button, a new window will open which allows us to chose what we want to look at. For example, we could list formulas by selecting the All formulas from the Formulas node in the tree (as shown). Note that all formulas are listed which includes a reference to an external book.
Don’t like the window? We can even export the results for any number of selected nodes (to a new workbook) by hitting the ‘Excel Export’ button (in this window).
We can investigate the relationships (both Precedents and Dependencies) of a particular cell (just click the Cell Relationship button in the ribbon). Here a new window opens with our choices (we can chose the direction and number of expansion levels (for investigation)).
This post has just scratched the surface – there are a few other interesting options to explore (eg file comparisons and passwords), however, this feature should be very useful for tracing (and perhaps more importantly documenting) formulas in books.
Paul te Braak 