Excel Single Cell Array Formula Menu Lifewire Tech for Humans Newsletter! Search Close GO Software & Apps > MS Office

Excel Single Cell Array Formula

Learn how to create an array formula

By Ted French Ted French Writer Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs. lifewire's editorial guidelines Updated on March 1, 2020 Tweet Share Email Manuel Breva Colmeiro / Getty Images Tweet Share Email MS Office Excel Word Powerpoint Outlook In Excel, an array formula is a formula that carries out calculations on one or more elements in an array. Array formulas in Excel are surrounded by curly braces "{ } ". These are added to a formula by pressing the CTRL+SHIFT+ENTER keys after typing the formula into a cell or cells. Instructions in this article apply to Excel for Outlook 365, Outlook 2019, 2016, 2013, and 2010.

Types of Array Formulas

There are two types of array formulas - those that are located in multiple cells in a worksheet (multi-cell array formula) and those located in a single cell (single cell array formula). 01 of 04

How a Single Cell Array Formula Works

A single cell array formula differs from regular Excel formulas in that it performs multiple calculations in one cell in a worksheet without the need for nesting functions. Single cell array formulas usually first carry out a multi-cell array calculation — such as multiplication — and then use a function such as or AVERAGE or SUM to combine the output of the array into a single result. In the image above, the array formula first multiplies together those elements in the two ranges D1:D3 and E1:E3 that reside in the same row in the worksheet. The results of these multiplication operations are then added together by the SUM function. Another way of writing the above array formula would be:
(D1 * E1) + (D2 * E2) + (D3 * E3) 02 of 04

Entering the Tutorial Data

​ To begin the tutorial it is necessary to enter our data into an Excel worksheet as seen in the image above. Cell Data
D1 - 2
D2 - 3
D3 - 6
E1 - 4
E2 - 5
E3 - 8
03 of 04

Adding the SUM Function

The next step in creating the single cell array formula is to add the sum function to cell F1 — the location where the single cell array formula will be located. Select cell F1, which is where the single cell array formula will be located. Type an equal sign ( = ) to begin the sum function. Type the word sum followed by a left round bracket (. Drag select cells D1 to D3 to enter these cell references into the sum function. Type an asterisk symbol ( * ) since we are multiplying the data in column D by the data in column E. Drag select cells E1 to E3 to enter these cell references into the function. Type a right round bracket ) to close the ranges that will be summed. At this point, leave the worksheet as is. The formula will be completed in the last step of the tutorial when the array formula is created. 04 of 04

Creating the Array Formula

The last step in the tutorial is turning the sum function located in cell F1 into an array formula. Creating an array formula in Excel is done by pressing the CTRL+SHIFT+ENTER keys on the keyboard. The effect of pressing these keys together is to surround the formula with curly braces: { } indicating that it is now an array formula.

Tutorial Steps

For help with these steps see the image above. Hold down the CTRL and SHIFT keys on the keyboard then press and release the ENTER key to create the array formula. Release the CTRL+SHIFT keys. If done correctly cell F1 will contain the number "71" as seen in the image above. When you select cell F1 the completed array formula { =SUM (D1:D3 * E1:E3) } appears in the formula bar above the worksheet. Was this page helpful? Thanks for letting us know! Get the Latest Tech News Delivered Every Day Subscribe Tell us why! Other Not enough details Hard to understand Submit More from Lifewire How to Combine the ROUND and SUM Functions in Excel How to Count Data in Selected Cells With Excel's COUNTIF Function Perform Multiple Calculations With Excel Array Formulas How to Create an Excel Lookup Formula With Multiple Criteria Ignore Error Values When Finding the Average in Excel How to Subtract Dates in Excel Excel SUMIFS: Sum Only Values Meeting Multiple Criteria How to Use a Dynamic Range in Excel With COUNTIF and INDIRECT Find The MEDIAN IF Your Formula Meets Criteria in Excel Arrays, Array Formulas, and Table Arrays in Excel How to Sum Columns or Rows in Google Sheets Excel SUM and OFFSET Formula How to Calculate Weighted Averages in Excel With SUMPRODUCT How to Nest Multiple IF Functions in Excel How to Add Numbers in Excel Using a Formula Excel Multi-Cell Array Formula Calculations Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up Newsletter Sign Up By clicking “Accept All Cookies”, you agree to the storing of cookies on your device to enhance site navigation, analyze site usage, and assist in our marketing efforts. Cookies Settings Accept All Cookies