At Infotron we have assembled a list of 25 risks that occur as a result of common practices in Excel spreadsheets. Thanks to our tight cooperation with clients and the Delft University of Technology spreadsheet lab, the number of risk types PerfectXL discovers continues to grow. Currently, 21 risks have already been implemented in our tool PerfectXL, as delineated below.
A fixed number is a number typed directly in a formula, rather than a reference to another cell containing the number. When numbers change in a later stage, the risk is that one or more numbers are accidentally skipped. Using references is also preferable to using fixed numbers for it is difficult to find all instances of a fixed number.
Standard Excel Errors
Default Microsoft Excel error messages as a result of formula mistakes is considered a risk, for a dysfunctional formula is usually unintentional. Excel’s errors are #DIV/0! (trying to divide by 0), #N/A! (a formula or a function inside a formula cannot find the referenced data), #NAME? (text in the formula is not recognized), #NULL! (a space was used in formulas that reference multiple ranges), #NUM! (a formula has invalid numeric data for the type of operation), #REF! (a reference is invalid), #VALUE! (the wrong type of operand or function argument is used).
Exactly the same formula with the same (absolute) references in more than one cell is flagged as a duplicated calculation. It increases the risk of mistakes in future changes or improvements if one forgets to adapt one of both. When a formula contains neither functions (e.g. SUM or PRODUCT) nor operators (e.g. + and *), identical formulas are not considered a duplicated calculation.
Interrupted formula range
It is hard to spot a value amidst a range of formulas.
Only when you look at the formulas does an interruption become apparant.
Usually a range of formulas is uninterrupted, because a column is typically designed to perform all equal types of calculations. When a formula differs from the ones above and below (or right and left), it is considered unusual thus risky.
A range (number of cells > 4) referenced by a function to a range that is larger in the sheet. If the length of a table is shorter than the reference in the formula cell, there is a great chance that the reference is outdated, or simply incorrectly chosen. Note that we currently do not support ranges with a size of 50 cells and over to detect unexpected ranges.
Circle chain of worksheets
Sheet1:B7 uses a value from Sheet3.
Sheet2:B5 uses a value from the first sheet.
Sheet3:D4 uses a value from the second sheet.
A to and fro relationship between two worksheets, or circular references between three or more worksheets. This might cause chaotic workbook structures due to its complexity. Circle chains are usually not necessary.