At the right-hand end of the Structural Overview group on the Rainbow tab are three functions which can help you gain an overview of external data flows in a spreadsheet. The most important of these is probably the Data Explorer, which gives you a pair of linked reports showing:
- All the pivot tables, query tables and data tables in the spreadsheet, and
- The underlying pivot caches and data connections, i.e. the data sources.
In other words, you can see all the places where the spreadsheet is using a structured (usually external) set of data, and you can see exactly where that data set comes from. The screenshot above shows an example of the first report, and the screenshot below illustrates the second report.
You will notice that items in both reports share the same colour coding. This is because the reports are hyperlinked to each other; click one of the colour-coded items in one report, and it will take you to the corresponding entry in the other report. And where there is a pivot table (as on row 14) in the first report, this will be linked to two items (as on rows 7 and 11) in the second report:
- The pivot cache (e.g. row 7) which stores the report data in the spreadsheet, and
- The data connection (e.g. row 11) which defines the external source of the data.
You will also see that both reports are in two halves; a standard table on the left, and an area on the right which lists more detailed information about one of the rows (indicated by the bullet point on the View button) in the table on the left. You can simply click the View button (in column G) for any row in the table on the left, and information about that item will be displayed in the area on the right.
And as well as just viewing detailed data in the right-hand area, you can change any of the data items highlighted in yellow. So in this example you can enter a new name or a new cache index number for the pivot table, or a new connection string or new command text (database table name) for the pivot cache, and then just click the Apply New Values button, and the value(s) will be updated.
Under the Data Explorer button on the Rainbow tab are two other functions: the Links Report and the Macro Explorer. The Links Report helps you track down those elusive links (references) to external Excel files; it searches not just in formulas across all worksheets but also in any defined names and any charts (including chart sheets as well as embedded charts).
The Macro Explorer is illustrated in the screenshot above. As you can see, on the left it gives you a summary listing of all the macros (Visual Basic code) in a workbook, and you can then click the View button (in column G) for any macro to see the actual code on the right. Of course macros can perform many functions, but here we have shown (the first few lines of) one which manages the copying of data from an external workbook.
You can also see from column E of the report that on row 17 there is a “Function” type macro named “LastUpdated”; if you clicked the View button you would see that this returns the date when the UpdateHR macro was last run. So this appears to be a user-defined function, i.e. one which can be used directly in formulas in the workbook; for instance you could type =LastUpdated() into the formula bar. Excel’s own Find tool makes it easy to check for any formulas that use this LastUpdated function; just select all worksheets, press Ctrl-F, type LastUpdated( into the search box, and click the Find All button.