Rainbow Analyst Professional 12, our flagship product for Excel 2007 onwards. (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.)
The Rainbow tab
The rest of this page gives you a whirlwind tour of Rainbow Analyst Professional (Rainbow Pro), running through all its functions as shown on the Rainbow tab in Excel (above).
In order to squeeze all Rainbow’s functions into just one tab, we have made extensive use of “split buttons”. This means that the large buttons on the Rainbow tab represent Rainbow’s main functions, and you can click on the top half of the button (the icon) to go straight to the relevant function. And if you click the bottom half of the button (the function name), you will see a small drop-down menu with between 1 and 3 subsidiary functions related to the main function.
As you can see in the screenshot above, the Deep Clean drop-down menu has two additional items, the Deep Compare and Temp Files functions. We cover the main functions in the section immediately below, and the subsidiary functions (on the drop-down menus under the main functions) in the following section.
Main functions summary
The Rainbow tab has five groups of functions: The Basics, The Details, My Files, My Colours, and Help. As the names suggest, it’s a good idea to start with The Basics and then move on to The Details and any other functions you need.
The Basics group consists of two functions that can quickly diagnose a large number of spreadsheet problems and issues, plus a function to help you to track and resolve the issues:
- Summary Report gives you a quick overview of a workbook and a overall quality score, and highlights possible issues for further analysis.
- Formula Scan highlights (and reports on) groups of copied formulas, and it also runs a battery of tests on the first formula in each group.
- Issue Tracker is another interactive function which enables you to track and annotate progress on resolving issues flagged by you or by Rainbow.
In The Details group are four functions which tell you about the flow of data and logic in the spreadsheet, either by generating colour “maps” to help you visualise the spreadsheet calculations, or by helping you to follow the details of the logic:
- Inter-Sheet Logic generates a tabular matrix that shows you the flow of logic between sheets in your workbook.
- Data Explorer tells you where your spreadsheet is using tables of data, and how these are linked to external databases or other sources.
- Cell Inspector is an interactive function which lets you trace the logic of any individual cell at a glance, and is very useful in conjunction with Formula Scan.
- Risk Map is a powerful analysis tool which can report on up to 24 different types of potential risk and highlight all the relevant cells in different colours.
The My Files group contains several utility functions to help you manage individual workbooks and track changes between different versions of spreadsheets:
- The initial set of three functions is about change management. You can use Save Format to record the current formatting of your selected worksheet(s), so that you can then restore it/them again using Undo Changes. And File Save As works like the File Save As item on the Excel menu, except that it also makes sure that Rainbow recognises the old file under its new name.
- Quick Clean-Up gives you a simple and fast way to prepare a workbook for analysis by unhiding hidden items, unprotecting worksheets, and optionally clearing cell colours.
- Deep Clean operates directly on a saved workbook file to reset custom formats, analyse external links (including phantom links), and delete names with phantom links.
- Comparison Toolbox is a comprehensive function that compares workbooks, worksheets or ranges, giving you a key tool for controlling versions and scenarios.
The three functions in the My Colours group let you check the colours in a cell and manage the gallery of colours that Rainbow uses:
- Identify Colours gives you an explanation of the Rainbow colour(s) in the selected cell, and lets you search for other similarly-highlighted cells.
- Colour Chart gives you an interactive visual display of all the colours that Rainbow uses to map different types of cells.
- Clear Cell Colours removes existing cell colours so that you can see clearly when Rainbow adds its own highlights.
And finally in the Help group are two menu buttons that give you access to important information and options:
- The User Guide button lets you view and print the Rainbow User Guide.
- Preferences enables you to change a wide range of options and settings.
Here we cover the functions on the drop-down menu under each of the main functions. You will notice that each drop-down menu also includes the main function; this is to allow you to access both the main and the subsidiary functions using the keyboard shortcuts.
In The Basics group:
- Under Summary Report, Hide/Protect can hide or unhide and protect or unprotect selected worksheets, and also unhide hidden rows and columns.
- Under Formula Scan, Logic Map gives you a visual picture that helps you quickly grasp the flow of calculation from inputs through workings to final results, and Logic Colours provides a key to the colours used in the Logic Map and lets you search for them in the worksheet.
- Under Issue Tracker, Comments Report generates a report on all flagged issues and/or all cell notes or threaded comments in the workbook. Delete Comments correspondingly lets you delete all flagged issues and/or all comments.
In The Details group:
- Under Inter-Sheet Logic, Data Links helps you track down where your spreadsheet calculations are linked to individual cells in another workbook.
- Under Data Explorer, Macro Explorer lets you easily see any macros in the workbook and get a sense of how they affect your workbook data.
- Under Cell Inspector, Merged Cells Tool gives you a report on merged cells and various options for unmerging them, and Names Toolbox provides a range of tools to help you audit and control the use of defined names in the workbook logic.
- Under Risk Map, Logic Analysis can report on up to 24 items related to cell types and dependency logic, and Analysis Wheel lets you select from Rainbow’s whole range of analysis options, run the analysis, and then review the highlighted cells in the current sheet.
In the My Files group:
- Under Quick Clean-Up, Clean Up Formats removes superfluous formats (which can generate an Excel error message), Trim Unused Space removes blank space around the used area of a worksheet (which can cause bloating), and Tidy Up Reports converts Rainbow reports for sharing with colleagues who do not use Rainbow.
- Under Deep Clean, Deep Compare finds differences in the structure of two workbooks which are not explained by changes in cell contents, and Temp Files opens a File Explorer window on the folder which contains the Rainbow Log file and also any unzipped files created by the Deep Clean function.
- Under Comparison Toolbox, Sample Data selects a random or periodic sample of data from a worksheet and copies it to a report for further auditing. Scramble Workbook replaces the data and text in a workbook with meaningless values, so that you can send it to Rainbow support or to colleagues who may need to examine the workbook structure but not the data.
In the My Colours group:
- Under Identify Colours, Format Editor works in conjunction with other Rainbow functions to enable you to apply formatting changes or convert text to numbers.
- Under Clear Cell Colours, Graphics Report lets you generate a detailed report on buttons and graphics (e.g. charts), and Delete Graphics removes all such graphics from the workbook or selected worksheet(s).
In the Help group:
- Under User Guide, Rainbow Log lets you view the Rainbow log file, About Rainbow enables you to license or re-license your copy of Rainbow, and Check For Updates can automatically install the latest updated version of Rainbow.
- Under Preferences, Reset Reports lets you tell Rainbow that you want it to put reports in a different workbook from the one it is currently using, Restore Defaults restores the factory default settings, and Uninstall removes Rainbow from your system.
There is much more to Rainbow’s capabilities than we can describe here. For a detailed explanation, you can read the full Rainbow Pro 12 User Guide, but really it’s better to try it for yourself. Simply click the button below for your free trial copy:
Rainbow Analyst Professional 12
(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.)