Excel Array & Array Formula Examples

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.

1. An Excel array is a collection of data values that are wrapped by curly braces({}).
2. And each value in the array is separated by the delimiter character.
3. 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.
4. 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.
5. 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.
6. Most language versions of excel use the comma(,) to separate column data, and use semicolon(;) to separate row data.
7. 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.

1. 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.
2. Select an empty cell in the worksheet, then input the equal character = in the formula bar.
3. Select one excel row, column, or cell range that contains data in the excel worksheet.
4. Then you can see the text in the formula bar has been changed to something like =A1:C1, =A1:A6, or =A1:C6.
5. 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.
6. 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.
7. The above function only exists in the Microsoft 365 version of Excel.

2. Excel Array Formula Examples.

2.1 What Is Array Formula.

1. An array formula is an excel formula that can operate on multiple values that are saved in an excel array object.
2. The array formula can return either a single value or a range of values that are saved in an array.
3. The array formula is very flexible when you want to operate multiple data values saved in excel cell ranges.
4. 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.
5. 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.

1. 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

2. Column A stores the Unit Price value and column B stores the Sales Count value.
3. We want to calculate the value of column A * column B and save the result value in column C.
4. That means we want to save A2*B2 to C2, A3*B3 to C3, and A4*B4 to C4.
5. To implement the above feature, you should first select cell range C2:C4.
6. Then input the formula =A2:A4*B2:B4 in the excel formula bar input text box.
7. And then press the Ctrl + Shift + Enter key at the same time.
8. Then you can see that column C contains values of column A * column B accordingly.
9. And the formula bar text is wrapped with curly brackets {}.
10. 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.
11. There is another function ARRAYTOTEXT provided in excel 365 only, this function can convert the excel array to a line of the text string.
12. Select cell E2, input the formula =ARRAYTOTEXT(A2:A4*B2:B4) in the formula bar, and press Enter key.
13. Then you can see the text string 1000, 450, 560 in cell E2.
14. To calculate the total number of cell values in column C, we can select cell D2.
15. Then input the formula text =SUM(A2:A4*B2:B4) in the formula bar and press Enter (excel 365) or press Ctrl + Shift + Enter.
16. The excel SUM function will add all the number values in the array and return the result.

2.3 Excel Array Formula Advantages.

1. Excel array formula has the below advantages.
2. 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.
3. 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.