Array formulas in Excel are like multi-tasking superheroes. Imagine you have a bunch of numbers or data points, and you want to do the same magic math trick on all of them at once.
That’s what an array formula does—it takes an action and applies it to a whole set of values, called an array, in one go. This can help you do some pretty impressive calculations that might be tricky or time-consuming to do one by one.
Now, you might be used to formulas that work with a single value, like adding two cells together. Array formulas are different because they handle many values at the same time.
They’re especially handy when you need to perform more complex calculations, like adding up all the numbers that meet certain criteria or comparing two lists of data to find what’s common or different.
Instead of doing these tasks step by step for each value, you tell Excel to do it for the whole bunch at once.
Understanding Array Formulas
Before you start working with array formulas, it’s important to understand that they are powerful tools in Excel that allow you to perform complex calculations.
These formulas can process multiple values simultaneously and come in different types to handle a variety of tasks.
Definition and Key Concepts
Array formulas are a step up from regular Excel formulas.
They perform operations on a series of values known as an array. You can identify an array by looking at your formula bar; an array will be surrounded by curly braces ({}).
This might look like {1,2,3} for a row, or {1;2;3} for a column, where commas divide row items, and semicolons divide column items.
When you press Ctrl + Shift + Enter (CSE), Excel understands you’re entering an array formula, and it adds these braces for you.
Array formulas can be categorized based on whether they return a result in a single cell or multiple cells.
So, you might come across terms like single-cell array formula or multi-cell array formula. The multi-cell variety is what you’d use when the result of your calculations needs to be displayed across a range of cells.
Types of Array Formulas in Excel
Excel offers two main types of array formulas: legacy array formulas and dynamic array formulas.
Legacy array formulas, or CSE formulas, have been around for a while. These are created by pressing Ctrl + Shift + Enter, hence the name CSE. They are good for when you know the size of your output, as you have to select the output range before you confirm the formula.
On the other hand, dynamic array formulas are the newer, more efficient breed of array formulas. These are known as native array formulas because Excel automatically understands these as array formulas without needing to press any special key combination.
When you enter a dynamic array formula, Excel intelligently spills the results over into multiple cells, creating a so-called “spill range.” This means if your formula calculates multiple results, Excel shows all of them in the range that starts with the cell where you entered the formula.
This could be a single row, a single column, or a multi-row and multi-column range.
An example of a dynamic array function would be the SORT function, which sorts a range or array and returns the sorted array without any additional keystrokes. This can save you a lot of time, especially when you’re dealing with large datasets.
Also read: Calculation Modes in Excel (Manual, Partial, and Automatic)
Using Array Formulas in Excel (Examples)
Let’s now have a look at some examples of using Array Formulas in Excel and see how it can make your Excel calculations more efficient.
Use Array Formula to Link a Range of Cells Quickly
Array formulas help you to link a range to another cell range with a single formula.
Below, I have two tables. The first table shows the existing rates, and the second table shows the proposed rates for different consultation types. I want to link the consultation types of column A of Table 1 to column D of Table 2.
I can use the below array formula in cell D3.
=A3:A5
The good thing about doing it this way is that if you make any changes to the first group, it will show up in all the connected sections.
So, you won’t have to go through the hassle of looking for and fixing all the cells that should match the first group – it happens automatically.
For example, if you change cell A3 to “Budgeting and Planning”, it will update on cell D3 as well.
Also read: AutoSum in Excel
Use Array Formula to Calculate Set of Subtotals at Once
You can use array formulas to calculate a set of subtotals using a single formula.
Below, I have a dataset where I have different consultation types in column A, the rate per hour in column B, and the number of hours in column C, and I want to calculate the income for each consultation type in column D by multiplying Column B value from Column C value.
I can use the below array formula and calculate all incomes separately.
=B2:B4*C2:C4
With this array formula, you don’t have to bother applying the formula to every single row below. Excel just figures out the answer for each row on its own.
You can use array formulas to get a set of subtotals for columns as well.
So, if you click on any cell within that spill range, you’ll notice a blue border around it.
But here’s the catch: if you want to adjust the formula, you have to go back to the very first cell where you initially entered the formula. You can’t adjust the formula when you are in another cell of a spill range.
Use Array Formula to Calculate a Single Value of a Set of Calculated Values
Sometimes, you and I want to find a single value after doing some calculations for a set of values.
In such a situation, you can use array formulas to get that single value without calculating values for each row or column.
Below, I have a dataset where I have different consultation types in column A, the rate per hour in column B and the number of hours in column C, and I want to calculate the total income in cell B6.
Instead of calculating income for each consultation type separately and then taking the sum of that, I can use the below array formula in cell B6.
=SUM(B2:B4*C2:C4)
When you use this formula, Excel calculates income for each row by multiplying column B (Rate) from column C (hours) and taking the total of calculated values.
You can use this kind of array formula to find the maximum or minimum values as well.
This type of array formula helps you to save your working space in your Excel sheet. If you don’t use this array formula, you have to use Column D to calculate income for each consultation type.
Use Array Formula to Do Conditional Calculations
When you want to do a conditional calculation of a range, you can easily use an Excel array formula.
Below, I have a dataset where I have different consultation types in column A, the rate per hour in column B, and the total income in column C.
Now, in cell B7, I want to calculate the total income generated from the consultation types that have a rate below $50.
I can use the below array formula to find that.
=SUM((B2:B4<B6)*(C2:C4))
In this formula, you evaluate the Rate condition with the first part (B2:B4 < B6). After that, you multiply each result (TRUE or FALSE) by the corresponding income column value.
You can create similar array formulas and save your time for complex calculations.
Use Array Formula to Count Total Number of Characters of a Range
You can use an array formula to calculate the total number of characters of a range by combining the Excel LEN and SUM functions.
Below I have three types of consultation types for a company in cells A2 to A4. I want to count how many characters are in cells A2 to A4.
I can use the array formula below to find the total number of characters in the given range.
=SUM(LEN(A2:A4))
Using the LEN function, I can get the number of characters for each cell in the given range separately. Then, using the SUM function, I can take the total of each count.
Use Array Formulas Inside Array Functions
Sometimes, we can use array formulas inside array functions. This is very common for functions such as SUMPRODUCT, FILTER, etc.
Below, I have a dataset where I have client names in Column A, different consultation types in column B, and income in column C.
Now, I want to filter the consultation type and the income only for the given client in cell F1.
To filter data, I can enter the below formula in cell E3.
=FILTER(B2:C10,A2:A10=F1)
In the above formula, I use the array formula to enter the condition for filtering. That is “A2:A10=F1”.
You can apply these types of array formulas in various other functions.
Other articles you may also like:
