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.

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

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.

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

<< Go Back to Excel Formulas | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afrina Nafisa

Afrina Nafisa Alam, BSc, Industrial and Production Engineering from Ahsanullah University of Science and Technology. She has been working with the Exceldemy project for over 6 months and is currently a web content developer here. She has published over 18 articles and reviewed several during this period. She is keen to learn different features and deliver the knowledge in her current project. She is interested in learning different features of Microsoft Office, especially Excel, Power Query, Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF