Coffee-pot by Matthes // PerfectXL Spreadsheet Validation

Excel Risk Inspections

Range Issues

Larger spreadsheets are usually stuffed with formulas based on cell ranges. When spreadsheets are edited or expanded over time, different types of range issues may occur. These issues are usually hard to detect, but they can be very risky! We’ve seen big numbers and large sums of money ‘disappear’ because of range issues.

The risks of range issues

A range is a group of cells within a row and/or column in a spreadsheet. Ranges can be referred to from other cells or formulas. This possibility is of course one of the advantages of Excel, but when the referencing goes wrong, it can cause a very risky situation.

 

Range issues // Excel risk inspections

Empty cell references

A formula with a reference to an empty cell is either meaningless or a mistake. It’s better to remove the reference or replace it with the intended location. One exception: sometimes, we see some template spreadsheets from our clients. In template files, not all source data cells must be filled in advance. That’s why empty cell references are not always as risky as they look like.

 

Range issues in Excel :: Empty cell references

Interrupted formula range

When a formula in a cell is different from the ones above and below (or right and left), PerfectXL marks it as a range issue. For example: C2=A2+B2; C3=A3+B3; C4=A4+D4; C5=A5+B5. C4 is the interruption.

Usually a range of formulas is not interrupted, because a column is typically designed to perform equal types of calculations. If the interruption was intended, it’s better to move it outside the formula range. If the interruption is unintentional, replace the value of the cell by the formula found in the range.

 

Interrupted formula range :: Range issues in Excel

Interrupted formula range :: Range issues in Excel // Example

Merged cells

Merged cells are considered a range issue, because it is not obvious how to correctly refer to merged cells that contain a formula or other value. Problems with adding or deleting rows or columns may also occur. It’s better to unmerge the cell group and try other solutions, like borders and colors, to clarify your sheet.

 

Range issues in Excel :: Merged cells

Read our guideline about merged cells

Referencing merged cells

A formula reference to a merged cell is another range issue. Every group of merged cells has more than one reference-possibility, but only one is correct.

 

Range issues in Excel :: Referencing to merged cells

Unexpected ranges

This range issue occurs when a function references to a cell range (in this case a range of >4 cells), but the range in the sheet is actually larger. Or when the length of a table (range) is shorter than the reference in the formula cell. In this case there is a great chance that the reference is outdated, or simply incorrectly chosen.

 

Range issues in Excel :: Unexpected ranges

Unexpected shifts

An unexpected shift is when cell references suddenly shift by more than 1 cell in an otherwise consistent formula range. Check if this is intentional or by accident.

 

Range issues in Excel :: Unexpected shifts

Risk inspection categories

Below you’ll find an overview of the risk categories that PerfectXL Risk Eliminator detects. Click a category to read more.

Hiding “unimportant information” from other users may seem like a good idea to make complex spreadsheets more user friendly. But in reality that’s not the case. Hidden information can be altered by a user, without him even realizing what he’s doing, resulting in potential negative results.

Excel does what you ask for, but are you asking the right question? We see many formulas that give us reason to doubt that. For example, when hard coded numbers or double operators are used. This category of risks we call ‘calculation doubts’.

Larger spreadsheets are usually stuffed with formulas based on cell ranges. When spreadsheets are edited or expanded over time, different types of range issues may occur. These issues are usually hard to detect, but they can be very risky! We’ve seen big numbers and large sums of money ‘disappear’ because of range issues.

Currently Excel offers about 475 standard functions and new ones are added regularly. Most of these functions are rock-solid, but a couple of them we consider vulnerable functions. Some of which are frequently used by many Excel professionals. We suggest you try to avoid using them. Excel offers a range of fine alternatives.

The creation of a good structure is one of the most important, yet most underestimated aspects in the development of a spreadsheet. Usually a spreadsheet starts small, but then it grows and nobody gets around to reorganizing the structure anymore. PerfectXL Risk Eliminator groups the risks resulting from ill structure under ‘structure issues’.

The art of programming is the art of keeping things simple. Complex formulas are hardly ever wise to use. Calculations can usually be simplified. You just have to figure out how. PerfectXL points out different types of complex formulas in your spreadsheet and it offers suggestions to improve the complexity.

Excel is a powerful tool. And when it can’t do what you need, VBA can fill the gap. Unfortunately, using VBA can lead to unpredictable behaviour, slow performance, and corrupt Excel files. PerfectXL checks your macro-riddled spreadsheets for potential problems in the VBA code.