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.

Fixed numbers

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).

Referencing many different cell groups

When too many cell groups are mentioned in one formula, one is likely to forget one reference or another or to include one too many. In addition, it makes the formula less readable and non-transferable.

Many different operations

Too many operations in one formula makes the formula unreadable, incomprehensible and non-transferable to users other than the original authors.

Conditional complexity

Conditional complexity occurs when too many nested IF operations exist in a formula, which is risky for it is hard to read.

Incorrect formula location

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

Hidden rows

As with hidden formulas, hidden rows potentially obscure the way a spreadsheet works. Contrary to hidden formulas, rows can be hidden even when the sheet is not protected. Ignoring hidden information while working with the spreadsheet might give problems later on.

Hidden formulas

When a cell is formatted to hide its formula in a protected sheet, it is ambiguous to the user what will happen in the spreadsheet when changing values, formulas or structure. Ignoring hidden information while working with the spreadsheet might give problems later on.

Duplicated formula

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.

Hidden worksheets

Hidden worksheets potentially conceal important information to understand the working of the spreadsheet. Ignoring hidden information while working with the spreadsheet might give problems later on.

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.

Merged cells

A formula with a reference to an empty cell is either meaningless or a mistake. Better to remove the reference or replace it with the intended location.

Referencing merged cells

A formula reference to a merge cell. Every group of merged cells has more than one reference-possibilities, but only one is correct.

Referencing empty cells

A formula with a reference to an empty cell is either meaningless or a mistake. Better to remove the reference or replace it with the intended location.

Sum includes subtotals

A subtotal/sum that includes both data and subtotals/sums of the same data generally results in a wrong outcome. Some or all numbers are summed up twice accidentally. Possible solutions are to remove subtotals or to consider using a separate column for subtotals.

Double operator sign

A formula that contains two operators next to each other is sometimes hard to detect and often undesired, especially because it yields no Excel error. A classical example is a double hyphen (--), which equates to a plus (+) operator yet looks like a minus sign (-).

Unexpected range

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.

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.

Hidden columns

As with hidden formulas, hidden columns potentially obscure the way a spreadsheet works. Contrary to hidden formulas, columns can be hidden even when the sheet is not protected. Ignoring hidden information while working with the spreadsheet might give problems later on.

Approximate lookup

When the last argument in a VLOOKUP or HLOOKUP is omitted or set to TRUE, an unexpected match might be returned. When the lookup range is sorted, either an exact or an approximate match is returned, always approximate when the range is not sorted. Usually just an exact match is desired.