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 groups the risks resulting from ill structure under ‘structure issues’.

Structure Issues – Risks

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

Structure Issues :: Risk Detection in Spreadsheets :: PerfectXL

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 Chain of Worksheets 1 of 3 :: Structure Issues :: Risk Detection :: PerfectXL

Circular Chain of Worksheets 2 of 3 :: Structure Issues :: Risk Detection :: PerfectXL

Circular Chain of Worksheets 3 of 3 :: Structure Issues :: Risk Detection :: PerfectXL

Circular Reference

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 Circular References that Excel doesn’s recognize at all, because they are conditional.

Circular Reference 1 of 2 :: Structure Issues :: Risk Detection :: PerfectXL

Circular Reference 2 of 2 :: Structure Issues :: Risk Detection :: PerfectXL

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.

Duplicated Calculation :: Structure Issues :: Risk Detection :: PerfectXL

Read our guideline ‘Calculate a formula 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.

Incorrect Formula Location 1 of 2 :: Structure Issues :: Risk Detection :: PerfectXL

Incorrect Formula Location 2 of 2 :: Structure Issues :: Risk Detection :: PerfectXL