Introduction

Visual cues can help to clarify a workbook's structure. The Visualisation Tools create Excel Formula Maps by colour coding your worksheet to make them easier to understand. These Excel formula maps are indispensable when auditing or reviewing Excel workbooks.

The Visualisation Tools assist you to identify and understand:

  • Cell Roles - identifying cells based on what they do
  • Structure - identifying how cells interact with other cells; and
  • Anomalies - allowing visual representation of inconsistencies.

The four "Excel Formula Maps" or Views included in the Visualisation Tools allow you to identify cell roles, structure and anomalies within a worksheet.

Excel Formula Map Tools

Each of the Views is activated by toggling its corresponding button: Formula, Dependents, Function or Heatmap. Additionally the original formatting can be restored using the Restore button if the Backup checkbox was checked prior to selecting a new View.

Restore Original

Spreadsheet Studio allows users to backup and restore the worksheet's original formatting. This allows you to seamlessly switch between various Views and back again. The backup functionality can be enabled by checking the Backup checkbox prior to switching to a new view. If the Backup check was unchecked, the worksheet will retain its new formatting.

Original View

The worksheet image on the left is used to demonstrate each of the available Excel Formula Maps described below. If the Backup checkbox was checked, the original formatting can be recovered after applying a new View by clicking the Restore button.

View 1: Formula Consistency

The Formula Consistency View shades all cells with the same formulae using the same colour. The Formula Consistency View is sometimes referred to as an Audit Formula Map.

Consistent Formula View

Using the Formula Consistency View, inconsistencies can be easily identified where formulae are expected to be consistent but the resulting cell shading is not.

View 2: Dependency

The Dependency View sets out each cell's relationship with other cells on the sheet and in the rest of the workbook.

To identify data flow, the Dependency View assesses how the value of each populated cell is used and divides them into four categories, according to whether the cell has:

  • [abbr title="A formula on another sheet links directly to the cell"] Direct External Dependents[/abbr]: Red
  • [abbr title="A local cell with direct or indirect external dependency links to the cell"] Indirect External Dependents[/abbr]: Orange
  • [abbr title="Dependent cells located only on the same same sheet"]Local Dependents Only[/abbr]: Yellow
  • No Dependents: White

The Dependency View can be used to effectively test whether:

inputs and formulae flow off a worksheet as expected;data can be safely removed from a workbook without affecting dependent calculations.

Dependents View

The Dependency View has particular application in checking that inputs and assumptions in financial and valuation models correctly flow off a worksheet to the financial statements and valuation calculations that are often located on a different sheet.

View 3: Heatmap

The Heatmap View shades cells based on the number of cells linking to them from within the worksheet.

Heatmap View

Neighbouring cells usually have the same number of dependents. The Heatmap View shows you where the number of dependents differs, helping you identify potential linking errors.

View 4: Functional

The Functional View, sometimes referred to as the Inputs and Function View, applies a widely accepted colouring convention where inputs are coloured blue and formulae are coloured black. This can be used to:

  • Automatically apply or reapply the convention to your own workbooks; and
  • Present a workbook you have received in a convention you are familiar with.

Inputs and Functions View

Applying the Functional View will apply font colouring convention that you and others recognise.