Array Formulas

ARRAY Formulas & Functions

Imagine being able to calculate new prices for 100 different items in your inventory with one formula or being able to project new sales totals, based on an assumed increase or decrease percentage, updating every one of the prices or projections, all at the same time. That’s the power an Array formula gives you – enabling you to edit multiple cells in one fell swoop, with one formula.

In this example, we have a simple, but fairly typical setup that’s a perfect situation for using an Array formula.

We have a list of sales offices around the world, for a global company. Their current sales are in one section of the worksheet, and the upcoming year’s projections are in another section. As the two sections are identically designed – the layout of the cells in each section is the same – an array formula that takes the current sales and projects the new year’s sales based on a single projection percentage, will work perfectly.

In order to create the Array formula, I select the target cells (F7 through F25) – the cells where I want to see the projected sales numbers – and then I type an equal sign. It appears in the first cell in the target range as well as on the formula bar.

=

Then, I drag through last year’s sales, in cells C7 through C25, and type an asterisk, to say I intend to multiply all the cells in that range by some value.

Then I click in the cell containing the projected percentage, which is cell I4.

Then, instead of pressing Enter, to turn this into an Array formula, I press CTRL + SHIFT + ENTER.

All of the projections are inserted, and note the “curly brackets”, officially known as French Braces, that encompass the entire formula. You see them on the formula bar. That’s what tells you – and Excel – that it’s an Array.

Each cell contains the same formula, regardless of the address of the cell you click in within the range that was filled in by the formula.

I can edit cell I4 to see how other percentages – higher or lower – will affect each city’s total. Here, I’m changing it from 15% to 20%.

So – as long as the source and target ranges are the same size and layout, an array formula gives you the ability to do a whole lot of identical calculations, all at once!