Excel Data Analysis Inquire - Excel Data Analysis

What is Excel Data Analysis Inquire?

You can use Inquire to −

  • Compare two workbooks.
  • Analyze a workbook for problems or inconsistencies.
  • View the links between workbooks.
  • View the links between worksheets.
  • View the relationships between cells.
  • Clean excess cell formatting.
  • Manage passwords.

The INQUIRE tab may be on the Ribbon. if you find the INQUIRE tab at the Ribbon, you may skip to the following section.

If you do now not locate the INQUIRE tab at the Ribbon, make the Inquire add-in active.

  • Click file > options.
  • In Excel options window, click on add-Ins.
  • In manage box, click on COM add-ins.
  • Click go.

Excel Data Analysis - Inquire

COM Add-Ins dialog box appears.

Excel Data Analysis - Inquire

  • Check the Inquire box.
  • Click OK. Now, Inquire Add-In is active. You will find the INQUIRE tab on the Ribbon.

INQUIRE Commands

  • Let us learn about the INQUIRE commands.
  • Click the INQUIRE tab. You will find the following commands −
  • Workbook Analysis
  • Workbook Relationship
  • Worksheet Relationship
  • Cell Relationship
  • Compare Files
  • Clean Excess Cell Formatting
  • Workbook Passwords

Excel Data Analysis - Inquire

Comparing Two Workbooks

You can compare two workbooks cell by cell and find the differences, if any, in terms of changes in the second workbook in comparison to the first.

Follow the below given steps −

  • Open workbooks that you need to compare.
  • Click on INQUIRE tab at the Ribbon.
  • Click on on compare files in the compare group.

Excel Data Analysis - Inquire

  • The Select Files To Compare dialog box appears.
  • Check the file names displayed in the boxes next to Compare and To.

Excel Data Analysis - Inquire

  • If any File name that is displayed is not the one you wanted, click the down-arrow next to that File name.
  • Only the workbooks that are open will be displayed.

Excel Data Analysis - Inquire

  • Select the File.
  • Check if the order of Files in Compare and To is OK.

Excel Data Analysis - Inquire

  • If the order is not OK, click Swap Files. The order of Files in Compare and To gets changed.
  • Click Compare.

Excel Data Analysis - Inquire

The results of the comparison seem in a -pane grid −

  • The workbook at the left corresponds to the "compare" record you have selected.
  • The workbook on the right corresponds to the "To" record you have selected.

Information of changes in workbook-To compare to Workbook-compare appear in a pane below these grids. The changes are highlighted by color, depending on the kind of change. The legend for the highlight colors appears in the lower-left pane.

Excel Data Analysis - Inquire

Click Resize Cells to Fit on the Ribbon to view the cell contents in the Compare and To workbooks. The cells in both the workbooks are resized so that the contents are visible.

Excel Data Analysis - Inquire

Click the Export Results in the Export group on the Ribbon.

Excel Data Analysis - Inquire

The Save As dialog box appears. You can save the results to an Excel workbook. Note that only .xlsx file type is available.

Excel Data Analysis - Inquire

If you need the results in another application, you can do it by copying it to Clipboard.

Click Copy Results to Clipboard in the Export group on the Ribbon.

Excel Data Analysis - Inquire

Paste in an application you want.

Creating an Interactive Report

You can use the Workbook analysis command to create an interactive file that can show precise information about the workbook and its structure, formulas, Cells, ranges and Warnings.

  • Click the Inquire tab at the Ribbon.
  • Click Workbook analysis in the file group.

Excel Data Analysis - Inquire

The report will be displayed after the Workbook Analysis is completed.

Excel Data Analysis - Inquire

The Report has the following six categories −

  • Summary − General information about the structure and content of the workbook.
  • Workbook (with subcategories) − General workbook statistics.
  • Formulas (with subcategories) − Specific information about formulae in the workbook.
  • Cells (with subcategories) − Specific information about cells in the workbook.
  • Ranges (with subcategories) − Specific information about ranges in the workbook.
  • Warnings − Different types of warnings about workbook structure and content.

Excel Data Analysis - Inquire

Selecting a category gives you more information about that category.

Check the Formulas option. The sub-categories of formulas will be displayed.

Excel Data Analysis - Inquire

You will observe the following in the workbook that you are analyzing −

  • All formulas are 224 in number.
  • With numeric values, they are 224 in number.
  • Click Sub-Category With numeric values.

In the Results pane, for each of the cells with formula with numeric values, worksheet name, cell address and formula are displayed.

Excel Data Analysis - Inquire

Click the Excel Export button. The Save As dialog box appears.

Excel Data Analysis - Inquire

  • Save the Report as an Excel file.
  • Load Export File button appears next to Excel Export button.
  • Click on Load Export File button.

Excel Data Analysis - Inquire

The saved Report Excel workbook opens and you can clearly view the workbook analysis results.

Excel Data Analysis - Inquire

Viewing with Diagrams

You can view workbook relationships, worksheet relationships and cell relationships with interactive diagrams created by links. The links display the dependencies between the nodes in the diagram. you may drag the links or nodes to arrange them and align them to view anything you are looking for.

Viewing Workbook Relationships
You can have an interactive, graphical map of workbook dependencies created by connections (links) between files using workbook relationship diagram.

The types of links inside the diagram can consist of other workbooks, access databases, text files, HTML pages, square Server databases and other data assets.

  • Click the INQUIRE tab on the Ribbon.
  • Click on Workbook relationship in the Diagram group.

Excel Data Analysis - Inquire

The Workbook Relationship Diagram appears, showing links of the workbook with different Data Sources.

Excel Data Analysis - Inquire

Viewing Worksheet Relationships

You can use Worksheet relationship Diagram to create an interactive, graphical map of connections (links) between worksheets in the equal workbook and /or worksheets in other workbooks.

  • Click the INQUIRE tab on the Ribbon.
  • Click on Worksheet relationship in the Diagram group.

Excel Data Analysis - Inquire

The Worksheet Relationship Diagram appears, showing links between the worksheets in the same workbook, and in other workbooks.

The difference between these two can be identified by the direction of the arrows.

Excel Data Analysis - Inquire

Viewing Cell Relationships

You can use the Cell Relationship Diagram to get a detailed, interactive map of all links from a selected cell to cells in other worksheets or even other workbooks.

  • Click the INQUIRE tab on the Ribbon.
  • Click Cell Relationship in the Diagram group.

Excel Data Analysis - Inquire

The Cell Relationship Diagram Options dialog box appears.

  • Check Span sheets and Span workbooks.
  • Select Trace both under Trace cell precedents and Trace cell dependents.
  • Under Initial number of expansion levels, select limited and type 5 in the box next to it.
  • Click OK.

Excel Data Analysis - Inquire

The Cell Relationship Diagram appears, showing links between the selected cell and the cells in the same worksheet, same workbook and in other workbooks, based on the options you have chosen.

Excel Data Analysis - Inquire

Click Zoom. You can view the nodes clearly.

Excel Data Analysis - Inquire

Cleaning Excess Cell Formatting

When you locate that a workbook is getting loaded slowly, or has become huge in size, it might have formatting applied to rows and/or columns that is not needed (for example, conditional formatting a whole column that has less than 15 values).

You can use the clean excess cell Formatting command to remove extra formatting and greatly reduce the file size. This also results in improving Excel's speed.

Before cleaning the excess cell formatting, create a backup copy of your Excel document because there are certain cases where this method can also increase your file length, and there is no way to undo the change.

  • Click the INQUIRE tab on the Ribbon.
  • Click clean excess cell formatting in the Miscellaneous group.

Excel Data Analysis - Inquire

The Clean Excess Cell Formatting dialog box appears. Choose All Sheets in the Apply to box

Excel Data Analysis - Inquire

You will get a message about saving changes. Click OK.

Excel Data Analysis - Inquire

Managing Passwords of Files

If you are using Workbook Analysis or Compare Files commands for workbooks that are password protected, you can avoid having to type the password each time those files are opened. This is possible with using Password Manager.

  • Click the INQUIRE tab on the Ribbon.
  • Click Workbook Passwords in the miscellaneous group.

Excel Data Analysis - Inquire

The Password Manager Dialog box appears. Click the Add button to add passwords of your workbooks.

Excel Data Analysis - Inquire

Add password descriptions also, for the passwords you added.

Excel Data Analysis - Inquire

Next time when you need to use any of these files for comparing or analyzing, you do not have to key in the passwords.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Excel Data Analysis Topics