PerfectXL Data Visualization Tool
Visualization is an important tool when it comes to spreadsheet validation.
Through data visualization, PerfectXL shows you how all worksheets, input, calculations, formulas and output within your spreadsheet are related to each other.
The spreadsheet is translated into a schematic representation, in which the flow of information is indicated by of arrows and colour coded labels. PerfectXL makes use of different types of data visualization, with every type examining different aspects of your spreadsheet.
Why Spreadsheet Visualization?
An elaborate spreadsheet with a large amount of worksheets can be quite complicated. It usually takes some effort for anyone unfamiliar with the spreadsheet to fully understand how it is set up. Working with spreadsheets you haven’t worked with before is like trying to find your way through a disorienting maze. In order to navigate a maze, you need a map. And that’s where data visualization comes in.
With the visualization of your worksheets, you can spot at a glance how all worksheets of a spreadsheet are connected to each other or to external sources. It also shows in what way input, calculative sheets and output sheets are separated.
Normally it would take hours to explain your spreadsheet to other users. With proper spreadsheet visualization, it takes mere minutes.
One practical purpose for visualization is that it allows for quick checkups of the spreadsheet. Any mistakes, like omitted worksheets or the lack of a proper separation between input and calculation sheets, are at first glance noticeable in a visualization of the worksheets in your spreadsheet.
Another reason to use visualization has to do with accessibility for other users. Suppose you’ve been working on one particularly complicated spreadsheet. You’ve pretty much been working on it solo. A deadline is due and the spreadsheet needs to be updated. However, there is no way you’ll be able to do it, due to pressing matters elsewhere. The only way the spreadsheet is going to be updated in time is if a colleague is going to fill in for you. But save for yourself, no one at the office is familiar with your spreadsheet.
In order to navigate a maze, you need a map. And that’s where visualization of the worksheets comes in handy.
Normally it would take hours to explain your spreadsheet to other users. With proper visualization, it takes mere minutes. By using a clear overview of the structure of your spreadsheet, your colleague can now easily figure out the inner workings of the spreadsheet and provide those necessary updates.
In short, visualization is a tool that allows you to efficiently examine spreadsheets.
How to Use the Visualization Tool
With PerfectXL, applying data visualization to your spreadsheets is easy. Just upload your spreadsheet to your account on PerfectXL and click the tab button for visualization. You can now easily navigate the visualization of your spreadsheet by zooming in or clicking on tiles representing worksheets to get a closer look of said worksheet. Hovering the cursor over a tile lets you see to which other worksheets the current sheet is exchanging information. In a similar fashion, you can now easily examine current calculations and formulas by clicking arrows that represent them.
Types of Visualization
PerfectXL provides four types of data visualization:
- Default View
- Risks View
- Input/Output View
- Data Count & Formulas View
Spreadsheet Visualization: Default View
The default view shows clearly how all worksheets within the spreadsheet are connected, what external sources are used and which worksheets remain hidden (which we advise you to avoid, read our guideline!) In this way, you get a basic view of the current structure of the spreadsheet.
In short, visualization of the worksheets is a tool that allows you to efficiently examine spreadsheets.
Spreadsheet Visualization: Risks View
The risks view marks worksheets with colours, indicating which sheets contain the most amount of risk. At Infotron we have assembled a list of 25 types of risk that are commonly present when using Excel spreadsheets. 18 of these risks are currently implemented in the risk validation tool of PerfectXL. With Risks View, your spreadsheet is mapped with the risk validation tool, thus showing you what parts of your spreadsheet need to be examined. See Risk Validation to learn more about the types of risk we’ve identified and how to deal with them.
Spreadsheet Visualization: Input/Output View
The input/output view shows how input, calculation and output sheets are currently separated within the spreadsheet. A solid Excel spreadsheet has a clear separation of these different types of worksheets. With Input/Output View, arrows and different colour labels for input and output sheets explain how information currently flows through your spreadsheet.
Spreadsheet Visualization: Data Count & Formulas View
The data count & formulas view show how much information (i.e. cells that aren’t empty) is currently contained per worksheet. The amount of information exchange between worksheets is represented by numerical values and the size of the arrows. In some cases, you might see a connection between sheets you wouldn’t expect. For example, you might encounter an input sheet that references directly to an output sheet. In this case, you’d need an overview of what types of formulas are being used. Data Count & Formulas View provides a list of all formulas that are currently being used within the spreadsheet.