Visyond Help Home
  1. Home /
  2. I would like to... /
  3. Audit or debug the spreadsheet /
  4. How to Audit Spreadsheets in Visyond
  1. Home /
  2. Tips and shortcuts /
  3. How to Audit Spreadsheets in Visyond
How to Audit Spreadsheets in Visyond

How to Audit Spreadsheets in Visyond

Visyond is designed to drastically cut the time and effort involved in auditing and debugging spreadsheets.

If you would like to audit an Excel spreadsheet, please visit this article to learn how to import your Excel spreadsheet into Visyond.

Audit Mode

Audit Mode provides a set of tools to automatically detect root causes of errors in your spreadsheet, help you maintain consistent formatting of data, and provide better readability of your models.

We recommend that you keep the Audit Mode on to get a bird’s eye view of the model structure, row trends and potential errors.

Audit Mode on

 

To activate Audit Mode, click the switch in the top right corner:

Activate Audit Mode

Auto-formatting

It is useful to color code cells to be able to assess spreadsheet structure at a glance. Professional modelers, format at least input cells in a different way.

Visyond helps you understand whether a cell contains a number, a string, a date or a formula by auto-formatting them when Audit Mode is on, and automatically changes these formats based on the cell content.

A client once complained that after importing an Excel file in Visyond, many cells were marked as errors. The reason was that after passing so many hands, someone deleted a worksheet, invalidating the references to many formulas. With Visyond, you can immediately uncover these kinds of issues.

Audit Mode is turned on by default when you create a new project. If you are importing from Excel, it is turned off so you can see the same formatting you had in your Excel file.

In Audit Mode, each cell looks different based on the type of data it contains:

  • White cells contain formulas
  • Yellow cells contain hard coded values, font color is used to distinguish between diferent types of data:
    • Blue: numbers or percentages
    • Green: dates
    • Dark-red: strings
  • Cells with red borders and fonts indicate wrong or incomplete formulas, or other function-specific errors
Different data types

 

Trend lines

When in Audit Mode, Visyond automatically creates trend lines to show you visually whether there are any unexpected spikes on that row. This way, you don’t have to inspect numbers one by one or chart them to identify inconsistencies.

Trendlines

 

To switch between different types of trend visualizations, click on the arrow:

Trends visualizations

 

Anomaly Detection

Cells with orange border and a red dot indicate potential anomalies based on the types and formulas of adjacent cells.

For example, as shown in the image below:

  • All the formula cells have been projected started from B1 = A1+1, so B2 =A2+1, C1 = B1 + 1, and so on
  • Cell C2 = $B2+1 has been accidentally altered and all surrounding cells detect that the projection pattern has been broken

Visyond detects the following anomalies:

  • Adjacent formulas have different size
  • Adjacent formulas have different structure
  • Adjacent formulas have different functions in the same position
  • Adjacent formulas have different numbers in the same position
  • Adjacent formulas have different text in the same position
  • Dollar sign mismatch in adjacent cells
  • Unexpected row number given the pattern in adjacent cells
  • Unexpected column number given the pattern signs in adjacent cells
  • Some adjacent cells have a different value type (e.g., string vs number
  • Some adjacent cells are of a different type (e.g., formula vs hard coded)

You can switch anomaly detection on/off from the Settings Panel.

Error Root Cause Analysis

When in Audit Mode, Visyond offers a simple way to track errors and their root cause:

  1. It highlights errors and cells with circular references
  2. Trend lines help you visually identify unexpected spikes in values
  3. Precedents Panel displays the precedent cells that are causing the error in the currently selected cell.
  4. Error Root shows the root cause of the error propagation chain (the very first cell that causes the error in the current cell)

    Precedents and dependents tracking when cell B2 is an error cell

In the example below, we see that cell A9 is an error cell and has a precedent cell, A8, that is an error cell too. You can click on A8 and check which one of its precedents are also errors, and so on until you find the root cause – the first cell causing error propagation throughout the model.

Error in A9 is caused by A8 – a precedent cell

 

If you have a larger model it is difficult to track the root cause of errors, so Visyond offers a convenient Error Root panel. When there are errors among a cell’s precedents, the Error Root panel appears and shows the root cause for that cell. In this case, as shown below, we immediately know that cell A9 (the selected cell) is an error because cell A4 is an error. So we can fix the error in A4 and check again until no errors are left.

 

Error Root button indicates that the root cause of error in A9 is cell A4

 

Visyond helps easily navigating the precedents and dependents of a cell. Just select a cell and click on their precedents or dependents listed at the bottom of the screen.

 

 

You can see a full list of all the error cells in the spreadsheet by using the Report / Overview tab. In this report you will also see any unused cells, cells that contain formulas with external sources and anomalies.

Error roots, external sources, and unused cells

 

 

Dependency Analysis

Read more about Visyond’s Dependency Analysis that can help you easily trace dependent and precedent cells in your spreadsheet and present a bird-eye view of your model, with the impact of each cell on its dependencies.

 

how to
July 4, 2019

Was that post helpful?

Related Articles