With Excel increasingly used for Business Information (BI) projects, it’s essential to keep track of the sources of data in your spreadsheet. Unlike most other spreadsheet utilities, Rainbow gives you a number of tools to help with this, and 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 each coloured cell in columns E and F is hyperlinked to a corresponding entry in the second report; just click the hyperlinked cell to jump to the relevant entry.
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 column G) in the table on the left. You can simply click the View button for any row in the table on the left, and information about that item will be displayed in the area on the right. In this right-hand area you can change any of the data items highlighted in yellow, and then just click the Apply New Values button to update.
Another important function in keeping track of your data is the Data Links report (see the dialogue box in the screenshot above). This 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).
You can find more information about these and other Rainbow functions on our Overview and Why Rainbow pages, and in the Version 10 User Guide. But the best way is really to try it out for yourself; just click the button below to install a 30-day free trial:
(Note: If Windows SmartScreen reports the download file as an “unrecognised application”, just click “More info” in the SmartScreen message, then click the “Run anyway” button.)