Spreadsheet Visualization Tutorial
To understand how a spreadsheet works, you need to know about its structure. In this spreadsheet visualization tutorial we explain to you how to use the visualization feature of PerfectXL. With this feature you can reveal the structure easily yet very effectively.
How to Use the Visualization Feature
With PerfectXL, applying data visualization to your spreadsheets is easy. Just upload your spreadsheet to your account on PerfectXL, click ‘analyse’ and then the name of the spreadsheet to see the full analysis. Here you click the visualization tab in the navigation bar.
You can now easily navigate the visualization of your spreadsheet by zooming in or clicking on tiles representing worksheets to get a closer look. 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.
Detailed Visualization of a Worksheet
When you click on a tile representing a worksheet in the visualization of your spreadsheet, you’ll find another visualization of this specific worksheet and its relation(s) to other worksheets.
Types of Spreadsheet 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 29 types of risk that are commonly present when using Excel spreadsheets. These risks are all implemented in the risk detection feature of PerfectXL. With Risks View, your spreadsheet is mapped with the risk analysis feature, thus showing you what parts of your spreadsheet need to be examined.
Spreadsheet Visualization: Input/Output View
The input/output view shows you 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 (read our guideline ‘Separate Input & Output’!). 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 shows 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.
Watch our Video Tutorials
In the video’s below, several features and possibilities of PerfectXL are described and demonstrated thoroughly. In time we will add more video tutorials.
Tracing Problems in Calculations
When spreadsheets contain inconsistencies or suspected miscalculations, it takes great effort to find the problem manually. In this video we’ll show you how to use PerfectXL’s Formula Breakdown feature to locate and fix the problems in spreadsheet calculations within a matter of minutes.Learn how to trace problems in spreadsheet calculations
How to Check Your Input Sources in Excel
In this video we’ll show you how to run a quick basic check of the input sources of your spreadsheet and how to determine if there is any unknown input. We’ll also show you a powerful way to visualize your spreadsheet’s information flow to help determine the function and dependability of each worksheet.Learn how to check your input sources in Excel
How to Quickly Validate your Spreadsheet
88% of spreadsheets contain hidden issues. These issues can be a serious risk to the outcome of your spreadsheet. This video shows you how PerfectXL helps you to quickly validate your spreadsheet on 29 types of risk, without having to check every cell manually.Learn how to validate your spreadsheet