Excel Array Formula

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn how to create an Excel array formula. Here we will create an array formula for single-cell and multi-cell values using different functions. Here we will also create an array formula using the keyboard shortcut and evaluate the formula using the keyboard key. We can create an array formula for multiple conditions as well.

Creating an array formula is quite a useful process. Once you create an array, sorting and analyzing the data is quite easy. You can easily search the data using the array. We can use an array in Dynamic programming to minimize the hassle.

Download Practice Workbook

You may download the below workbook for practice.

Array Formula.xlsx

Table of Contents Expand

How to Create Array Formula in Excel

In the below methods, we will create an array using a keyboard shortcut and evaluate how the array works using the keyboard key.

Here, we will apply the keyboard shortcut CTRL + SHIFT + ENTER to create an array formula in Excel.

{=SUM(C5:D5)}

How to Evaluate Array Formula in Excel

Here, we will evaluate the array formula in Excel by pressing the keyboard key F9.

=SUM({94,97})

Note: You must select a portion of the formula; otherwise, pressing F9 will simply show the output.

Read More: What Is an Array in Excel?

How to Apply Array Formula for Single-Cell Value: 2 Methods

You can create an array formula for a single cell value using different functions. Here you will find some single-cell value formulas.

1. Combining SUM and IF Functions

Therefore, we will combine the SUM and IF functions and make this combined formula an array formula using the second bracket.

{=SUM(IF($C$5:$C$12<>"700",$D$5:$D$12))}

2. Using MAX Function

In this method, we will use the MAX function and create an array formula to create a single-cell value formula.

{=MAX(C5:C12)}

Note: There are many single-cell value functions to create an array formula, such as in the MIN function, the AVERAGE function, etc.

How to Apply Array Formula for Multi-Cell Values: 2 Methods

In this part, we will create an array formula for multi-cell values. Here we will show some examples using the below functions to execute this process.

1. Using TRANSPOSE Function

The TRANSPOSE function is an array function, and the output of this function returns an array.

=TRANSPOSE(B4:D8)

2. Applying the FILTER Function

Here, we will use the FILTER function to create an array formula for a multi-cell value.

=FILTER(B5:C12,D5:D12)

Note: There are some other functions where the output works as an array. For instance, the FREQUENCY function, the LINEST function, etc.

How to Use Array Constant in Excel Formula: 3 Methods

Sometimes, we need an array constant in Excel. Suppose we need to get the average of the first two numbers in a dataset. In that case, we can use an array of constants as {1,2} in the formula. Follow the below methods to use array constants.

1. Getting Array Constant Row-wise

In this method, we use the array constant row-wise using the AVERAGE and LARGE functions.

=AVERAGE(LARGE($D$5:$D$12, {1,2,3}))

2. Getting Array Constant Column-wise

In this method, we will use an array constant column-wise.

=AVERAGE(LARGE($C$6:$F$6, {1,2}))

3. Applying Array Constant for Multiple conditions

In this method, we will apply an array constant to multiple conditions using the SUM and COUNTIFS functions.

=SUM(COUNTIFS(C5:C12,"T-shirt", D5:D12,{"Delivered","Pending"}))

How to Add Operators with Array Formula in Excel: 2 Methods

In this part, we will add other operators to array formulas in Excel. Follow the below methods to execute this process.

1. Using AND Operator with Array Formula

In this method, we will use the AND operator with an array formula to get the total value for specific criteria.

=SUM(IF(((C5:C12="T-shirt") * (D5:D12="Delivered")), (E5:E12)))

2. Applying OR Operator with Array Formula

Here we will use the OR operator with an array formula to get the output for specific criteria.

=SUM(IF(((C5:C12="T-shirt") + (D5:D12="Delivered")), (E5:E12)))

Things to Remember

You can’t apply an array formula to a merged cell. First, you need to unmerge the cell and then apply the formula.
Select a portion of the formula before evaluating it; otherwise, the F9 key will show the final output.
An array formula is built to return the output in multiple cells but you cannot put an array formula in multiple cells. We need to select a cell to enter the formula and the formula will return the output as a range.
While entering a range in an array formula try to avoid selecting the total column and row.

Conclusion

Here, we covered how to create an Excel array formula using keyboard shortcuts and evaluate the formula. Here we also created an array formula for single-cell values and multi-cell values. We cover every way to execute this process. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions, or you can also visit Exceldemy to explore more.

Frequently Asked Questions

Q1: How do I use an array formula in Excel?

Ans: To use an array formula, select cell >> Enter the formula >> press CTRL + SHIFT + ENTER.

Q2: What is a multi-cell array formula in Excel?

Ans: A multi-cell array formula is a formula that returns the value as an array, and the output will be a range.

Q3: What is the dynamic array formula in Excel?

Ans: The formula that returns a range is called the dynamic array formula, and you cannot edit a single cell value if the array formula is dynamic.