Discover the power of Array formulas in performing complex calculations across multiple cells, a useful tool in forecasting and inventory management. Learn how to seamlessly project sales figures based on percentage increases or decreases, making large-scale calculations simpler and faster.
Key Insights
- Array formulas allow for the calculation of new prices for multiple items in an inventory using a single formula, projecting sales totals based on assumed percentage increases or decreases.
- An Array formula enables the simultaneous editing of multiple cells.
- In scenarios where the source and target ranges are of the same size and layout, array formulas can perform a host of identical calculations at once.
- Array formulas are particularly useful for global companies with multiple sales offices, as it allows for the projection of the next year's sales based on a single percentage.
- To activate an Array formula, press CTRL + SHIFT + ENTER instead of just ENTER.
- The formula is recognized as an Array when it is encompassed by French Braces or curly brackets, visible on the formula bar.
Array Formula Power
-
Operate on Ranges
Apply a single formula to whole arrays at once.
-
Dynamic Arrays (M365)
FILTER, SORT, UNIQUE, SEQUENCE spill results into adjacent cells.
-
Legacy Ctrl+Shift+Enter
Older Excel needs CSE to activate array behavior — wrapped in { }.
-
Cleaner than Helper Columns
One formula replaces dozens of intermediate calculations.
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!
Video Transcription:
Full Video Transcript
Introduction to Array Formulas
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.
Setting Up the Example
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.
Creating the Array Formula
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 that 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 curly braces encompass the entire formula. 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.
Editing and Flexibility
I can edit cell I4 to see how other percentages, higher or lower, will affect each city's total. Here I'm changing the value from 15 to 20. So as long as the source and target ranges are the same size and the same layout, an array formula gives you the ability to do a whole lot of identical calculations all at once.
