Abacus by Schott // PerfectXL Spreadsheet Validation

PerfectXL Explore

Check the input sources of your Excel files

In this video we’ll show you how to run a quick basic check of the input sources of your Excel files 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.

Case: are the spreadsheet results based on complete and up-to-date information?

Say you’re an investment manager at a large corporation and you receive a spreadsheet in preparation for a potentially big investment.

For a proper assessment, it is vital to know where the spreadsheet gets its input from. In particular, you want to know that the spreadsheet results are based on complete and up-to-date information. We’ll use PerfectXL to verify this.

Input/Output Visualization

Logged in, we drag the spreadsheet in for analysis. Once it has finished, we jump straight to the Input/Output visualization. This shows all the worksheets in the spreadsheet. Arrows indicate where sheets get their input from. Sheets marked in black are solely used as input, so in the blink of an eye we can see that all input sheets are used in calculations.

If you wanted to go into further detail, visit the “constants” page to reveal a list of all the numbers defined in the spreadsheet that are used by formulas.

Visualization of input sources en output sources of your Excel file // PerfectXL Explore

Check for Input of External Sources

So far we’ve only looked at input from within the spreadsheet. How about external sources? We’ll go to the Characteristics section of the analysis, where at the bottom we instantly note that there’s important information coming from another spreadsheet. Curiously, we only ever got one spreadsheet. Worth to check with the provider of the Excel file if the information is really complete.

Summary

As you can see, in just over a minute we’ve been able to do a basic check of input sources and determined that part of the spreadsheet’s input is unknown, because we are missing an external source. We’ve also seen a powerful way to visualize the information flow in your spreadsheet to help determine the function and dependability of each worksheet.

  • It’s fast & easy to use
  • Get detailed insights
  • Save frustration and time

Read more about exploring Excel files

On this page you will find a step-by-step walkthrough of the PerfectXL Explore Tool. This page will give you information on how you can use explore to gain a better understanding of complex Excel files.

This is the technical page for PerfectXL Explore. Here you will find information like system requirements, release notes, and answers to most questions about our software. If you have more questions please use the form at the bottom of the page.

When we read a book or a report or a website, we read from top to bottom and from left to right. Keep this in mind when you create an Excel file. Try your best to follow that structure both within each worksheet and between them. We advise you to distinguish separate input & output sheets as well as sheets for calculations, documentation and dashboards.

Numbers without meaning are worthless. Excel is a program for numbers, but it is only useful if it is clear to the reader or user what these numbers mean. So, make sure that you provide textual information for all data and formulas, let’s call these pieces of text information labels.

Treat transferring a spreadsheet like you would treat the process of renting your house via Airbnb. Most people realize it will stay neater if you clean it first. Mess stimulates mess, and neatness stimulates neatness. Remember that in an average spreadsheet there will be 12 different users that have to use it after you! What do you think happens when this chain starts with a messy file?

Garbage in, garbage out. So pay serious attention to cleaning your input data as it is the foundation of your spreadsheet and if possible, do it in such a way that it becomes an automated process for the next time you use the same spreadsheet.

Understanding the workings of a spreadsheet is key to correctly interpreting its results. Spreadsheet visualization is an essential feature of PerfectXL Explore. It allows you to create a graphical model of the general flow of information, which also shows of risks, input & output and data & formula flows.

Curious to see what your spreadsheet’s all about? PerfectXL Explore gives you a great starting point. An overview of worksheets, input sources and potential risks are all part of our general spreadsheet characteristics section. It also gives you an insight into the amount of formulas used in your spreadsheet.

Be honest with yourself, how much time does it take you to spit through an Excel file? The opportunities in Excel are endless but this comes with a downside. PerfectXL has the solution. Starting now you can gain a better understanding of your spreadsheets, to help you in improving, documenting, and cleaning up your files. Here are 9 tasks that PerfectXL Explore makes easy.

How can PerfectXL help you?

Share your questions about our products. We will be
in touch with you in the next 48 hours.