172x Filetype XLSX File size 0.04 MB Source: www.themodelanswer.com
Rainbow Analyst User Guide User Guide Contents If you have turned off in-cell editing, you can double-click any of the items immediately below to go to the relevant heading. Row Heading 038 Quick Start Guide 063 Overview of Rainbow functions 101 Using Rainbow in practice 117 Helpful hints 133 Rainbow functions in detail 137 Quick Clean-Up 143 Reset Reports 149 Set Baseline 157 Undo Changes 163 Summary Report 179 Inter-Sheet Logic 197 Dependency Map 219 Map Risks 255 Report Objects 267 Manage Names 297 Sample Data 309 Formula Groups 327 Cell Inspector 339 Compare Worksheets 380 Find Colours 388 Colour Chart 402 Format Editor 418 (Un)Hide/Protect 428 Preferences 456 User Guide 460 Rainbow Log 468 About Rainbow 474 Installation and licensing Quick Start Guide 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 Analyst. Page 1 of 28 When you click a button on the Rainbow tab in the Ribbon, you'll generally see a dialog box with "Click OK to …" near the top. This is Rainbow's guess as to what you want to do, based on your currently selected workbook and worksheet(s). 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. Most Rainbow Analyst functions perform one or more of the following actions: • apply colour to specific cells in the worksheet(s) you have selected; • generate (a) worksheet map(s) in which specific cells have been coloured to reflect information about the corresponding cells in the worksheet(s) you have selected; • generate a worksheet report listing specific cells or other items. You can 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 Analyst function you have selected always generates a worksheet report (i.e. the report can't be turned off). If you want Rainbow Analyst to apply colour to your selected worksheet(s), but you want to be able to restore the original colouring afterwards, then you first need to use the Set Baseline function (in the File Control group). This will record the current formatting of your selected worksheet(s), so that you can restore it afterwards using the Undo Changes function. If you need to stop Rainbow in the middle of running a function for any reason, simply press Esc or Ctrl+Break. Rainbow will stop and 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. As with all the tabs on the Excel "Ribbon", there are keyboard shortcuts for the Rainbow tab and all the Rainbow functions; you can see them by pressing and releasing the Alt key. So for instance the quickest way to run the Find Colours function is to press Alt, then Y (for Rainbow), then F (for Find Colours). That's all you need to know to get started. For full information on how to use 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. Please consult the final section of the User Guide for full information on installing Rainbow Analyst, and also for important information on copyright and liability. Overview of Rainbow functions Page 2 of 28 The Rainbow tab has five groups of functions: File Control, Structural Overview, Risk Analysis, Formula Checking, and Other Tools. Within each group, the first function is generally the most important; if you were to use only two of Rainbow's functions, they should probably be the Summary Report and Formula Groups. The first set of functions on the Rainbow tab (in the File Control group) can help you get set up before you start using Rainbow's analysis tools: • The two functions on the File Clean-Up menu let you quickly clean up a workbook and then save it as a working copy. • Reset Reports lets you tell Rainbow that you want it to put reports in a different workbook from the one it is currently using. • Set Baseline records the current formatting of your selected worksheet(s), so that you can restore it again using Undo Changes; • Undo Changes lets you restore cell colours and other selected aspects of cell formatting from the last baseline that you set. The next three items on the Rainbow tab (in the Structural Overview group) are powerful "floodlight" functions, which are probably the first ones you'll want to use when starting to analyse a spreadsheet: • Summary Report gives you a quick overview of a workbook, and lists data for each sheet which can already start to suggest possible problem areas. • Inter-Sheet Logic provides a fast analysis of logic flows between sheets, with a matrix-style report that lets you drill down for detailed information. • Dependency Map shows you the role of each cell in the logic of the spreadsheet, and highlights potential issues such as blank input cells. The next group of items (the Risk Analysis group) consists mainly of "spotlight" functions which zoom in to examine specific details: • The three functions on the Map Risks menu generate reports on possible errors (e.g. hard-coded numbers), dependencies and other cells, and these can all be highlighted together on the worksheet or map. • Report Objects lets you generate detailed reports on links to other workbooks, and also on comments (cell notes), pivot and data tables, and buttons and graphics. • Manage Names generates an interactive report on defined names, and lets you convert names to cell references and delete invalid names. • Sample Data selects a random or periodic sample of data from a worksheet and copies it to a report for further analysis. The next three items (in the Formula Checking group) are designed to help you move on to a detailed audit of the calculations in a spreadsheet: • Formula Groups highlights (and reports on) groups of copied formulas, so that you can easily spot inconsistencies and adjustments. • Cell Inspector is a powerful interactive function which lets you trace the logic of any individual cell at a glance, and is very useful in conjunction with Formula Groups. Page 3 of 28 • Compare Worksheets is a comprehensive comparison function (comparing workbooks and ranges as well as worksheets), and it provides a key tool for control of versions and scenarios. In the Other Tools group are some utility functions to manage Rainbow's colours and assist with spreadsheet development: • Find Colours gives you an explanation of the Rainbow colour(s) in the selected cell, and lets you search for other similarly-coloured cells. • Colour Chart gives you an interactive visual display of all the colours that Rainbow uses to map different types of cells and different groups of formulas; • Format Editor works in conjunction with other Rainbow functions to enable you to apply formatting changes, for instance to all input cells; • (Un)Hide/Protect can hide or unhide and protect or unprotect selected worksheets, and also unhide hidden rows and columns. • Preferences lets you set various options, for instance whether to create reports as separate workbooks or as worksheets in one workbook. The final item on the Rainbow tab is the User Guide (Help) menu: • User Guide displays this User Guide in the form of a new Excel workbook that you can view, save or print. • Rainbow Log lets you view the Rainbow log file, with details of items recently unhidden or unprotected, or any system errors. • About Rainbow enables you to register your copy of Rainbow Analyst, and displays licensing and support information. Using Rainbow in practice Before using Rainbow's analysis functions, you may find it helpful to use the the two functions on the File Clean-Up menu. The Quick Clean-Up function unhides any hidden sheets, rows and columns, unprotects protected sheets, and clears existing cell colours to avoid confusion with Rainbow's own highlighting colours. File Save As allows you to save the cleaned-up workbook under a different name as your working copy. When you first start to look at a spreadsheet developed by someone else, you may want to begin by understanding the overall structure and logical flow. This is the purpose of the Summary Report, which can highlight possible problem areas, and the Inter-Sheet Logic function, which can show you at a glance, for each sheet, which other sheets (and other workbooks) it depends on, and which depend on it. Page 4 of 28
no reviews yet
Please Login to review.