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.

Spreadsheet Visualization Tutorial :: PerfectXL

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.

Spreadsheet Visualization Tutorial - Options :: PerfectXL

A. Back to the full spreadsheet visualization; B. Place the cursor over a worksheet to see which other worksheets information is exchanged; C. Purple: formulas in the other sheet use cells from this sheet; D. Black: formulas in this sheet refer to cells in the other sheet.

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 Tutorial - Default View :: PerfectXL

The Default View instantly visualizes how worksheets interrelate (blue), which external sources are used (yellow) and which worksheets are hidden (grey and white).

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 Tutorial - Risks View :: PerfectXL

The risks view colours the worksheets after the highest risk detected in the sheet. This could reveal, for instance, that the source of a relatively safe sheet is actually quite risky. 

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 Tutorial - Input/Output View :: PerfectXL

In the Input/Output view, distinctive colors and arrows help to show precisely how information flows through the 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. 

Spreadsheet Visualization Tutorial - Data Count & Formulas View :: PerfectXL

In the Data Count & Formulas view, worksheets are colored according to the amount of information (=non-empty cells) they contain. How much information is relatively exchanged is indicated by the numbers and arrow thickness.

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.

Spreadsheet Visualization Tutorial - Data Count & Formulas View :: PerfectXL

Blue circles indicate the number of formulas between two worksheets. Click to reveal the formulas that constitute the connection. After that: click on a formula to inspect it more thoroughly. This brings you to the Formula Breakdown.

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.

Video Tutorial: Tracing Problems in Spreadsheet Calculations :: PerfectXL

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

Video Tutorial: How to Check Your Input Source in Excel :: PerfectXL

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

Video Tutorial: How to Quickly Validate Your Spreadsheet :: PerfectXL

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