How to Create Excel Array Formulas

To become a skilful and accomplish Excel user, a person should have sufficient knowledge of using Excel Array formulas to perform calculations and functions that cannot be performed using non-array formulas. They are applied to a range of cells and are useful by increasing consistency and reducing the creation of similar formulae, having same functionality.

What are Array Formulas ?

An array formula is a formula is used to execute multiple calculations on one or more of the cells in an array . You can think of an array as a collection of cells, having similar values stored in them, whether it be a row of values, a column of values, or even, a combination of rows and columns of values. Array formulas can be used to either return either a single result, as in case of Array Formulas giving single result or multiple results, as in case of Array Formulas giving multiple results .

For example, In a society, if the total working hours need to be computed for employees working for different months seperately, an array formula giving multiple result would be used. However, if the total working hours need to be computed for employees working for year, in totality, an array formula giving single result would be used.

An array formula that includes multiple cells and produces multiple results is called a multi-cell formula, and an array formula in a single cell, producing a single result is called a single-cell formula.

Completing an Array Formula in Excel

Array formulas are also, often referred to as CSE (Ctrl+Shift+Enter) formulas because instead of just pressing Enter to produce the result, you need to press Ctrl+Shift+Enter to complete the formula and produce the result.

Why are Array Formulas Used ?

Excel can be used to perform various complex and sophisticated mathematical operations and calculations, by using simple formulas. While some of the formulas, can be applied to single cells, array formulas in Excel offer the advantage of producing results, that are applied to a range of cells. For example: In an organization of employees, array formulas can be used to calculate total salary of each employee for a year, that are provided on a monthly basis (Multi-Cell Array Formula) and also the total salary of all the employees by using SUM function (Single-Cell Array Formula).

Thus, Array Formulas can be used to perform a variety of diverse and complex tasks such as:

  1. Count the number of values contained in a range of cells.
  2. Sum or count numbers that meet certain conditions of conditional formatting, such as the lowest values in a range or numbers that fall between an upper and lower boundary.
  3. Calculate sum of certain value in a range of values.

Array Formulas offer the following advantages:

  1. Array formulas provide consistency, precision and accuracy in the formulas and values computed, being applied to a range of cells.
  2. Array formulas reduce wastage of time in non-productive operations by applying formula directly to a range of cells, and need not be required to be applied separately.
  3. Array formulas ensure non-redundancy and provide safety to its users, as a component of a multi-cell array formula cannot be replaced by any other value, and the entire cell array formula needs to be selected for modification or permanent deletion. Also, as an added precautionary measure, you have to press CSE simultaneously for the change of formula.
  4. Array formulas make file sizes smaller by the usage of a single formula and not various similar intermediate formulas.
  5. Array formulas reduce chances of erroneous results, when formulas are applied wrongly.
    For example, when the sum of the working hours for each quarter of months has to be computed, a single formula of sum can be applied to the array (multi-cell array formula) rather than applying 4 different array formulas.

Syntax of an Array Formula

The syntax of an Excel formula is very general and constructed upon various built-in functions. Like other formulae, the array formula begins with an equal (=) sign. The key difference between a general arithmetic formula and an array arithmetic formula is that when using an array formula, you press Ctrl+Shift+Enter to produce the result of entered formula, while in the former case, Enter is used to produce result. When this is done, Excel automatically surrounds your array formula with braces. Manually typing the braces would convert it into a text string, as in case of other computer languages such as C, and simply won’t work and serve the function.

Array functions provide an efficient and effective way to build a complex formula and perform various complex calculations.

For example: To compute the sum of working hours of different employees and awarding the best employee with most number of working hours is done as follows:
{=D3:D12+E3:E12+F3:F12+G3:G12}
which is same as
=SUM(D3:G3) applied to H3, =SUM(D4:G4) applied to H4, ……., =SUM(D12:G12) applied to H12.

This is a multi-cell array formula.

Entering and Changing Array Formulae

  1. An array formula always begins with an equal sign.
  2. Once the array formula is typed, applied to a range of cells, whether it is a single cell or multi cell, its functionality can be applied only when Ctrl+Shift+Enter are simultaneously entered to produce the result. This is used to recognize it as an array formula and store it in its memory by applying braces automatically to the formulae. Manually, typing braces would make Excel treat it as a text string, that won’t serve the requisite function.
  3. While working with multi-cell formulae, always apply the result by selecting the ‘range’ of cells on which the array formulae needs to be applied.
  4. The individual contents of an individual cell in an array formula cannot be changed or modified. If an attempt is made in doing so, Excel displays the message that you cannot change part of an array, as when an array formula is entered, Excel treats it as data that are related to each other, and need to be changed collectively.
  5. Citing the above point, the individual cell cannot be deleted, and the entire array needs to be deleted by selecting the entire array and pressing delete. To delete any array formula, select the entire formula (for example, =D3:D12+E3:E12+F3:F12+G3:G12), press Delete, and then press Ctrl+Shift+Enter.

Creating an Array Formula

Creating an array formula, either single cell or multi cell, as followed above, follows a standard syntax, the detailed procedure for which is discussed below:

Creating Single Cell Array Formula

This type of array formula is used to simplify a worksheet by applying an array formula using a range of cells, to produce a single result, rather than operating different intermediate formulae on different range of cells, and further using them to produce the single result. The latter approach is erroneous and tedious.

The sequential procedure to create a single cell array formula is:

  1. You need to click on cell in which you want to enter the array formula.
  2. Begin the array formula with the equal sign and follow the standard formula syntax and use mathematical operators or built in functions in Excel formula, as required.

    For example:
    To calculate total working hours of all the employees collectively and producing result in cell H13, the array formula used is
    =SUM(D3:D12,E3:E12,F3:F12,G3:G12)

  3. Press Ctrl+Shift+Enter to produce the desired result. This is done so that Excel automatically inserts braces within formula and recognizes it as an Array Formula.

Note: Manually typing braces around a formula will not convert it into an array formula but would consider it as a text string.

Also, the following function of calculating total working hours could have been performed by using a multi-cell array formula applied onto a range of cells from H3:H12 and displaying the result of sum in H13.

Creating Multi Cell Array Formula

This type of array formula is used to simplify a worksheet by applying an array formula using a range of cells, to produce multiple results, rather than operating different intermediate formulae on different range of cells, and further using them to produce the multiple results. The latter approach is erroneous and tedious.

Note: In a multi cell array formula calculating multiple results, the array formula needs to be entered in the range of cells having exactly the same number of rows and columns as applied in array formula.

The sequential procedure to create a single cell array formula is:

  1. You need to click on the range of cells in which you want to enter the array formula.
  2. Begin the array formula with the equal sign and follow the standard formula syntax and use mathematical operators or built in functions in Excel formula, as required.

    For example:
    To calculate working hours of each employee within the year and producing result in the range of cell from H3:H12, the array formula used is:
    {=D3:D12+E3:E12+F3:F12+G3:G12}

  3. Press Ctrl+Shift+Enter to produce the desired result. This is done so that Excel automatically inserts braces within formula and recognizes it as an Array Formula.

Editing an Array Formula

The elements or values of an array formula cannot be modified individually, and can only be modified collectively, as discussed above. Excel would display the dialog box, saying that parts of an array cannot be changed.

Editing a Single Cell Array Formula

Following steps need to be followed sequentially in order to edit the single cell array formula:

  1. Select the cell in which single cell array formula is applied.
  2. Press key F2 on keyboard.
  3. Make the requisite changes.
  4. Press Ctrl+Shift+Enter on keyboard.

The formula is edited.

Editing a Multi Cell Array Formula

Following steps need to be followed sequentially in order to edit the single cell array formula:

  1. Select the range of cells in which multi cell array formula is applied.
  2. Press key F2 on keyboard.
  3. Make the requisite changes.
  4. Press Ctrl+Shift+Enter on keyboard.

The formula is edited.

Moving an Array Formula

The individual cells containing the array formula cannot be moved, but all of them can be moved collectively as an array, such that the cell references contained in the formula will automatically change.

Following steps need to be followed sequentially in order to move an array formula:

  1. Select the range of cells that need to be moved.
  2. Press Ctrl+X on keyboard.
  3. Select the new location of cells.
  4. Press Ctrl+V on keyboard to locate them to the new desired position.

Deleting an Array Formula

Individual cells involved in an array formula cannot be deleted as a part of an array cannot be modified, manipulated or delete. However, we can delete the entire formula and can write the new formula to produce the desired result.

When you delete an array formula, the result of the formula is deleted as well. If the value of the array formula needs to be retained, then only the array formula can be removed.

Deleting an Array Formula completely (Not retaining result of array formula)

Following steps need to be followed sequentially in order to delete array formula completely:

  1. Select the cell or range of cells that contain the mathematical array formula.
  2. Press Delete on keyboard.

The desired function of complete deletion is accomplished.

Removing an Array Formula (Keeping result of array formula)

Following steps need to be followed sequentially in order to remove array formula while retaining results of array formula:

  1. Copy the formula and then paste it in the same cell by using the Paste Values option obtained by right clicking on the requisite cell or on Home Tab under Clipboard section and then press delete, to get the values deleted.

OR

  1. Select the cell or range of cells containing formula that need to be removed.
  2. On the Home tab, under Editing Section, click on Find & Select option, and then click on Go To.
  3. Click Current array.
  4. On the Home tab, in the Clipboard group, click Copy.  
  5. On the Home tab, in the Clipboard group, click the arrow below Paste  , and then click Paste Values.
  6. Select the range of cells and press delete.

Using Array Constants

Array constants are values added inside braces, such that, it makes it easier to deal with related values together by naming an array constant, as well as processing them together. They collectively use constants as well as a range of values. While using an array formula, type an opening brace, enter the desired values you want, and finally, type a closing brace. For example: =PRODUCT(A1:E1*{1,2,3,4,5})

Note: The braces in array constants are typed manually. The rest of the array formula is entered, as desired, and Ctrl+Shift+Enter is pressed to produce the desired result for array constant.

Using Array Constant to enter values in column

Let us solve the following using an example:
If we need to enter five values, namely, 10, 20, 30, 40 and 50 in a single column A sequentially, the following steps need to be adopted:

  1. Select the cells where the values need to be entered.
  2. Enter an equal sign and the desired constant values that need to be contained in cells. If the value is a string, put the text in doubly inverted commas (“).
    Separate the values of the constants with semicolons and not commas.
  3. Press Ctrl+Shift+Enter on keyboard such that the values in column are contained.
    For example: ={10;20;30;40;50}

    This is also referred to as one dimensional vertical constant array.

Using Array Constant to enter values in row

Let us solve the following using an example:
If we need to enter five values, namely, 10, 20, 30, 40 and 50 in a single row 1 sequentially, the following steps need to be adopted:

  1. Select the cells where the values need to be entered.
  2. Enter an equal sign and the desired constant values that need to be contained in cells. If the value is a string, put the text in doubly inverted commas (“).
    Separate the values of the constants with commas now and not semicolons.
  3. Press Ctrl+Shift+Enter on keyboard such that the values in row are contained.
    For example: ={10;20;30;40;50}

    This is also referred to as one dimensional horizontal constant array.

Using Array Constant to enter values in rows and columns

Let us solve the following using an example:
If we need to enter twenty five values, namely, 10, 20, 30, 40,50…..240,250 sequnetially, in multiple rows and columns, constituting five rows and columns, the following steps need to be followed:

  1. Select the cells where the values need to be entered.
  2. Enter an equal sign and the desired constant values that need to be contained in cells. If the value is a string, put the text in doubly inverted commas (“).
    Separate the values of the constants that need to be contained in rows with commas and with semicolons, wherein the values of the constants need to be contained in columns.
  3. Press Ctrl+Shift+Enter on keyboard such that the values in row are contained.
    For example:  ={10,20,30,40,50;60,70,80,90,100;110,120,130,140,150;160,170,180,190,200;210,220,230,240,250}

    This is also referred to as two dimensional constant array because it constitutes both rows and columns.

Points to Remember

  1. Equal to sign(=) needs to be added in the beginning of each Excel Array Formula.
  2. Braces cannot be manually put in while dealing with Array Formula. They can only be put in manually while dealing with array constants.
  3. You can use numbers and texts in entering array constants. However, while dealing with array constants containing text, enter it in double quotes (“ ”).
Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!