Your spreadsheet validation starts here

PerfectXL is a state of the art web tool, developed by Delft University of Technology and Infotron. With this tool, checking the quality of spreadsheets becomes a breeze. Upload one or more spreadsheets, look at the visualisations and analysis results and draw your conclusions. Support your conclusions with easy-to-create reports.

4 essential validations with PerfectXL

Validation #1

Validation #2

Validation #3

Validation #4

Visualisatie

Internal structure check

18 risicos

Check for 18 types of risk

Formules

Scan formulas and constants

PDF rapport

Share and review results

Validation #1

Internal structure check

Visualise references and sources

The worksheets in many spreadsheets are connected like plate of spaghetti. With PerfectXL, you can spot at a glance how worksheets are connected to each other and to external sources. The structure of the spaghetti will be revealed.

Screen Shot 2015-09-21 at 15.20.19

Input, calculations, output

A well-designed Excel spreadsheet maintains a clear separation between input, calculative, and output sheets. The input-output visualisation in PerfectXL shows you whether this separation has been applied. Arrows and colours clarify the routing of information. Also the size of a sheet and the number of references to and from other worksheets are made clear.

Screen Shot 2015-10-04 at 21.06.27

Use of information and formulas

PerfectXL allows you to create a copy of the original spreadsheet which highlight formulas, text and numbers. The existing formatting will be replaced by a colour mapping for different cell types. Unexpected disruptions in patterns are thus quickly spotted.

mapping-type-short

Internal and external references

PerfectXL creates visualisations ("mappings") of worksheets where colours differentiate between references within the sheet, references to other sheets and references to outside the spreadsheet. This way, you can regocnise with ease where a worksheet gets its important information from. Additionally, you can see which input data are used nowhere else in the spreadsheet.

mapping-flow-short

Validation #2

Complete check for 18 types of risk

PerfectXL detects up to 18 types of risk in spreadsheets. Each of these risks can be marked a low risk , a medium risk  or a high risk .

  • Approximate lookup

    When the final function paramter in a VLOOKUP or an HLOOKUP is either omitted or set to TRUE, the function returns incrorrect values for unsorted ranges.

  • Hidden formula

    These make it hard for users to understand a sheet and oversee the results of changes they make.

  • Referencing many cell groups

    When too many cell groups are referred in one formula, one may easily forget one reference or another or include one too many.

  • Circle chain of worksheets

    A circular reference between two or more worksheets results in a chaotic workbook structure.

  • Hidden or very hidden sheets

    These make it hard for users to understand the spreadsheet and oversee the information flow and results of changes they make.

  • (Referencing) merged cells

    Merged cells pose a risk to becoming incorrectly referenced or deleted, and make inserting additional rows or columns more complex.

  • Conditional complexity

    Formulas with too many IF operations are hard to read and hard to check.

  • Hidden rows or columns

    These make it hard for users to follow or change a series of steps of a calculation or to follow a timeline development.

  • Second degree error

    An Excel error, caused by a standard Excel error in another cell, is an extra reason to fix the first one.

  • Double operator

    A formula with two operators next to each other is often undesired. It gives no MS Excel error, but may result in opposite calculations.

  • Incorrect formula location

    A formula with too many references to one other worksheet makes the formula unreadable, because the values it depends on are not clear.

  • Standard Excel error

    Dysfunctional formulas are usually unintentional. Moreover they make a spreadsheet difficult to read.

  • Duplicated formula

    Formulas with the exact same absolute references increase the risk of mistakes in the future. One may easily forget to replace one of the two or more.

  • Many different operations

    Too many operations in one formula make the formula unreadable, incomprehensible and non-transferable.

  • Sum includes subtotals

    A (sub)total/sum that includes both data and subtotals/sums of the same data generally results in a wrong outcome.

  • Fixed number

    Numbers typed directly into a formula are risky if they have to change or to be checked. One may easily forget to adjust one.

  • Referencing empty cells

    Reference to an empty cell are either meaningless or a mistake.

  • Unexpected range

    If the length of a table different from the range of the formula, there is a great chance that the reference is outdated, or simply incorrectly chosen.

Validation #3

Scan of formulas and constants

Formulas and constants

Sometimes it’s useful to have an overview of all the formulas in a spreadsheet. What does this spreadsheet all caculate?

Likewise, sometimes it is required to have an overview of all numbers, constant values, which are being used in formulas throughout the spreadsheet. For example, such overview makes it possbile to quickly check all the largest figures, or alternatively check very specific numbers, like a VAT percentage value.

Output web tool in Excel

PerfectXL supports two methods for looking into formulas and constants. They can be viewed in the web tool itself, or they can be exported into an extra worksheet added to the original spreadsheet. In this export, clicking a formula brings you to the location where it was originally found.

Validation #4

Sharing and reviewing results

Within PerfectXL, checking your spreadsheet for structure, possible risks, formulas and constants takes little effort. But PerfectXL helps you beyond that: for instance, you can download the original spreadsheet supplemented with useful information for spreadsheet builders, such as a list of risks directly linked to their respective locations in the spreadsheet. Or you could download a PDF, to share the results with colleagues.

Summary and extended PDF report

With the PDF export in PerfectXL you can effortlessly share with colleagues your findings regarding complexity and riskiness. You can choose between a summary or an extended report. The most important visualisation are present in the PDF as well.

Useful Excel exports

PerfectXL gves you two options for exporting back to Excel. One option is to download a copy of the original spreadsheet that is supplemented with a new sheet containing a list of found risks, formulas or constants. This list contains hyperlinks to the respective locations in the spreadsheet. This allows you to easily apply PerfectXL’s advice in your own spreadsheets.

Another option is to create so-called mappings of a spreadsheet. This option also downloads a copy of the spreadsheet, but overwrites the formatting of a worksheet of choice with colour codings for formulas, text fields, numbers, and so forth. This exposes the internal structure of the information in the worksheet.

Experts are convinced

"PerfectXL is an excellent tool for doing spreadsheet risk analysis and for finding problems in your Excel model."

Jan Karel Pieterse, MVP Excel Expert