casinos

Excel Tip: Options using INQUIRE June 11th, 2013

Vinod Kumar

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”.

INQUIRE Tab

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:

  1. Cell Relationship
  2. Worksheet Relationship
  3. Workbook Relationship and
  4. 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.

  1. Visible Sheets
  2. Hidden Sheets
  3. Formulas
  4. Formulas With Errors
  5. Formulas With Logical Values
  6. Formulas With Numeric Values
  7. Formulas With Date/Time Values
  8. Formulas Without Cell References
  9. Formulas Referencing Blank Cells
  10. Formulas Referencing Hidden Cells
  11. Formulas Referencing External Workbooks
  12. Positive Formulas
  13. Negative Formulas
  14. Duplicate Formulas
  15. Cells With Dependents
  16. Cells With Comments
  17. Cells With Validation Criteria
  18. Cells With Conditional Formatting
  19. Invisible Cells
  20. Blank Cells
  21. Hidden Rows and Columns
  22. Named Items
  23. Named Items With Errors
  24. Warnings

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.

Conclusion

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.

Tags: , , , , , , , , , ,

This entry was posted on Tuesday, June 11th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


4 Responses to “Excel Tip: Options using INQUIRE”

  1. vivek says:

    Hi Vinod,

    very interesting article, worth to share :)

  2. Jack Owens says:

    Refer image after section “Worksheet Relationship Diagram”
    In this image I see the tab name “Prod3″ & “GL Data” associated multiple times with “P&L Statement” & “Cost Cap” respectively

    Why the names are displayed multiple times when just a single reference relation is self-explanatory. Does multiple time display of name (“Prod3″ & “GL Data”) mean something?

    • Vinod Kumar says:

      Jack,

      I can have multiple data regions and tables associated as a reference and hence you are likely to see double references for Sheet reference to the same worksheet. And to answer your other queries,
      We will see Cell# in our diagrams
      I will try to add a mock Excel sheet that you can use maybe once the series is complete.

      The power of these are worth to watch inside Excel 2013. Do try it out.

      Thanks again.

Leave a Reply