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

How Visyond automates Spreadsheet Audit and Debugging

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

When in Audit Mode, Visyond will automatically:

  • Detect the very first error cells that cause all the other errors in your spreadsheet
  • Detect cells that have pattern anomalies compared to their adjacent cells, indicating a potential human error
  • Show you the type of cells so you can spot architectural glitches
  • Help you maintain consistent formatting of data
  • Provide better readability of your spreadsheet

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

Activate Audit Mode

 

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

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 but, ultimately, A4 is the first cell that causes the propagation of errors

 

If you have a larger model it is difficult and time consuming to track the root cause of errors by navigating through the precedent cells that have errors.

Visyond offers a convenient Error Root panel that, when there are errors, shows the root cause for that cell. In this example, as shown below, we immediately know that cell A9 (the selected cell) is an error because its precedent A8 is an error and, more importantly, because cell A4 is the error root cause of the recalculation chain. So we do not need to check all the precedent cells and can directly fix the error in A4. This will solve all the errors (including the ones in A8 and its precedents).

 

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

 

If there are multiple cells that initiate error propagation, you can see them all by using the Report / Overview tab. You can click on them to find them in the spreadsheet. 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

 

Pattern Anomaly Detection

Cells with orange border indicate potential anomalies based on the type and formula patterns 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 number of elements

e.g., =3 + SUM()  vs  =3 + SUM() + 5

– Adjacent formulas have different types of elements in the same position

e.g., =3 + SUM()  vs  =ABS() + 5

– Adjacent formulas have same structure but different functions in corresponding positions

e.g., =3 + SUM()  vs  =3 + ABS()

– Adjacent formulas have different numbers in the same position

e.g., =3 + SUM()  vs  =5 + SUM()

– Adjacent formulas have different text in the same position

e.g.,  =CONCAT('Revenues', B3)  vs  CONCAT('Rev', B3)

Dollar sign mismatch in adjacent cells

e.g., B1 = $A1  vs  C1 = A$1

Unexpected row number given the cell projection pattern in adjacent cells

e.g., B1 = A$1  vs  B2 = A$2 : with dollar sign on the row number we do not expect A$1 to become A$2

– Unexpected column number given the cell projection pattern in adjacent cells

e.g.,  B1 = $A1  vs  C1 = $B1  : with dollar sign on the column number we do not expect $A1 to become $B1

– Some adjacent cells have a different value type

e.g., A1 = 'Revenues' vs  B1 = 5
Formula vs non-formula discontinuity
e.g.,  A1 = 'Revenues'  vs  B1 = NPV()

You can switch anomaly detection on/off and change the sensitivity to pattern variations also from the Settings Panel.

Each type of anomaly can be due ti a pattern discontinuity in any direction.

Each  type of anomaly has a different severity. For example, it is usually normal to have projected formulas in a column and, at the very bottom, a different formula which sums all the cells above. However, if this is the case, Visyond expects that the other adjacent cells of the bottom summation cell have the same type of formula. If this is the case, the discontinuity is not marked as potential anomaly unless we set the sensitivity slider to its maximum level.

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

 

Precedents and Dependents Navigation

Visyond helps navigate 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 also rotate through all precedents and dependents.

 

how to
January 28, 2020

Was that post helpful?

Related Articles