{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Guide","Colours","Menu","Prefers","Control","Temp"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":89,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Guide","index":0,"n_rows":1134,"n_cols":2,"rows_kept":1134,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Rainbow User Guide"]
[]
[null,"User Guide Contents"]
[]
[null,"If you have turned off in-cell editing, you can double-click any of the items immediately below to go to the relevant heading."]
[]
[null,"Row     Heading"]
[null,{"$f":"TEXT(ROW($B$65),\"000\") & \"      \"&$B$65","v":"065      Quick Start Guide"}]
[null,{"$f":"TEXT(ROW($B$90),\"000\") & \"            \"&$B$90","v":"090            Overview of Rainbow's Functions"}]
[null,{"$f":"TEXT(ROW($B$156),\"000\") & \"            \"&$B$156","v":"156            How to Use Rainbow"}]
[null,{"$f":"TEXT(ROW($B$195),\"000\") & \"      \"&$B$195","v":"195      Function Details"}]
[null,{"$f":"TEXT(ROW($B$199),\"000\") & \"            \"&$B$199","v":"199            Summary Report"}]
[null,{"$f":" TEXT(ROW($B$256),\"000\") & \"            \"&$B$256","v":"256            Security Manager"}]
[null,{"$f":"TEXT(ROW($B$266),\"000\") & \"            \"&$B$266","v":"266            Password Tool"}]
[null,{"$f":"TEXT(ROW($B$280),\"000\") & \"            \"&$B$280","v":"280            Calc Manager"}]
[null,{"$f":"TEXT(ROW($B$295),\"000\") & \"            \"&$B$295","v":"295            Structure Map"}]
[null,{"$f":"TEXT(ROW($B$311),\"000\") & \"            \"&$B$311","v":"311            Inter-Sheet Logic"}]
[null,{"$f":"TEXT(ROW($B$329),\"000\") & \"            \"&$B$329","v":"329            Data Links"}]
[null,{"$f":"TEXT(ROW($B$339),\"000\") & \"            \"&$B$339","v":"339            Formula Scan"}]
[null,{"$f":"TEXT(ROW($B$363),\"000\") & \"            \"&$B$363","v":"363            Logic Map"}]
[null,{"$f":"TEXT(ROW($B$393),\"000\") & \"            \"&$B$393","v":"393            Logic Colours"}]
[null,{"$f":" TEXT(ROW($B$399),\"000\") & \"            \"&$B$399","v":"399            Sample Data"}]
[null,{"$f":"TEXT(ROW($B$417),\"000\") & \"            \"&$B$417","v":"417            Issue Tracker"}]
[null,{"$f":" TEXT(ROW($B$439),\"000\") & \"            \"&$B$439","v":"439            Scramble Workbook"}]
[null,{"$f":"TEXT(ROW($B$451),\"000\") & \"            \"&$B$451","v":"451            Data Explorer"}]
[null,{"$f":"TEXT(ROW($B$477),\"000\") & \"            \"&$B$477","v":"477            Macro Explorer"}]
[null,{"$f":" TEXT(ROW($B$489),\"000\") & \"            \"&$B$489","v":"489            Names Toolbox"}]
[null,{"$f":"TEXT(ROW($B$525),\"000\") & \"            \"&$B$525","v":"525            Cell Inspector"}]
[null,{"$f":" TEXT(ROW($B$541),\"000\") & \"            \"&$B$541","v":"541            Find Cells"}]
[null,{"$f":" TEXT(ROW($B$547),\"000\") & \"            \"&$B$547","v":"547            Merged Cells Tool"}]
[null,{"$f":"TEXT(ROW($B$555),\"000\") & \"            \"&$B$555","v":"555            Risk Map"}]
[null,{"$f":"TEXT(ROW($B$645),\"000\") & \"            \"&$B$645","v":"645            Logic Analysis"}]
[null,{"$f":"TEXT(ROW($B$748),\"000\") & \"            \"&$B$748","v":"748            Analysis Wheel"}]
[null,{"$f":"TEXT(ROW($B$769),\"000\") & \"            \"&$B$769","v":"769            Import Colours"}]
[null,{"$f":" TEXT(ROW($B$777),\"000\") & \"            \"&$B$777","v":"777            Save Format"}]
[null,{"$f":" TEXT(ROW($B$785),\"000\") & \"            \"&$B$785","v":"785            Undo Changes"}]
[null,{"$f":" TEXT(ROW($B$797),\"000\") & \"            \"&$B$797","v":"797            File Save As"}]
[null,{"$f":" TEXT(ROW($B$803),\"000\") & \"            \"&$B$803","v":"803            Quick Clean-Up"}]
[null,{"$f":" TEXT(ROW($B$809),\"000\") & \"            \"&$B$809","v":"809            Clean Up Formats"}]
[null,{"$f":" TEXT(ROW($B$821),\"000\") & \"            \"&$B$821","v":"821            Trim Unused Space"}]
[null,{"$f":"TEXT(ROW($B$831),\"000\") & \"            \"&$B$831","v":"831            Tidy Up Reports"}]
[null,{"$f":"TEXT(ROW($B$841),\"000\") & \"            \"&$B$841","v":"841            Deep Clean"}]
[null,{"$f":" TEXT(ROW($B$863),\"000\") & \"            \"&$B$863","v":"863            Deep Compare"}]
[null,{"$f":" TEXT(ROW($B$875),\"000\") & \"            \"&$B$875","v":"875            Temp Files"}]
[null,{"$f":"TEXT(ROW($B$881),\"000\") & \"            \"&$B$881","v":"881            Comparison Toolbox"}]
[null,{"$f":" TEXT(ROW($B$924),\"000\") & \"            \"&$B$924","v":"924            Comments Report"}]
[null,{"$f":" TEXT(ROW($B$931),\"000\") & \"            \"&$B$931","v":"931            Delete Comments"}]
[null,{"$f":"TEXT(ROW($B$937),\"000\") & \"            \"&$B$937","v":"937            Identify Colours"}]
[null,{"$f":"TEXT(ROW($B$945),\"000\") & \"            \"&$B$945","v":"945            Format Editor"}]
[null,{"$f":" TEXT(ROW($B$959),\"000\") & \"            \"&$B$959","v":"959            Colour Chart"}]
[null,{"$f":" TEXT(ROW($B$971),\"000\") & \"            \"&$B$971","v":"971            Clear Cell Colours"}]
[null,{"$f":" TEXT(ROW($B$985),\"000\") & \"            \"&$B$985","v":"985            Graphics Report"}]
[null,{"$f":" TEXT(ROW($B$989),\"000\") & \"            \"&$B$989","v":"989            Delete Graphics"}]
[null,{"$f":" TEXT(ROW($B$993),\"000\") & \"            \"&$B$993","v":"993            User Guide"}]
[null,{"$f":" TEXT(ROW($B$997),\"000\") & \"            \"&$B$997","v":"997            Rainbow Log"}]
[null,{"$f":"TEXT(ROW($B$1009),\"000\") & \"            \"&$B$1009","v":"1009            About Rainbow"}]
[null,{"$f":"TEXT(ROW($B$1013),\"000\") & \"            \"&$B$1013","v":"1013            Check For Updates"}]
[null,{"$f":" TEXT(ROW($B$1021),\"000\") & \"            \"&$B$1021","v":"1021            Preferences"}]
[null,{"$f":" TEXT(ROW($B$1076),\"000\") & \"            \"&$B$1076","v":"1076            Reset Reports"}]
[null,{"$f":" TEXT(ROW($B$1084),\"000\") & \"            \"&$B$1084","v":"1084            Restore Defaults"}]
[null,{"$f":" TEXT(ROW($B$1088),\"000\") & \"            \"&$B$1088","v":"1088            Uninstall"}]
[null,{"$f":"TEXT(ROW($B$1094),\"000\") & \"      \"&$B$1094","v":"1094      License Agreement"}]
[]
[null,"Quick Start Guide"]
[]
[null,"If you're someone who likes to get straight on with using a product, this \"Quick Start\" section tells you the key things you need to know to get the best out of Rainbow's spreadsheet error visualisation capabilities, which are all available when you click the Rainbow tab on the Excel (\"ribbon\") menu."]
[]
[null,"When you load Rainbow for the first time, you may see a message saying that Rainbow needs to switch on access to Excel's Visual Basic modules in order to create interactive reports. In most cases you can just follow Rainbow's prompts to resolve this issue, and even if this is not possible, you can still use all Rainbow's functions other than the Data Explorer, the Macro Explorer, the Names Toolbox report, and the Tidy Up Reports function. And later if you want Rainbow to try again to switch on access to Visual Basic, just click Preferences, then select \"Enable access to Visual Basic\" on the Reports tab, and click Save Defaults."]
[]
[null,"Rainbow installs itself as an additional Rainbow tab on the standard Excel menu. Most of the items on the Rainbow tab are \"split buttons\"; you can click the top of the button to go straight to one of Rainbow's main functions, or you can click the bottom part to access a menu of subsidiary functions. And you can also access any function just with your keyboard; press and release the ALT key to see all Excel menu shortcuts, then press the indicated shortcut for Rainbow, followed by (for example) M to select the Menu Guide button or S for the Summary Report."]
[]
[null,"Using the Menu Guide"]
[]
[null,"The Menu Guide button at the start of the Rainbow tab provides a further way to access Rainbow's functions. It displays a spreadsheet showing all the functions laid out in a grid of rectangles, zoomed out to fit on your screen. If you would like the Menu Guide spreadsheet to be loaded at startup, you can set this in your Preferences under the Options tab. Each rectangle in the Menu Guide grid consists of two parts:"]
[null,"•  At the top is the name of the function, shown in a pale grey rectangle, and you can click on this to run the function just as if you had clicked on it in the Rainbow tab."]
[null,"•  Underneath is the summary text that appears if you hover over the function on the Rainbow tab. You can click on this to see the full help text for the function as it appears in the User Guide."]
[]
[null,"Easy spreadsheet auditing"]
[]
[null,"You can use just a few of the main functions along with Rainbow's workflow management features  to make systematic spreadsheet auditing as easy as 1-2-3:"]
[null,"• 1)  Run the Summary Report function to get a high-level overview of the spreadsheet. Decide what action is required to address any problems highlighted on the report, particularly any red highlights."]
[null,"• 2)  Generate a Structure Map to give you a picture of the overall structure and logic flows in the spreadsheet. Assess which sheets are most crucial in the chain of calculations, as well as the extent of any dependencies on external workbooks."]
[null,"• 3)  Run the Formula Scan function, with formula checking and issue flagging turned on. Visually inspect the resulting colour map of formula groups, and investigate any obvious inconsistencies or isolated formulas. You can also use the Cell Inspector (ALT+F10) to check the logic of any suspicious formulas."]
[]
[null,"That's all you need to know to get started. Below is an overview of Rainbow's functions, as well as further information on how to use Rainbow. For detailed information on each of Rainbow's various functions, please read the rest of the User Guide (accessible as a worksheet from the User Guide button). To obtain a printed copy of the Guide, simply print it like a normal spreadsheet."]
[]
[null,"Please consult the final section of this User Guide for full information on the terms of the License Agreement that you accepted when installing your Rainbow license."]
[]
[null,"Overview of Rainbow's Functions"]
[null,"Main Functions"]
[]
[null,"As well as the Menu Guide button, the Rainbow tab (menu) has five groups of functions: Initial Overview, Detailed Analysis, Management & Clean-Up, Colours, and Help. As the names suggest, it's a good idea to start with Initial Overview and then move on to Detailed Analysis and any other functions you need. In this section we cover the main functions in each group; the subsidiary functions (on the drop-down menu under each main function) are explained in the next section."]
[]
[null,"The Initial Overview group consists of three functions that can quickly give you a picture of how your spreadsheet works, and help diagnose a wide range of problems and issues:"]
[null,"•  Summary Report gives you a quick overview of a workbook and an overall score, and highlights possible issues for further analysis."]
[null,"•  Structure Map creates a network diagram that lets you visualise the relationships and logic flows between the sheets in your workbook."]
[null,"•  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."]
[]
[null,"In the Detailed Analysis group are various functions that give you detailed information about the data and logic in the spreadsheet:"]
[null,"•  Issue Tracker is an interactive function which enables you to track and annotate progress on resolving issues flagged by you or by Rainbow."]
[null,"•  Data Explorer tells you where your spreadsheet is using tables of data, and how these are linked to external databases or other sources."]
[null,"•  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."]
[null,"•  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."]
[]
[null,"The Management & Clean-Up group contains several utility functions to help you manage individual workbooks and track changes between different versions of spreadsheets:"]
[null,"•  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."]
[null,"•  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."]
[null,"•  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."]
[null,"•  Comparison Toolbox is a comprehensive function that compares workbooks (and macros), worksheets or ranges, giving you a key tool for controlling versions and scenarios."]
[]
[null,"The three functions in the Colours group let you check the colours in a cell and manage the gallery of colours that Rainbow uses:"]
[null,"•  Identify Colours gives you an explanation of the Rainbow colour(s) in the selected cell, and lets you search for other similarly-highlighted cells."]
[null,"•  Colour Chart gives you an interactive visual display of all the colours that Rainbow uses to map different types of cells."]
[null,"•  Clear Cell Colours removes existing cell colours so that you can see when Rainbow adds its own highlights, and it can also remove the Rainbow highlights."]
[]
[null,"And finally in the Help group are two menu buttons that give you access to important information and options:"]
[null,"•  The User Guide button lets you view and print this User Guide."]
[null,"•  Preferences enables you to change a wide range of options and settings."]
[]
[null,"Subsidiary Functions"]
[]
[null,"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."]
[]
[null,"In the Initial Overview group:"]
[null,"•  Under Summary Report, Security Manager can hide or unhide and protect or unprotect selected worksheets, and Password Tool allows you to unprotect worksheets even when you do not know the password. Calc Manager lets you control the calculation and recalculation of ranges and worksheets as well as all open workbooks."]
[null,"•  Under Structure Map, Inter-Sheet Logic generates a tabular matrix that shows you the flow of logic between sheets in your workbook, and Data Links helps you track down where your spreadsheet calculations are linked to individual cells in another workbook."]
[null,"•  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."]
[]
[null,"In the Detailed Analysis group:"]
[null,"•  Under Issue Tracker, Sample Data selects a random or periodic or conditional sample of data from a worksheet and copies it to a report for auditing, and it can also calculate required audit sample sizes, and 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."]
[null,"•  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, and Names Toolbox provides a range of tools to help you audit and control the use of defined names in the workbook logic."]
[null,"•  Under Cell Inspector, Find Cells lets you search for different data types using a powerful and flexible set of criteria, and Merged Cells Tool gives you a report on merged cells and various options for unmerging them."]
[null,"•  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."]
[]
[null,"In the Management & Clean-Up group:"]
[null,"•  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."]
[null,"•  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."]
[null,"•  Under Comparison Toolbox, 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."]
[]
[null,"In the Colours group:"]
[null,"•  Under Identify Colours, Format Editor works in conjunction with other Rainbow functions to enable you to apply formatting changes or convert text to numbers."]
[null,"•  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)."]
[]
[null,"In the Help group:"]
[null,"•  Under User Guide, Rainbow Log (F10) 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."]
[null,"•  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."]
[]
[null,"Special Functions"]
[]
[null,"Nearly all Rainbow functions operate on a spreadsheet as loaded into your computer's working memory and displayed on screen, and so if you make changes and then close the spreadsheet without saving it, the spreadsheet file on disk will be unchanged. However, Deep Clean, Deep Compare, and the Password Tool work directly on the saved spreadsheet file, which enables them to perform operations that are not possible on the spreadsheet as loaded into memory. And so there are now three tasks that can be performed in a limited way on the spreadsheet as loaded into memory, or more thoroughly by working on the saved spreadsheet file:"]
[null,"•  You can unprotect worksheets and/or the workbook structure quite simply with the Security Manager function. However, if they are password-protected and you do not have the password, you will need to use the Password Tool to delete the password protection."]
[null,"•  You can use the Data Links function to get a report of all the formulas that reference cells in external spreadsheets. However, if you need details of other (\"phantom\") external links, for instance in defined names, you will have to use the Deep Clean function and select Report link references."]
[null,"•  You can generally use the Clean Up Formats function to delete superfluous custom formats. However, where the custom formats have become corrupted, you will need to use the Deep Clean function and select Reset custom formats in order to remove the corrupted data."]
[]
[null,"How to Use Rainbow"]
[null,"Dialog boxes"]
[]
[null,"When you click a button on the Rainbow menu you'll generally see a dialog box with \"Click OK to …\" near the top left. This is Rainbow's guess as to what you want to do, based on your currently selected workbook and worksheet(s). And if your currently selected worksheet is a Rainbow report, Rainbow will assume that you want to analyse the reported-on workbook rather than the report workbook itself."]
[]
[null,"To change the selected workbook and worksheet(s), click the Selection… button. This will open a box where you can select a different (currently open) workbook and worksheets. Hold down the Shift key to select a range of worksheets, or the Control key to select or deselect individual sheets, or click the Select All button."]
[]
[null,"If your selected workbook contains hidden or protected sheets, in most cases Rainbow will automatically check and prompt you to unhide or unprotect them before you can continue. This is because most Rainbow functions generate errors when hidden or protected sheets are present. However, if you are sure that you will be using Rainbow in a way that will only involve access to sheets which are visible and unprotected, then you can disable the automatic check. Click Preferences, and uncheck (untick) the box marked \"Unhide and unprotect sheets\" on the Options tab, then click Apply."]
[]
[null,"Most of Rainbow's functions perform one or both of the following actions:"]
[null,"•  Apply colour to specific cells either in the worksheet(s) you have selected or in separate \"map\" worksheet(s) which mirror the structure of your selected worksheet(s);"]
[null,"•  Generate worksheet report(s) listing specific cells or other items, usually with clickable links to the relevant cells, and often with interactive buttons allowing you to view and/or change selected features."]
[]
[null,"You can often select which of these actions you want in the Output area of the dialog box (usually under the Selection… button). Some options may not always be enabled (i.e. you can't click on them). For instance if the Report option box is ticked but it is not enabled, this means that the Rainbow function you have selected always generates a worksheet report (i.e. the report can't be turned off)."]
[]
[null,"Reports"]
[]
[null,"Most of Rainbow's reports include a hyperlink on each row (often in the \"Cell\" column) which you can click in order to jump straight to the relevant cell in the workbook you are analysing. To make this easier, Rainbow turns off the warning message that Excel normally displays when a hyperlink is clicked, but if you wish you can set your Preferences (under the Reports tab) to turn this message back on ."]
[]
[null,"It is usually a good idea to make a copy of your workbook that you can use for analysis by Rainbow, so that it does not matter when Rainbow adds highlighting to the workbook. However, it is also possible to restore the original colouring of your selected worksheet(s) after  Rainbow has applied its own colours. To do this, you first need to use the Save Format function. This will save the current formatting of your selected worksheet(s), so that you can restore it afterwards using the Undo Changes function."]
[]
[null,"If you need to stop Rainbow in the middle of running a function for any reason, simply press Esc or Ctrl+Break (sometimes you may need to do this more than once). If possible Rainbow will stop, and it will generally let you know which cell it reached in running the analysis. Of course this means that if Rainbow was producing a report, the report will probably be incomplete."]
[]
[null,"The default Preferences settings (on the Reports tab) are to include all Rainbow reports in one report workbook, and for Rainbow to look for currently open report workbooks. So if you want to use a workbook from a previous session, make sure it is already open in Excel when you start using Rainbow. Alternatively you can make use of this feature to append reports as additional worksheets at the back of the workbook you are analysing."]
[]
[null,"Nearly all Rainbow reports (except for the Summary Report, the Structure Map, and the Inter-Sheet Logic matrix) are formatted as data tables. This means that you can easily sort them to highlight certain values, or filter them so that for instance they show only results for a specific sheet. And you can change the formatting style of the table using the Styles group on the Home tab in Excel."]
[]
[null,"If you have selected output in the form of a map, Rainbow will include the map worksheet(s) as well as the report in your selected report workbook. As far as possible it will give them the same names as the worksheets they are mapping."]
[]
[null,"Troubleshooting"]
[]
[null,"Normally Rainbow will look at all cells within the used range of a worksheet. However, if you have selected a range containing more than one cell, it will give you the option to analyse only the selected range. If you have selected one or more columns or rows, it will analyse the column(s) or row(s) within the used range of the worksheet. This can save a great deal of processing time in a large worksheet, if you do not need to analyse every cell."]
[]
[null,"Although Rainbow has been designed to perform quickly and efficiently, in a very large workbook some functions may take many minutes to complete. In this situation the application title (at the very top of the screen) may show \"Not Responding\", or the working area of the screen may go blank. This does not generally indicate a problem; you can usually see that Rainbow is still working by looking at the progress message in the status bar (near the bottom of the screen), though sometimes this too may freeze. If you do need to interrupt Rainbow, just press Esc or Ctrl+Break (sometimes you may need to do this more than once)."]
[]
[null,"If you need help while using a Rainbow function, all functions which generate a dialog box include a Help button; this displays the relevant section of the User Guide, and you can also view this Quick Start Guide. And if access to Visual Basic is switched on (as explained above), all Rainbow's reports will also include a Help button at the top right of the report, which will similarly display the relevant section of the User Guide."]
[]
[null,"You can scroll up and down through the help text with your mouse scroll wheel or the Page Up, Page Down, and cursor keys. The mouse scroll wheel is enabled by default for scrolling through the help text, but if for any reason this does not work with your hardware, you can simply disable it in your Preferences under the Options tab."]
[]
[null,"Function Details"]
[]
[null,"This section covers all of the functions on the Rainbow menu in the order in which they appear, and options which are specific to each function are explained in detail below."]
[]
[null,"Summary Report"]
[]
[null,"It is always worth generating a Summary Report before looking at a spreadsheet in detail. The report is quick to produce, and as well as giving an overall quality rating for the spreadsheet, it can indicate unexpected issues such as Visual Basic code, hidden sheets or hard-coding in formulas. Issues which have impacted the overall quality rating will be highlighted in yellow (somewhat significant) or red (very significant). The report will run even if some sheets are protected, but it can only show a very limited amount of data for protected sheets."]
[]
[null,"Under \"Formula checking\" on the Summary Report dialogue box, you can select the level of criterion used for detecting lone formulas. In the case of a badly-structured spreadsheet the default \"Enhanced\" level may generate too many results to be useful, and you may prefer to select Basic detection. Conversely with a very well-structured spreadsheet you may find Maximum detection most useful. And if you find you are regularly changing the default option, you can select your preferred default option in Preferences, under the Parameters tab."]
[]
[null,"By default Rainbow will check for lone formulas but not produce a separate report. If you find you often then use the Risk Map function to generate a Lone formulas report, it will probably prove quicker to check the \"Generate Lone Fmlas report\" box and generate this report alongside the Summary Report. On the other hand, if your spreadsheets are so large that even checking for lone formulas takes too long, you can click the drop-down box and select \"Do not check formulas\" to run a very quick Summary Report without any detailed formula checking. Columns I to O of the sheet listing in the Summary Report (and the overall score) will then be left blank."]
[]
[null,"Provided that access to Visual Basic modules has been switched on, as explained in the Quick Start Guide, the Summary Report can be your \"information hub\". From here you can simply click one of the 24 function buttons on the report (in column C and in row 26) to go directly to the appropriate Rainbow function for more detailed analysis. There are also 2 buttons (in column J) which allow you to turn on or off the display of graphical objects and sheet tabs."]
[]
[null,"Note that if access to Visual Basic modules has not been switched on, you can still use the Summary Report, but the function buttons will not be present, and instead the explanatory comments (cell notes) will tell you how to access the relevant Rainbow functions for more detailed analysis. In addition, cells B11, B22 and B23 of the Summary Report will indicate that Visual Basic information is \"N/K\" (Not Known); this can also occur if the Visual Basic project in your spreadsheet has been password-protected."]
[]
[null,"Most of the data items in the Summary Report are self-explanatory, but there are a few which deserve further explanation:"]
[null,"•  The Visual Basic (VB) project name in row 11 can be useful as it often indicates the file name under which the workbook was originally saved."]
[null,"•  The Orignl name column shows a shortened form of the name with which each sheet was originally created, e.g. Sheet1 or Chart1. This can indicate the order in which the sheets were first developed."]
[null,"•  The Circlr refce column shows when a cell in the sheet is involved in a circular reference, and it will also show \"No Calc\" when calculation of the sheet has been disabled. This can be re-enabled with the Calc Manager function."]
[null,"•  The Data cells column shows constant (non-calculated) cells as a percentage of total cells, and it  simply indicates whether a sheet is primarily used for data input (high Data cells value) or for calculation (low Data cells value)."]
[null,"•  Lone fmlas are cells which have a different formula from the formula which appears in the majority of the 8 immediately surrounding cells. Such lone formulas often indicate a problem, which is why the Summary Report can optionally be accompanied by a Lone Fmlas report identifying each instance."]
[null,"•  Copied fmla groups shows the number of blocks of copied formulas in each sheet. In practice this is a more useful metric than the number of unique formulas, because if the same formula appears twice in different contexts, each occurrence will still need to be checked. The Formula Scan function gives you a more precise calculation of formula groups, and may yield a slightly lower number than shown here."]
[null,"•  The Max fmla ops (maximum formula operations) column uses the same metric as the Complex operations report in the Risk Map function, and you can click the hyperlink to go directly to the most complex formula in each sheet."]
[null,"•  The Fmla complexity column uses a metric of formula complexity to assess the likely degree of difficulty involved in auditing the formulas in each sheet, based on average formula complexity and the number of copied formula groups."]
[null,"•  The columns for Hard-coded numbers, Lookup functions, and Static + dynam. arrays show you the numbers of formula groups in which these three items occur, rather than the number of individual formulas (which would generally be much larger)."]
[null,"•  In the Static + dynam. arrays column, the second value (after the + sign) is the number of dynamic arrays. Clearly this will always be zero if you have an older version of Excel which does not support dynamic arrays."]
[null,"•  Similarly in the Notes + comnts column, the second value is the number of threaded comments, which are not supported in older versions of Excel. The first value is the number of cell notes, sometimes called comments in older Excel versions."]
[null,"•  The Macro buttons + objects column shows the number of macro buttons + the number of other graphic objects, which will include charts as well as e.g. form controls."]
[null,"•  The Merged cells x area column shows the number of merged cells and the total area (number of individual cells) covered. So if there are 2 merged cells each covering an area of 3 cells, this will be displayed as \"2x6\"."]
[]
[null,"If you have selected to generate the Lone Fmlas report, this will be on the sheet tab after the Summary Report. It gives you a detailed breakdown of the results in the Lone fmlas column of the Summary Report. It uses the same algorithm as the Lone formulas report in Risk Map, but it generally runs much more quickly. Column E in the report shows an example of the formula in the surrounding cells, preceded by the address of the relevant neighbouring cell in [square brackets]."]
[]
[null,"By default the Summary Report will calculate an overall score for the workbook. This score attempts to provide some indication of the likely reliability of the workbook in the absence of any auditing, and so it will be lower for a large and complex workbook. A low score does not necessarily mean that there are in fact significant errors, or that the workbook is of poor quality, but it does indicate that the workbook will require thorough auditing before it can be relied upon. The score is based on an algorithm that compares the levels of the following 20 indicators with pre-determined reference levels (as shown in brackets) :"]
[null,"•  Total sheets (5,000)"]
[null,"•  Other sheets (200)"]
[null,"•  Hidden sheets (20)"]
[null,"•  Styles (formats) (50,000)"]
[null,"•  Defined names (20,000)"]
[null,"•  External links (500)"]
[null,"•  Data connections (500)"]
[null,"•  Lines of Visual Basic code (10,000)"]
[null,"•  Maximum rows x columns (100,000)"]
[null,"•  Circular references (50)"]
[null,"•  Hidden rows x columns (500)"]
[null,"•  Lone formulas (usually 2,000)"]
[null,"•  Maximum formula operations (200)"]
[null,"•  Formula complexity (2,000)"]
[null,"•  Hard-coded numbers (500)"]
[null,"•  Lookup functions (2,000)"]
[null,"•  Error values (5,000)"]
[null,"•  Comments (2,000)"]
[null,"•  Graphic objects (20,000)"]
[null,"•  Merged cells (5,000)"]
[]
[null,"The algorithm includes an adjustment to prevent any one indicator from having an excessive impact on the score. However, the score is only intended as a general guide, and it will be most helpful when applied to spreadsheets of the kind normally used for financial calculations. The score is calculated on the basis of detailed cell-by-cell analysis, and it can be turned off by selecting \"Do not check formulas\" in the drop-down box under \"Formula checking\" on the Summary Report dialog box."]
[]
[null,"By default the Summary Report will also highlight in yellow (somewhat significant) or red (very significant) the values which have most impacted the overall score. The highlighting indicates which areas need to change in order to improve the score, but of course a highlight does not mean that there is definitely a problem, and absence of highlighting does not mean that all is satisfactory. If you prefer the report without highlighting, you can turn this off on the Options tab in your Preferences settings. Here you can also turn off the explanatory comments (cell notes) attached to the cells behind each button."]
[]
[null,"Note that the Summary Report, the Data Links function, the Names Toolbox option to convert names, and the Security Manager and Password Tool functions, are the only Rainbow functions which operate on chart sheets as well as worksheets. All the other functions operate only on worksheets."]
[]
[null,"Security Manager"]
[]
[null,"The Hide selected sheets option in the Security Manager dialog box provides a quick way to hide several sheets, including chart sheets as well as worksheets. With the default Very Hidden option (in the Hide box under the Selection... button), the hidden sheets will be effectively invisible in normal use.  If you choose the Hidden option, the hidden sheets can be unhidden by right-clicking any sheet tab, or from the Hide & Unhide item on the Format menu in the Cells group on the Home tab in Excel."]
[]
[null,"The Unhide all sheets option can unhide any hidden chart sheets as well as worksheets. With the default Very Hidden option, it will unhide both ordinary hidden sheets and any sheets which have been set as very hidden by Rainbow or by another Excel macro.  If you choose the Hidden option it will only unhide sheets which have been hidden in the normal way (i.e. those listed as hidden when you right-click any sheet tab or under the Hide & Unhide menu item mentioned above)."]
[]
[null,"The Unhide rows and columns option will unhide any hidden rows and columns in the selected sheets, and it will display a list of all rows and columns that it has unhidden."]
[]
[null,"The Protect / Unprotect selected sheets options offer a quick way to protect or unprotect several sheets at once, either with or without a password. Rainbow will also protect or unprotect the workbook structure at the same time. And if you need to unprotect but have forgotten the password, Rainbow's Password Tool allows you to remove protection by deleting sheet or workbook passwords."]
[]
[null,"Password Tool"]
[]
[null,"Most Excel users will have come across a situation where they needed to unprotect an old spreadsheet where the sheets and/or the workbook structure were protected with a password, and nobody could remember the correct password. Rainbow's Password Tool can help in this situation, enabling you to remove protection by deleting sheet or workbook passwords, including passwords for any hidden sheets."]
[]
[null,"However, it will not unlock workbooks which require a password to open; these are encrypted and cannot be opened without knowing (or cracking) the password. In addition, the Password Tool will be unable to \"unzip\" any workbooks which require a password to open, even when they have already been opened with the correct password. You will need to click the File tab, then Info, and remove the password protection, then save the workbook and try again. "]
[]
[null,"The Password Tool uses the same technology as the Deep Clean function. This means that (unlike the Security Manager and most other Rainbow functions) it operates on the saved copy of the spreadsheet by editing the underlying XML files. And instead of changing the existing spreadsheet, it creates a new saved spreadsheet with a \"+\" added to the file name, which you can then open from Excel's File menu."]
[]
[null,"You can choose whether to clear passwords from the worksheets or the workbook structure or both. As well as clearing any passwords, by default Rainbow will remove protection from the sheets and/or workbook , but you can choose to switch off this default. Rainbow will then clear any passwords, but will leave the sheets and/or workbook protected (without a password), and you can easily unprotect them from the Review tab in the Excel menu."]
[]
[null,"If the default option to remove protection remains checked (ticked), Rainbow will remove protection from sheets that do not have a password as well as from those that do. When it has finished, it will display details of the sheets that it has unprotected, with separate lists of those that had a password and those that did not. These details are also written to the Rainbow Log file (click User Guide, then Rainbow Log)."]
[]
[null,"Note that the Password Tool does not \"crack\" any passwords, in the sense that it is not aware of the original passwords, and it has no way to discover them because they are securely encrypted in the XML code. The Password Tool simply removes the section of XML code that defines the sheet or the workbook structure as password-protected."]
[]
[null,"Calc Manager"]
[]
[null,"Excel makes it easy to select Automatic or Manual calculation of a workbook; just click Calculation Options on the Formulas tab, or click the File tab, then Options and Formulas. But Calc Manager gives you access to many other ways of managing calculations in Excel, most of which are not available through the standard Excel menu. These will be especially useful with large workbooks where calculation has been set to Manual in order to avoid a long wait for the workbook to recalculate whenever an input is changed."]
[]
[null,"Early spreadsheet software used to calculate cells row-by-row from top left to bottom right. But since about 2000, whenever Excel first needs to calculate a workbook, it constructs a \"dependency tree\" indicating which cells are logically dependent on which others. It then calculates cells in a dynamically optimised order, starting from the pure input data values at the root of the tree and finishing with the final output values which have no further dependents. And when an input value is changed, this enables Excel to recalculate all and only the cells which are dependent (directly or indirectly) on the changed input value."]
[]
[null,"When you press F9, Excel performs this normal (dynamically optimised) recalculation for all open workbooks. But with Calc Manager you can tell Excel to recalculate only the selected worksheet(s) or a selected range (though unfortunately it is not possible to recalculate just the current workbook). And if you are recalculating a selected range, you can deselect the Dynamic Order checkbox to recalculate the range row-by-row from top left to bottom right, which can sometimes be useful in order to check the flow of logic."]
[]
[null,"By selecting the Force full calculation option, you can make Excel perform a full calculation of all cells, not just those which are dependent on changed input values. And if you select All open workbooks, you can then select the Force full dependency rebuild option, which will make Excel perform a full rebuild of its dependency tree before recalculating all cells. Obviously this is likely to take much longer than a normal dynamically optimised calculation."]
[]
[null,"Calc Manager also lets you set various flags which affect how Excel will perform certain future recalculations:"]
[null,"•  For a selected range, you can tell Excel to calculate the whole range on the next recalculation as if every cell in the range had been changed (even if in fact there have been no changes)."]
[null,"•  For selected worksheet(s), you can disable and re-enable calculation. If you disable calculation, the worksheet(s) will appear in the Summary Report with \"No Calc\" in the Circular references column. Calculated values in the worksheet(s) will then not be updated until you run Calc Manager again in order to re-enable calculation."]
[null,"•  For a selected workbook, you can tell Excel to rebuild the dependency tree and perform a full calculation of all cells in the workbook every time there is a recalculation. You can then run Calc Manager again when you need to reset the workbook calculation back to normal."]
[]
[null,"Structure Map"]
[]
[null,"Although tabular reports (like the Summary Report) can display a large amount of information, it is not always easy to make sense of them. And so the Structure Map perfectly complements the Summary Report, giving you an immediately intelligible diagram of the structure of a workbook, showing all the sheets in the workbook as circles (nodes), linked by arrows (connectors) representing the logic flows between sheets."]
[]
[null,"The size of each circle in the diagram is an approximate indication of the size of (number of cells in) the sheet, and the circles are colour-coded to show whether a sheet contains mainly input data (purple), intermediate workings (green), or output calculations (blue). Similarly the arrows are colour-coded to indicate whether the flow of logic (number of links) between the sheets is small (yellow), medium (orange), or large (red), and the logic flows with the largest numbers of links are displayed as larger (thicker) arrows. And you can simply click on any circle or any arrow to see detailed information such as the exact numbers of inter-sheet links."]
[]
[null,"Where there are links to external workbooks, the Structure Map will display a notional sheet named \"Other Workbooks\", which may be linked-to from any actual sheet. Note that a link from sheet A to sheet B means a formula in sheet A that refers to a value in sheet B, and so it retrieves the value from sheet B for use in sheet A. Thus the direction of the link is from sheet A to sheet B, but it results in a flow of data from sheet B to sheet A. By default the arrows in the Structure Map show the direction of the data flow, but you can reverse this in your Preferences settings (on the Options tab) to show the direction of the link."]
[]
[null,"There is no single \"correct\" way to lay out the circles and arrows in the Structure Map; in fact there are endless possible variations. So Rainbow begins each actual layout from a semi-random set of start positions, and it then repeatedly iterates a complex \"force-directed algorithm\" which gradually moves the circles and arrows towards a more optimum layout. And if you are not happy with any particular layout, you can click the Re-Draw button to re-run the calculations from a different set of start positions."]
[]
[null,"There is also a Repeat button, which re-runs the calculations from the same set of start positions. This can be useful if, for instance, you want to reproduce the same layout but with the arrows reversed (in your Preferences settings on the Options tab). And on the Options tab you can also adjust the strength of attraction and the default separation (in points) between circles. Increasing the strength of attraction will produce a smaller, neater diagram, but clusters and groupings of circles may be less easy to see."]
[]
[null,"The Structure Map function uses the same process as the Inter-Sheet Logic function in order to calculate the links between different sheets. And so if you select the option to \"Generate logic report table\", Rainbow will produce a standard Inter-Sheet Logic tabular report alongside the Structure Map. This may be useful if you wish to drill down and see exactly which cells in each sheet contain the relevant links."]
[]
[null,"By default the Structure Map function checks the links for all sheets, but the Selection… button allows you to limit it to selected sheets. This can save time, but it will not give a full picture of inter-sheet relationships. Any sheets not checked will be shown as input data sheets, which can be linked-to from other sheets, but which (like the notional \"Other Workbooks\" sheet) do not contain any calculations of their own. "]
[]
[null,"Inter-Sheet Logic"]
[]
[null,"The Inter-Sheet Logic function uses Rainbow's Item number colours (Item 1, Item 2 etc.) to highlight the cells in each sheet which have precedents or dependents (as appropriate) in other sheets. The cells are highlighted in specific colours indicating which other sheet they are linked to, and you can set your Preferences (on the Colours tab) to change the start colour. The first (start) colour is always used for links to external workbooks, rather than links to other sheets in the same workbook."]
[]
[null,"As well as highlighting links in colour, Rainbow can generate a report showing in tabular format how each sheet is linked to other sheets (and also showing the selected colour for each sheet). This report is in the form of an outlined worksheet; click the number 2 to the left of the column headers at the top to display the details of individual cells, and click the number 1 to collapse the details and show only the totals. Note that the external links shown in this report will ignore cells with a #REF! error, and so it may give a different result from the Data Links function."]
[]
[null,"The colour mapping can only show one link for each cell (the first inter-sheet link in the cell's formula for precedents), but the report will normally show all inter-sheet links for each cell, unless you select the option (under Quick Reporting) to report only the first link for each cell. This option can speed up report production, but is not necessary if you are producing a grouped precedents report (the default setting), as this particular report runs very quickly."]
[]
[null,"The default option to group copied cells together (where appropriate) in the report is normally preferable to the option to list each cell separately, particularly as the grouped precedents report runs much more quickly than the non-grouped version. The non-grouped report is useful if you need to see the links for specific individual cells, but for a large spreadsheet it may require a very large number of rows."]
[]
[null,"By default Rainbow will highlight in the report the logic flows (between two sheets) which have the highest number of links, shaded from red through orange to yellow, with around the top 5% in red, the next 5% in orange, and the next 5% in increasingly pale yellow. The purpose of this is simply to enable you to see at a glance any overall patterns in the inter-sheet logic, and (as with the Summary Report) you have the option in your Preferences settings (on the Options tab) to turn off the highlighting."]
[]
[null,"You also have an option in your Preferences settings (under the Options tab) to expand the report to include within-sheet logic (as well as inter-sheet logic). The report will then include all cells with any precedents/ dependents, including precedents/ dependents in the same worksheet. This will give you a very complete picture of the logic flow, but it can also generate a rather large report. Note that within-sheet logic will be shown only in the report, and not in the colour mapping."]
[]
[null,"If you are running a Dependents report, Rainbow may occasionally find cells with so many dependents that it would take a very long time to generate the report. You can control how Rainbow handles this situation on the Logic Analysis tab in your Preferences settings."]
[]
[null,"If you were to select all sheets and run both a Precedents report and a Dependents report, you might expect that the two reports would show the same results but with the axes reversed. In the simplest cases this will indeed be true, but the results will be distorted whenever a calculation references a multi-cell range in another sheet (e.g. in a SUM() formula). This will count as one reference in the dependent sheet but as several in the precedent sheet. To complicate matters even further, under some circumstances each of the precedent cells may record multiple dependents (instead of just one)."]
[]
[null,"Data Links"]
[]
[null,"The Data Links function helps you find the exact location in your workbook of any external links to other Excel files (to see these click Edit Links in the Connections group on the Data tab in Excel). It searches for references to the external Excel files in all worksheets and also in any graphical shapes, any data validation definitions, any defined names and any charts (including chart sheets as well as embedded charts). Any worksheet-specific defined names will be shown preceded by the name of the relevant worksheet."]
[]
[null,"The Data Links report lists all the occurrences of each link in turn, and the linked file name in column A of the report is highlighted using Rainbow's Item number colours (for more information see the Colour Chart section of the User Guide) to indicate the link number. So references to a specific link will all be highlighted in the same colour, and you can select a highlighted cell and click Identify Colours, and for instance the third link will be identified as \"Item 3\"."]
[]
[null,"Note that Excel can sometimes lose track of external links in a workbook. In other words, there can be external links (which Excel may prompt you to update, but) which are not listed in Rainbow's Data Links report or in Excel's own Edit Links function (on the Data tab). These are often referred to as \"phantom links\", and Rainbow's Deep Clean function can give you a complete list of these if you select the \"Report all external links\" option, and it can attempt to delete them if you select \"Delete names with phantom links\"."]
[]
[null,"Another situation where the Data Links function will be unable to find links is if they are located in a data table in rows which are hidden because they have been filtered. In this situation you can either clear all data table filters, or (as mentioned above) use Rainbow's Deep Clean function and select the \"Report link references\" option."]
[]
[null,"Formula Scan"]
[]
[null,"The Formula Scan function analyses row and column differences to identify groups of copied formulas, and it then uses Rainbow's Item number colours (for more information see the Colour Chart section of the User Guide) to highlight each group. This lets you see at a glance where there are inconsistent formulas in a row or column (which may indicate an error), and it allows you to check just one cell in a group and know that all the other cells are the same."]
[]
[null,"In addition, Formula Scan can run a battery of tests on the first formula in each group, and it can flag some or all formulas so that progress on auditing them and resolving issues can be tracked with the Issue Tracker function. And Rainbow normally also generates a report which lists each formula group by row or column, showing the first and last occurrence and the number of occurrences of each formula, and any checks which have proved positive (shown by the colours in the final \"Issues\" column, with an optional attached comment)."]
[]
[null,"Rainbow allows you to group formulas either along rows (horizontally) or down columns (vertically). This mainly affects the way formulas are grouped in the report, though it will also affect the way formulas are coloured if you have unchecked the option to use the same colour for each unique formula (explained below). In most cases the default grouping along rows will be preferable; this treats as one group all identical formulas on a row even if they are not in adjacent cells, for example the repeated totals in a multi-year spreadsheet with sets of 12 monthly columns followed by an annual total column. Grouping down columns (vertically) may be preferable where there are repeating sets of identical calculations every so many rows, or where the sequence of the calculations is across columns rather than down rows."]
[]
[null,"The checkbox option to use the same colour for each unique formula tells Rainbow to check each new group of copied formulas that it encounters, to see if the formula is the same as one that it has already encountered in a previous group of formulas. If so, then Rainbow will apply the same highlight colour as for the previous formula group, and if not, then Rainbow will recognise this as a new unique formula. Rainbow will then add this new formula to the numbered list of unique formulas that it has encountered during the current Formula Scan operation, and it will apply the corresponding highlight colour, i.e. the Item 1 colour for the first unique formula, the Item 2 colour for the next, and so on."]
[]
[null,"In the Formula Scan report, Rainbow will always list each group of copied formulas on a separate row, with its own reference number (\"Ref\") in column B.  If you have unchecked the option to use the same colour for each unique formula, Rainbow will then highlight column B in the appropriate colour corresponding to each reference number."]
[]
[null,"However, it will generally be more useful to leave the unique formula option checked. Rainbow will then insert an additional column C showing the unique formula number (\"Fx#\") for the formula in each copied group, and it will highlight this column in the corresponding colour. So if each group of copied formulas is unique, then the numbers in column C will be the same as in column B, whereas if some formulas are copied across different areas of a worksheet, or across different worksheets, then the numbers and colours in column C will be the same for each of these copied groups (and different from those in column B)."]
[]
[null,"This means that you can click the drop-down box at the top of column C (in cell C4), and select to sort column C in ascending order, and you will then be able to see all occurrences of each unique formula together in the report. Then if your spreadsheet is designed to have a consistent repeating pattern of rows or columns in a worksheet, or a consistent structure across several worksheets, you can use the Formula Scan report (now sorted by column C) to check whether this repeating pattern or structure has been implemented correctly."]
[]
[null,"The checkbox option to show Cell Inspector descriptions lets you choose whether the penultimate column of the report simply shows the relevant formula, or whether it displays a description of the cell as generated by the Cell Inspector function. It is worth bearing in mind that if you select this option, it will make the Formula Scan run significantly slower."]
[]
[null,"Under \"Risk checking and issue flagging\", you can select which checks you want Rainbow to carry out on the first formula in each group. The checks are drawn from both the Risk Map and the Logic Analysis functions, and you can select whether to carry out all relevant checks or just the \"favourite\" options. Note that there are some checks which are not included even when you select \"Check for all risks and all logic issues\", either because the checks are not applicable to formulas, or because they involve comparison with other adjacent cells, or because they are simply a form of highlighting. You can see these excluded options if you go to Risk Map or Logic Analysis and click the \"Risks/Items not in Formula Scan\" button."]
[]
[null,"In the Risk checking and issue flagging box, you can also select whether you want Rainbow to flag up either all cells, or all cells with issues (where one or more of the selected checks proves positive). Rainbow will then place a red \"Query\" flag (diagonal line) across all the relevant cells, and if the Insert comments option is selected, it will also add a custom comment listing all checks which have proved positive. Both the flag and the comment can then be tracked and edited with the Issue Tracker function."]
[]
[null,"Normally Rainbow first uses the colour labelled Item 1, then Item 2, Item 3 and so on, but you can set your Preferences (under Grouped cell colours on the Colours tab) to start with a different colour (e.g. at the Item 10 colour rather than at Item 1). And by default the Preferences are set to Rotate through all colours, which means that for each new formula group Rainbow will simply use the next available colour (i.e. the colour for Item 1, Item 2, Item 3 etc.). If you uncheck this option, Rainbow will use the minimum number of colours needed to distinguish different formula groups (while using consistent colours for formulas in each row or column). However, this will not then allow Rainbow to use the same colour for each unique formula."]
[]
[null,"Logic Map"]
[]
[null,"All cells in a spreadsheet can be placed in one of the following categories according to whether they have any precedents (cells on which they depend for their calculations) and/or dependents (cells which depend on them):"]
[null,"•  Blank inputs are empty cells which have dependents but no precedents."]
[null,"•  Other inputs are non-empty cells which have dependents but no precedents."]
[null,"•  Intermediate workings are cells which have both precedents and dependents."]
[null,"•  Circular references are intermediate workings which link back to themselves."]
[null,"•  Output calculations are cells which have precedents but no dependents."]
[null,"•  Labels are non-empty cells which have neither precedents nor dependents."]
[]
[null,"The Logic Map function lets you create a visual map of the logical structure of a spreadsheet based on either the dependency types listed above, or the number of dependents of each cell, or both. For numbers of dependents Rainbow uses the corresponding Item number colour, which you can see with the Analysis Wheel; for instance a cell with 3 dependents will be highlighted in the Item 3 colour."]
[]
[null,"If you select to map the dependency types, then after creating the map Rainbow will display the Logic Colours dialog box, with buttons showing the colour for each dependency type. You can click any one of these Find Next buttons to find the next occurrence of the corresponding dependency type on the current sheet, and if you continue to click, you can cycle through all occurrences on the sheet."]
[]
[null,"The default Logic Map option shows both of these colours (for dependency type and number of dependents) together in each cell; the colour for the cell's dependency type is shown on the left and the colour for the number of dependents is on the right, with a graduated transition in between. To check the exact colours displayed in any specific cell, just select the cell and click the Identify Colours button."]
[]
[null,"By looking at this visual map of a worksheet, you will see very quickly whether the sheet is well structured, with consistent patterns of colour running across rows and down columns, input cells grouped together, and no unused \"label\" cells in the middle of a block of inputs. A badly structured worksheet, on the other hand, will look like a mess of different colours, with cells of a different type or cells with different numbers of dependents occurring in unexpected locations."]
[]
[null,"Because this function calls on Excel to check the dependents and precedents of nearly every cell, it runs more slowly than some other Rainbow functions. If you need to check a large workbook, you may decide to select all sheets and then set Rainbow running while you continue with other work. Alternatively you may be able to save time by checking only a few columns or rows. You can also set the threshold (on the Logic Analysis tab in your Preferences settings)  for Rainbow to skip processing dependencies when it is taking too long."]
[]
[null,"You may want to select View, Zoom and e.g. 50%, to get a quick high-level view of the spreadsheet, especially if you have selected to show just the five dependency types. Areas of the spreadsheet which are labelled as being Inputs or Assumptions should obviously contain mainly input cells, and there should be no un-used \"inputs\", which Rainbow will detect as labels in cells which are marked as Inputs or Assumptions."]
[]
[null,"Similarly there should be no input cells without a descriptive label (except sometimes within a range, e.g. with SUM() or LOOKUP()), and there should be no used or referenced cells lying in odd places or outside the main area of the spreadsheet. You can easily spot any blank (empty) input cells, as the Logic Map function highlights these in a different colour from non-blank inputs."]
[]
[null,"However, note that an incorrect reference in a formula could result in blank input cells in any part of a worksheet (e.g. row 1,048,576, or column XFD). If these invalid inputs were outside the used range of the worksheet (the range covering all non-empty cells), they would not be detected by the Logic Map function. To find any such invalid inputs, you can use the Logic Analysis function to check for all cells which have blank cells as precedents."]
[]
[null,"The Logic Map function will detect circular references where all the cells involved in the circular chain of calculations are in the same worksheet. However, if the calculation chain goes outside the current worksheet, the Logic Map function will highlight the circular reference cell shown in the Excel status bar (provided that this is an intermediate working), but it will not show the whole calculation chain. (To see this, click the drop-down arrow beside Error Checking in the Formula Auditing group on the Formulas tab in Excel, then hover over Circular References.)"]
[]
[null,"The Logic Map function does not produce a report, as this would list nearly all cells in the spreadsheet and would be of little use. However, you can produce separate reports on each of the dependency types with the Logic Analysis function (under the Dependents tab). You can also use the Logic Analysis function to generate a different kind of map showing constant and calculated numbers, dates, logical values and text. You can select these items under the Cells and Data tabs, or click the \"All items on Cells and Data tabs\" button."]
[]
[null,"Logic Colours"]
[]
[null,"The Logic Colours dialog box is intended mainly for use with the Logic Map function, and it is automatically displayed after you have run a Logic Map with either of the first two options (dependency types and numbers, or dependency types only). It shows each of the dependency types and the corresponding cell colour, and if you click the corresponding Find Next button, it will take you to the next group of cells with this colour in the current worksheet."]
[]
[null,"The Find Next buttons work in the same way as the Next Block button in the Identify Colours function (with Matching set as Loose), and just as in the Identify Colours function, you can continue to click the button to cycle through all instances of the corresponding colour in the current worksheet. Also as with the Identify Colours function, the Logic Colours dialog box will let you click outside it on the underlying Excel worksheet, so that you can check different cells or even switch to another sheet."]
[]
[null,"Sample Data"]
[]
[null,"Most of Rainbow's functions are designed to help you analyse the logic of a spreadsheet rather than the data it contains. The Sample Data function helps fill this gap by enabling you to to generate a sample of data from a worksheet (e.g. a trial balance download) for testing. Rainbow can format the sample data as a report (added to the current report workbook), and/or it can highlight the sample rows in colour on the source spreadsheet. Before you click the Sample Data button, you can select the area of the source worksheet containing the data to be sampled, and Rainbow will then select the sample rows only from this area. "]
[]
[null,"If you select a range for the source data that has a row of data labels at the top, you can click the Top row - Labels box, and Rainbow will copy the data labels into the Sample Data report. Otherwise it will simply label the data columns as Col A, Col B etc."]
[]
[null,"You can choose from three different sampling methods: (1) random sampling, (2) periodic sampling, or (3) conditional search, i.e. selecting data that meets certain specified conditions. If you choose random sampling, the first box under Sample selection will contain the random number seed. If you set this to 0, the seed (and the sample selected) will change each time you run Sample Data. If you set the seed to a positive number, the sample will be the same (for any given seed value) every time."]
[]
[null,"Rainbow treats conditional search as a form of periodic sampling. So if you choose periodic sampling, and leave the Conditional search checkbox ticked, a third tab (labelled Conditional search) will appear on the Sample Data form. This tab gives you all the power of Rainbow's Find Cells function to search for (and select as samples) specific data such as entries made by an identified individual, or entries created over the weekend or late at night, or SKU numbers ending in a particular code. The only difference from the standalone Find Cells function is that you can specify a particular column (within the main sample range) in which the data is to be found."]
[]
[null,"If you choose conditional sampling (i.e. periodic sampling with the Conditional search checkbox ticked) and you enter 0 in the box labelled Total samples needed, Rainbow will return all the data rows that match your criteria. If this results in more samples than you need, you can specify (on the Summary tab in the box labelled Interval for item sampling) that you want only every other item, or every third item, and so on. Or you can simply enter the number of samples you want in the box labelled Total samples needed."]
[]
[null,"If you choose periodic sampling and untick the Conditional search checkbox, then Rainbow will perform simple periodic sampling. So under Sample selection, the first box will contain the row sampling interval, i.e. if you enter 4, Rainbow will select every fourth row. If you then enter 0 in the box labelled Total samples needed, Rainbow will simply select every fourth row until it reaches the end of the source data. However, if you enter a sufficiently large value for total samples needed, Rainbow will loop back to the beginning of the source data and continue selecting every fourth row. It will check and warn you if this means that it will select the same row of data more than once."]
[]
[null,"As an alternative to entering the required number of samples manually, you can tell Rainbow to calculate the required sample size for you, and you can then click the Auto button to enter this calculated sample size automatically into the box above. However, you will first need to select the Sample size tab (on the Sample Data form) and enter the relevant input data. Most items are self-explanatory, but if you are not sure of the estimated non-compliance rate, you can simply enter 50% (or 0.5). The margin of error is the amount by which your results may differ from the true population value, so for instance you could specify that you need a sample size that will enable you to be 95% confident that the difference between your results and the true population value is not more than 10%."]
[]
[null,"Rainbow's sample size calculator uses the well-known Cochran formula: Sample size = (Z score ^ 2 * p-value * (1 - p-value)) / (margin of error ^ 2).  This assumes an infinite population, and the usual correction for a finite population is then applied: Sample size = (uncorrected sample size * population size) / (uncorrected sample size + population size - 1). Although this is a widely accepted formula, it is not a substitute for making your own informed decisions about sampling, and The Model Answer (as the developer of Rainbow) can accept no responsibility for your use of the sample size calculator."]
[]
[null,"Issue Tracker"]
[]
[null,"The Issue Tracker function lets you work systematically through cells which have been flagged, and create or edit messages which you can pass on to colleagues indicating outstanding work to be completed or issues that have been resolved. It also gives you a simple tool which can quickly correct one or more erroneous formulas."]
[]
[null,"When you invoke the Issue Tracker, it looks at the currently active cell to see whether it has either or both of the following:"]
[null,"•  A flag, which can have one of three values: Resolved (green upwards diagonal), Query (red downwards diagonal), or Error (red diagonal cross)."]
[null,"•  A custom Rainbow comment, i.e. a comment with a blue background and starting with the numeral ① followed by the date and user name."]
[]
[null,"In the Issue Tracker window, the current flag value is indicated by blue highlighting on one of the four buttons at the top right. You can change the flag at any time (or clear it entirely) by clicking on the relevant button. And sometimes this may be all that you need in order to manage your spreadsheet auditing process."]
[]
[null,"If you need to indicate more clearly which issues are the most urgent, you can set a priority level for any of the three flags. When you first apply a flag to a cell, the priority will be 0 (i.e. no priority), and you can change this at any time."]
[]
[null,"For a much more sophisticated level of workflow management, particularly when working in a team, you can use the Issue Tracker's messaging system. This is based on custom Rainbow comments as described above, allowing each comment to carry within it any number of separate messages. The Issue Tracker detects the number of messages in each comment, and it displays the most recent message in the message window. If the messages are from more than one user, the Issue Tracker will display a different background colour for each user so that you can easily see where there is a dialogue going on between different users."]
[]
[null,"You can scroll backwards and forwards through all the messages simply by clicking or right-clicking in the message window, or by using the scroll bar on the left. And where a message is too long to fit into the message window, you can scroll up and down through the text of the message with your mouse scroll wheel."]
[]
[null,"You can easily add a message to any comment (or to a cell without any comments) by clicking where indicated in the editing box. This box will then expand so that you can type your message, and the two Edit Fmla buttons underneath will change to \"Post message\" and \"Cancel\". You can then click the Post message button to add your message (or the Cancel button to leave the comment unchanged). And you can click the Delete button to delete all messages (the entire comment) for the current cell."]
[]
[null,"With the two Edit Fmla buttons you can very quickly correct errors in one or more formulas. For example, suppose that Formula Scan has identified a group of formulas where a hard-coded number has been incorrectly inserted, and this group of formulas is in the middle of a larger block which contains the correct formula (with no hard-coding). You can simply navigate (with Issue Tracker) to the first of the incorrect formulas, and click either Edit Fmla ▶ to copy from the cell to the left, or Edit Fmla ▼ to copy from the cell above. Issue Tracker will ask you to confirm the action, and it will then automatically update the incorrect cells to match the nearby correct formulas. Note that the Edit Fmla buttons will only be enabled if the current cell (in Issue Tracker) contains a different formula from the cell to the left and/or the cell above."]
[]
[null,"When you have finished viewing or editing flags or messages or formulas for the current cell, you can click the Previous cell or Next cell button to search for other flagged cells in the current sheet. You can also click the Report button to generate a report listing all flagged cells in the current sheet. And the Issue Tracker window (like the Cell Inspector) also allows you to click outside it on the underlying spreadsheet, and you can even switch to a different sheet to continue the audit process. However, if you need a report on all flagged cells in the entire workbook, you can use the Comments Report function (under Comparison Toolbox), which generates a report for all sheets by default."]
[]
[null,"Scramble Workbook"]
[]
[null,"Sometimes you may want to share a workbook which contains confidential data with someone (e.g. Rainbow support) who needs to understand the workbook structure but not its data. The Scramble Workbook function provides a way for you to do this, by scrambling (randomising) both the data and optionally the text in the workbook."]
[]
[null,"There are four options for randomising numerical data. The default is the second option, which leaves date values unchanged, and also any series where the numbers increase by 1 each time (often used for period numbers). All other input numbers are randomised, while mostly retaining the same order of magnitude in order to show the likely operation of any conditional logic."]
[]
[null,"The first option simply replaces all input numbers and dates with the value 1, and the third option randomises all input numbers and dates. Formulas are left unchanged in these first three options, whereas in option four (as well as randomising numbers and dates) any links to external workbooks are edited so as to yield a meaningless value."]
[]
[null,"Text values will be left unchanged by default, but if you check (tick) the box \"Replace text with lorem ipsum\", all input text values will be replaced with the corresponding length of \"lorem ipsum\" text, i.e. the standard filler text used in publishing and graphic design. Any comments (cell notes) will also be replaced with lorem ipsum text."]
[]
[null,"There is also a fifth option, \"Raise error message\". This is not about randomising data; it simply creates an artificial error (number 9979, Automation error), which forces Rainbow to generate an error log sheet. The log sheet contains a set of diagnostic data, and you can email it to support@themodelanswer.com to help with diagnosing system problems."]
[]
[null,"Data Explorer"]
[]
[null,"Excel now has many ways to represent blocks of data; as data tables, as query tables, as combined data/query tables, and as pivot tables. And underlying these visible blocks of data are two kinds of entities that are not normally visible:"]
[null,"•  Connections are the \"data pipes\" that define how data flows from external applications such as databases, OLAP \"cubes\", or Web pages, into the visible tables."]
[null,"•  Pivot caches are the \"data stores\" that hold the data for pivot tables, enabling you to select the row, column, page and data fields to be displayed."]
[]
[null,"The Data Explorer function helps you make sense of this complexity, by providing you with two linked and interactive reports listing all the tables, connections and pivot caches in your workbook. These interactive reports let you drill down to see further information about each item, some of which you can then easily edit."]
[]
[null,"The first Data Explorer report lists all the various types of tables in your workbook, and this report also shows (in the Source column) which pivot cache is used by any pivot tables, and (in the Connection column) which connection is used by any tables that reference external data. This information about pivot caches and connections is colour-coded to link it to the relevant row in the second Data Explorer report."]
[]
[null,"This second report lists all the pivot caches and connections in your workbook. Pivot caches do not have a name; they are identified simply by their index number. So the report first lists any pivot caches (which are shown as Pivot Cache 1, Pivot Cache 2, etc.) and then it lists any connections (which are identified by name)."]
[]
[null,"You can choose to run either of these reports separately, but the default is to generate them both at the same time, and they will then be hyperlinked so that you can click on any of the colour-coded cells in the Tables report to jump straight to the relevant row in the Caches/Connections report. The default is to report the tables for the whole workbook, but you can also choose to report only for selected sheets."]
[]
[null,"Both of these reports have a View column (column G) with a button for each item that you can click to display additional information about the item, in columns I and J. These columns list the values of various properties of the item, such as its name; note that different types of item will have different sets of properties. The item for which information is currently displayed is indicated by → ● on the View button."]
[]
[null,"Where there is a yellow highlighted cell in column I referring to a new value, e.g. New Name, this means that you can enter (in this case) a new name for the item. The easiest way is often to copy the existing value (e.g. the current name) from the row above and then edit it. When you have done this, simply click the Apply New Values button."]
[]
[null,"As an example, suppose your workbook contains two pivot tables which are based on exactly the same underlying data, but they each have their own separate pivot cache. If you want to make them both use the same pivot cache (e.g. Pivot Cache 1), click the View button for the second pivot table, and you should find that it has a Cache Index of 2. In the row underneath (against New Cache Index (1 to 2)) enter 1, and then click the Apply New Values button. If you now re-run the Data Explorer report, you will find that both pivot tables use Pivot Cache 1, and Excel has deleted Pivot Cache 2."]
[]
[null,"As another example, imagine you have a connection that currently points to an external database file OldData.mdb, and you wish to change it to point to NewData.mdb. Click the View button for this connection, and you will see that the database file name is stored in the Connection property. Copy the current value of this property down to the New Connection row, then simply edit it to replace OldData.mdb with NewData.mdb, and click the Apply New Values button."]
[]
[null,"After you have changed any values for a table, pivot cache or connection, Rainbow will attempt to update the Data Explorer report to reflect the new values. However, if you have made any complicated changes (e.g. to the Cache Index or Connection properties), it is advisable to re-run the Data Explorer report to make sure the changes have taken effect. As mentioned above, Excel will automatically delete any pivot cache which is not used, and it will then re-number the remaining pivot caches."]
[]
[null,"Note that you can edit other properties of your workbook connections if you click Connections in the Connections group on the Data tab in Excel. Rainbow's Data Explorer does not aim to replace this Excel function; its purpose is to give you a much clearer picture of how the tables, pivot caches and connections are all linked together."]
[]
[null,"Macro Explorer"]
[]
[null,"Excel macros in modern versions of Excel consist of lines of Visual Basic code; they are very easily overlooked, and yet they can make fundamental changes to a workbook. The Macro Explorer function generates a report which gives you an immediate overview of all the macros in your workbook, and it allows you to quickly drill down and see the lines of code for each macro."]
[]
[null,"Macros are normally grouped together in modules, so the Macro Explorer lists the relevant module and shows the line number in the module which marks the start of the macro. There are often several blank lines before the real start of the macro, so the report also shows the line at which the macro code really begins. It also shows the macro type; most macros are Subs (sub-routines), but some may be Functions."]
[]
[null,"Subs are often linked to a button in a worksheet; you can use the Graphics Report in Rainbow to check whether your workbook contains any buttons, and what macros they are linked to. Some Functions, on the other hand, may be \"user-defined functions\" which can be called directly from inside an Excel formula (just like Excel's own built-in functions). To check whether a particular function is used in this way, you can search for it in your spreadsheet with Rainbow's Find Cells function."]
[]
[null,"Each macro in the Macro Explorer report has a View button (in column G), which you can click to view the lines of Visual Basic code in columns I and J. Rainbow ignores blank lines at the start of the macro, so the first line shown may not be the same as either of the line numbers in the report. The macro for which the lines of code are currently displayed is indicated by → ● on the View button."]
[]
[null,"Note that, like the Summary Report, the Macro Explorer will be unable to access information on Visual Basic code unless your Macro Security settings allow this (see the Quick Start Guide for more details). And even if you have set up the appropriate Macro Security settings, Rainbow will be unable to access the information if the Visual Basic code has been password-protected."]
[]
[null,"Names Toolbox"]
[]
[null,"The Names Toolbox function supplements Excel's Name Manager by allowing you to:"]
[null,"•  Generate an interactive report of all defined names (even hidden names)."]
[null,"•  Use this report to hide/unhide, list references, convert, change local/global scope or delete any name."]
[null,"•  Convert all names to cell references (and optionally delete them)."]
[null,"•  Delete all names which are not used anywhere in the workbook."]
[null,"•  Delete all names which are no longer valid (i.e. they refer to #REF)."]
[]
[null,"When you generate a Names Toolbox report, Rainbow can also check and report whether each name is used anywhere in the workbook. This can be very useful, e.g. to show which names are unused, but it can take a long time, as Rainbow searches not just formulas, but also any data validation, conditional formatting, embedded charts, chart sheets, other defined names, and VBA code. To turn on this feature, select \"Check\" in the box under \"Usage\". If you let Rainbow check usage, it also gives you the option to highlight in colour any cells where a name is used."]
[]
[null,"If you have selected to check usage, Rainbow will place an arrow → in column B of the report against any name which is used in your workbook, whether in calculations, in charts, in other names, or in macro code. If you have selected to highlight used names in colour, Rainbow will highlight column C in the colour it has used to show occurrences of the name in cells in the workbook. And any name which is not used anywhere in the workbook will be indicated by a cross × in column B."]
[]
[null,"Any hidden names are shown in the report with the name in (brackets). Any names which evaluate to an array are shown with {curly brackets} in the Value column, and any which refer to an Excel 4.0 macro start with \"(XLM)\" in the Refers to column. Where the name refers to a simple cell or range, Rainbow inserts a hyperlink in the Refers to column so that you can click to go straight to the referenced range."]
[]
[null,"The Scope column shows whether a name applies across the whole workbook or just in a specific sheet. And in the Comment column Rainbow can show (in brackets) the description generated by the Cell Inspector function if there is no comment; by default this option is turned off, but you can turn it on in your Preferences settings (on the Options tab)."]
[]
[null,"For each name in the report, you can select an action in the final column from the drop-down list (click on the drop-down arrow by each cell). You can choose to:"]
[null,"•  Hide the name, so that it will not be shown in Excel's Name Manager (or unhide the name if it is hidden)."]
[null,"•  Generate a report listing where the name is used, i.e. all cell formulas, chart formulas, other names, and macro code, where the name is referred to."]
[null,"•  Convert the name to its referenced value, i.e. substitute the value of the name in all places where it is used (see example below)."]
[null,"•  Change the scope of the name from global to local, or from local to global, or from one (local) sheet to another."]
[null,"•  Delete the name."]
[]
[null,"Once you have selected the required actions, simply click the Perform Actions button. Rainbow will ask you to confirm, and will then go ahead and perform all actions that you have requested, and it will attempt to update the report to reflect the requested actions. However, if you have requested any complicated actions, particularly if these relate to duplicate names with differing scopes, it is advisable to re-run the report to check that the changes have taken effect. And if you have deleted any names, you will need to re-run the report before you can perform any further actions."]
[]
[null,"The second Names Toolbox option (Convert names to formulas) searches for all references to defined names within formulas, data validation, conditional formatting, charts, other names, and VBA code in the workbook, and substitutes the \"Refers to\" value of the name. So for example if you have defined the name \"Tomorrow\" with the value \"=TODAY()+1\", and cell C3 contains the formula \"=Tomorrow\", Rainbow will replace this with \"=(TODAY()+1)\". The name \"Tomorrow\" will then be unused (after Rainbow has made similar changes to any other cells in the spreadsheet which reference this name)."]
[]
[null,"If you wish, you can set your Preferences (on the Options tab) so that when running this second Names Toolbox option, Rainbow will also delete the converted names (since they are no longer referred to anywhere), along with any other names that are not referred to anywhere in the spreadsheet. However, Rainbow will not delete the \"Print_Area\" and \"Print_Titles\" names used by Excel when printing, and it will also not convert or delete invalid (#REF!) or macro (XLM) names."]
[]
[null,"The third Names Toolbox option gives you a convenient tool to clean up the names in a spreadsheet by deleting all those which are not used. Like the second Names Toolbox option, it will not delete the \"Print_Area\" and \"Print_Titles\" names used by Excel. If the current worksheet is a Names Toolbox report showing used and unused names, Rainbow will offer to delete the unused names as shown in the report, rather than performing a new check for unused names."]
[]
[null,"The fourth Names Toolbox option provides a quick and simple way to delete all names which are no longer valid (where the name refers to #REF!)."]
[]
[null,"Note that, unlike Excel's Name Manager, the Names Toolbox report only includes \"defined names\",  i.e. it does not include the names of data tables. These are quite different from defined names, and are listed in Rainbow's Data Explorer report."]
[]
[null,"Cell Inspector"]
[]
[null,"The Cell Inspector (which you can invoke by pressing ALT+F10) is an interactive function which displays the precedents and dependents of the currently selected cell, and also provides a best-guess description of the meaning of each cell. It will take you to each precedent or dependent (double-click the relevant line or click the appropriate button) so that you can click on the underlying spreadsheet and explore the logic and meaning of each cell. Then click the back-arrow button to return to where you were before. The Cell Inspector title bar shows how many steps you are from the original cell (#1) where you started."]
[]
[null,"For most cells, the description will be in two parts separated by a dash, e.g. \"Net Profit - Year 1\". This indicates that the row heading is \"Net Profit\" and the column heading is \"Year 1\". Sometimes there may be only one heading, e.g. \"Net Profit\", and sometimes a heading may have two parts, e.g. \"All Products Net Profit\". To find the headings, Cell Inspector uses a probabilistic scoring system which cannot be guaranteed to be always accurate, but has been found to be reliable across a range of financial models, and can provide a very fast indication of possible faulty logic."]
[]
[null,"As an example, suppose you run Cell Inspector on cell H74 in sheet Operations, and Cell Inspector shows this as Availability Fee - 30/09/2015, with the formula \"=Inputs!H101*(1-C26)\". If you then look at Cell Inspector's list of Precedents and see that Inputs!H101 is Availability Charge - 30/09/2014, you can immediately spot that there may be a problem with misaligned columns in different sheets referring to different periods."]
[]
[null,"If you come across a complex formula, perhaps with several nested \"IF\" statements, you can click Parse Formula to see a structured layout of the formula which will quickly identify which are the relevant components of each \"IF\" statement. To go back to the standard Cell Inspector layout, just click Show Dependents."]
[]
[null,"Rainbow tries to make sure that the currently selected cell is always visible by placing the Cell Inspector dialog box in the top right of the screen, and if necessary by scrolling the spreadsheet to bring the cell into view. You can also click the Minimise button so that you can see any parts of the worksheet which may still be hidden by the Cell Inspector dialog box. And at any time you can click outside the dialog box to move around the worksheet and workbook."]
[]
[null,"Cell Inspector won't take you to precedents or dependents in closed external workbooks or in hidden sheets, though it will attempt to indicate precedents in closed workbooks. Cell Inspector will ignore dependents in hidden sheets when these are the first that it finds, and if the first two dependents are both hidden it will assume the cell has no dependents. So you should always use Rainbow's Security Manager function first if you think there may be any links to hidden sheets."]
[]
[null,"If you have selected a cell with hundreds or even thousands of dependents, Rainbow will by default only process the first 120 dependents, since otherwise Cell Inspector may take a long time to run. You can change this setting in your Preferences on the Parameters tab (under Cell Inspector), and if you wish to be sure that all dependents are processed, you can choose the maximum value of 32,000 (the maximum set by Excel)."]
[]
[null,"Find Cells"]
[]
[null,"The Find Cells function lets you specify what you are looking for much more precisely than Excel's own Find function. It covers six different data types (text, formulas, numbers, dates, days, and times) and up to 12 different search conditions. It will be particularly useful when searching data for audit purposes; for instance you can search for data entry records dated to weekends or during night hours."]
[]
[null,"The text search function is case-sensitive by default, but you can turn this off by unchecking the Case Match box. Like most Rainbow functions, Find Cells highlights identified cells on the worksheet, and you can use the Identify Colours function to confirm the type of search that was performed."]
[]
[null,"Merged Cells Tool"]
[]
[null,"The Merged Cells Tool produces a detailed report listing all merged cells, and by default it highlights in yellow those merged cells which have a numeric value, since these carry an added risk of causing incorrect calculation results."]
[]
[null,"In addition, the tool can unmerge either all merged cells or all those which have a numeric value. By default Excel places the unmerged value in the top left cell (cell number 1) within what was the merged area, but the Merged Cells Tool lets you choose where to put the value, i.e. in which cell number within the merged area. If you select cell number 0, the tool will place the unmerged value in the middle cell of each merged area, or you can place it in the last cell of each merged area by selecting a very high cell number, e.g. 999."]
[]
[null,"If you simply want to unmerge cells without producing a report, you can turn off the reporting option. And if you want to unmerge different cells in different ways (e.g. placing the unmerged value in different cell numbers), you can limit the operation of the tool to the currently selected range in the worksheet (provided that this covers more than one cell)."]
[]
[null,"Risk Map"]
[]
[null,"The Risk Map function enables you to highlight cells and generate reports analysing any or all of 24 different potential risks. Each potential risk has its own colour, which you can see from the Analysis Wheel or the Colour Chart, and Rainbow uses these colours to highlight the corresponding cells in your spreadsheet, and also on each Risk Map report. So you can select a highlighted cell in a report, click Identify Colours, then switch to your spreadsheet and use the Identify Colours search buttons (e.g. Next Block) to see all occurrences of the corresponding risk in the current worksheet."]
[]
[null,"For detailed help on all the 24 risk analysis options  (i.e. 6 potential risks on each of 4 tabs), click the Help bar that runs down the right-hand edge of each tab. If you wish, you can of course select all 24 options (just click Clear all checkboxes and then Invert current selection),  but the analysis will run faster with fewer options selected. So when you first click Risk Map, you will find it has pre-selected some of the most useful options; these are the default set of \"favourites\", which you can clear and then re-select by clicking the Favourites button."]
[]
[null,"You can make a more precise selection of the risks you want to analyse by selecting them individually (on the Patterns, Checks, Formulas, or Hidden tab), or you can click the \"Risks not in Formula Scan\" button to run the checks that are excluded from the Formula Scan function (as explained earlier). Later you may end up with a standard set of analyses that you use regularly, and you can then click the Save favourites button to set these as your default choice (and you can always restore the original Favourites by clicking Restore Defaults, under Preferences)."]
[]
[null,"Under the option buttons is the Analyse macros box; if the Macros checkbox is selected, Rainbow will analyse any Visual Basic (macro) code in the selected workbook. It will detect and report on any macros which are set to run automatically when you open the workbook, as these can sometimes present a serious security risk. It will also report any macros which appear to have been generated with Excel's \"Record Macro\" function, because without further testing and editing these recorded macros are often very context-specific and unreliable."]
[]
[null,"At the bottom of the Risk Map form is the Insert box, where you can select to insert a \"Query\" flag (diagonal red line) and/or a custom comment (cell note) on each highlighted group of cells, and you can then track and edit this with the Issue Tracker. The comment lists all the analysis types found in the cell, even if there are more analysis types than can be shown with colour highlighting (depending on the Maximum colours value on the Colours tab in your Preferences settings). Note that a large number of comments can significantly increase the size of the spreadsheet file."]
[]
[null,"By default, the Risk Map form will open with the Analyse macros and Insert flags options selected, and with Output options to colour cells and generate a report. If you wish to change these defaults, simply run an analysis with your preferred options selected, and then click the Preferences icon (on the Rainbow tab) and click the Save Defaults button."]
[]
[null,"Risk Map - Patterns"]
[]
[null,"• 1)  Lone cell types are cells which are of a different type (e.g. blank, text, logical, date, array, formula or value) from the majority of the 8 immediately surrounding cells. This can indicate a problem, where for instance a cell in a block of calculations has been overwritten with a value. You can adjust the criteria that control how Rainbow detects lone cell types and lone formulas in your Preferences (under the Parameters tab)."]
[]
[null,"• 2)  Lone formulas are cells which have a different formula from the formula which appears in the majority of the 8 immediately surrounding cells. As with lone cell types, this can often indicate where a formula has been adjusted to yield the required result."]
[]
[null,"• 3)  Different formats shows cells which are of a different type or different number format from the cells around them, and it also finds cells where the formula has been set to be hidden. If the \"map\" generated by this option displays a lone cell with formatting differences in the middle of a homogenous block of cells, this may well indicate a potential problem or error. An example report item could be:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C119] Hidden Formula, «General» → «0.00%»"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This indicates that cell C119 has been set with its formula hidden, and also it has a percentage format rather than the General number format of surrounding cells."]
[]
[null,"• 4)  Hard-coded numbers refers to  cells where an input number has been combined with a formula, and this analysis option will highlight all formulas containing a hard-coded value of 10 or above, or a decimal value. If you wish you can change your preferences (on the Risk Map tab) to include single-digit numbers from 2 to 9, though formulas containing a hard-coded 0 or 1 are very normal and will always be excluded. By default, Rainbow will also include as hard-coded numbers any cells containing a formula such as \"=365/12\", which may be regarded as bad practice but is not generally dangerous. However, you can change your Preferences settings to highlight only cells which combine a hard-coded number with a reference to another cell, e.g. \"=C63/12\"."]
[]
[null,"• 5)  Outlier numbers are values in a data set that is expected to be normally distributed, but which appear to fall outside the expected distribution. Rainbow uses the Generalized Extreme Studentized Deviate (ESD) test, which is a recognised standard method for finding multiple outliers; Rainbow assumes a maximum of 10 outliers, but you can change this on the Risk Map tab of your Preferences settings. Column F of the report shows key statistical information, and indicates where an outlier has been \"masked\" by other outlier values. Note that Rainbow will detect whether each worksheet is arranged by rows or columns, and will then test for outliers along each whole row or column. If your worksheet contains multiple data sets which need to be tested separately, you will need to select each data set in turn, and then click in the Applicable Range box at the bottom of the Risk Map dialog box to test only the relevant data."]
[]
[null,"• 6)  Variant text values are text values which differ by only one character from other nearby text values, and so may be the result of a \"typo\". The report shows which character has been added, omitted or changed, and what is the nearby text that may be the correct value. As with outlier numbers, Rainbow will automatically detect whether each worksheet is arranged by rows or columns, and will then compare along each row or column as appropriate."]
[]
[null,"Risk Map - Checks"]
[]
[null,"• 1)  Cells with  error values are cells with a value of #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, #SPILL! or #VALUE!. By default, Rainbow will show (in the Error Values column of the report) the type of error (e.g. #REF!) and the cell formula. And sometimes if there is a group of copied cells, there may be more than one type of error in the same group. For example, if the spilled-to range of part of a dynamic array is blocked, and a divide-by-zero error is present in another part of the array, you might see a report item something like this:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"#SPILL! #DIV/0! ← =A1:A10"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"But many of these cells display an error value because they have one or more precedents with error values, not because of any problem with the cell itself. So if you prefer, you can tell Rainbow (on the Risk Map tab in your Preferences settings) to trace the precedents of each cell and show which are the error source cells (as distinct from dependent or secondary error cells). Clearly this analysis will take longer to run, but it will show (for dependent error cells) the precedents with error values, and it will highlight the error source cells in yellow in the report. Following are examples of the two different types of report item for this option:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C17] #DIV/0! → Error source cell"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This item (which will be highlighted in yellow unless you have turned off highlighting) indicates that cell C17 has the error value #DIV/0, and that this is due to a division by zero in the formula of the cell itself (not inherited from another cell)."]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[L49] #DIV/0! ← C17"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This shows that cell L49 also has the error value #DIV/0, but that this value is inherited from cell C17, which is one of the cell's precedents."]
[]
[null,"• 2)  Hidden error values are cells containing one or more conditional IF() statements, where the unused branch of the statement would generate an error. A simple example would be where cells B3 and B4 contain the value 0, and B5 contains the formula \"=IF(B3>0,B3/B4,0)\". This would generate the following item for cell B5 in column F of the report:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"#DIV/0! ← B3/B4"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This indicates that the formula element \"B3/B4\" in cell B5 would generate a #DIV/0! error, but that this element (the \"true\" branch of the conditional statement) is not currently used, because the condition \"B3>0\" is false. Of course the conditional statement may be written to ensure that the current \"false\" branch is used only when it will not generate an error; Rainbow is not able to check for this level of logic."]
[]
[null,"• 3)  Excel error checks lists cells which have been flagged by Excel's background error checking (indicated by a dark green triangle in the top left corner of the cell). This can flag up the following types of errors: empty cell references, cells with error values, inconsistent formulas (for a region or a list), data validation errors, numbers or dates entered as text, omitted cells, and unlocked formula cells. The Risk Map report shows the error type(s) for each cell."]
[]
[null,"• 4)  Conditional formatting lists cells which have conditional formatting (on Excel's Home tab) applied to them. The report shows (in column F) the cell value and the type of conditional formatting that has been applied, but you will need to click the hyperlink (in column C) and go to the cell to see the effect of the formatting in each case."]
[]
[null,"• 5)  Data validation lists cells which have data validation (in Data Tools on Excel's Data tab) applied to them. Column F of the report shows the cell value and the type of validation, and column G indicates whether the current value of the cell (or the first cell in a block) is valid. The report will highight in yellow where the current value is invalid."]
[]
[null,"• 6)  SUM containing subtotals lists formulas beginning with \"=SUM(\" where the summed range itself contains formulas with the SUM() function or other totals of cell values. These are not necessarily errors, but clearly in some cases the subtotals may include values that are also included in the main SUM() total, leading to double-counting."]
[]
[null,"Risk Map - Formulas"]
[]
[null,"• 1)  Complex conditions are cells with a formula which contains at least 2 levels of nested IF() statements. Multiple nested IF() statements are widely recognised as a common source of errors in formulas."]
[]
[null,"• 2)  Multiple operations lists cells with a formula which involves a specified number (or more) of Excel functions or other operations in brackets (parentheses). The default value is 4 operations, but you can change this on the Risk Map tab of your Preferences settings."]
[]
[null,"• 3)  Multiple references lists cells which have a specified number (or more) of precedents. The default value is 6 precedents, but you can change this on the Risk Map tab of your Preferences settings. If you click a hyperlink in the report to go to a particular cell, you can then use the Cell Inspector function to navigate to each of the cell's precedents."]
[]
[null,"• 4)  LOOKUP functions finds cells containing any of these functions: HLOOKUP(), INDEX(), LOOKUP(), MATCH(), VLOOKUP(), XLOOKUP(). These functions are widely used but have complex parameters which are recognised to be a common cause of incorrect results. In some cases the LOOKUP(), HLOOKUP() and VLOOKUP() functions may not return the correct result unless the list of looked-up values is sorted in ascending order; in these cases there is even greater potential for error, and Rainbow will highlight them in yellow in the report."]
[]
[null,"• 5)  Volatile functions finds cells containing any of the following functions: NOW(), TODAY(), RAND(), RANDARRAY(), RANDBETWEEN(), OFFSET(), INDIRECT(). These functions (and occasionally some others) are described as \"volatile\" because their value changes each time Excel recalculates. If heavily used, they can significantly impact on spreadsheet performance."]
[]
[null,"• 6)  Array formulas are a special type of formula that Microsoft Excel can use to calculate results across an array (block) of cells. They can be a very effective method of performing certain complex calculations, but they need to be carefully checked. And besides these \"static\" array formulas, modern versions of Excel allow for dynamic arrays, where a formula in a single cell generates an array of results which \"spills\" into neighbouring cells. Rainbow will highlight any dynamic array formulas in yellow in column F of the report, and will show the \"spilled-to\" range."]
[]
[null,"Risk Map - Hidden"]
[]
[null,"• 1)  Cells in hidden row or column are any non-empty cells located in a row or column which is either hidden or set with a row height or column width of less than 4 points (normally 5 pixels or less). Clearly there is a risk of such cells being used to hide problematic data or calculations."]
[]
[null,"• 2)  Hidden-format formulas are any cells containing formulas where the protection status of the cell has been set to Hidden, meaning that when the worksheet is protected, the cells will still display the calculated value but the formulas will not be visible."]
[]
[null,"• 3)  Numbers formatted as text are numbers which have been formatted (e.g. by prefixing them with a single quote mark) so that Excel will interpret them as textual rather than numeric values. By default (depending on your settings on the Risk Map tab in Preferences), this option will also find numbers which are preceded or followed by up to three or four letters, e.g. \"¥1000\" or \"568 ml\". If you then wish to convert these cells into simple numeric values, you can use option 1 in the Format Editor."]
[]
[null,"• 4)  Formulas formatted as text are (as with the preceding item) formulas where the cell's \"number format\" has been set as \"Text\", or where the formula itself has been prefixed with a single quote mark. Excel will often recognise where \"Text\" formulas return a numeric value, but if this value is then converted to a simple number it will be evaluated as zero."]
[]
[null,"• 5)  Invisible cell contents refers to cells where the content of the cell has been made invisible either by setting it to the same colour (e.g. white) as the background or by applying a custom format such as \";;;\". These techniques are often used to hide incidental parameters in a spreadsheet."]
[]
[null,"• 6)  Hidden operators lists any formulas containing a double negative (--) operator, which can easily be entered by mistake and will evaluate to a positive (+). In addition, in modern versions of Excel which support dynamic arrays, Rainbow will list in this report any formulas containing the \"implicit intersection operator\" @, which instructs Excel's calculation engine to return a single value rather than a dynamic array."]
[]
[null,"Logic Analysis"]
[]
[null,"The Logic Analysis function works very much like the Risk Map function, and it similarly has four tabs and 24 analysis options (but no Analyse macros box). However, unlike the Risk Map function, the analysis options here are not necessarily indicators of potential risks, but rather they include three different types of item:"]
[null,"•  On the Cells and Data tabs are items mainly indicating different types of cells (numeric, date, constant, calculated etc.) which can be used to create a visual map of a worksheet."]
[null,"•  The Dependents tab contains items indicating the different roles that cells can play (input, output etc.) in the spreadsheet logic. These items can also be used to create a visual map of the spreadsheet logic, but this can be done more efficiently with the Logic Map function."]
[null,"•  Finally, the items on the Precedents tab relate to various potential abnormalities in a cell's precedents (e.g. precedents of different cell type). These items, like those in the Risk Map function, do indicate potential risks; however, the process of tracing all a cell's precedents can be slow, and so these Precedents items take much longer to run than the items in the Risk Map function."]
[]
[null,"For detailed help on all the 24 analysis options, as with the Risk Map function, click the Help bar that runs down the right-hand edge of each tab. And (as with Risk Map) when you first click Logic Analysis, you will find it has pre-selected some options to create a useful \"map\" of the worksheet. You can clear and then re-select this default set of \"favourites\" by clicking the Favourites button. And if later you end up with a standard set of analyses that you use regularly, you can then click the Save favourites button to set these as your default choice."]
[]
[null,"You can make a more precise selection of the analyses you want to run by selecting them individually on each tab, or you can click the \"All items on Cells and Data tabs\" preset button to generate a colour \"map\" of the structure of a worksheet which can indicate poor spreadsheet design (rather like the Logic Map). Reporting on individual cells is not usually relevant for this purpose, and is turned off by default (though you can turn it on if you wish)."]
[]
[null,"If you use the items on the Cells and Data tabs to create a visual map, you will notice that numbers are shown in blue, dates in purple, logical values in green, and text in yellow. In each case calculated values are a slightly darker shade than constant values, e.g. calculated logical values are a darker shade of green than logical constants (inputs). Note that cells containing numbers formatted as text (e.g. '1234) will be highlighted as both numbers and text, which can offer an alternative to using Risk Map for this purpose."]
[]
[null,"As previously mentioned, the items on the Dependents tab can generate the same \"map\" as the Logic Map function. Logic Map provides a fast and direct way to generate this map, and also lets you show numbers of dependents, whereas the Dependents tab in the Logic Analysis function allows you to select individual dependency types and optionally  generate reports and insert flags and comments."]
[]
[null,"With the Precedents analysis options, Rainbow uses column F of the report to show you the details of the formatting or other issues that it has found (as explained further in the detailed help). Where Rainbow has found a block of copied cells, it will show the details of the issues for the first cell in the block, but it may not be able to show exact details for all the remaining cells, though it will attempt to provide as much information as it usefully can."]
[]
[null,"By default (as with Risk Map), the Logic Analysis form will open with the Insert flags option selected, and with Output options to colour cells and generate a report. If you wish to change these defaults, simply run an analysis with your preferred options selected, and then click the Preferences icon (on the Rainbow tab) and click the Save Defaults button."]
[]
[null,"Logic Analysis - Cells"]
[]
[null,"• 1)  Constant numbers are cells which contain an input number (i.e. not a formula). This includes input numbers formatted as text (e.g. '1234), but does not include dates."]
[]
[null,"• 2)  Calculated numbers are cells containing a formula which calculates a number. This does not include dates, even though dates are stored as numbers."]
[]
[null,"• 3)  Constant date values are cells which contain an input date value (i.e. not a formula). Note that Excel treats time values as numbers rather than dates."]
[]
[null,"• 4)  Calculated date values are cells containing a formula which calculates a date value. Note that Excel treats time values as numbers rather than dates."]
[]
[null,"• 5)  Constant logical values are cells which contain an input logical value (normally TRUE or FALSE)."]
[]
[null,"• 6)  Calculated logical values are cells containing a formula which calculates a logical value, e.g. =(WEEKDAY(TODAY())>1)"]
[]
[null,"Logic Analysis - Data"]
[]
[null,"• 1)  Constant text values are cells containing text rather than a number, though this includes numbers formatted as text (e.g. '1234)."]
[]
[null,"• 2)  Calculated text values are cells containing a formula which calculates a text value, e.g. =CHOOSE(MOD(NOW(),7)+1,\"Sat\",\"Sun\",\"Mon\",\"Tue\", \"Wed\",\"Thu\",\"Fri\")"]
[]
[null,"• 3)  Defined scenarios are sets of input values that have been defined using Scenario Manager (under What-If Analysis on Excel's Data tab) and are saved with the workbook. The report shows in column C the location of these input values, and in column F the name and other details about each scenario. The value of each cell is shown in column G."]
[]
[null,"• 4)  Formulas without cell references are formulas which do not reference any other cells, e.g. =NOW() or =2597/12"]
[]
[null,"• 5)  Unlocked cells are any cells where the protection status of the cell has been changed from locked (the default) to unlocked, meaning that when the worksheet is protected these cells can still be changed. If these are the input cells, you can highlight them here and then use option 3 in the Format Editor to change the colour, if you want them displayed in a different colour on your spreadsheet. "]
[]
[null,"• 6)  Hidden-formula cells are any cells where the protection status of the cell has been set to Hidden, meaning that when the worksheet is protected, any formulas in these cells will not be visible."]
[]
[null,"Logic Analysis - Dependents"]
[]
[null,"• 1)  Blank input cells are cells which are empty but which are used in one or more calculations. There may be no problem if they are part of a larger block of cells, but individual blank input cells may indicate that an input value is missing or a formula elsewhere has been mis-typed. As with the Logic Map, this option will only detect blank input cells within the used area of the worksheet."]
[]
[null,"• 2)  Non-blank input cells are a normal part of the calculation logic, as also are the following three dependency types. They are not significant on their own, but together they can provide a useful \"map\" of the worksheet. Note that (depending on your settings on the Logic Analysis tab in Preferences) Rainbow may skip processing dependents for some cells where it is taking too long to do so. Where this happens, Rainbow will report the details in the Rainbow Log file (click User Guide, then Rainbow Log)."]
[]
[null,"• 3)  Intermediate workings are calculations which provide the inputs to one or more further calculations."]
[]
[null,"• 4)  Circular references are intermediate workings that are part of a chain of calculations that refers back to itself. As with the Logic Map, this option will detect circular references where the all the cells involved in the circular chain of calculations are in the same worksheet. However, if the calculation chain goes outside the current worksheet, it will only highlight the circular reference cell shown in the Excel status bar (provided that this is an intermediate working)."]
[]
[null,"• 5)  Output calculations are calculations which have no further dependent calculations."]
[]
[null,"• 6)  Labels are cells which are not empty, but which are not used in any calculations. Usually they are text cells serving as headings (labels) for a row or a column."]
[]
[null,"Logic Analysis - Precedents"]
[]
[null,"• 1)  Inconsistent precedent copies are cells which are copied from another cell above or to the left, but where the cell type of one or more of the cell's precedents is different from the cell type of the corresponding precedent of the original cell from which it is copied (which is shown in column G of the report). By default (depending on your Preferences settings) this option also shows precedent ranges which vary in size (combining absolute and relative references), but it ignores differences in number format. This may seem complicated, but it provides a level of deep analysis which can indicate problems such as where a precedent should have been (and has not been) specified as an absolute reference, or where a formula has been copied beyond the end of the relevant source values. Following are two example entries in column F of the report for this option:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C96] C55 Date → Formula, B61:C61 1-cell → 2-cells"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"If column G of the report shows \"B96\", this entry in column F indicates that cell C96 is copied from B96, and that (a) cell B96 refers to B55, which is a date, whereas in cell C96 the corresponding precedent C55 is a formula, and (b) cell B96 refers to $B61:B61 (1 cell), which in cell C96 has become $B61:C61 (2 cells)."]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[H39] Changed internal precedents"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"You may occasionally see this entry in the report, meaning that the formula in cell H39 has some complexity which has caused Excel to process its precedents in a different order from the cell from which it is copied. This does not mean that there is an error, but it suggests that the formula is sufficiently complex to deserve careful checking."]
[]
[null,"• 2)  Precedent block inconsistencies refers to cells with a block of precedents that includes more than one type of cell within the block. By default (depending on your Preferences settings) this option also shows where there are row or column differences within the block, but it ignores differences in number format. Precedent block inconsistencies are not necessarily a problem, but they may indicate that the precedent block has included too many cells, e.g. that a SUM() calculation has included a subtotal as well as the basic data. Following is an example report item (column F):"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C87] {C51:C86} C55 Value → Blank, C63 Column Difference"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This indicates that cell C87 refers to a block of precedents C51:C86, and that within this block (a) cell C55 is blank, whereas the preceding cell C54 is a numeric value, and (b) cell C63 contains a different formula from the preceding cell C62."]
[]
[null,"• 3)  Precedents only part of block is in some ways the reverse of the preceding option; it shows cells with a block of precedents which forms part of a larger block of cells of the same type. This can  help you spot one of the most classic spreadsheet errors: a SUM() calculation that has failed to include all the relevant basic data cells. In order to show the most relevant items, Rainbow only looks for additional rows or columns where the block already has more than one row or column and extends beyond the row or column of the dependent cell, and it highlights in yellow where the additional row or column is a direct copy from the current block. Here is an example report item:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C134] {C97:M99} ↑C96:M96, ↓C100:M100, ≡→N97:N99"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This item will be highlighted in yellow unless you have turned off highlighting (in your Preferences settings on the Options tab). It indicates that cell C134 refers to the precedent block C97:M99, and that the cells above (C96:M96) and below (C100:M100) this block are all of the same type as in the block itself. In addition, the cells to the right (N97:N99) of the block all contain formulas that are a direct copy (as shown by the ≡ symbol) of the formulas on the  right-hand edge (M97:M99) of the block."]
[]
[null,"• 4)  Precedents referring forwards means cells which have precedents (in the same sheet) either on a row underneath or in a later column on the same row. This is not a problem for a modern spreadsheet program like Microsoft Excel, but it can indicate where the flow of the calculation logic is particularly complex and may need additional checking. Below is an example report item:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[D53] →F53:AS53, ↓F125"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This shows that cell D53 refers to cells F53:AS53 in the same row to the right and that it also refers to cell F125 in a later row."]
[]
[null,"• 5)  Precedents of different cell type shows cells which have precedents of a different cell type from the cell itself, for instance numeric cells which have a text cell as a precedent. This is not necessarily incorrect (cp. =LEN(A1) with A1 as text), but sometimes it can indicate a potential problem, for instance where a SUM() calculation includes text cells. Note that if you are doing this analysis together with Precedent block inconsistencies (which similarly scans blocks of precedents), you can speed up processing by selecting (in your Preferences settings on the Logic Analysis tab) to check only the corners of blocks (e.g. A1, A4, D1, D4 in block A1:D4). An example report item might be:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C104] Formula ← B41:B43 Blank, C66 Logical, C8 Text"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This indicates that cell C104 is a numeric formula, and it has blank precedents in cells B41:B43, a logical (TRUE or FALSE) precedent in C66, and a text string precedent in C8."]
[]
[null,"• 6)  Cells with blank precedents is in some ways the inverse of Blank input cells under the Dependents tab; it shows the dependents of blank input cells. However, it is more comprehensive than the Blank input cells option, because (unlike the latter) it will report on dependents of blank input cells even if the input cells are outside the used area of the worksheet (e.g. in row 1,048,576, or column XFD). Instances of blank input cells outside the used area are more likely to be errors, and so are highlighted in yellow.  An example of this is the following report item:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"[C71] V13:AN13; [E71] → AP13"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"This item (which will be highlighted in yellow unless you have turned off highlighting, in your Preferences settings on the Options tab) shows that cell C71 has a block of blank precedents in V13:AN13, and also that cell E71 (which is copied from C71) has a blank precedent in cell AP13 which is outside the used area of the worksheet (as indicated by the → symbol)."]
[]
[null,"Analysis Wheel"]
[]
[null,"The Analysis Wheel is a multi-purpose tool for accessing and managing any of Rainbow's 48 cell type analysis options. It lets you:"]
[null,"•  Scroll through all the analysis options, plus the thousands of Item number colours, or search for any of them by name."]
[null,"•  Quickly access the relevant Help information for each option."]
[null,"•  Run an analysis on the current sheet, or add other sheets or options."]
[null,"•  Review each of the highlighted cells after an analysis has been completed."]
[null,"•  Choose which colours are assigned to which cell types."]
[]
[null,"When you click the Analysis Wheel button, you will see up to nine of Rainbow's analysis options presented as a segment of a wheel. The central option is the one currently selected (by default, Multi-digit hard-coded numbers), and you can click the Analyse button to go to the Risk Map or Logic Analysis function with the relevant option pre-selected, so that you can then just click OK (or press Enter) to run an analysis."]
[]
[null,"When you have run an analysis, click the Analysis Wheel button again and it will open at the option you last selected. So you can then simply click the Review button to browse through each cell of the selected type that Rainbow's analysis detected and highlighted in the current sheet. And if you want to make the current cell type the default option, just click the Preferences icon (on the Rainbow tab) and click the Save Defaults button."]
[]
[null,"You can select a different analysis option by clicking any of the four buttons above or below the central option. You can also navigate around the 48 different options more quickly by using your mouse scroll wheel or the scroll bar on the left, or you can search for a specific option in the search box at the bottom."]
[]
[null,"Your mouse scroll wheel and the scroll bar, as well as the search box, will also allow you to access the numbered colours that Rainbow uses for highlighting items such as formula groups, so you can then click the Review button to find highlighted cells in the current sheet. To access these Item number buttons, simply scroll down (with your mouse scroll wheel or the scroll bar) to Item 1, after the 48 named analysis options,  then continue scrolling on down. Or in the search box type the item name, e.g. \"Item 14745\", and press Enter or click the search button."]
[]
[null,"The top button on the right will normally be labelled Export, and you can click this to export a Cell Colour Sheet showing how the first 48 colours in Rainbow's palette are currently assigned to the 48 analysis options. At the top right of this sheet is a Help button which will explain how to modify the colour mapping (by changing the indices in column H). And if you click the Analysis Wheel button when the current sheet is a Cell Colour Sheet, the top button on the right will now be labelled Import, allowing you to click it and re-import your modified colour mapping from the Cell Colour Sheet."]
[]
[null,"Under the Export/Import button are three Help buttons, the first of which simply displays this Help information. The second (Item) Help button displays an explanation of the currently selected item, which also indicates where to find it within the Risk Map or Logic Analysis functions. And the third (Colour) Help button gives you an overview of the Colour Chart and Rainbow's palette of colours."]
[]
[null,"Import Colours"]
[]
[null,"In the Analysis Wheel dialog box, the top button on the right will normally be labelled Export, and you can click this to export the Cell Colour Sheet showing how the first 48 colours in Rainbow's palette are currently assigned to the 48 analysis options. Although you cannot change the colours themselves, you can change the order in which they are mapped to the 48 analysis options. The current order is shown in columns G and H, and (unless it has already been changed) you will see that the first 6 colours are assigned to the 6 dependency types (as used in the Logic Map), even though these are near the end of the Cell Colour Sheet (in rows 42-47)."]
[]
[null,"To change the order in which the colours are assigned, all you need to do is to change the order of the index numbers in column H (highlighted in yellow as an input area). So for instance if you want blank input cells coloured yellow and non-blank inputs coloured red, simply swap the values 3 and 5 in H42 and H43. If you launch the Analysis Wheel again, you will see that the top button on the right is now labelled Import, and you can simply click this button to load your new colour mapping into Rainbow."]
[]
[null,"The only restriction on your new colour mapping is that in column H it must include all and only the numbers from 1 to 48; you cannot omit or repeat any numbers, or use numbers beyond 48. The Import function on the Analysis Wheel will check this and alert you if any colour numbers are missing. If all 48 colours are present, it will calculate how many you have edited, and ask if you are ready to load your changes into Rainbow."]
[]
[null,"Save Format"]
[]
[null,"Before you change the formatting of a spreadsheet with the functions described in other sections of this User Guide, you can use the Save Format function to set a \"format restore point\", in other words to take a snapshot of the formatting for selected worksheets, so that you can later undo the changes made by Rainbow. You can then use the Undo Changes function to restore the relevant formatting (e.g. cell colours) back to the way it was the last time you set a format restore point."]
[]
[null,"Rainbow only holds one format restore point at a time, so it will restore only the last set of formatting information that you recorded. This means that if you set a restore point before you use e.g. the Logic Map function, Rainbow will be able to undo any formatting changes made by this function, but it will not undo any previous changes. Any saved formatting information is effectively lost when you close Microsoft Excel."]
[]
[null,"If some formatting information has already been saved, this will be overwritten when you set a new restore point; Rainbow will tell you what workbook and sheet(s) have been previously saved, and will ask you to confirm before overwriting this information."]
[]
[null,"Undo Changes"]
[]
[null,"The Undo Changes function allows you to restore cell colours and other formatting that has been changed by Rainbow's other functions such as Formula Scan. You can restore this formatting in two different ways."]
[]
[null,"The first way is available if you have already saved information about the formatting by using the Save Format function to set a \"format restore point\". So for example if you set a format restore point with Save Format immediately after running the Quick Clean-Up function, and you then use Risk Map to highlight various cells, you can click Undo Changes to restore the \"clean\" formatting as it was after the Quick Clean-Up. You can restore all of the saved formatting, or you can choose to restore just the cell colours or other elements."]
[]
[null,"Even if no format restore point has been set (with Save Format), Undo Changes still gives you a second way of restoring formatting. This works by detecting and clearing any cell colours that have been generated by Rainbow; in this way it can at least partly undo Rainbow's formatting changes, though it will not be able to restore any previous cell colouring that has been overwritten by the Rainbow-generated colours. You can also choose whether to delete the flags (diagonal lines) and custom comments created by Rainbow and used by the Issue Tracker function."]
[]
[null,"In case you are not sure what information you recorded the last time you used Save Format to set a format restore point, Rainbow will alert you if it has no formatting information saved for some or all of the worksheets you have selected to restore, or if it has formatting information saved for worksheets in a different workbook."]
[]
[null,"Rainbow keeps track of your worksheets by name, so you can still Undo Changes if you have moved a worksheet to a different position in the workbook, but not if you have renamed the worksheet. And it is important to remember that if you have inserted or deleted rows or columns, Rainbow will restore the formatting incorrectly as if nothing had changed."]
[]
[null,"File Save As"]
[]
[null,"After you have cleaned up a workbook (using the Clean-Up Tools) or analysed it with some of Rainbow's other functions, you can use the File Save As function to save it under a different name and leave the original file unchanged. If you have saved any formatting information with the Save Format function, Rainbow will update the saved information to link it to the new workbook name."]
[]
[null,"You can also use the File Save As function to save the current report workbook under a different name, and Rainbow will then continue to use this for further reports."]
[]
[null,"Quick Clean-Up"]
[]
[null,"The Quick Clean-Up function provides a quick and simple way to prepare a workbook for detailed analysis using Rainbow's other functions. It combines various operations from the Security Manager and Format Editor functions to unhide hidden sheets, unprotect protected sheets, unhide hidden rows and columns, and optionally clear cell colours and patterns. It also selects Normal view (rather than e.g. Page Break Preview) for all sheets."]
[]
[null,"If you would like the Quick Clean-Up function to clear cell colours and patterns (in addition to its other operations), you can set this in your Preferences settings (on the Colours tab), by checking the box under Quick Clean-Up. And after you have \"cleaned up\" a workbook with the Quick Clean-Up function, if you wish you can use the File Save As function to save it under a different name and leave the original file unchanged."]
[]
[null,"Clean Up Formats"]
[]
[null,"The Clean Up Formats function removes all custom (i.e. not built-in) styles, and it also attempts to reset the built-in styles to their default values. This can be particularly useful if you receive a \"Too many different cell formats\" error message. This often occurs as a result of copying  or importing worksheets from one workbook into another, because by default Excel also imports all the custom styles from the source workbook."]
[]
[null,"You can run the Summary Report to see how many styles there are in the workbook (in cell B18). Generally there are about 50 basic styles, but when worksheets have been copied or imported many times, the number of styles can increase up to a maximum of 64,000. This can double the size of a workbook and make it very slow to load."]
[]
[null,"Another occasional problem with formatting is that the default \"Normal\" style can become corrupted, in which case your spreadsheet may appear to be full of (for example) dates or currency values. Rainbow will check for this, and if there appears to be a problem it will offer to reset the Normal style back to the usual (General) format."]
[]
[null,"In most cases it is quite safe to delete all the custom styles and reset the built-in styles; this will not change the underlying data or formulas, and generally it will not even affect the visible formatting. However, if your spreadsheet is particularly sensitive to formatting changes, you should use the File Save As function to save the cleaned-up workbook under a new name, and check it against the original."]
[]
[null,"Note that Excel's internal list of custom styles can sometimes become corrupted. When this happens, the Clean Up Formats function will be unable to delete the custom styles, and on the status bar you will be able to see the number of styles that could not be deleted. In this situation you will need to use Rainbow's Deep Clean function, where the \"Reset custom formats\" option can remove all custom styles even when these have become corrupted."]
[]
[null,"Trim Unused Space"]
[]
[null,"Excel automatically keeps track of what it considers to be the \"used range\" of each worksheet, i.e. the range of cells containing all the data and calculations in the sheet. Generally this system works quite well, but just occasionally Excel's internally stored \"used range\" may extend thousands of rows or columns beyond the edge of the real used range. This can make the workbook unnecessarily large and slow to load."]
[]
[null,"The Trim Unused Space function finds the real used range for each worksheet (i.e. the range containing all non-empty cells) and deletes all rows and columns beyond the edge of this, which forces Excel to align its internally stored \"used range\" with the real used range. In most cases this should not affect the operation of the spreadsheet; however, if there are blank inputs outside the real used range, this can result in #REF! errors. In addition, any coloured (but empty) cells outside the real used range may be deleted."]
[]
[null,"There may also be charts and other graphical objects outside the real used range, i.e. beyond the area containing all non-empty cells. The Trim Unused Space function now incorporates an additional check to make sure that any such charts and graphics are preserved and not deleted. This additional check may make the function slightly slower, and if necessary it can be disabled by unchecking the \"Preserve charts and graphics\" box."]
[]
[null,"If you have any concerns about whether the Trim Unused Space function may have unwanted consequences for your workbook, you should use the File Save As function to save the cleaned-up workbook under a new name, and compare it against the original. You can use the Logic Analysis function to check for any cells with blank precedents (blank inputs) outside the used range."]
[]
[null,"Tidy Up Reports"]
[]
[null,"Standard Rainbow reports are in the form of an Excel worksheet with a Help button at the top right (which you can click to see the relevant part of the User Guide). Some more complex reports have additional buttons, sometimes with comments explaining what they do. All the buttons are able to run parts of the Rainbow program code by means of a Visual Basic link (\"reference\") to Rainbow which is set up in the workbook containing the Rainbow report(s)."]
[]
[null,"This system generally works very well, but it can occasionally cause problems if you send a Rainbow report to a colleague who does not have Rainbow installed, in which case clicking a button can generate an error message. So the Tidy Up Reports function enables you to remove the Visual Basic link to Rainbow, and optionally also the buttons, macro code and comments, so that the report becomes just a plain ordinary spreadsheet."]
[]
[null,"There can also be a problem if you have more than one version of Rainbow installed, in which case the report will link to the version of Rainbow that created it, and you may get two versions of Rainbow trying to run simultaneously. In this situation the Tidy Up Reports function lets you remove the link to the old version of Rainbow (without deleting the buttons, macro code and comments) and create a new link to your current Rainbow version."]
[]
[null,"When you run the Tidy Up Reports function, it will first check whether a Visual Basic link to Rainbow exists in your selected workbook. If there is an existing link then it will offer to remove it, and otherwise it will offer to create a new link. Note that if you remove a link and then immediately try to create a new link, you may get an error message stating \"Cannot run the macro 'TidyReports'.\" You can resolve this simply by closing and re-opening the workbook, or by switching to a different workbook and then re-running Tidy Up Reports and selecting the original report workbook from within the Tidy Up Reports dialogue box."]
[]
[null,"Deep Clean"]
[]
[null,"This function works on a different level from most Rainbow functions. When you open an Excel workbook, Excel reads the saved workbook file (e.g. on your computer's hard disk) and loads it into the computer's working memory in a form that is suitable for display on your screen as a familiar Excel spreadsheet. And nearly all Rainbow functions (all except Deep Clean, Deep Compare, and the Password Tool) operate on the spreadsheet as loaded into your computer's working memory and displayed on screen. Thus if you use Rainbow to make changes to a spreadsheet (e.g. with Quick Clean-Up), and close the spreadsheet without saving it, then the workbook file on disk remains unchanged."]
[]
[null,"The Deep Clean function, on the other hand, operates directly on the saved workbook file. It can do this because workbook files from Excel 2007 onwards (with filename suffixes like .xlsx) are saved in a defined format known as Open XML. Files in this format are created by a two-stage process: (1) all the data that makes up the workbook is saved in a set of several files and folders (e.g. with one specific file just for data about styles), in a format known as XML (eXtensible Markup Language); (2) this set of files and folders is then compressed (or \"zipped\", using the ZIP method of compression) into a single workbook file with the .xlsx (or similar) suffix."]
[]
[null,"And so Deep Clean is able to modify the saved workbook file by carefully editing one or more of the underlying XML files. This involves several steps: (1) the workbook file is copied to a temporary folder (the same folder where the Rainbow Log file is stored), and renamed as a ZIP file; (2) this file is \"unzipped\" into its underlying set of XML files and folders; (3) Deep Clean analyses and (if necessary) edits one or more of these XML files; (4) the (edited) set of files and folders is re-compressed back into a ZIP file; (5) this file is given a slightly different name (with \"+\" at the end, e.g. \"filename+\", to avoid overwriting the original file), and then copied back to the original folder as a workbook file. You can open this modified workbook file by selecting it from Excel's list of recent files."]
[]
[null,"This multi-stage process is reflected in the six options that are currently available in the Deep Clean dialogue box. The first and last options (to unzip and re-zip the workbook file) are self-explanatory, and the others are explained below. You can select any or all of these options, and Rainbow will make sure that your selections make sense; for instance if you select to re-zip the file, Rainbow will automatically also select the option to unzip it. The only exception to this is when you have already unzipped the file and have not saved any further changes; in this situation Rainbow will use the existing unzipped file and will leave the first (unzip) option deselected. And if you want to examine the unzipped file for yourself, the Temp Files function opens a File Explorer window on the RainbowAnalyst program folder where you can see both the ZIP file and the underlying XML files (and also the Rainbow Log file)."]
[]
[null,"The \"Reset custom formats\" option has a similar purpose to the Clean Up Formats function; in other words it removes all custom (i.e. not built-in) styles. And because it operates by editing the underlying styles.xml file, it can deal with workbooks where the styles/formats data has become corrupted (and so cannot be cleaned with Clean Up Formats), as well as with the common \"Too many different cell formats\" error message. At the end it will let you know how many bytes of data it has cleared out of the styles.xml file, and because it clears out the excess styles/formats data in a single pass, rather than one format at a time, you may find that sometimes it is much quicker than Clean Up Formats."]
[]
[null,"The options to \"Report all external links\" and \"Report link references\" do not modify the workbook file in any way; instead, they generate a pair of reports which are similar to the report from the Data Links function, but can provide much more information. The External Links report shows three kinds of links to external spreadsheets: (1) \"Data Links\", which are the defined links to external spreadsheets as shown in Rainbow's Data Links report and in Excel's own Edit Links function (on the Data tab); (2) \"Phantom Links\", which are defined links that should have been shown in Excel's Edit Links function, but which have been incorrectly omitted, often because they only appear in a defined name and not in any cell formulas; (3) other links to an external spreadsheet which are not defined links as shown in the Edit Links function, for instance clickable hyperlinks or pivot table data."]
[]
[null,"The \"Data Links\" and \"Phantom Links\" are shown in the External Links report as numbered external links, e.g. \"ExternalLink1\", and each link is highlighted according to the link number using Rainbow's Item number colours (for more information see the Colour Chart section of the User Guide). The Link References report then lists all the places where each numbered link is used, including defined names, charts, drawings and sparklines, as well as cell formulas. Column B of the report shows a snippet from the relevant XML file, which can initially seem confusing but is actually not difficult to understand. For instance where an external link is used in a cell formula, columns A to D of the Link References report might show something like this:"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"Calcs (sheet1)    |    <c r=\"B11\"><f>ROUND(B2/(    |    ExternalLink4    |    Sheet1!$D$8),3)"]
[null,"───────────────────────────────────────────────────────────────────"]
[null,"If the External Links report shows that ExternalLink4 is a link to C:\\Users\\Anon\\Documents\\Test Model.xlsx, then the above excerpt from the Link References report indicates that the formula in cell (range) B11 of worksheet Calcs is \"=ROUND(B2/('C:\\Users\\Anon\\Documents\\[Test Model.xlsx]Sheet1'!$D$8),3)\"."]
[]
[null,"As noted above, the problem of phantom links is often caused by links which are no longer actively used, but which are still present in one or more of the defined names in a workbook. The \"Delete names with phantom links\" option offers a simple way to address this problem, by deleting any defined names which contain phantom links. Rainbow does not attempt to check whether these names (or the phantom links) are used in any other way in the workbook, so after running this option you should confirm that the modified workbook has not lost any functionality when compared to the original. To help you with this task, the \"Delete names with phantom links\" option generates a pair of reports listing Phantom Links and Deleted Names."]
[]
[null,"Deep Compare"]
[]
[null,"The Deep Compare function allows you to compare the structure of two workbooks and see where there are differences in, for instance, the external links or the drawing objects, which might not be picked up by the Comparison Toolbox function. This is because the Comparison Toolbox function only looks at cell formulas and values (and macros) as displayed on your screen, whereas Deep Compare (like Deep Clean) examines the saved workbook files (e.g. on your computer's hard disk). You can read more about this in the Help information for Deep Clean."]
[]
[null,"Deep Compare first checks that the selected workbooks have been saved in Open XML format (e.g. with filename suffixes like .xlsx). It then copies the workbook files into the RainbowAnalyst program folder (which you can access with the Temp Files function), and \"unzips\" them into their underlying XML files and folders. This then enables it to examine each XML (or other) file in turn and compare it with the corresponding file for the other workbook, or detect if the other workbook has no corresponding file."]
[]
[null,"If there is a corresponding file, Deep Compare then compares both files byte by byte and reports whether it has found any differences between them. The report lists the size (in bytes) of each file, and within each file the first and last location at which a difference has been found. In other words the two files are identical up to the \"First Diff\" location, and they are also identical after the \"Last Diff\" location, which is shown as negative because it is measured by counting backwards from the end of the file."]
[]
[null,"The final column of the report is a bar chart which shows the differences between the files in graphical form. The full width of the cell represents the size (in bytes) of whichever file is larger, and within this the smaller file is shown as a bar coloured green and/or red. If the Workbook 1 file is smaller, it will be shown on the left of the cell, and if the Workbook 2 file is smaller it will be shown on the right. The green represents the areas of the file which are identical, i.e. the areas before the first difference and after the last difference. The red represents the area containing all the differences between the files, though of course there may also be some sections within the red area where the files are identical."]
[]
[null,"When you look at a Deep Compare report, the most helpful approach is to see which are the files where there appear to be significant differences (i.e. large red areas). Some of these files may be unfamiliar, for instance the calcChain.xml file holds details of cell dependencies and calculation order, so this will reflect any changes in the workbook logic. The text (\"strings\") in the worksheets are all stored in the sharedStrings.xml file (rather than in individual worksheet files), so any change in descriptions or titles will be reflected here. And if you notice that one of the individual worksheet files shows a large amount of red, whereas the others are green, then you will know immediately where to find most of the differences between the two workbooks."]
[]
[null,"Temp Files"]
[]
[null,"Both the Rainbow Log file (RainbowLog.txt) and any unzipped files created by Deep Clean or Deep Compare are stored in a \"hidden\" folder for program files. This will be at C:\\ProgramData\\RainbowAnalyst if you have permission to write to this folder; otherwise it will be at C:\\Users\\[Your user name]\\AppData\\Local\\Temp\\RainbowAnalyst. The Temp Files function simply opens a File Explorer window on this folder so that you can access the relevant files."]
[]
[null,"Alternatively of course you can navigate to the folder manually in File Explorer, but you may find that you cannot see the C:\\ProgramData folder or the AppData folder under your user name folder. If this is so, then you need to enable Windows to show hidden files and folders; instructions on how to do this can be found by searching online for \"windows show hidden files\"."]
[]
[null,"Comparison Toolbox"]
[]
[null,"The Comparison Toolbox function can work in several different ways, depending partly on what you select in the Comparison Toolbox dialog box, and partly on what range(s) you have selected before you click Comparison Toolbox:"]
[null,"•  It can compare one sheet in a workbook with any or all of the other sheets in the same workbook."]
[null,"•  Provided you have more than one workbook open, it can compare corresponding sheets (i.e. sheets with the same name) in two different workbooks."]
[null,"•  Again if you have more than one workbook open, it can compare all the modules and macros (Visual Basic code) in two different workbooks."]
[null,"•  If you select the appropriate range(s) before clicking Comparison Toolbox (see below for more detail on this), it can compare a particular multi-cell range (rather than the whole worksheet) with corresponding ranges in other sheets or another workbook."]
[null,"•  If you first use the Ctrl key (with the mouse) to select multiple areas (e.g. tables) in a single worksheet, it can compare the first area in the worksheet with all the others."]
[]
[null,"As with other Rainbow functions, when you first click Comparison Toolbox, Rainbow will try to guess what workbook(s) and worksheet(s) you wish to compare. If you find it has guessed wrongly, simply click the Selection… button to change the selection. This will open a dialog box with two selection areas, for the workbook and worksheet(s) you want to compare (on the left) and what you want to compare them with (on the right). If you have chosen to highlight (Colour or Map) differences, Rainbow will highlight them in the worksheet(s) you select in the right hand selection area. You can also choose for Rainbow to add a \"Query\" flag (red diagonal) to the first in each group of highlighted cells, which you can then track with the Issue Tracker."]
[]
[null,"The title bar at the top of this selection dialog box will confirm what type of comparison is currently selected:"]
[null,"•  If you select two different workbooks, the title bar will show \"Comparing Workbooks\", and you will be able to select any or all worksheets in the left-hand area only, for comparison with corresponding sheets in the right-hand workbook."]
[null,"•  If you select the same workbook on both sides, the title bar will show \"Comparing Worksheets\", and you will then be able to select only one worksheet in the left-hand area. A worksheet that you select in the left-hand area will be deselected in the right-hand area, and you will be able to select any or all of the other worksheets to compare with it."]
[null,"•  If you select the same workbook on both sides, and you select the same one sheet in the right-hand area as you have selected on the left, then if this sheet has multiple areas selected, the title bar will show \"Comparing Areas in Sheet\"."]
[]
[null,"Once you have selected the workbook(s) and worksheet(s) you want in the selection dialog box, click OK to return to the main Comparison Toolbox dialog box, where you can select other relevant options:"]
[null,"•  Cell comparison lets you choose whether to compare formulas (i.e. the contents of cells) or values (the input or calculated results), and also whether to compare all cells, or to ignore (if comparing formulas) cells containing an input value, or (if comparing values) cells containing a calculated formula."]
[null,"•  If you are comparing two different workbooks, Macro comparison lets you choose whether to compare the macros (VB code) in the workbooks as well as comparing the worksheets (cells). And you can deselect the cell comparison if you only wish to compare the VB code."]
[null,"•  If you have selected to compare formulas, Formula grouping can group together in the differences report all differences which result from copying the cells in both worksheets (rather like the Formula Scan function). This means that the report is much smaller, and lets you see immediately how many real changes have been made."]
[null,"•  The Ranges to compare option only applies if you have selected a multi-cell range to compare; it then allows you to compare either the same range across all sheets or different ranges in different sheets (see below for more details)."]
[]
[null,"The Comparison Toolbox dialog box always appears with the same options initially selected. If you find that you regularly use a different set of options, you can save these as the default (initial) selection by clicking Save Defaults."]
[]
[null,"If you choose (under Ranges to compare) to compare different ranges in different sheets, the way the comparison then operates is dependent on whether you are comparing workbooks or worksheets:"]
[null,"•  If you are comparing two workbooks, then for any sheets in the source workbook which have a multi-cell range selected, Rainbow will compare the selected range with the same range in the corresponding sheet in the other workbook, and for any sheets which have just one cell selected, it will compare the same range as in the first selected worksheet."]
[null,"•  If you are comparing worksheets, Rainbow will compare the selected range in the source worksheet with a range of the same dimensions (rows x columns) in all other selected worksheets; for worksheets which have a multi-cell range selected, it will start the comparison at the top left corner of the selected range, and for any sheets which have just one cell selected, it will compare the same range as in the source worksheet."]
[null,"•  However, if you are comparing multiple ranges (areas) within a single worksheet, you don't need to select the whole of each area. Rainbow will compare the first selected range with other ranges of the same dimensions, starting the other range(s) at the top left corner of each other range (whether single-cell or multi-cell) selected on the worksheet."]
[]
[null,"If you choose (under Comparison types) to compare formulas only (ignoring input values), the comparison will ignore changes in constants (simple numbers and text) and only report differences where one or both cells contain a formula. However, it is important to appreciate that there may be constants which are not inputs (simply labels or memo cells), and inputs or hard-coded numbers (e.g. =365/4 or =B7*1.175) which are formulas, not constants."]
[]
[null,"A common reason for differences between two versions of a workbook is that rows or columns may have been inserted or deleted in the later version. Rainbow will normally attempt to adjust for this by detecting how many rows or columns have been inserted or deleted and inserting rows or columns temporarily to bring the two workbooks into line with each other. It leaves the inserted items in place while doing the comparison, and then automatically removes them at the end to restore both workbooks to their original state. (Note however that automatic insertion is disabled when comparing multiple areas in a single sheet, to avoid unexpected interactions.)"]
[]
[null,"This feature can often reduce the number of reported differences by an order of magnitude. However, Rainbow will not always guess correctly where to insert rows or columns, and so you can set your Preferences (on the Parameters tab) to disable this feature if you wish, and you can also choose an option not to remove the automatically inserted rows or columns at the end of the comparison."]
[]
[null,"This last option may be useful it there are insertions or deletions in several sheets which depend on each other, in which case you may need to run the comparison, leave the adjustments in place, and then run it again. Generally however, where there are insertions or deletions in one sheet (e.g. Inputs) on which other sheets depend, all you need do is make sure this is the source sheet when you run the comparison, in order to ensure that the adjustments are made in this sheet before the dependent sheets are compared."]
[]
[null,"Where Rainbow finds differences, it will report the formulas in each cell as they appear after any adjustments that it has already made. Clearly this can mean that the formulas may refer to different rows from those which they referenced before the adjustments were made. However, Rainbow always shows the original address (before adjustments) of each cell in the columns headed \"Cell 1\" and \"Cell 2\"."]
[]
[null,"Sometimes Rainbow will be unable to detect an insertion or deletion, even though it may seem obvious to you, or conversely Rainbow may detect an insertion or deletion where in fact there is some other reason for a difference between cells. In this case, you could try changing your Preferences (on the Parameters tab) to adjust the minimum number of non-blank cells that Rainbow regards as adequate evidence for deciding if there has been an insertion or deletion. Or you could manually insert blank rows or columns to bring the two workbooks into line with each other."]
[]
[null,"If you have selected to compare macros, Rainbow will generate a separate report listing all non-blank modules in both workbooks, and all macros (procedures) in each module, and any differences within each macro. And in the same way as when it is comparing cells, Rainbow will attempt to detect when lines have been inserted in a macro, using the same algorithm as for row and column insertions."]
[]
[null,"Comments Report"]
[]
[null,"The comments report lists any or all of three types of comment, which are colour-coded in column A: (1) issues (and flags), (2) cell notes, and (3) threaded comments. For each comment, the report shows the cell address and value, plus additional information as described below."]
[null,"•  For issues, unless you have selected not to include flags, the report lists both the current flag and the messages (if there are any), showing the message number and the date, author and text of each message. In column C it shows the current flag and a number from 1 to 3  (1 for Resolved, 2 for Query, and 3 for Error), so that you can filter this column to show any particular flag. Similarly you can filter or sort the Priority level (1 to 9) in column D."]
[null,"•  For cell notes (which were previously called comments), the report lists the author and the text of the comment. If there happens to be a flag in the same cell, the report will show details of the flag on the same row of the report."]
[null,"•  For the newer threaded comments (which are similar to the messaging system for issues in Rainbow), the report lists the message number and the date, author and text of each message in the thread. If you are using an older version of Excel which does not natively support threaded comments, they will be displayed in a more basic format generated by Excel, prefaced by \"[Threaded comment]\". If the thread has been resolved, column C will show \"Resolved\". As with cell notes, if there happens to be a flag in the same cell as the comment, the report will show details of this on the same row."]
[]
[null,"Delete Comments"]
[]
[null,"With the Delete Comments function you can quickly clear from a workbook or worksheet(s) any or all of (1) the flagged issues created by Rainbow's Issue Tracker system, (2) cell notes (which were previously called comments), and (3) the newer threaded comments. This can be particularly useful after running the Formula Scan, Risk Map, or Logic Analysis functions."]
[]
[null,"Before you use Delete Comments to remove any existing comments, you might first wish to list them with the Comments Report function, which lets you report on both the standard Excel comments and Rainbow's own custom comments."]
[]
[null,"Identify Colours"]
[]
[null,"The Identify Colours function (which you can invoke by pressing ALT+F9) checks the colour(s) of the selected cell against Rainbow's defined cell colours (as displayed if you click the Analysis Wheel or the Colour Chart). It lists each colour, with the corresponding description, and you can click the Info... button for each colour to see the Analysis Wheel for that colour."]
[]
[null,"You can also search for further occurrences of the same colour(s) in the worksheet, either by blocks of similar cells or one cell at a time, and searching either forwards or backwards. If you select Loose Matching, Rainbow will search for any cells that have any of the colour(s) in the current cell, whereas with Tight Matching, Rainbow will search only for cells with exactly the same colour(s) as the current cell. If you continue to click, you can cycle through all occurrences in the current worksheet."]
[]
[null,"Like Excel's own Find function, the Identify Colours dialog box will let you click outside it on the underlying Excel worksheet. This means that if (for instance) you wish to find inter-sheet links to a specific sheet within your workbook after running the Inter-Sheet Logic report, you can click (in the report) on the colour that Rainbow has used for the relevant sheet, and then click Identify Colours to identify this colour. You can then click outside the Identify Colours dialog box and switch to the workbook, and simply click Next Block or Next Cell to find any relevant links."]
[]
[null,"Format Editor"]
[]
[null,"The first Format Editor option, to Convert numbers formatted as text, uses the same definition of \"numbers formatted as text\" as the corresponding option in the Risk Map function (on the Hidden tab), but instead of highlighting any relevant cells it replaces the textual values with the corresponding numbers. Like the Risk Map option, it can also (if selected in your Preferences settings on the Risk Map tab) deal with numbers preceded or followed by up to three or four letters, e.g. \"¥1000\" or \"568 ml\"."]
[]
[null,"The remaining options in the Format Editor dialog box work differently from other Rainbow functions; they allow you to apply certain formatting operations to all cells with the same colour as the cell which is active when you click the Format Editor button. Consequently you need to make sure you have selected an appropriate cell (i.e. one of the type that you wish to change) before you click Format Editor. You can then change the cell colour, font colour or locked status of all cells that match the one you have selected."]
[]
[null,"Although the cells in a typical spreadsheet are usually white, and the font is usually black, these colours often result from the cell colour being set to \"No Fill\" (showing the cell borders) and the font colour being set to \"Automatic\". You can apply these settings with the Format Editor if you select white as the new cell colour, or black as the new font colour. You will then be asked to confirm whether you wish to set (as appropriate) the cell colour to No Fill or the font colour to Automatic."]
[]
[null,"You can use the last four Format Editor options in conjunction with other Rainbow functions. For instance you can click Logic Analysis (on the Risk Map drop-down menu), and then select the Dependents tab and click Non-blank input cells to colour all (non-blank) input cells yellow. You can then select an input cell and click Format Editor to set all matching yellow cells as unlocked. You can now use Security Manager to protect the worksheet, and your input area is ready for use!"]
[]
[null,"When you are changing the formatting of cells in this way, Rainbow offers the options of Loose Matching or Tight Matching:"]
[null,"•  For new font colours, if you select Loose Matching, Rainbow will change any cells that have the same font colour as the current cell, whereas with Tight Matching, it will change only cells with the same font colour and exactly the same cell colour(s)."]
[null,"•  For new cell colours and for cell locking, if you select Loose Matching, Rainbow will change any cells that have any of the colour(s) in the current cell, whereas with Tight Matching, it will change only cells with exactly the same colour(s) as the current cell."]
[]
[null,"Colour Chart"]
[]
[null,"Rainbow now has a built-in Colour Generator capable of generating a set of 14,793 unique colours, in which adjacent colours are always visibly different from each other. Rainbow uses the first 48 of these colours to highlight specific types of cells (with the Analysis Wheel or the Risk Map and Logic Analysis functions), and these 48 colours and cell types are displayed when you click the Colour Chart button. The remaining 14,745 colours (named from \"Item 1\" to \"Item 14745\") are used to identify items such as formula groups and defined names, and you can see these colours (as well as the first 48) displayed in the Analysis Wheel."]
[]
[null,"So there is no longer any significant risk that Rainbow will run out of colours when (for instance) analysing grouped formulas; each group will have its own unique colour, allowing you to search for it using the Identify Colours function. But Rainbow's colours are also designed to be slightly different from most standard colours, and there is less than a 0.1% probability that a pre-existing cell colour in a spreadsheet will turn out to be the same as one of Rainbow's colours."]
[]
[null,"The Colour Chart function provides an interactive display of the 48 colours that Rainbow uses for all the various risk types and logic items. You can hover the mouse over any colour to see its exact RGB (Red, Green, Blue) values, and you can click on it to bring up the Analysis Wheel for that colour. This then lets you get help on the meaning of the specific cell type, find occurrences of the relevant colour in the current worksheet, and also re-order the cell type colours as explained below."]
[]
[null,"In the Analysis Wheel dialog box, the top button on the right will normally be labelled Export, and you can click this to export a Cell Colour Sheet showing how the first 48 colours in Rainbow's palette are currently assigned to the 48 analysis options. Although you cannot change the colours themselves, you can change the order in which they are mapped to the 48 analysis options. The Help button at the top right of the Cell Colour Sheet explains in detail how to do this by changing the indices in column H."]
[]
[null,"If you then launch the Analysis Wheel when the current sheet is a Cell Colour Sheet, the top button on the right of the Analysis Wheel dialog box will now be labelled Import, allowing you to click it and re-import a set of colour mapping options from the Cell Colour Sheet. This means that you can save a set of colour mapping options and then go back to them at any time later, simply by exporting and saving the relevant Cell Colour Sheet. To go back to the saved colour mapping options, simply open the saved Cell Colour Sheet workbook, launch the Analysis Wheel, and click Import."]
[]
[null,"Clear Cell Colours"]
[]
[null,"The Clear Cell Colours function has two options. The first option clears all cell colours and patterns (and by default any diagonal borders, which Rainbow uses to flag issues). It is very useful to clear pre-existing colours in a spreadsheet before running a Rainbow analysis, and it can also be used to clear a set of Rainbow colours and flags before running a different analysis."]
[]
[null,"The second option clears only those specific cell colours which have been  generated by Rainbow. This provides an alternative to the Undo Changes function, and in some cases it can fully undo Rainbow's changes (except for Rainbow's s comments). However, if Rainbow has overwritten an existing cell colour, this function will only clear the Rainbow colour, and it will not restore the original colour."]
[]
[null,"The set of colours used by Rainbow has been specifically designed to be very slightly different (with slightly different RGB values) from the colours which are generally available in Excel. However, there remains a small possibility that a workbook might use one of the Rainbow colours, which would then be cleared even with the second Clear Cell Colours option."]
[]
[null,"By default the Clear Cell Colours function clears all conditional formatting (even formatting which may not necessarily change the cell colour). This is because conditional formatting overrides the normal colour of the cell, and so can hide colours that Rainbow has generated."]
[]
[null,"In general it is sensible to run the Clear Cell Colours function when you first open a workbook for Rainbow analysis, in order to clear conditional formatting as well as normal cell colours. But after this, in order to clear colours, flags, and comments that Rainbow has added, it is best to use the Undo Changes function (even if you have not saved any formatting information)."]
[]
[null,"Normally (if the Optimise range box is ticked) this function will clear only the range which is actually used in any worksheet, rather than the whole of what Excel identifies as the \"used range\", which can be much bigger. However, if you find that there are cells (outside the optimised range) with colours which have not been cleared, you can simply untick the Optimise range box and re-run the Clear Cell Colours function."]
[]
[null,"Graphics Report"]
[]
[null,"The report on buttons and graphics lists all graphical objects (other than comments), with some additional detail about form controls (e.g. buttons) and chart titles. It also shows which objects (such as buttons) call a macro; these will correspond with the first element of the \"Macro buttons + objects\" column in the Summary Report, and you can use the Macro Explorer function to check what the called macro does."]
[]
[null,"Delete Graphics"]
[]
[null,"Graphical objects (sometimes called drawing objects) include charts and buttons as well as shapes and pictures. They are not usually a problem, but occasionally a worksheet can become corrupted with thousands of graphical objects, which make it difficult to navigate and edit. The Delete Graphics function simply goes through the worksheet(s) deleting all graphical objects (but omitting comments, which can be removed with the Delete Comments function)."]
[]
[null,"User Guide"]
[]
[null,"The User Guide function simply displays this User Guide as an Excel spreadsheet. You can then print, save or close it just like any normal spreadsheet."]
[]
[null,"Rainbow Log"]
[]
[null,"This function (which you can access by pressing F10) displays the contents of the Rainbow Log file (RainbowLog.txt), which is stored in a \"hidden\" folder for program files, either at C:\\ProgramData\\RainbowAnalyst or at C:\\Users\\[Your user name]\\AppData\\Local\\Temp\\RainbowAnalyst. The exact location of the file is stated at the beginning (on line 2) of the log file itself."]
[]
[null,"The log file holds details of sheets, rows and columns recently unhidden or unprotected (with the Quick Clean-Up or Security Manager functions), defined names converted or deleted, potential risks identified with Risk Map, text cells converted to numbers, rows where dependent checking has been skipped, and duplicate highlighting colours, as well as any occasions when Rainbow processing was interrupted."]
[]
[null,"The log file also stores relevant information in the unlikely event of a system error occurring. In this case, Rainbow will display a single-sheet workbook with the contents of the log file, and this workbook will also list instructions on how to email it very simply to Rainbow support so that the problem can be resolved."]
[]
[null,"To ensure that the log file does not become too large, Rainbow will alert you when the file size exceeds 20 KB and will offer to delete it and start a new file. If you wish to keep the existing file, you can go to the RainbowAnalyst folder and rename the file or copy it to another folder. As mentioned above, the RainbowAnalyst folder is a \"hidden\" folder located either at C:\\ProgramData\\RainbowAnalyst or under your user name folder at \\AppData\\Local\\Temp\\RainbowAnalyst."]
[]
[null,"The simplest way to view the RainbowAnalyst folder (and access the Rainbow Log file) is by using Rainbow's Temp Files function. Alternatively you can navigate to the folder manually in File Explorer, but you may find that you cannot see the C:\\ProgramData folder or the AppData folder under your user name folder. If this is so, then you need to enable Windows to show hidden files and folders; instructions on how to do this can be found by searching online for \"windows show hidden files\"."]
[]
[null,"About Rainbow"]
[]
[null,"This function displays copyright and licensing information, and allows you to enter the registration code if this is currently an unlicensed copy. If you have already licensed your copy of Rainbow, then the About Rainbow box displays information about your license and about how to access support. If the support period of your license is about to expire, and you have purchased a new license, click the \"Register a new license\" button to enter your license details."]
[]
[null,"Check For Updates"]
[]
[null,"The Check For Updates function provides a quick way to check whether an updated version of Rainbow Analyst is available. If there is an updated version, and the date of this version is within the support period of your license, Rainbow will offer to download and install the updated version automatically. This involves briefly closing and then re-opening Excel."]
[]
[null,"This function and the Uninstall function both use VBScript to perform the requested operations. Because VBScript is a very powerful language, it is blocked by some anti-virus software and in many corporate security environments. If this happens, you may be able to enable VBScript by disabling advanced threat protection in your anti-virus software, or by creating an exception for C:\\Windows\\System32\\cscript.exe (32-bit) or C:\\Windows\\SysWOW64\\cscript.exe (64-bit). If this is not possible, you can still check the latest Rainbow version and (if appropriate) update your own copy manually from the Rainbow Analyst website."]
[]
[null,"If there is an updated version of Rainbow which was released after the end of the support period of your license, you can still download and install this from the Rainbow Analyst website. However, it will install as a 30-day free trial version, and the details of your existing license will be overwritten. To recover your license, you will need to download and re-install an earlier version, and then re-enter the details of your license."]
[]
[null,"Preferences"]
[]
[null,"This function displays the Preferences dialog box, so that you can customise Rainbow to reflect your own preferences. When you have set your preferences, you can click Save Defaults to save your preferences to disk as your new defaults. Alternatively you can click Apply to use your new preferences only until you close Rainbow or exit from Microsoft Excel. The Preferences dialog box has six tabs: Options, Parameters, Reports, Colours, Risk Map, and Logic Analysis."]
[]
[null,"On the Options tab, the six options under General allow you to control the behaviour of various system-wide features:"]
[null,"•  You can choose whether you want the Menu Guide spreadsheet to be loaded at startup, or whether you prefer it to be created when you first click the Menu Guide button."]
[null,"•  You can specify whether Rainbow should automatically prompt you to unhide and unprotect any hidden or protected sheets. Such sheets can cause errors in many Rainbow functions, and so this option is selected by default. However, you might deselect it if you want to run a Rainbow analysis that you are sure involves only sheets which are visible and unprotected."]
[null,"•  You can select whether Rainbow should optimise the range that it analyses by detecting the range that is really used in each worksheet rather than relying on Excel's internally stored \"used range\" (see the help on Trim Unused Space for more details). You would probably only need to turn off this option if you wanted the Logic Map or Logic Analysis function to check for blank input cells outside the real used range."]
[null,"•  You can choose whether you want Rainbow to set Excel's calculation mode (under the Formulas tab in Excel) to Automatic after each analysis, rather than attempting to revert to the original calculation mode (after setting the mode to Manual while running the analysis)."]
[null,"•  You can decide whether Rainbow should display the status bar at the end after it has finished analysis."]
[null,"•  The final option determines whether mouse wheel scrolling is enabled when viewing Help text and the Rainbow Log file."]
[]
[null,"The two further sets of options under the Options tab relate to the Structure Map and Names Toolbox functions:"]
[null,"•  Under Structure Map, you can specify the strength of attraction and the default separation (in points) between sheets. You can also select to have the connector arrows showing the direction of the links rather than the direction of data flow. Increasing the strength of attraction will produce a smaller, neater diagram, but clusters and groupings of sheets may be less easy to see."]
[null,"•  Under Names Toolbox, you can select whether Rainbow uses the descriptions generated by Cell Inspector in the Comments/ (Descriptions) column of the report, and whether it should delete all used names after it has converted them to cell references."]
[]
[null,"The options on the Parameters tab control the Lone cell types and Lone formulas reports (both in the Risk Map function and in the Summary Report), and how the Comparison Toolbox detects and manages inserted and deleted rows and columns:"]
[null,"•  Under Summary Report, you can indicate whether you want Rainbow to highlight (in yellow and red) specific results which may indicate potential problems. You can also select whether it should insert comments (cell notes) to help you find more detailed analysis of specific items in other Rainbow functions. The option to control highlighting also applies to the yellow highlighting in some Risk Map and Logic Analysis reports."]
[null,"•  Under Lone cell types & formulas, you can select the Basic, Enhanced or Maximum level of detection for lone cell types and lone formulas. And then you can choose whether the Lone Fmlas report is generated at the same type as the Summary Report (see the Summary Report help section for more details). Your choices here will be shown as the default options when you run the Summary Report, and you can change your selection in the Summary Report dialog box."]
[null,"•  Under Comparison Toolbox, you can control whether the Comparison Toolbox automatically inserts rows where it considers they have been deleted, and then whether it deletes them after insertion. And you can adjust the minimum number of non-blank cells that are needed to decide when rows or columns may have been inserted (see the Comparison Toolbox help section for a more detailed explanation)."]
[null,"•  Under Cell Inspector, you can set the maximum number of (precedents and) dependents that Rainbow will process and display in Cell Inspector. The default value is 120, meaning that Cell Inspector will not waste time processing hundreds or thousands of dependents that are unlikely to be relevant. However, if you wish to be sure that all dependents are processed, you can choose the maximum value of 32,000 (the maximum set by Excel)."]
[]
[null,"On the Reports tab, the first set of options tells Rainbow what to do when no workbook has been selected for it to use for reports. This means that, for instance when you first run a report after starting Microsoft Excel, Rainbow can go ahead and generate the report without first having to stop and ask you which workbook to put it in. You can tell Rainbow to do one of four things:"]
[null,"•  First look for a workbook that already appears to contain one or more Rainbow reports (based on cell A2 in the last sheet), and if it does not find one, then to create a new workbook for the report. This is the default option."]
[null,"•  Put the report in the current workbook, i.e. the workbook that is currently visible on your screen. This could be the workbook you are reporting on, or it could be a general Rainbow Reports workbook, and you could then use the Selection... button on the Rainbow dialogue box to select a different workbook to report on."]
[null,"•  Put the report in a new workbook, even if there might be a workbook already open which contains Rainbow reports."]
[null,"•  Ask the user to select a current or new workbook. This was the only option in earlier versions of Rainbow."]
[]
[null,"The remaining options on the Reports tab (in the Individual Reports box) first let you select to include all reports in the same workbook (the default setting) or to generate each report as a separate workbook. Then you can tell Rainbow to routinely select all worksheets for analysis rather than just the current sheet(s). Next you can decide whether Rainbow should add a Help button at the top of each report. And you can then choose whether Excel displays a warning when you click on a hyperlink. Finally you can select whether Rainbow should switch on access to Visual Basic code modules (as explained in the Quick Start Guide)."]
[]
[null,"On the Colours tab, the most important set of options concerns the use of multi-colour cells. This feature was introduced in Rainbow 7.0, replacing the use of patterns in previous versions, but you can choose to turn it off if you wish Rainbow to behave more like previous versions and show only one colour in each cell. If you allow multiple colours, Rainbow will then be able to show multiple characteristics in a single cell. For instance, the Risk Map function might highlight a cell with three colours, showing that it contains (1) a calculation which has (2) a hard-coded number, and it is also (3) unlocked."]
[]
[null,"Under Multi-colour cells on the Colours tab, if the option to allow multiple colours in a cell is enabled, you can then choose various further options:"]
[null,"•  You can set the maximum number of colours allowed in a cell. This can be up to 16 colours, but a high number will slow down Rainbow's analysis, so a setting of 4 to 6 is recommended."]
[null,"•  You can choose what Rainbow does if a cell already contains the maximum number of colours and it wishes to add a further colour. Since Rainbow adds colours from the right, it can either  replace the last colour (on the right) with the new colour and leave the other colours in place, or (the default option) remove the first colour on the left of the cell, shift the other colours along, and then add the new colour on the right."]
[null,"•  You can choose the notional angle (from 0 to 180 degrees) of the boundaries between the colours. A purely vertical or horizontal angle can be confused with the boundaries between neighbouring cells, and so a diagonal angle of around 20 degrees is recommended. The precise actual angle will be different from the notional angle, depending on the width of the cell."]
[]
[null,"There are two other sets of options on the Colours tab:"]
[null,"•  Under Grouped cell colours, you can choose the Start colour number, i.e. which of the Item number colours Rainbow should start from when selecting colours for the Inter-Sheet Logic, Formula Scan and Comparison Toolbox functions. And the option to Rotate through all colours tells Rainbow to use each colour sequentially, rather than trying to minimise the number of colours it uses. Note that if you set the Start colour number to be greater than 1 (e.g. 3), Rainbow will re-label the preceding Item number colours (e.g. as Item -1 and Item 0)."]
[null,"•  Under Quick Clean-Up, you can select whether you want the Quick Clean-Up function to clear cell colours and patterns."]
[]
[null,"There are five sets of options on the Risk Map tab as follows:"]
[null,"•  Under Hard-Coded Numbers, you can choose whether Rainbow should include \"numbers-only\" formulas such as \"=365/4\" as an instance of hard-coded numbers (under the Patterns tab in Risk Map), or whether it should only look for formulas which include references to other cells. And you can specify whether you want Rainbow only to highlight multi-digit hard-coded numbers (10 or above, or decimal values), or whether you also want it to include single-digit values from 2 to 9 (it will always ignore 0 and 1, as these are very normal)."]
[null,"•  Under Error values, you can select whether you want Risk Map (under the Checks tab) simply to report all cells with error values, or whether Rainbow should check the precedents of these cells to establish which are the primary error source cells (from which the error originates), and which are secondary errors (where the error is a result of errors in one or more of the precedents). Clearly the analysis will take longer if you select this option."]
[null,"•  Under Numbers formatted as text, you can indicate whether you want Rainbow to recognise numbers preceded or followed by up to three or four letters, e.g. \"¥1000\" or \"568 ml\", as well as straightforward cases of numbers formatted as text. Note that this option affects both the Risk Map function (under the Hidden tab) and the Format Editor function (option 1)."]
[null,"•  Under Outlier values, you can set the maximum number of potential outliers that Risk Map should examine (under the Patterns tab). Note that Risk Map detects outliers using the Generalized ESD test, which requires this input parameter."]
[null,"•  Under Complex Formulas, you can set the criteria for Risk Map to detect Multiple operations and Multiple references (on the Formulas tab)."]
[]
[null,"On the Logic Analysis tab are three sets of options relating to Precedents analysis, two settings for Inter-Sheet Logic, and a setting that affects analysis of Dependents in various functions:"]
[null,"•  Under  Inconsistent precedent copies, you can select whether you want Rainbow to include cases where a block changes size as the formula is copied along a row; for instance =SUM($A1:B1) includes 2 cells, but it will change to =SUM($A1:C1) (including 3 cells) and so on as the formula is copied along a row. You can also select whether to include inconsistencies in the number format of cells (and this selection also applies to the analysis of Precedent block inconsistencies)."]
[null,"•  Under Other precedents logic, for Precedent block inconsistencies, you can select whether you want Rainbow to check for row differences and column differences (as well as inconsistent cell types and formats); often (e.g. in a SUM() calculation) row and column differences can be a good indicator of possible errors."]
[null,"•  Also under Other precedents logic, for Precedents of different cell type, you can choose whether Rainbow should speed up processing by checking only the corner cells in a block of precedents (e.g. A1, D1, A4, D4 where the formula is =SUM(A1:D4)), or whether it should check all cells in the block. This option is particularly relevant if you are analysing Precedent block inconsistencies as well as Precedents of different cell type."]
[null,"•  Under Inter-Sheet Logic, you can tell Rainbow whether or not to highlight (in yellow and red) the logic flows with the highest frequencies. You can also choose to include within-sheet logic (i.e. formulas referencing cells on the same sheet) in the Inter-Sheet Logic report."]
[null,"•  Under Excess same-sheet dependents, you can choose what Rainbow does (in Logic Map, Inter-Sheet Logic, the Dependents tab in Logic Analysis, and Cell Inspector) when it finds a cell with so many dependents in the same sheet that it takes Excel a long time to list them. If you enter 0 (zero) here, Rainbow will keep going regardless of how long it takes, whereas if you enter a positive number of seconds (the default is 5), then if it takes longer than this to list the dependents for any one cell, Rainbow will skip processing dependents for all further cells on the same row. Where this happens, Rainbow will report the details in the Rainbow Log file (click User Guide, Rainbow Log)."]
[]
[null,"Reset Reports"]
[]
[null,"Reset Reports tells you the file name of Rainbow's current report workbook, i.e. the workbook that Rainbow is using to hold reports that it generates, and it lets you clear Rainbow's record of this (without deleting the report workbook itself). This means that when you next request a report, Rainbow will have to select a new report workbook, which can be either a currently open workbook, or a new workbook that Rainbow can set up for this purpose."]
[]
[null,"If you have chosen one of the first three options (in your Preferences settings on the Reports tab) to enable Rainbow to select a new report workbook automatically, then the Reset Reports function will let you decide whether next time you want to select the new report workbook manually yourself, or whether you want Rainbow to make the selection automatically. This only applies to the next time you run a report; after that Rainbow will go back to automatic selection based on your Preferences setting."]
[]
[null,"Whenever a new workbook is created for reports, Rainbow will initially set this up with Excel's default new workbook name (e.g. Book1). You can then use the File Save As function to save the workbook under a more helpful name, and Rainbow will record the new name and continue to use the workbook for further reports."]
[]
[null,"Restore Defaults"]
[]
[null,"The Restore Defaults function lets you restore Rainbow back to its original state (as initially downloaded). This function removes all information about Rainbow from the Windows registry, including saved Preferences, modified colours, and favourite analysis options. It can also allow you to remove any license data, and you can then either reinstall your license on the same computer or move it to another one. To re-enter your license key (registration code), simply click User Guide and About Rainbow."]
[]
[null,"Uninstall"]
[]
[null,"This function will first check whether you want to remove the details of any existing license (and also any Preferences settings that you have saved). It will then uninstall the Rainbow add-in, which involves briefly closing and then re-opening Excel."]
[]
[null,"This function and the Check For Updates function both use VBScript to perform the requested operations. Because VBScript is a very powerful language, it is blocked by some anti-virus software and in many corporate security environments. If this happens, you may be able to enable VBScript by disabling advanced threat protection in your anti-virus software, or by creating an exception for C:\\Windows\\System32\\cscript.exe (32-bit) or C:\\Windows\\SysWOW64\\cscript.exe (64-bit). If this is not possible, you can still uninstall Rainbow manually from within Windows Settings. Click Apps, then scroll down and click Rainbow Analyst, then click Uninstall."]
[]
[null,"License Agreement"]
[]
[null,"This License Agreement (\"the License\") relates to the Rainbow Professional software product (\"the Software\") supplied by M G Shallcross trading as The Model Answer (\"the Licensor\"). This is a legal agreement between you, the end user (\"the Licensee\"), and the Licensor. You should read the following terms and conditions carefully before you use the Software. Further download or use of the Software certifies that you accept these terms and conditions. Particular attention is drawn to the LIMITED WARRANTY, LIFETIME OF THE LICENSE, and FILE INTERACTIONS sections of this document."]
[]
[null,"1. SPYWARE, ADWARE, COOKIES AND DATA HARVESTING"]
[null,"1.1 We take your privacy and personal information seriously. Legitimate versions of the Software contain no known spyware, adware, data harvesting techniques or malicious code. "]
[]
[null,"2. LICENSE"]
[null,"2.1 The Licensor grants the Licensee a non-exclusive license to install and use the Software on any computer, laptop, or network for use only by the Licensee. The Software is \"installed\" on a computer or network when it is copied on to any storage device on that computer or network."]
[]
[null,"3. RESTRICTIONS AND EXTENDING USE TO OTHER COMPANIES"]
[null,"3.1 This License is your proof of license to exercise the rights granted herein and must be retained by you. You may not rent or lease or re-sell the Software. In addition, the Licensee is expressly forbidden from extending, lending or giving the software or installing the software or having the software installed on the computer or network of any other company without the express prior written permission of the Licensor. "]
[null,"3.2 The Licensee may transfer their rights under this License on a permanent basis provided they transfer this License Agreement, the Software, and all accompanying written materials (if any) and retain no copies, and the recipient agrees to the terms of this License. You must notify the Licensor in writing of your intention to transfer your rights and you must have received written acknowledgement of this intention from the Licensor before the transfer takes place. Any transfer of the Software must include the most recent update and any available prior versions."]
[null,"3.3 The Licensee may not copy, reverse engineer, decompile, or disassemble the Software in whole or part, except as permitted by law which is incapable of exclusion between the parties."]
[]
[null,"4. COPYRIGHT & INTELLECTUAL PROPERTY"]
[null,"4.1 The Licensor warrants that it is the sole legal owner of the Software, and is entitled to grant this license in relation to the Software."]
[null,"4.2 All Intellectual Property Rights in the Software belong, and shall belong, to the Licensor."]
[null,"4.3 The Software is protected by UK copyright laws and International treaty provisions. You must treat the Software and the documentation accompanying the Software like any other copyrighted material except that you may copy the Software and documentation to a computer or network solely for backup purposes."]
[]
[null,"5. LIMITED WARRANTY"]
[null,"5.1 The Licensor warrants that the Software will perform substantially in accordance with the accompanying documentation for a period of 1 year from the date of first installation. Any implied warranties on the Software are limited to 1 year."]
[null,"5.2 Almost no software is completely error-free and the Licensor does not warrant that the Software will operate without errors. We both encourage and welcome comments and suggestions for improvements or notification of errors."]
[null,"5.3. The Licensor's entire liability and your exclusive remedy shall be, at the Licensor's option, either (a) return of any price paid or (b) return of up to 3 months of any agreed support costs or (c) replacement of the Software that does not meet the Licensor's Limited Warranty."]
[null,"5.4 The Licensor's website has security features in place to ensure the contents are not tampered with. However, we are aware that websites can be hacked and their contents changed. Legitimate versions of the Software are digitally signed and contain no known adware or spyware or malicious code. Please alert us immediately if you suspect otherwise, but please be aware we take no responsibility for the consequences of any \"hacked\" code."]
[null,"5.5 IN ALL OTHER RESPECTS, THIS SOFTWARE IS PROVIDED \"AS IS\" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE LICENSOR SHALL NOT BE LIABLE FOR ANY DIRECT, INDIRECT, CONSEQUENTIAL, OR INCIDENTAL DAMAGES ARISING OUT OF THE USE, THE RESULT OF USE, OR THE INABILITY TO USE THIS PRODUCT."]
[null,"5.6 Some countries or states do not allow the exclusion or limitation of implied warranties or limitation of liability for incidental or consequential damages, so the above limitation or exclusion may not apply to you."]
[]
[null,"6. RIGHT OF REJECTION"]
[null,"6.1 For a period of 10 days commencing on the installation of the Software, the Licensee may discontinue this license of the Software if it does not perform to the Licensee's reasonable satisfaction. If the Licensee does so, the Licensor shall immediately refund all monies paid by the Licensee under this license and, on receipt of that refund, this license shall terminate. Further use of the software will be a breach of this clause and full license and/or maintenance fee(s) will be payable. Training and consultancy fees are NOT refundable."]
[]
[null,"7. LIFETIME OF THE LICENSE"]
[null,"7.1 Payable Licenses are normally granted on an annual or biennial basis. The Licensee’s particular expiry date can be viewed on the \"About Rainbow\" form within the Software. If this license has been transmitted by email then the license expiry data may also be contained within the accompanying documentation. "]
[null,"7.2 Continuing use of the Software after the expiry date is permitted, but users are not entitled to access support or to install updates to the Software. Any attempt to install updates after the expiry date may result in deletion of the Licensee's stored license data, and the Software will then revert to being unlicensed and available for use only on a \"free trial\" basis."]
[null," "]
[null,"8. FILE INTERACTIONS"]
[null,"8.1 In rare circumstances, PARTICULARLY BUT NOT LIMITED TO SPREADSHEETS THAT CONTAIN AUTOMATION, REFERENCES, LINKS OR VBA CODE, the Software or Microsoft Excel itself may inadvertently affect the operation of other Excel files OR VICE-VERSA. It is the sole responsibility of the Licensee to determine whether any interaction between files is taking place and take the necessary steps to avoid the interaction, INCLUDING BUT NOT LIMITED TO REMOVAL OF THE SOFTWARE. Where removal of the Software is deemed necessary by the Licensee and reasonable evidence of the issue is supplied to the Licensor, at the Licensor's option the Licensee will be entitled to either (a) return of any price paid or (b) return of up to 3 months of any agreed support costs or (c) replacement of the Software."]
[]
[null,"9. GENERAL"]
[null,"9.1 This agreement will be governed by the laws of the UK."]
[null,"9.2 Any questions regarding this agreement or the Software should be directed to: support@themodelanswer.com."]

{"sheet":"Colours","index":1,"n_rows":53,"n_cols":13,"rows_kept":53,"cut":false,"hidden":false,"merged":[]}
[]
[null,"Rainbow Cell Colour Sheet"]
[]
[null,"Cell type","Current","Red","Green","Blue","Index","New","Default","TestCopies","Favourites","Default","Formula"]
[null,null,null,null,null,null,null,"index","index",null,null,"faves","scan"]
[null,"Lone cell types",null,0,127,127,7,7,7,true,true,true,false]
[null,"Lone formulas",null,127,127,0,8,8,8,true,true,true,false]
[null,"Different formats",null,127,0,127,9,9,9,true,true,true,false]
[null,"Hard-coded numbers",null,128,128,254,10,10,10,false,true,true,true]
[null,"Outlier numbers",null,128,254,128,11,11,11,true,false,false,false]
[null,"Variant text values",null,254,128,128,12,12,12,true,false,false,false]
[null,"Error values",null,0,191,191,13,13,13,true,true,true,true]
[null,"Hidden error values",null,191,191,0,14,14,14,true,true,true,true]
[null,"Excel error checks",null,191,0,191,15,15,15,true,false,false,false]
[null,"Conditional formatting",null,128,128,191,16,16,16,true,true,true,true]
[null,"Data validation",null,128,191,128,17,17,17,true,true,true,true]
[null,"SUM containing subtotals",null,191,128,128,18,18,18,true,true,true,true]
[null,"Complex conditions",null,128,191,191,19,19,19,false,true,true,true]
[null,"Multiple operations",null,191,191,128,20,20,20,false,true,true,true]
[null,"Multiple references",null,191,128,191,21,21,21,false,false,false,true]
[null,"LOOKUP functions",null,64,64,191,22,22,22,false,true,true,true]
[null,"Volatile functions",null,64,191,64,23,23,23,false,true,true,true]
[null,"Array formulas",null,191,64,64,24,24,24,true,true,true,true]
[null,"Cells in hidden row or column",null,64,64,254,25,25,25,true,true,true,true]
[null,"Hidden-format formulas",null,64,254,64,26,26,26,true,true,true,true]
[null,"Numbers formatted as text",null,254,64,64,27,27,27,false,true,true,false]
[null,"Formulas formatted as text",null,64,191,191,28,28,28,false,true,true,true]
[null,"Invisible cell contents",null,191,191,64,29,29,29,true,true,true,true]
[null,"Hidden operators",null,191,64,191,30,30,30,false,true,true,true]
[null,"Constant numbers",null,64,254,254,31,31,31,false,true,true,false]
[null,"Calculated numbers",null,0,223,223,37,37,37,true,true,true,false]
[null,"Constant date values",null,254,64,254,33,33,33,false,true,true,false]
[null,"Calculated date values",null,223,0,223,39,39,39,true,true,true,false]
[null,"Constant logical values",null,128,223,128,41,41,41,true,true,true,false]
[null,"Calculated logical values",null,0,159,159,34,34,34,true,true,true,true]
[null,"Constant text values",null,254,254,64,32,32,32,false,true,true,false]
[null,"Calculated text values",null,223,223,0,38,38,38,true,true,true,false]
[null,"Defined scenarios",null,159,0,159,36,36,36,true,false,false,false]
[null,"Formulas without cell references",null,128,128,223,40,40,40,true,false,false,true]
[null,"Unlocked cells",null,159,159,0,35,35,35,true,false,false,true]
[null,"Hidden-formula cells",null,223,128,128,42,42,42,true,false,false,false]
[null,"Blank input cells",null,254,0,0,3,3,3,"N/A",false,false,false]
[null,"Non-blank input cells",null,254,254,0,5,5,5,"N/A",false,false,false]
[null,"Intermediate workings",null,0,254,0,2,2,2,"N/A",false,false,false]
[null,"Circular references",null,0,254,254,4,4,4,"N/A",false,false,true]
[null,"Output calculations",null,0,0,254,1,1,1,"N/A",false,false,false]
[null,"Labels",null,254,0,254,6,6,6,"N/A",false,false,false]
[null,"Inconsistent precedent copies",null,128,223,223,43,43,43,"N/A",false,false,false]
[null,"Precedent block inconsistencies",null,223,223,128,44,44,44,"N/A",false,false,false]
[null,"Precedents only part of block",null,223,128,223,45,45,45,"N/A",false,false,true]
[null,"Precedents referring forwards",null,64,64,159,46,46,46,"N/A",false,false,true]
[null,"Precedents of different cell type",null,64,159,64,47,47,47,"N/A",false,false,true]
[null,"Cells with blank precedents",null,159,64,64,48,48,48,"N/A",false,false,true]

{"sheet":"Menu","index":2,"n_rows":51,"n_cols":6,"rows_kept":51,"cut":false,"hidden":false,"merged":[]}
["Menu sheet"]
["Row","Column","Line","Control","Label","Supertip"]
[0,0,3,"summaryRpt","Summary Report","Provides an overview of the current workbook, with an overall score and key information about potential risks in each sheet."]
[1,0,{"$f":"C3+1","v":4},"hideProtect","Security Manager","Easily (un)hides or (un)protects multiple sheets, including sheets completely hidden from ordinary users."]
[2,0,{"$f":"C4+1","v":5},"passTool","Password Tool","Unzips the workbook file to delete passwords and remove protection from worksheets and the workbook."]
[3,0,6,"calcMgr","Calc Manager","Lets you recalculate specific ranges, worksheets, or workbooks, with either minimal or full recalculation."]
[0,1,7,"structMap","Structure Map","Creates a visual map of the structure of a workbook showing the sheets and their interconnections."]
[1,1,8,"interSheet","Inter-Sheet Logic","Generates a tabular matrix showing logic flows between sheets, with ability to drill down to individual cells."]
[2,1,9,"reportLinks","Data Links","Searches for links to external workbooks and produces a report listing where they are used."]
[0,2,10,"formGroups","Formula Scan","Highlights blocks of copied formulas (by row or column), and generates a report for systematic spreadsheet audit."]
[1,2,11,"mapDepends","Logic Map","Produces a colour map of the logical structure of a worksheet, showing dependency types and numbers of dependents."]
[2,2,12,"colourLogic","Logic Colours","Displays the dependency colours used in the Logic Map, and finds them on the current sheet."]
[0,3,{"$f":"C14+1","v":14},"trackIssues","Issue Tracker","Helps you track unresolved issues and collaborate with colleagues to correct errors."]
[1,3,{"$f":"C12+1","v":13},"dataSample","Sample Data","Selects and reports a random, periodic, or conditional sample of rows for auditing, and includes a sample size calculator."]
[2,3,{"$f":"C13+1","v":15},"scrambleData","Scramble Workbook","Substitutes random values for numerical and other data in order to remove confidential elements from a workbook."]
[0,4,16,"exploreData","Data Explorer","Generates an interactive report of pivot, query and data tables and lets you edit the underlying pivot caches and data connections."]
[1,4,17,"exploreMacros","Macro Explorer","Produces an interactive report that lists all macros (including user-defined functions) and lets you drill down to see the code."]
[2,4,18,"nameTools","Names Toolbox","Generates an interactive report of defined names, enabling conversion to cell references, change of scope and other actions."]
[0,5,19,"cellInspect","Cell Inspector","[Press ALT+F10] Interactive tool for tracing precedents and dependents, with a best-guess description of what each cell means."]
[1,5,20,"findCells","Find Cells","Searches for specific formula, text or number values, dates, days, or times, with a wide range of search criteria."]
[2,5,21,"mergedCells","Merged Cells Tool","Produces a report listing all merged cells, with options for un-merging some or all of them."]
[0,6,22,"riskReports","Risk Map","Generates reports and colour maps showing potentially risky patterns, checks, formulas and hidden cells."]
[1,6,23,"precedeReports","Logic Analysis","Generates colour maps and reports showing the logical structure of cell types, dependents and precedents."]
[2,6,24,"colrScroll","Analysis Wheel","Lets you scroll through cell type analysis options, perform analyses, and revisit previous analysis results."]
[0,7,25,"saveFmt","Save Format","Sets a format restore point so that you can later use Undo Changes to remove colours added by Rainbow Analyst."]
[1,7,26,"resetCells","Undo Changes","Restores formatting of cells as it was at the last restore point created with Save Format."]
[2,7,27,"fSaveAs","File Save As","Lets you save the current workbook under a different file name."]
[0,8,28,"quickCleanup","Quick Clean-Up","Unhides hidden sheets, rows and columns, unprotects protected sheets, and optionally clears cell colours."]
[1,8,29,"clearStyles","Clean Up Formats","Clears all custom styles (superfluous formats)."]
[2,8,30,"clearUnusedAreas","Trim Unused Space","Deletes unused rows and columns around the edge of the working area."]
[3,8,31,"cleanReports","Tidy Up Reports","Tidies up Rainbow reports for sharing with colleagues who do not use Rainbow."]
[0,9,32,"deepClean","Deep Clean","Unzips the workbook file and lets you reset custom styles, list all external links, and delete names with phantom links."]
[1,9,33,"deepComp","Deep Compare","Compares the deep structure of workbooks to find differences not explained by changes in cell formulas."]
[2,9,34,"viewTemp","Temp Files","Opens File Explorer so you can see the folder containing the unzipped workbook and XML files and the Rainbow log file."]
[0,10,35,"goCompare","Comparison Toolbox","Compares worksheets, workbooks, or ranges within a worksheet or workbook, and also macros in different workbooks."]
[1,10,36,"reportComments","Comments Report","Produces a report listing details of all issues, cell notes or threaded comments."]
[2,10,37,"deleteComments","Delete Comments","Deletes issues, cell notes or threaded comments."]
[0,11,38,"colourFind","Identify Colours","[Press ALT+F9] Displays the colour type(s) of the selected cell, and finds other similarly highlighted cells."]
[1,11,39,"editFormats","Format Editor","Automates formatting operations such as converting text to numbers, changing cell or font colours, and cell unlocking."]
[0,12,40,"galCellMapping","Colour Chart","Displays a gallery of all the colours used for highlighting cell types, and lets you access the Analysis Wheel for any colour."]
[0,13,41,"deleteColours","Clear Cell Colours","Deletes cell colours and patterns."]
[1,13,42,"reportGraphics","Graphics Report","Produces a report showing you the locations of charts, buttons and other graphical objects."]
[2,13,43,"deleteGraphics","Delete Graphics","Deletes all charts, buttons and other graphical objects."]
[0,14,44,"userGuide","User Guide","Generates a copy of the User Guide as a single-sheet workbook that you can read and/or print."]
[1,14,45,"rainbowLog","Rainbow Log","[Press F10] Lets you view the Rainbow log file, with details of items unhidden and/or unprotected and other information."]
[2,14,46,"aboutRainbow","About Rainbow","Lets you register your Rainbow licence key, or check your licence and Rainbow version if you have already registered."]
[3,14,47,"updateChk","Check For Updates","Checks whether you have the latest version of Rainbow and advises how to upgrade."]
[0,15,48,"setPrefers","Preferences","Lets you adjust some Rainbow functions to suit your own preferences."]
[1,15,49,"resetRpts","Reset Reports","Sets Rainbow to prompt for a new workbook in which to put reports next time a report is required."]
[2,15,50,"resetDefaults","Restore Defaults","Resets Rainbow to its original default settings."]
[3,15,51,"deleteAddIn","Uninstall","Deletes the Rainbow add-in file and clears licence data."]

{"sheet":"Prefers","index":3,"n_rows":137,"n_cols":7,"rows_kept":137,"cut":false,"hidden":false,"merged":[]}
["Preferences sheet"]
[null,null,null,null,null,"Current","Report"]
["Columns F:G used in ColourCells for mapping"]
[]
["Current book/sheets and report book/sheets"]
[]
["UserDetails","Current","Original"]
[]
["UserName"]
["UserEmail"]
["UserCode"]
["ExpiryDate","2024-10-20T10:17:31"]
["ExpiryPeriod"]
["Version","24.09.0"]
[]
["GenlPrefers","Current","Original"]
[]
["Reporting"]
["ReportOption",1,1]
["SameRptBook",true,true]
["RptBookName"]
["AllSheets",false,false]
["RptHelpBtn",true,true]
["NoWarnings",true,true]
["EnableVBOM",true,true]
[]
["Multi-Colour Cells"]
["ColrOverlap",true,true]
["ColrLimit",6,6]
["ShiftFIFO",true,true]
["ColrAngle",20,20]
[]
["Mapping"]
["ColrStart",1,1]
["ColrRotate",true,true]
["QuickClear",false,false]
["DoMap",false,false]
["ColrNames",true,true]
["WheelStart",4,4]
[]
["Undo Changes"]
["DelFlags",true,true]
["DelComments",true,true]
[]
["General Options"]
["StartGuide",false,false]
["ClearSheets",true,true]
["OptimiseRange",true,true]
["CalcAutomatic",false,false]
["DisplayStatus",true,true]
["MouseScroll",true,true]
[]
["Summary Report"]
["TrafficLights",true,true]
["ExpComments",true,true]
["LoneBoxIndx",1,1]
["LoneFmlasRpt",false,false]
[]
["Formula Scan"]
["FGrpReport",true,true]
["RiskBoxIndx",3,3]
["FlagBoxIndx",1,1]
["UniqFmla",true,true]
["UseDesc",false,false]
["RptComments",true,true]
[]
["Sheet Diagram"]
["DefAttraction",400,400]
["DefSeparation",400,400]
["RevArrow",false,false]
[]
["Inter-Sheet"]
["IShtReport",true,true]
["IShtLights",true,true]
["IncLocals",false,false]
[]
["Risks and Dependencies"]
["OthReport",true,true]
["DoMacros",false,false]
["InsFlags",true,true]
["InsComments",false,false]
["ShowTime",5,5]
["ShowDeps",120,120]
[]
["Hard-coded numbers"]
["AllNumbers",true,true]
["SingleDigits",false,false]
[]
["Outlier numbers"]
["MaxOutliers",10,10]
[]
["Cell errors"]
["ErrorSources",false,false]
[]
["Numbers formatted as text"]
["DetectLetters",true,true]
[]
["Complex formulas"]
["MultiOps",4,4]
["MultiRefs",6,6]
[]
["Inconsistent copies"]
["SizeChanges",true,true]
["FormatDiffs",false,false]
[]
["Other precedents logic"]
["RowColDiffs",true,true]
["OnlyCorners",false,false]
[]
["Defined Names"]
["UseInspector",false,false]
["NameDelete",false,false]
[]
["Comparison"]
["RowInsert",true,true]
["RowDelete",true,true]
["CellsChecked",6,6]
[]
["CompWkshts","Current","Original"]
[]
["CompForms",true,true]
["CompVals",false,false]
["CompInclude",true,true]
[]
["CompMacros",false,false]
["CompCells",true,true]
[]
["GroupForms",true,true]
["SepForms",false,false]
[]
["CompRangeAll",true,true]
["CompRangeFix",false,false]
["CompRangeVary",false,false]
[]
["CompFlags",true,true]
["CompColour",true,true]
["CompMap",false,false]

{"sheet":"Control","index":4,"n_rows":999,"n_cols":27,"rows_kept":999,"cut":false,"hidden":false,"merged":[]}
["Control sheet"]
[null,null,null,null,"Book","Sheet","Cell",null,null,"Precede","Book","Sheet","Cell","Type","Value","Description","Visible",null,null,"Depend","Book","Sheet","Cell","Type","Value","Description","Visible"]
["WkBook"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]
["UsedRng"]

{"sheet":"Temp","index":5,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}