Excel array formulas are among the most powerful tools a user can get his hands on to tackle complex calculations. However, at the same time, they are somewhat difficult to master. That is why some users, even with years of Excel experience, straight out avoid using them. Some even might not have heard about these formulas. And those few who are familiar with the concept are scared to give it a try, owing to the supposed complexity of the formulas. Hence, today’s article will not only shed light on Excel array formulas but also try to make them feel familiar enough for daily use.
What is an array formula?
Let us start the topic by addressing what actually an array is. In simple terms, an array is a collection of items. Meaning, an array can be any collection of numbers and or texts while residing in a single row and column format or covering multiple ones.
Hence, an array formula is different from a regular one as it possesses more than a singular value. That is, in Excel, an array formula evaluates all individual values in an array and, at the same time, performs multiple calculations on one or more than one cell.
That’s why an array formula is not only suitable for handling several values simultaneously, but it can also return multiple values as a result. As such, the answer to an array formula can easily be an array.
Here is an example of an array formula that calculates the sum of all characters in a range.
{=SUM(LEN(range))}Why use an array formula?
It brings us to the question of necessity. Why even use an array formula when Excel can pretty much function without one? Simply put, array formulas are only used to handle complex problems, where it does the task of literally hundreds of formulas. There are also additional uses for this function. It can get used to count characters, add date, day, or time or even perform random data picks.
How to insert an array formula in Excel?
And finally, we are at the segment where we will learn exactly how to insert such a formula in an Excel sheet. First and foremost, remember that you can only change a regular formula into an array by using this key combination: Ctrl + Shift + Enter.
Other than that, one should also heed the following:
- Only after you have pressed the Ctrl + Shift + Enter, the formula will change into an array, denoted by curly brackets.
- Also, manually typing curly brackets by wrapping a formula doesn’t work.
- While editing an array formula, it is mandatory to press the Ctrl + Shift + Enter keys for the formula to register as an array, as the curly brackets automatically disappear during the process.
- Forgetting to press the CSE will only result in it remaining a regular formula which will only take into account the initial value.
Note – Since all array formulas require users to pass the Ctrl + Shift + Enter, they are also known as CSE formulas.
How to create and use an array formula?
It’s high time the users learn how to create and use an array formula. But remember that Excel offers two kinds of arrays. The first one covers the formula that performs multiple calculations to present a single result while the other one gives multiple results. These are the only two kinds of formulas one would encounter in Excel.
To calculate a single result
The primary objective of a single array formula can be to simply replace multiple formulas from a worksheet. Hence, the steps to create such a formula are as follows:
- First, launch Excel and open a spreadsheet to work on.
- Now, click the cell where you want to enter the array formula.
- Then, type the formula, and be sure to press Ctrl + Shift + Enter. Otherwise, it won’t be registered.
- Those who have a current MS Office 365 subscription can directly press the Enter key.
- And you’re done.
To calculate multiple results
- Similarly, to calculate multiple results, launch MS Excel.
- Then, select a range of cells to enter the formula. However, remember that the formula and the range selected should be the exact same number.
- Now, enter the formula.
- Once again, press the Ctrl + Shift + Enter.
- And it’s over.
Types of Excel Array Constants
Now, we move on to a segment that is crucial to Excel Array. And that is the Array Constants. As the name suggests, Excel Array Constants are a set of static values. Meaning, they can not be changed. As such, these values remain the same throughout the process, no matter the operations performed.
As these arrays are a constant, they bring a semblance of stability to the whole concept. Thus, there are three different types of Constant arrays used in Excel:
Horizontal or Row Array
As you can guess, a Horizontal Array constant has multiple values in a row. And to create one, we have to:
- First, select the cells for the formula.
- Then go to the formula bar and enter the array in braces, separated by commas.
- And lastly, press Ctrl + Shift + Enter.
Vertical or Column Array
At the same time, a Vertical Array makes use of Excel columns. However, instead of commas, we have to use semicolons to separate values. Such as:
- Select the data range.
- Enter the array formula in braces separated by semicolons.
- And do not forget to press the Ctrl + Shift + Enter
Two-Dimensional Arrays
And finally, we have 2-D arrays that combine both rows and columns where the rows are separated by semicolons and columns by commas.
- As usual, fire up your Excel spreadsheets and select the cells in question.
- Then enter the required formula.
- And finally, press the Ctrl + Shift + Enter.
How to incorporate Excel Array Constants in your daily operations?
Since Excel Array Constants are crucial to array operations, there must be a way to use them in day-to-day operations. Why yes! There is. And the following tips will aid users in incorporating those array constants with ease.
1. Mind the Elements of an Array
As we have discussed above, an array can contain anything from text values to numbers, Boolean, and errors. All of this is within braces, separated by commas or semicolons. And finally, an array can’t contain different array formulas, ranges, dates, names, functions, etc.
2. Name the commonly used arrays
As you get adept at handling arrays, you will find yourself using a few array formulas regularly. Thus, to make your task easier, you can always give those commonly used formulas a name. And after you have defined a name for a particular formula, you can simply use the exact name the next time instead of the whole formula. To give the formula a name, follow the instructions below:
- Go to the formula tab and then click on Defined Names.
- After that, click on Define Name.
- Alternatively, you can always use Ctrl + F3.
- Now type in the required Name box.
- And lastly, enter the items of your array.
3. Error prevention
And lastly, we have error prevention, for obvious reasons. For this to work, first delimit the elements of your array constant with proper character spacing. And also, choose the range of cells in an exact manner that matches the number of items in your array.