Array Formulas in Excel

Array formulas are very useful and powerful formulas which are used to perform some of the very complex calculations in excel, array formula is also known as the CSE formulas as to execute array formulas we need to press CTRL + SHIFT + ENTER together instead of just pressing enter, there are two types of array formulas one which gives us a single result and another which gives us a multiple results.

Array Formulas in Excel

Arrays in excelArrays In ExcelA VBA array in excel is a storage unit or a variable which can store multiple data values. These values must necessarily be of the same data type. This implies that the related values are grouped together to be stored in an array more can be termed as array formulas in excel. Array in excel is very powerful formulas that enable us to perform complex calculations.

First things first, what is an Array? An array is a set of values or variables in a data set like {a,b,c,d} is an array which has values from “a” to “d.” Similarly, in excel array is a range of cells of values,

Arrays in Excel example 1

In the above screenshot, cells from B2 to cell G2 is an array or range of cells.

These are also known as “CSE formulas” or “Control Shift Enter Formulas,” as we need to press Ctrl + Shift + Enter to create an array formula in Excel.

In excel, we have two types of Array formulas:

  1. One which gives us a single result.
  2. Another which gives us more than one result.

We will learn both the types of Array formulas on this topic.

Explanation of Array Formulas in Excel

As explained, array formulas in Excel are powerful formulas that help us to perform very complex calculations.

From the above examples, we learned these formulas in excel do simplify the complex and lengthy calculations. There is one thing to remember, though, in example 2, where we returned multiple values using these formulas in excel. We cannot change or cut the value of the cell as it is a part of an array.

Suppose we wanted to delete cell G6 excel will give us an error, or if we change the value in the cell type, any random value in the cell excel will give an error.

For example, type a random number in cell G6 and press enter it gives the following error,

Upon typing a random value 56 in the cell G6, it gives an error that we cannot change a part of the array.

If we need to edit an array formula go to the function bar and edit the values or the range provided, or if we want to delete the array formula delete the whole array i.e., cell range B6 to G6.

How to Use Array Formulas in Excel?

Let us learn an Array Formula by a few examples:

You can download this Array Formula Excel Template here – Array Formula Excel Template

Array formulas can be used in two types:

  1. If we want to return a single value, use these formulas in a single cell, as in example 1.
  2. If we want to return more than one value, use these formulas in Excel by selecting the range of cells as in example 2.
  3. Press CTRL + Shift + Enter to make an array formula.

Example #1

In restaurant sales data, there are products, the price of each product, and the number of products sold.

The owner wants to calculate the total sales done by those products.

Arrays Formula in Excel example 1

The owner can go ahead and multiply the number of items sold of each product then sum them up as in the below equation,

Arrays Formula in Excel example 1-1

And he will get the total sales,

Arrays Formula in Excel example 1-2

But this is a lengthy task, and if the data were bigger, it would be much more tedious. Excel offers Array formulas for such tasks.

Arrays Formula in Excel example 1-3

We will create our first Array formula in cell B7.

Following are the steps to create first array –

  1. In cell B7, type =sum and then press the tab button on the keyboard. It opens up the sum formula.

    Arrays Formula in Excel example 1-4

  2. Select cell range B2 to G2.

    Arrays Formula in Excel example 1-5

  3. Now put an asterisk “*” sign after that to multiply.

    Arrays Formula in Excel example 1-6

  4. Select cell range B3 to G3.

    Arrays Formula in Excel example 1-7

  5. Instead of pressing enter key press CTRL + SHIFT + ENTER.

    Arrays Formula in Excel example 1-8

    Excel gave the value of total sales by multiplying the number of products to the price in each column and summing them up. In the highlighted section, we can see that excel created an array for cell range B2 to G2 and B3 to G3.

    Arrays Formula in Excel example 1-9

    The above example explains how excel array formulas return a single value for an array or set of data.

Example #2

Continuing with the same data now, what if the owner wants to know the sales for each product separately, i.e., sales of product 1 and product 2 and so on.

Arrays Formula in Excel example 2

He can either go a long way and, in each cell, perform a function that will calculate the sales value.

Arrays Formula in Excel example 2-1

The equation in cell B5, he has to repeat it for cell c4, d4, and so on.

Again if it were larger data, it would be a tedious and tiresome task.

In this example, we will learn how array formulas in excel return multiple values for a set of arrays.

Example 2-2

#1 – Select the cells where we want our subtotals, i.e., per product sales for each product. In this case, it is a cell range B8 to G8.

Example 2-3

#2 – Type an equals to sign “=” a

Example 2-4

#3 – Select cell range B2 to G2.

Example 2-5

#4 – Type an asterisk “*” sign after that.

Example 2-6

#5 – Now, select cell range B3 to G3.

Example 2-7

#6 – As for array formula, do not press enter, press CTRL + SHIFT + ENTER.

Example 2-8

From the above example, it is explained how an array formula can return multiple values for an array in excel.

Cells B6 to G6 is an array.

Example #3

For the same restaurant owner, he has the data for sales done by the restaurant for five months Jan Feb March April, May, and June. He wants to know the average growth rate for sales.

Example 3

Now what the owner can normally do is subtract the sales value of Feb months to Jan in the cell C2 and so on then calculate the average.

Example 3-1

Again if this were a larger data, it would be a very tiresome task. Let us do this with the array in excel formulas.

#1 – In the cell D8, type =average and then press the TAB button.

Example 3-2

#2 – To calculate growth, we need to subtract the values of one month from the previous month to select the cell range from B3 to B7.

Example 3-3

#3 – Put a subtract (-) sign after that,

Example 3-4

#4 – Now select cells from B2 to B6.

Example 3-5

#5 – As for array in excel, do not press enter, press CTRL + SHIFT + ENTER.

Example 3-6

Array formulas in excel easily calculated the average growth for the sales without any hassle.

The owner now does not need to calculate each month’s growth and then perform the average function after that.

Things to Remember

  1. These are also known as CSE Formulas or Control Shift Enter ExcelControl Shift Enter ExcelCtrl-Shift Enter In Excel is a shortcut command that facilitates implementing the array formula in the excel function to execute an intricate computation of the given data. Altogether it transforms a particular data into an array format in excel with multiple data values for this more Formulas.
  2. Do not make parenthesis for an array; excel itself does that, it would return an error or incorrect value.
  3. Entering the parenthesis “{“ manually, excel will treat it as a text.
  4. Do not press enter; instead, press CTRL + SHIFT + Enter to use an array formula.
  5. We cannot change the cell of an array, so to modify an array formula, either modify the formula from the function bar or delete the formula and redesign it in the desired format.

Recommended Articles

This has been a step by step guide to Array formulas in Excel. Here we discuss how to Use Array in Excel using basic SUM and AVERAGE formula and use it to solve Array in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *