Microscope by Bennett // PerfectXL Spreadsheet Validation

PerfectXL Explore

Separate Input & Output

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.

The Rule of Thumb

Have you ever gotten a visit from an accountant? Did they check that one important spreadsheet that contains your investment plans? Maybe they asked something like: What are the input values? What assumptions are these calculations based on? Can you give an example of a calculation? Where are the conclusions?

This is just one of these moments in which you will benefit from a clear Excel model. The rule of thumb is: separate input & output. That’s the first major step to really improving readability and maintainability of any Excel file. When we zoom into this further, we see the following as the categories we would generally group worksheets in:

Input sheets

Use 1 table per worksheet in input sheets. Make a differentiation between dynamic data that is gathered from outside systems, for example imported data from a CRM system, and data that can be found within the spreadsheet, such as calculations. Place references as close to the data as possible and make them easy to read, by using a color coding for example. This will make your accountant very happy and will make your spreadsheets, in general, much easier to work with.

Calculation sheets

In the calculation worksheets you store information that doesn’t change on a regular basis. These worksheets are often somber and purely functional. The purpose of these sheets is to use input data to draw conclusions. Remember that a spreadsheet stays in use for an average of 5 years, so even in these sheets things can change, just make sure the formulas are easy to read and to adjust.

Output sheets

In the output sheets, we give an overview of the conclusions that can be drawn from the input data and calculations. Make sure you use tables, charts and nice layouts. Also think about making these worksheets printable by changing the margins. This is especially important for managers and decision makers.

Separate input & output sources on different worksheets in your Excel file // PerfectXL Explore

Documentation

There is one special category of worksheets meant for documentation. Use these sheets to explain the functionality of calculations and the source of input data. Other similar sheets to this are version control sheets and index sheets.

Dashboards

Some spreadsheet users love the use of so-called dashboards: worksheets that contain input, output and calculations together. While dashboards disrupt the flow of data, and the clear separation of input and output, they can be very insightful when modeling dynamic processes.

We do advise to minimize them, use a maximum of 1 or 2 (depending on the size of the spreadsheet) and make it clear that is concerns a dashboard worksheet.

Conclusion

It is important to recognize different roles a worksheet can play, and make deliberate choices when creating a spreadsheet. We often encounter mixed sheets that are messy and confusing.

Do you want to learn more about the flow of your worksheet? PerfectXL Explore helps you visualize the dependencies between worksheets in no time.

Visualization of the information flow of a spreadsheet // PerfectXL Explore

The image shows the visualization of a spreadsheet in PerfectXL.

  • 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.

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.

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.