# Structure Issues

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

## Risks of Structure Issues

Sometimes a formula is working flawless in itself, but impossible or ill-advised considering the spreadsheet as a whole. PerfectXL Risk Eliminator detects these structure issues and sends a warning. The features Visualization and Formula Breakdown provide a clear insight into the structure of your spreadsheet.

### Circular Chain of Sheets

This refers to a ‘to-and-from’ 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. Make sure the information flow in your Excel spreadsheet follows a linear pattern. Try to break any cycles.

### Circular References

When the formula in a cell refers back to itself through another cell, we speak of a circular reference. This might cause an error in the calculation of all cells in this reference circle (and further). PerfectXL also detects hidden circular references that Excel doesn’t recognize at all, because they are conditional.

### Duplicated Calculation

When the same formula with the same (absolute) references is found in more than one cell, it is flagged as a duplicated calculation. For example C1 = A1+B1 and D1 = A1+B1. It increases the risk of mistakes in future changes or improvements if the user forgets to adapt one of both. The solution is to use the formula in only one cell and refer to this cell in all other instances.

N.B. When a formula contains neither functions (e.g. SUM or PRODUCT) nor operators (e.g. + and *), identical formulas are not considered a duplicated calculation.

Tip: Read our Excel Guideline ‘Calculate formulas only once

### 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. Try to keep formulas close to their input. One exception: sometimes using large ranges of source input cells can’t take place in the source sheet.

## Risk inspection categories

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

#### Hidden information

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.

#### Calculation doubts

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

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

#### Vulnerable functions

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.

#### Structure issues

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

#### Complex formulas

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.

#### VBA issues

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.