What is a formula breakdown

A formula breakdown refers to the disentanglement of the items in a formula. In Excel, most of these components will be clear at a glance, like constants, values, functions and operator signs.

A formula with cell references

When a formula contains cell references, the formula breakdown will be a lot more complicated. Cell references refer to a different cell or cell range in the spreadsheet, that may or may not contain formulas themselves. The reliability of the retrieved value is a hassle to evaluate manually. Especially when a formula contains many cell references. The disentanglement can take up hours!

Example of a complex formula

Take a lot at the example below, with focus on the formula in the input bar:

The selected formula contains many components and is very difficult to read:

=IF(OR($B140=””;ISERROR(VLOOKUP(CONCATENATE($B140;$E140);’Input OpCo FMR’!$B:$O;I$4;FALSE)));0;’control panel’!$M$21*-VLOOKUP(CONCATENATE($B140;$E140);’Input OpCo FMR’!$B:$O;I$4;FALSE))

How can you be sure that all the cell ranges are correct and no mistakes are made?

Keep formulas readable

Our advise is to keep your formulas short and simple, so that they are easy to read. Read our guideline on how to avoid long formulas.