

Array formulas are an advanced topic in Excel. Usually Excel users discover them when reaching the limits of – let’s call them – normal formulas, e.g. SUM, VLOOKUP, COUNT and so on. This article provides an introduction of array formulas in Excel.
Contents
Microsoft writes about array formulas:
To become an Excel power user, you need to know how to use array formulas, which can perform calculations that you can’t do by using non-array formulas.
This article explains the basics of array formulas and provides examples.
Array formulas extend normal formulas. An array is a range of at least two or more cells. They are also referred to by “CSE” formulas which stands for Control, Shift, Enter. The reason is that after typing an array formula into an Excel cell, you must press Ctrl + Shift + Enter instead of just Enter. By pressing Ctrl + Shift + Enter, Excel adds the curly brackets { and } around the formula.
There are two types of array formulas:
Typically, an array formula performs several calculation steps in one formula. And that’s where array formulas are interesting for lookups: They can replace helper columns. This is especially useful, if the input data should stay “untouched”.
Example of a simple array formula.
So how to create a simple array formula in Excel? Let’s say you’ve got 3 products, each with amount sold and price as shown in the screenshot on the right-hand side. You want to know the total revenue.
The formula in cell D7 is
{=SUM(C3:C5*D3:D5)}
The curly brackets are added when pressing Ctrl + Shift + Enter. The underlying calculation steps are as follow:
=C3*D3+C4*D4+C5*D5 =300*24+350*26+400*22 =7200+9100+8800 =25100
This is probably an easy example for a single-cell-array formula. Of course, the result can also be achieved with the SUMPRODUCT formula or an additional helper column.
Error message “You can’t change part of an array formula”.
When deleting array formulas, you have to differentiate between single-cell-array formulas in multi-cell-array formulas.
Steps for deleting multi-cell array formulas.
Deleting single-cell-array formulas is quite simple: Just select the cell containing the array formulas and press the delete key on the keyboard. Alternatively select the cell to delete, click on “Clear” on the Home ribbon and then on “Clear Contents” or “Clear All”.
If you want to delete multi-cell-array formulas, you have to work a little bit harder. Instead of just selecting one cell, you have to select all cells belonging to the current array of cells. If you don’t catch all related cells at the same time, you’ll receive the “You can’t change part of an array” error message.
So how to select the whole array (that means all cells belonging to the current multi-cell-array)?
Excel too slow? Speed it up. Get the book now!
Tired of waiting for Excel? Use the 30 best methods described in this book to speed up Excel calculations!
Learn more or get it on Amazon!
In practice you have to change the size of an array formula comparatively often. Unfortunately, that is not as simple as it sounds. Single cell array formulas are usually not a problem because you can handle them like normal formula cells. So once again, it’s the multi-cell array formulas that are challenging.
Example for resizing multi-cell array formulas.
The first approach (and often the fastest way to change the size of multi-cell array formulas) is to delete the complete formula and set it up again from the beginning. Especially in cases with complex array formulas this method might be troublesome. But in such case also the following second approach for resizing array formulas causes similar troubles.
The second approach is use the existing multi-cell array formula, change the cell references within the formula and apply it to the complete cell range. To demonstrate this, please take a look at the following example as shown in the screenshot on the right-hand side.
Steps for changing the size of a multi-cell array formula.
In this example you already have a simple TRANSPOSE formula. It exchanges rows and columns of the cells B3 to C4. The task is to extend it to some new data given in cells B5 and C5 (the highlighted cells).
Example for array constants.
Like non-array formulas (“normal formulas) you can work with numbers or other values in array formulas, so-called array constants. Array constants are a set of static values that don’t change. They can be used as arguments within array formulas. An example is shown in Figure 86. The constants are embraced by curly brackets and each value is separated by a semi-colon.
You have to manually insert the curly brackets around the array constants. Additionally, you have to press Ctrl + Shift + Enter after editing the formula so that a second pair of curly brackets is added around the whole formula.
We’ve said before that each value should be separated by a semi-colon. A semi-colon in terms of array constants means that all values are in one column. If you instead separate the values with commas, the values are in one row. Now you can combine commas and semi-colons. That way you create two dimensional array constants, similar to an Excel table with rows and columns.
Example: The following formula…
={“Q1 2017″,”Q2 2017″,”Q3 2017″,”Q4 2017″;”Q1 2018″,”Q2 2018″,”Q3 2018″,”Q4 2018”}
…translates into this table:
Q1 2017 | Q2 2017 | Q3 2017 | Q4 2017 |
Q1 2018 | Q2 2018 | Q3 2018 | Q4 2018 |
As you can see the first four value from “Q1 2017” to “Q4 2017” are separated by commas. These values represent the first row. Starting from the semi-colon the second row starts.
Let’s take it to the next level now. So now you know how to handle array constants. Next you can assign a name to the array constant. That way you don’t have to type it again in your array formula but instead only use the array constant by inserting the name into your formula.
Example for using array constants in given names.
Recommendation: Type your array constant one time directly into an Excel cell and test it. If it works, copy it. Now continue with the following steps (the number relate to the image on the right-hand side).
In this article you learned the basics of array formulas. Especially relevant for some special lookups, for example VLOOKUP to the left or VLOOKUPS with several criteria rely on array formulas. Some of these special lookups don’t have a non-array alternative. In such case you have to use array formulas. Furthermore – although probably nowadays not that relevant any longer – you could reduce the file size of Excel workbooks because you can conduct several calculation steps within one formula.
That said it’s time for some disadvantages and words of caution about array formulas.
Was the information helpful in this article?
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example
Comments 1
BobS
Henrik, thanks for posting this. I’ve used single-cell array formulae for many things, but i often run into limitations. For example, I’d like to use arrays for iterating through a series of SUBSTITUTE functions. My idea is that I should be able to provide an array of terms and an array of replacement terms and perform a search and replace for each term in a cell, without having to nest SUBSTITUTES. But i can’t get the substitute to honor the array. Do you have any ideas? I’ve tried using the “evaluate formula” but it only goes through the first set of elements in my array.
I’ve used Ctrl/Shift/Enter in all cases.
Function: =SUBSTITUTE(Word,FindArray,ReplArray)
Word: décor
FindArray: ={“á”,”é”,”í”,”ó”,”ú”,”ñ”,”Á”,”É”,”Í”,”Ó”,”Ú”,”Ñ”,”km/h”}
ReplArray: ={“a”,”e”,”i”,”o”,”u”,”n”,”A”,”E”,”I”,”O”,”U”,”N”,”kph”}