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.

Comment Browser

The Comment Browser allows you and your team to intuitively make notes and keep track of your ideas within the spreadsheet you are working on. The Comment Browser can be used to have complex discussions between multiple workbook users or to simply track your own work and manage your 'to-do' list.

The Comment Browser facilitates:

  • Task management; and
  • Information transfer between users

Comment Browser: A Guide

Comment Browser

The Comment Browser can be accessed through the collaboration group on the studio ribbon tab.

The Comment Browser displays Standard Excel Comments with the yellow background you are familiar with. Spreadsheet Studio also introduces and manages Advanced Excel Comments, displayed with a blue background.

Advanced Excel Comments

The blue Advanced Excel Comments introduce many powerful tracking features not available when using yellow Standard Excel Comments. To see our comparison between Standard and Advanced Excel Comments, click here.

Advanced Excel Comments can be differentiated from Standard Excel Comments by their light blue colour and informative layout.

Creating Advanced Excel Comments
Comment Browser Settings

To insert Advanced Excel Comments by default, set the "New Comment Format", under Settings, to Advanced.

Creating a Comment
Advanced Comment

Advanced Comment

When set as the default new comment type, Advanced Excel Comments can be inserted by right clicking a cell and selecting 'Insert Comment'. If not set as the default comment type, Advanced Excel Comments can be added by clicking the 'New Adv. Comment' button on the Studio tab.

Creating an Advanced Excel Comment requires you to assign it a category, provide comment text and, if you wish the Advanced Excel Comment to be a task, allocate a priority level.

Categorisation allows you to group your comments. You choose the category name relevant to your project, helping you to sort and find comments.

Advanced Excel Comment

Comment Text
Much like Standard Excel Comments, Advanced Excel Comments include text. This text should be typed in the comment box.
Giving a priority to a comment, using the slider, create a new actionable task. Tasks can be assigned to a user, under the Advanced Tab. Three priority levels are defined: Low, Medium and High.
Save Cell Value with Comment
An advanced option allows the original cell value to be saved as part of the Advanced Excel Comment. This provides context to the Advanced Excel Comment for the benefit of future readers. For example, a comment may become outdated as a result of subsequent modifications to the workbook. Providing the original value as part of the Advanced Excel Comment aims to avoid confusion.

Viewing Comments
Advanced Excel Comment Appearance
Advanced Excel Comments appear in your spreadsheet in the same way as Standard Excel Comments except they are blue and have richly formatted text elements. The text elements include the category, comment, task status, author, allocation and creation date.

Responding to Advanced Excel Comments

Two-Way Messaging

The Comment Browser lets you interact with team members through Advanced Excel Comments. Just like an email thread, an Advanced Excel Comment is displayed in the Comment Browser Task Pane with responses shown below. As team members reply to replies, you will see a free flowing conversation develop. This is useful to both the process of collaboration and review, and to creating a useful audit trail.

Replying to an Advanced Excel Comment is simple. Right click on the comment in the Comment Browser Task Pane, and select reply. Replying then follows the same process as creating an Advanced Excel Comment, you can even assign a task as part of the reply.

By double clicking on a conversation stack, it can be collapsed to free up screen real estate. A collapsed conversation stack can be identified by the shadow. To expand, simply double click on the thread again.

Advanced Functions

Comment Info

The Comment Browser also lets you:

  • Show a task as completed - just click the checkbox to the left of an Advanced Comment in the Comment Browser Task Pane;
  • Delete a comment - right click on a comment and select Delete;
  • Edit an Advanced Excel Comment or an entire thread; and
  • Inspect metadata associated with an Advanced Excel Comment. The metadata includes the original value (as described above) and the date and time of the comment's creation. Again, just right click on an Advanced Excel Comment and select Comment Info.

Quick Access Buttons

Quick Access Buttons

The Comment Browser has six Quick Access Buttons to help you quickly change the Comment Browser's settings:

  1. Lets you switch between displaying (i) all the comments on the current worksheet and (ii) all the comments in a workbook in the Comment Browser's Task Pane.
  2. Lets you hide completed tasks, so only outstanding tasks are shown in the Comment Browser's Task Pane.
  3. Allows you to view comments grouped by worksheet, status, allocation, category and date. You can cycle through these alternatives using this Quick Access Button.
  4. Shows and hides responses to Advanced Excel Comments.
  5. Refreshes comments - useful when a cell with an attached comment is pasted into a new cell.
  6. Opens Settings, allowing access to Advanced Functions.


  • Advanced Excel Comments created with Comment Browser can be viewed in all versions of Excel
  • Advanced Excel Comments can be viewed without installing Spreadsheet Studio

You will need Spreadsheet Studio to successfully edit an Advanced Excel Comment.

Even if another user ignores the warning and edits the text displaying in an Advanced Excel Comments without Spreadsheet Studio, we have you covered. The next time the workbook is opened on a computer with Spreadsheet Studio installed, you will be presented with an option to undo any changes made to the Advanced Excel Comments. You will be able to review the changes made by the other user before undoing their changes.

Finally, Standard Excel Comments can be quickly and easily upgraded to Advanced Excel Comments. In the Comment Browser's Task Pane, right click on a Standard Excel Comment and select Upgrade.

Standard Comments: Smart Tags

The Comment Browser can also extend the functionality of Standard Excel Comments, without using Advanced Excel Comments.

Smart Tags are special text fields typed into the body of a Standard Excel Comment. The Comment Browser can read Smart Tags and lets you manage categories and tasks.

Category Smart Tags
You can include a category in a Standard Excel Comment by typing the name within Scottish brackets { }. As with Advanced Excel Comments, you may choose any category name. For example, the Comment Browser will recognise {Future Improvements} or {Model Errors} as categories.

Priority and Completion
You may assign a priority to a Standard Excel Comment by typing one of the following keywords within square brackets [ ]:

  • High Priority: High or Urgent
  • Normal Priority: Todo, Normal, Medium or Open
  • Low Priority: Low
  • Completed: Done or Closed, Reviewed, Complete, Ok

For example, either [High] or [Urgent] will be recognised as High Priority.

Standard Excel Comments are displayed as demonstrated in the following examples. We show Standard Excel Comments with and without Smart Tags as displayed in the Comment Browser.