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
Internal structure check
Check for 18 types of risk
Scan formulas and constants
Share and review results
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.
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.
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.
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.
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 .
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.
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.
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.
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.
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.
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.
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.
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
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.
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