Yesterday I wrote about Trace Precedents / Dependents and this blog post is an extension to what we have with Excel 2013. In this blog post we will see how to use “INQUIRE” tab and how it can be yet another powerful visualization to find our chain of dependencies inside Excel worksheets.
Enabling Inquire Tab
The first step is to enable the INQUIRE toolbar. This is not enabled by default. Open Excel 2013, in a blank document select “File” –> “Options”. We will be presented with the below screen.
Select “COM Add-Ins” and click “Go…”. We will get this mini dialog. Select the checkbox “Inquire” and click “OK”.
Once we have done the above step, you will find a new Tab added called as INQUIRE as you can see below.
In this blog post, we will show the following:
- Cell Relationship
- Worksheet Relationship
- Workbook Relationship and
- Workbook Analysis
We will use the same worksheet as used in Trace Precedents / Dependents.
Cell relationship diagram
This in a way is like our Precedent and Dependent diagram but is way more cool. This will show us the dependency like a fishbone diagram. With our cursor in “B7” cell, we are going to click on Inquire –> “Cell Relationship” button. We will be presented with the below screen. I generally like to keep the defaults and hit “OK”.
The output will look like below. We see this because we selected the “Initial number of expansion levels” as “Limited –> 1”.
We can expand it to the next level and we will be presented with a more detailed view. Here we are seeing B7 –> Dependent on B9, B70, D25, D15 and so on. How cool is this when compared to our Trace Precedents? We are yet not over. There is more.
Worksheet Relationship Diagram
Now this is new and an great extension. Here click on “Inquire” –> “Worksheet Relationship”. This will show us the dependencies of each of the Worksheets.
In our example above, it is showing the XLS sheet “Statement 2012-11.xlsm” has various sheets like Documentation, P&L Statement, CASH_FLOW etc. While it shows for example “Cost Cap” sheet is dependent on “IM Data”, “GL Data” etc which in this case are a different File and Sheet. Cool isnt it? We arent done yet !!!
Workbook Relationship Diagram
This is taking our dependency diagram to the next level. Especially for file like P&L data can come from a number of external sources. And this option shows how we are dependent on other external sources. Also it also shows if that file currently exists or not :). Cool right.
In our example, I selected “Inquire” –> “Workbook Relationship” to get a typical output like below.
In the above example we can see current XLS sheet is dependent on Northwind1.mdb (access DB), table.html (from web), and a number of other excel sheets. A “Red mark” indicates the object is not available now.
Workbook Analysis Report
This is a great summary of information that is available in our Workbook. It shows a number of information shown, I am listing some of them here.
- Visible Sheets
- Hidden Sheets
- Formulas With Errors
- Formulas With Logical Values
- Formulas With Numeric Values
- Formulas With Date/Time Values
- Formulas Without Cell References
- Formulas Referencing Blank Cells
- Formulas Referencing Hidden Cells
- Formulas Referencing External Workbooks
- Positive Formulas
- Negative Formulas
- Duplicate Formulas
- Cells With Dependents
- Cells With Comments
- Cells With Validation Criteria
- Cells With Conditional Formatting
- Invisible Cells
- Blank Cells
- Hidden Rows and Columns
- Named Items
- Named Items With Errors
And many more that I have not listed. The below diagram shows a snapshot of the same information for our Demo P&L Excel sheet.
Hope you found this blogpost interesting and you got to learn something new. This feature is part of Excel and worth a look any day. We will look at many more Office tips coming this week. Stay tuned on the series.