How to use Array Formulas of Excel?

In this Article, you will be covering Excel’s most exciting, influential and least understood features is its ability to work with Arrays formula. After going through this concept, you will be well verse creating, editing and deleting Excel array along with the aptitude to perform complex spreadsheet Array formulas.

As an Excel user, I am sure you guys must have encounter formulas with curly braces “{ }” or shortcut key term Ctrl+Shift+Enter. These formulas are Excel Array Formula.

If you understand computer programming, you must have read the concept of an array. According to WikipediaAn array data structure, or simply an array, is a data structure consisting of a collection of elements (values or variables), each identified by at least one array index or key.” In simple terms, an Array is a collection of Items.

The key topics which we will be covering here are:

Download Your Example Excel File

  • While writing this I used Office 365, however, anyone using Excel 2016, Excel 2013 and even prior editions of Excel should be able to follow along with the content.

Types of Array in Excel

Array formula doesn’t require to be stored in Excel cells only it can also exist in Excel’s memory (not visible to the user). You will understand this as you progress further on the topic. There are two types of array in Excel:

  • Single cell Array – Display result in a single cell

The Single cell array formula returns the result in a specific cell. Let’s grab this by using a simple example.

Suppose you have a list of products with sales and vendor commission rate. Moreover, the assignment is to find the total commission for the vendor in cell D13.

The Array formula we are implementing is:

{=DOLLAR(SUM(C7:C11*D7:D11))}    ‘two array (Sales & Commission)

Note: While entering Array formula you need to press Ctrl+Shift+Enter (Excel add curly brackets)

The array formula returns the total commision in single cell
The array formula returns the total commision in single cell

In this example, the formula multiplies the corresponding values in two arrays and creates a new array in Excel Memory. The result is below. 

Multiplies two Array and create a new array.
Multiplies two Array and create a new array.

The new array exists in Excel memory that returns five values.

{13.32; 16.875; 13.68; 9.63; 7.5}

Moreover, the new array is in Excel’s memory looks like this (not visible)

{111*.12+125*.135+114*.12+107*.09+100*.075}

Although SUM function adds numbers but does not perform multiplication operation, we can perform such operations by converting a range of numbers to the array.

  • Multicell Array – Result is displayed in a range of cells

The Multicell array returns the result in a range of cells. In this example, the goal is to calculate commission for each product, not total commission as the last example using an array formula.

Multiply both range of cells and press Ctrl+Shift+Enter
Multiply both range of cells and press Ctrl+Shift+Enter

Column E contains a formula for calculating commission for each product.
To get the same result, we could have multiply each cell of column C and D in column E. Like this:

=C7*D7
=C8*D8
=C9*D9
=C10*D10
=C11*D11

Although using single or multicell array carry few advantages:

  • Easy to manage the accuracy of the formula
  • Unlikely to overwrite it considering restrictions to edit and delete an array formula.

Some potential disadvantages:

  • In some cases, you may find annoying due to the inability to insert or delete rows.
  • The formula doesn’t dynamically flow to adjust for new addition the way Excel Table does.
  •  
  • Make sure the count of selected ranges of cells are identical. 
  • For single cell array example, you can also use Excel SUMPRODUCT function that can handle array without using an array formula: 

    =SUMPRODUCT(C7:C11*D7:D11)

  • In case you forgot to press Ctrl+Shift+Enter, Excel will return “#VALUE!” error.

Array Constants

We can not only enter the range of cells but also hard-code a value. An array doesn’t have to be stored in a range of cells similar to previous examples but can also get stored in memory. 

The following formula uses COUNT function along with array constant. The formula returns the count of values as 5 in this case. 

=COUNT( {10,9,8,7,6} )

You can also create a formula with both an array constant and an array stored in a range. The following formula returns the count of cell range and array constants.

=COUNTA(B7:B11,{10,9,8,7,6})

The COUNTA function count of numbers and text within the reference list of arguments ignoring blank cells.

Include cell range and constants to create Array formula
Include cell range and constants to create Array formula

An array constant formula can contain text, numbers, logical values, and even error values, such as #REF! but not signs and symbols like %,(), etc. 

  • Note: Although formula uses curly braces {} but this not an Array formula
  • As you have directly specify the array element using {}, not require to press Ctrl+Shift+Enter.
  • Type braces around array constants and use commas and semicolons to separate their elements or areas.

Dimensions of an Array

Arrays can have one or two dimensions which enable to perform calculations most effectively unlike anything else. One dimensional array can be horizontal (all elements to a single row) and vertical (all elements to a single column).

One Dimensional – Horizontal

Each item of 1D horizontal array are exhibits in successive cells of a row and separated by comma (depending on Excel’s language).

Given below is 1D- horizontal array example:

  • Select six consecutive cells in a row
  • Enter each element using a comma and curly braces (Insert Function)
    ={100,200,300,400,500,600}
  • Press Ctrl+Shift+Enter
Enter numbers with Array and press Ctrl+Shift+Enter
Enter numbers with Array and press Ctrl+Shift+Enter

The above steps return following results with array constant.

One dimensional array result automatically occupy cells horizontally
One dimensional array result automatically occupy cells horizontally

One Dimensional – Vertical

Each item of 1D vertical array are exhibits in successive cells of a column and separated by semicolons (depending on Excel’s language).

Given below is 1D-vertical array example:

  • Select six consecutive cells in a column
  • Enter each element using semicolons (;) (Insert Function)
    ={100,200,300,400,500,600}
  • Press Ctrl+Shift+Enter
Select all cells in one column
Select all cells in one column

Two Dimensional Array

A two-dimensional array uses semicolons (;) to separate its vertical elements and commas (,) to separate its horizontal elements (depending on Excel’s language).

Given below is 2D-array example shows a 5 × 4 array constant:

  • Select five rows and four columns (20 cells)
  • Enter following formula (Insert Function)
    ={1,2,3,4;5,6,7,8;9,10,11,12;13,14,15,16;17,18,19,20}
  • Press Ctrl+Shift+Enter

Important: Semicolons (;) for columns and commas (,) for row.

A 5 × 4 array entered into a range of cells
A 5 × 4 array entered into a range of cells
  • In case you do not enter Semicolons (;) for columns and commas (,) for Row properly, Excel will return with first entry only.
  • If you select more cells or not appropriate row and columns. Excel displays #N/A in excess cells.

Naming Array Constants

The best part of Array comes now where you can create custom names using array constant. By doing this, we can easily add named arrays as named formula in Excel.

Check the following example of named array:

Let’s suppose you create array name with official working days. That may differ in which region you reside but to make things simple, exclude Saturday and Sunday both. Referring to the following array constant.

You can create named array of working days using the New Name dialog box in Excel.

{“Monday”,”Tuesday”,”Wednesday”,”Thursday”,”Friday”}

Create Custom Name Range using a Name dialog box
Create Custom Name Range using a Name dialog box

Note: Above you are using comma (horizontal array) while entering Array Constants.

Once you create a named array, you can use it in a formula.
Now you can enter this formula in Spreadsheet. To enter New Name, select five cells in a row, type =WrkDays, and press Ctrl+Shift+Enter.

Now we can easily integrate these Name Array with a formula to use for different applications.

Display Names using Array Constant Excel
Display Names using Array Constant

To replicate above example we can also use Excel table and naming by Name Ranges together to create array name. 

Another way is that we can convert a list of days into an Excel table and then name the table using Define Names under Name Range group.

Similarly, we can also use Excel TRANSPOSE function to vertically get the data without changing formula. Similarly you can can also perform with other Excel functions.

Use TRANSPOSE function for getting data horizontally.
Use TRANSPOSE function for getting data horizontally.
  • Excel interprets Equal sign (=) as a formula rather than a text string.
  • In case of large Array formula within a sizeable dataset can slow your worksheet’s.

Edit Array Formula

Well by now you know pressing Ctrl+Shift+Enter, and curly braces are a crucial aspect of creating an array formula. Moreover must select all of the cells in the results range before you enter a Excel formula. However, one more thing to catch here is managing an array formula.

If you try to edit one or more but not all elements of a multicell array formula, Excel will display following Error message.

Warning message reminding can’t edit or delete part of a multicell array formula.
Warning message reminding can’t edit part of a multicell array formula.

To Edit Array formula (can’t edit formula?)

  • Select all the cells with curly braces (Array formula)
  • Select formula bar
    Edit using formula bar on top
  • Edit the formula (Note: curly brackets disappear)
  • To reflect changes in all cells press Ctrl+Shift+Enter

Follow the same steps to expand or contract array formula. Now the change will automatically reflect all cells in the array range. Moreover, curly brackets will also reappear.

  • Can’t insert new cells without disabling or deleting array formula (select all cells).
  • You can not delete or move part of an array formula.