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.

**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)}`

**Read More: **How to Create Array Formula in Excel

**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)`

**Read More:** 5 Examples of Using Array Formula in Excel

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

## Excel Array Formula: Knowledge Hub

**How to Copy Array Formula in ExcelÂ****How to Use Dynamic Arrays in Excel****List of Dynamic Array Formulas in Excel**

**<< Go Back to Excel Formulas | Learn Excel**