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 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.
To activate Audit Mode, click the switch in the top right corner:
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
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.
To switch between different types of trend visualizations, click on the arrow:
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:
- It highlights errors and cells with circular references
- Trend lines help you visually identify unexpected spikes in values
- Precedents Panel displays the precedent cells that are causing the error in the currently selected cell.
- Error Root shows the root cause of the error propagation chain (the very first cell that causes the error in the current 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.
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.
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.
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.