Try now!

Validate your spreadsheet for free



Risk Validation

Risk Validation is a feature of PerfectXL that lets you automatically inspect your spreadsheet for any types of risk. The feature produces a list of all risks found within the spreadsheet, along with locations of all the cells where these risks are present. PerfectXL currently covers about 21 distinct risk types.

Why Risk Validation?

risks-output

Risk Validation is a way to inspect your spreadsheets for any mistakes. Risk Validation doesn’t actually correct these errors, but rather examines parts of your spreadsheet that are similar to some of the most commonly made mistakes with Excel. With Risk Validation, PerfectXL basically points to these parts of your spreadsheet and asks you a question: “Are you really sure about this?”

In short, Risk Validation seeks out all parts of your spreadsheet that could pose a risk for error, allowing you to improve your spreadsheet if needed.

How Does It Work?

After uploading your spreadsheet to PerfectXL, pressing the tab button “Risks & Improve” shows you a summary of all the types of risk that were recognized by Risk Validation. The summary provides detail per type of risk, such as how many times a certain risk type was found, a description of the mentioned type of risk and whether you’re dealing with a high or low risk type.

Risk Validation seeks out parts of your spreadsheet that could pose a risk for error.

Next to the tab button “Summary” is the tab button “All Risk & Improve”. By pressing on this button, the summary is broken down in all individual instances of risk. All instances are provided with the location within your spreadsheet, a description of what type of risk was found and what formula is involved.

So, How Many Types of Risk Are Out There?

At Infotron, we’ve currently assembled a list of 25 types of risk that are common when using Excel spreadsheets. 21 of these risk types are covered by our risk validation tool. Identifying risks is an ongoing process. Not only are we going to implement the remaining seven types of risk into PerfectXL, but we’re also involved in research to any other risk types. For this research, we’re in collaboration with both our clients and the Spreadsheet Lab of the University of Delft.

With Risk Validation, PerfectXL points to parts of your spreadsheet and asks you a question: “Are you really sure about this?”

21 Types of Risk

We have categorized the 21 types of risk into 5 risk categories:

PerfectXL Risk Validation Tool covers all these 21 risks.

Wrong Use of Formulas and Operators

1. Standard Excel Errors

Standard Excel Errors
 

2. Double Operator Signs

A formula that contains two operators next to each other is hard to detect, as these formulas won’t show up as Excel errors. A classic example of this is a double hyphen (–), which equates to a plus (+) operator yet looks like a minus sign (-).

3. Approximate Lookup

Approximate Lookup
 

4. Fixed Numbers

Fixed Numbers
 

5. Second Degree Excel Error

Second Degree Excel Error
 

6. Sums Including Subtotals

Sum Includes Subtotals
 

Complexity Issues

7. Referencing Many Different Cell Groups

A formula that mentions too many cell groups poses a risk. You could either unintentionally skip over one of the references, or you might end including one to many. Too many references also make the formula less readable.

Referencing Many Different Cell Groups

8. Conditional Complexity

Conditional complexity occurs when too many nested IF operations exist in one formula. This makes the formula harder to read, leading to more risks.

Conditional Complexity

9. Many Different Operations

Too many operations in one formula make the formula unreadable. At worst, this means the spreadsheet can only be used by the original author.

Many Different Operations

Range Issues

10. Interrupted Formula Range

Interrupted Formula Range
 

11. Referencing Merged Cells

(Referencing) Merged Cells
 

12. Unexpected Range

Unexpected Range
 

13. Referencing Empty Cells

Referencing Empty Cells
 

14. Merged Cells

It is not obvious how to correctly refer merged cells which contain a formula or other value. Problems with adding or deleting rows or columns may also occur.

Problems with the Structure

15. Circle Chain of Worksheets

Circle Chain of Worksheets
 

16. Duplicated Formula

Duplicated Formula
 

17. Incorrect Formula Location

A formula with too many references to another worksheet makes the formula unreadable, as the values that are relevant for the formula end up being unclear.

Incorrect Formula Location

18. Data Clone

Large chunks of cells that are copied and pasted (almost) unaltered usually indicate a bad design of your spreadsheet model.

Hidden Information

19. Hidden Formulas

Hidden Formulas
 

20. Hidden Columns or Rows

Hidden Rows or Columns
 

21. Hidden Worksheets

Hidden Worksheets
 

22. References to Hidden Data

If the referred data of a formula are not visible, it is hard to check if the formula is correct.