What is 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.

Cell References in a Formula

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 look at the example below:

Formula Breakdown - Complex Formula :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

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?

Formula Breakdown Feature

PerfectXL’s formula breakdown function composes a list of all cell references found in your formula, displaying the content of each reference separately. When the content exists of other formulas, these can be broken down all the way to the source too. Hence making it possible for the spreadsheet user to validate all items in the formula at a glance.

Let’s break down the formula we mentioned in the paragraph above, with PerfectXL:

Formula Breakdown - Example :: Excel Glossary :: PerfectXL Spreadsheet Validation Tool

First we selected cell reference $B140 in the main formula. The tool showed us the contents of cell B140:

=IF(LEFT(‘control panel’!$I$14,22)=”Interamerican Bulgaria”,I$1,IF(‘control panel’!$I$14=”Division Europe”,”INTBUL_HEA”,””””))

In this formula we selected cell reference I$1. The tool showed us the contents of cell I1:

VLOOKUP(‘control panel’!$I$14,Criteria!$B:$AO,40,FALSE)

And so forth. It becomes much easier to investigate possible mistakes, errors and risks in your spreadsheets!

Read more about the formula breakdown feature