## Excel array formulaArray formula is an extremely powerful but difficult one formula in Excel. A single array formula can perform multiple calculations at once. Basically, it is a type of formula that allows the users to perform any operation of multiple values rather than a single value. The returned value from the array formula can be an array item or an array of items. Usually, we perform operations on a single value or range of values. An array formula should be surrounded between curly braces ({}). In this chapter, you will learn the array formula in Excel and how you can perform it on Excel data. ## What is an array?An array is a collection of multiple values or you can say that an array contains more than one value. The array values reside inside the curly braces ({}) in Excel. Whenever you want to perform any operation on an array, put it inside the curly braces. Even for array formula, you need to put the formula inside curly braces. An array formula is not an easy task to perform on Excel data. You have to learn the way to perform this. ## How to enter an array formula?Enter a simple formula and use the Basically, it evaluates the simple formula as an array formula and returns the result accordingly. Entering an array formula manually to a cell does not work. Manually entering an array formula does not return any result. ## Representation of arrayAn array looks like The array values that are separated by the semicolon, represent the vertical range of data in an Excel sheet. While for horizontal range, a comma is used to separate the values inside the array. Both the examples for the one-dimensional arrays. A two-dimensional array uses both semicolons and commas for horizontal and vertical ranges in an Excel sheet.
A vertical range represents the values of a column. Basically, if you want to represent the value of a column as array, use the vertical range for it in an Excel sheet.
A horizontal range represents row data. A horizontal range refers to the values that appeared inside a row in an Excel sheet. ## Example 1: Array formula implementation## Without an array formulaWithout an array formula, multiple values like this (A2:A9) is just a range of cells containing values in cells selected from A2 to A9. See a simple scenario for a simple dataset in which we will show you a difference of simple formula and array formula. We have taken some products with their price and quantity to order.
=SUM(B2:B5*C2:C5) Currently, this formula is like the simple formula, but when you press the "
We have not got the correct resultant value (error) because the formula has not changed to an array formula. ## Example 1We have taken an example below in which you can see that the data in (B2:B13) is the sale of one year for each month. The data in (C2:C13) is for the expense of one year from month January to December. Column D contains the profit/loss of each month whose total is placed in the end (in cell D14). For calculating the yearly profit/loss, we have to first find the per month revenue and then the total sum of the calculated revenue. It can be a lengthy process for a large set of data. We can minimize this process and make it easy by using an array formula. The array formula makes this process short. ## Multi-cell array formulaThe multi-cell array formula reduces the lengthy calculation. By using the array formula, you can get the final result (yearly revenue) at once. You need only one array formula instead of this long calculation. If you try this formula without an array formula, you have to use the formula to calculate the yearly revenue as given below and press the =SUM(B2:B13*C2:C13) But this will either return you an incorrect value or #VALUE! Error. So, do not use formula like this. If you want to perform an operation like this formula, then press the
The array formula will be as follows for the above formula. {=SUM(B2:B13*C2:C13)} An array formula is surrounded between the {} (curly braces). You do not write an array formula directly in a cell to get the result. It will return nothing, neither error nor incorrect result. So, you need to follow the steps as given below:
The
" ## Why use array formulas in Excel?With the help of Excel formula, the users can perform calculations on Excel data to do complex tasks. It reduces the efforts of users to perform lengthy calculations to get the final result by using the array formula. A single array formula replaces the various unusual formulas. You can use the array formula for tasks for this it is good for: - For example, sum the N largest or smallest values in a range that meets with certain conditions.
- Calculate the revenue for entire years from the data of each month sales and expenses. In such a situation, an array formula ignores calculation profit and lost then sum of total and provide result with a single formula.
## Excel array constantAn array constant is a set of static values. As array constants have static values, they never change when you copy a formula to other cells. In MS Excel, there are three types of an array constant. - Horizontal array constant
- Vertical array constant
- 2D array constant
## Horizontal array constantA horizontal array constant refers to the values in a row. To create a horizontal array constant, type the value separated by a comma enclosed between curly braces ({}).
To create a horizontal array constant, select the corresponding number of cells in a row and enter the following array in the formula bar. ={1,2,3,4} Now, hit the ## Vertical array constantA vertical array constant resides in an Excel column. It means the values of vertical array constant refers to the values in a column. To create a vertical array constant, type the value separated by semicolon enclosed between curly braces ({}).
To create a horizontal array constant, select the corresponding number of cells in a row and enter the following array in the formula bar. ={10; 20; 30; 40} Now, hit the ## 2D array constant2D array constant is a combination of vertical and horizontal array constant. As it is a combination of horizontal and vertical constants, it uses both semicolon and comma to represent the row and column values.
To create a 2D array constant, select the corresponding number of cells in both row and column. Then, enter the following array in the formula bar. ={"a", "b", "c"; 1, 2, 3} Now, hit the Next TopicFreeze Columns in Excel |