Why Risk Validation?
Excel spreadsheets are a great tool for anything from financial reports to data analysis to simple one-off calculations. According to research, about 90% of all companies use Excel. Ever wondered how many of all those spreadsheets contain mistakes? According to other research, pretty much all of them. While Excel is at its core a user friendly tool for spreadsheets, the accessibility of Excel also leaves a large margin for human error. When your company is fully reliant on Excel for its financial management, this margin for error is something you really can’t afford.
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 are default Microsoft Excel error messages as a result of formula mistakes. Dysfunctional formulas are usually unintentional. Excel’s error messages are #DIV/0! (trying to divide by 0), #N/A! (a formula or a function inside of a formula that is unable to find the referenced data), #NAME? (unrecognized text within the formulas), #NULL! (usage of spaces in formulas that reference multiple ranges), #NUM! (a formula with an invalid numeric data for the type of operation), #REF! (invalid references) and #VALUE! (usage of the wrong type of operand or function argument).
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
Whenever the last argument in a VLOOKUP or HLOOKUP is omitted or set to TRUE, the result could end up being an unexpected match. When the lookup range is sorted, either an exact or an approximate match is returned. If the range isn’t sorted, the match is always an approximate. You probably only want to see exact matches.
4. Fixed Numbers
A fixed number is a number that is typed directly inside a formula, instead of using a reference to another cell containing that number. Changing numbers in a later stage creates the risk of one or more numbers being accidentally skipped. Finding each and every instance of a fixed number can be really difficult. So instead of fixed numbers, use references!
5. Second Degree Excel Error
Second Degree Excel Errors are standard Excel errors (e.g. #DIV/0! and #NAME?) that result from other Excel errors. Second degree errors are an extra incentive to fix the errors that caused them.
6. Sums Including Subtotals
A sum that includes both data and subtotals usually generates a faulty outcome. Some of the numbers end up being summed up twice. To solve this problem, try removing subtotals or consider using a separate column for subtotals.
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.
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.
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.
10. Interrupted Formula Range
A range of formulas is usually uninterrupted, as a column is typically designed to perform all equal types of calculations. Formulas that differ from the ones above and below (or to the left and right) are considered unusual. Unusual formulas usually imply some type risk.
11. Referencing Merged Cells
Formula references to merged cells are problematic. A group of merged cells has multiple references, but only one of these is actually the right reference.
12. Unexpected Range
Unexpected ranges are ranges (e.g. number of cells > 4) that are referenced by a function that actually require larger ranges within the sheet. If the length of a table is shorter than the reference in the formula cell, chances are that the reference is outdated, or is simply chosen incorrectly. Note that for the detection of unexpected ranges, we currently do not support ranges with a size of 50 cells or over.
13. Referencing Empty Cells
A formula with a reference to an empty cell is either meaningless or a mistake. You should either remove the reference or replace it with the intended location.
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 chains refer to relationships between two worksheets that go back and forth, or to circular references between three or more worksheets. Situations like these heighten the risk for chaotic workbook structures due to its complexity. Circle chains are usually not necessary.
16. Duplicated Formula
Whenever the exact same formula with the same (absolute) references occurs in more than one cell, it is flagged as a duplicated calculation. This increases the risk of mistakes in future changes of the spreadsheet. For instance, you might forget to adapt either one of the identical formulas. When a formula contains neither functions (e.g. SUM or PRODUCT) nor operators (e.g. + and *), identical formulas are not considered a duplicated calculation.
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.
18. Data Clone
Large chunks of cells that are copied and pasted (almost) unaltered usually indicate a bad design of your spreadsheet model.
19. Hidden Formulas
When a cell is formatted to hide its formula in a protected sheet, it is unclear to the user what will happen in the spreadsheet when values, formulas or other parts of the spreadsheet are changed. Also, unintentionally ignoring hidden information while working with the spreadsheet might cause problems later on.
20. Hidden Columns or Rows
Like hidden formulas, hidden columns may obscure the way a spreadsheet works. In contrast to hidden formulas, columns can be hidden even when the sheet is not protected. As with hidden formulas, hidden information might end up being ignored.
Like hidden formulas, hidden rows may obscure the way a spreadsheet works. And just like columns, rows can be hidden even when the sheet is not protected. Again, hiding information could cause problems in the future.
21. Hidden Worksheets
Hidden worksheets may conceal information that is essential for understanding how the spreadsheet works. As with the earlier examples of hidden formulas, columns and rows, hiding worksheets could end up obscuring relevant information for future users.
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.