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