Please read this article first: Auditors, Risk Managers: Excel spreadsheet analysis using the new Inquire tool

In this article, we discuss how to use the Relationship Diagrams using Inquire tool.

Linkages and dependents: Inquire Tool

Apart from detailed analysis, Inquire tool also provides a very good display of cell, worksheet and workbook linkages.

This process can be recursive and may take a long time. So make sure that you have a fast machine and have lots of time when you are performing this type of analysis.

Workbook Relationships

Usually we find linked files by going to the Data – Edit Links or File – Edit Links (File – Prepare – Edit links in 2007) option. This shows you only one level of links. It shows which files are referred to in the current file.

However, this is not enough. The linked files themselves may have more links. The Workbook Relationship option shows the links across files. Linked files which are not found are shown with a Red cross sign.

You can simplify the diagram by collapsing unwanted nodes and drill across linkages by expanding the plus signs.

This diagram can become very complex. You can zoom in and out of the linkages for a closer view. But if you get confused, you should open the Overview Window from the toolbar at the bottom of the window. This opens a small window showing overall diagram and your current position. This thumbnail has a viewport which can be moved to navigate complex linkages.

Right clicking on any node to highlight precedents or dependents. Those are color coded as red or blue respectively.

Worksheet dependencies

These show the interrelationship between sheets within a workbook. This diagram can look very complex and practically irrelevant in case of big files. But you can zoom in and use Overview window along with Dependents / Precedents check to make sense out of it.

Cell dependencies

Cell dependencies can be checked using Trace Precedents / Dependents options. But these require clicking multiple times till you hear a beep – which indicates that you have reached raw data (values) in all directions of dependents. This is cumbersome.

In addition, the dependents / precedents beyond current worksheet need to be navigated manually and repeatedly.

Inquire solves this problem by providing full depth analysis of cell dependencies across sheets as well as across workbooks – which is a great time saver. It also displays a nice graphical tree view from where you can navigate the levels easily.

Click inside a cell of interest and then choose Cell Dependencies. You can tweak the discovery process using these options…

Notice that each option may increase the time and resources required for analysis exponentially. Start within the worksheet / workbook first and then expand on demand. 2 levels of depth are enough to start with. Identify areas / cells of interest and then investigate further.

When you double click on a node, it will open that particular cell in the appropriate sheet of the correct workbook.

I only wish it retained the dependency tree window for further navigation. As of now it just closes the window and navigates to the clicked cell.

Practical usage considerations

As mentioned in the previous article, you must know what you are looking for and then use these tools with discretion. Arbitrarily creating complex dependency diagrams is only going to consume time and may add to confusion rather than clarity.

I suggest that you use these tools on files you know very well so that you learn the usage well. After that, use them on demand as a part of an overall audit, troubleshooting or analytical plan.

It is also a good idea to run this analysis on critical files which are used for taking important decisions. This way, some unknown and dangerous dependencies may be unearthed.

If you get complex workbooks from external vendors, contractors, outsourcing agencies, customers, etc. you must analyze them to check for consistency and the underlying structure as well as logic. This will protect your interests and help you identify errors of commission or omission.

What next?

In the next article we will discuss Spreadsheet Compare feature.

——