An Excel array is a set of data values, each row or column, or cell range in an excel worksheet can be mapped to an array. An array formula is a formula that can process values that are stored in an excel array. This article will tell you how to use excel array and excel array formulas with examples.

### 1. Excel Array Examples.

#### 1.1 Excel Array Concepts.

- An Excel array is a collection of data values that are wrapped by curly braces(
**{}**). - And each value in the array is separated by the delimiter character.
- One excel row can be mapped to an array such as
**{1,2,3}**, this means there are 3 columns in the row, and they are separated by a comma(,). This is also called a horizontal array. - One excel column can be mapped to an array such as
**{“IBM”;”Apple”;”Google”}**, it is also called a vertical array, it uses the semicolon(;) to separate the row data values, there are 3 rows in this example. - If you save the above data values in cell range
**A1:B3**, you can get the excel array such as**{1,”IBM”;2,”Apple”;3,”Google”}**, there are 3 rows and 2 columns in the array. - Most language versions of excel use the comma(,) to separate column data, and use semicolon(;) to separate row data.
- But for some language versions of excel such as Spanish, it uses different separator characters. It uses the backslash (\) to separate column data, and use the semicolon (;) to separate row data.

#### 1.2 How To Get Excel Array From Excel Cell Range.

- Now we have introduced the excel array concepts, but how to get it from the excel row, column, and cell ranges, now I will tell you how to do it.
- Select an empty cell in the worksheet, then input the equal character
**=**in the formula bar. - Select one excel row, column, or cell range that contains data in the excel worksheet.
- Then you can see the text in the formula bar has been changed to something like
**=A1:C1**,**=A1:A6**, or**=A1:C6**. - Now press the key
**F9**on the keyboard, then you can get the cell data array value like**{1,”IBM”;2,”Apple”;3,”Google”}**in the formula bar. - You can also use the excel function
**ARRAYTOTEXT(cell_range)**such as the formula**=ARRAYTOTEXT(A1:B2)**to get the cell range’s text array value. - The above function only exists in the Microsoft 365 version of Excel.

### 2. Excel Array Formula Examples.

#### 2.1 What Is Array Formula.

- An array formula is an excel formula that can operate on multiple values that are saved in an excel array object.
- The array formula can return either a single value or a range of values that are saved in an array.
- The array formula is very flexible when you want to operate multiple data values saved in excel cell ranges.
- When you apply the formula to an excel cell range ( cell array ), you should press the key
**Ctrl + Shift + Enter**at the same time after you input the formula in the excel formula bar. - But in excel 365, the array formula is a built-in feature, you do not need to press the
**Ctrl + Shift + Enter**key combination.

#### 2.2 Excel Array Formula Example.

- There are 4 columns in this example.

**A****B****C****D****1**Unit Price Sales Count Unit Total Total **2**10 100 1000 2010 **3**15 30 450 **4**80 7 560 - Column
**A**stores the**Unit Price**value and column**B**stores the**Sales Count**value. - We want to calculate the value of column
**A ***column**B**and save the result value in column**C**. - That means we want to save
**A2*B2**to**C2**,**A3*B3**to**C3**, and**A4*B4**to**C4**. - To implement the above feature, you should first select cell range
**C2:C4**. - Then input the formula
**=A2:A4*B2:B4**in the excel formula bar input text box. - And then press the
**Ctrl + Shift + Enter**key at the same time. - Then you can see that column
**C**contains values of column**A*** column**B**accordingly. - And the formula bar text is wrapped with curly brackets
**{}**. - If you are using excel 365 then you do not need to press the key combination, just press the
**Enter**key to do the same thing. - There is another function
**ARRAYTOTEXT**provided in excel 365 only, this function can convert the excel array to a line of the text string. - Select cell
**E2**, input the formula**=ARRAYTOTEXT(A2:A4*B2:B4)**in the formula bar, and press**Enter**key. - Then you can see the text string
**1000, 450, 560**in cell**E2**. - To calculate the total number of cell values in column
**C**, we can select cell**D2**. - Then input the formula text
**=SUM(A2:A4*B2:B4)**in the formula bar and press**Enter**(excel 365) or press**Ctrl + Shift + Enter**. - The excel
**SUM**function will add all the number values in the array and return the result.

#### 2.3 Excel Array Formula Advantages.

- Excel array formula has the below advantages.
- Using the array formula can prevent accidental deletion of data, in the above example, you can find you can not delete a single cell value in column
**C**, you can only delete column C entirely. - Using the array formula can make the formula size smaller because there is only one formula in the excel file. If you use excel autofill to fill the data in column
**C**, then each cell in column**C**will contain one formula, if there are a lot of cells in column**C**then the formula size is too big also.