Map

INTRODUCTION

The larger a worksheet becomes, the more difficult it is to manage, and finding particular cells can prove difficult in the same way as finding a specific location in a vast landscape.

The solution to both problems is to have a good map; one which produces a compact and accurate representation of a larger reality.  One of the drawbacks of Excel is that it has no built-in facility for generating such a map.

The OAK Review | Workbook | Map command generates a graphical map of the structure of every sheet in a workbook or of a selection of the worksheets in a workbook, helping you to identify various worksheet features and to locate errors in the logic of the worksheets.

OAK WORKBOOK/WORKSHEET MAP

The OAK Review | Workbook | Map  command provides you with a detailed map of the active workbook or a selection of worksheets within that workbook.  It uses text, color, symbols, highlighting, and outlining to draw attention to various features on the worksheet.

Why would you want to use it?

Map Workbook/Worksheets is primarily used as an auditing tool to allow worksheets to be rapidly checked for errors and inconsistencies.  By highlighting the different worksheet features using different cell formats, you can easily understand the logic of the worksheet and find many mistakes.

By creating a new map (and saving it) each time you make modifications to your workbook, you create an audit trail of the development of the workbook.

When printed, a worksheet map can be used to easily explain the overall structure and logic of the worksheet to somebody else.

How to use it

Choose the Map command from the OAK Review ribbon tab.  The Map Workbook/Worksheets dialog box is displayed:

There are five groups of options on the dialog box.  The first three (Formulas, Constants and Other), allow you to choose which of these types of cells to add to the map.  The Output option specifies where to put the maps that are generated and the Input options specify whether to generate maps of every worksheet in a workbook or of a selection of worksheets from a workbook.

OAK has a set of default options.  If you change the options on the dialog box, your choices will be retained until OAK is closed; you can press the ‘Default’ button to return to the pre-specified settings.

Formulas

Map Formulas.  Checking this box will add cells containing formulas to the map.  The cells to be mapped can be refined by using the remainder of the options in the group; unchecking this checkbox disables the remainder of the options in the group.

The relationship between formulas in neighboring cells is determined and represented in the map by the addition of symbols to the cells; these are explained in the Legend section.

Constants

Map Constants.  Checking this box will add cells containing constants to the map.  The cells to be mapped can be refined by using the remainder of the options in the group;  unchecking this checkbox disables the remainder of the options in the group.

Other

Map Named Ranges.  Checking this box will outline the areas which belong to named ranges.  Note that the names “Print_Area” and “Print_Titles” are mapped using a different color in order to avoid possible confusion due to overlapping named ranges.

Map Arrays.  Checking this box will crosshatch the areas which are part of an array formula or a data table.

Output

This group selects whether the legend is generated and offers a radio button selection of one of three options for the location where the maps will be placed:

Input

These options select which worksheets should be mapped.

Legend

The legend can be generated as part of the mapping process by checking the relevant box on the dialog.  The legend can also be found in the online help.

The chart below identifies the symbols used in the worksheet maps.  A color version of this legend can be found on the inside back cover of this manual.

To make maximum use of the power of the Worksheet Map it is important to be able to interpret it correctly.  This requires knowledge of the symbols used and an understanding of how they are related to each other.

The map symbols have been divided up into groups by type: formula cells, constant cells, and other features.

Wrinkles

This command will not function if applied to a protected worksheet.  You must first manually unprotect it.

The colors used in the map can be altered with OAK's Options command.

Excel cannot handle a worksheet in which every cell is a different color, typeface and font size.  Excel keeps a list of all the different combinations of cell formats in use in a workbook, and beyond some limit will allow no further permutations of formatting to be added because the list has a fixed size and has become full.  Though there is no danger of hitting this limit when drawing a map in a new workbook, OAK may take Excel beyond this limit when the map is superimposed on a worksheet that is already busy in formatting terms, since OAK achieves the mapping effect by applying appropriate formatting to cells.

The result will be that OAK will deliver an incomplete map.  It will issue a warning that it has had to stop the action prematurely.  Operis is working on addressing this.  The workarounds are to

What you could do if you didn't have OAK

Nearly all Excel add-ins that are similar in purpose to OAK have a feature resembling OAK’s map workbook/worksheets tool, something that  

Distinctive qualities of OAK's  version of this function are that

If you didn't have OAK or any other add-in, you would have to rely on features built in to Excel to get as close as possible to the same insights.

•

You can display all the formulas in a worksheet by using Formulas | Formula Auditing | Show Formulas (shortcut Ctrl+`, an obscure key that is in the top left corner of US and UK keyboards, between the F1, 1 and Tab keys, and is never pressed for any other purpose, at least not in Windows).  But Excel won't show you which formulas are distinct and which merely copies of neighbors.