Creation and usage of the Array formula is an essential part of Excel operations. The use of Multiple and Single-cell Array formulas is essential for getting output in the desired format. For example, we sometimes might want to create multiple outputs in one go. For this, we can create multiple cell array formulas to get the output in Excel. While using the array formulas, users sometimes face various difficulties like array spillage. not proper referencing etc. Every single one of them is addressed here with proper examples.
Below is a sample example showing where we have used the cell array to demonstrate the multiplication of the sales figure with the package sale and the determination of the final sales figure.
Here we need to get the final sale of values by multiplying the values in the sales and the number of the packages in the range of cells D5:D13,
What is Array Formula
A formula that can execute many calculations on one or more items in an array is called an array formula. A row, column, or combination of rows and columns of data can be thought of as an array. Array formulas can provide either many results or only one. The array formulas basically can be divided into two segments or types, Multiple Cell array type, and Single-cell array type. Both are described below with examples.
Multiple Cell Array Formula
Below we presented a sample formula for the multiple-cell array.
- Select cell F5 and enter the following formula:
=C5:C13*D5:D13
- Then we will see that there is an array symbol or array output in the range of the cell F5:F13.
Single Cell Array Formula
For output in a single cell, you can follow the below method, for this enter the following formula in cell F5.
=C5:C13*D5:D13
After entering the formula, we can see that the Summation value of the sales is now present in cell F5.
Read More: What Is an Array in Excel?
How to Launch VBA Editor in Excel
You can easily launch the VBA editor by pressing Alt+F11 or using the Visual Basic command from the Developer tab. As shown in the image below,
- After entering the Developer tab, tango to Insert > Module, and you will see an editor where you can put the necessary code.
Note:
In any case, if you can’t find the Developer tab in the ribbon, then you need to enable it from the options. You can easily enable the developer tab by following this article.
How to Create Array Formula in Excel: 13 Suitable Examples
In the following 10 examples, we will show you the various ways to create an array formula in Excel. According to the needs of the examples, we have used different datasets.
We have used Microsoft Excel 365 version for creating this Excel. So, we are pressing ENTER here, but if you are using any other versions then you have to press CTRL+SHIFT+ENTER.
Example 1: Create One Dimensional Array Formula with Constant Values in Excel
Here, we will make a list of products in the Product column vertically using an array formula.
- Here, we have used the names of different products inside of inverted commas and separated each product with semicolons. Finally, we have enclosed the whole list with the second brackets.
- Type the following formula in cell B4:
={"Apple";"Orange";"Kiwi";"Tomato";"Banana"}
- After pressing ENTER, you will get the following list of products vertically in the Product column.”
- If you want to enter the name of the products horizontally, then adopt a small change in your formula like the following technique.
- Type the following formula in cell C4:
={"Apple", "Orange", "Kiwi", "Tomato", "Banana"}
- Here, we have used the names of different products inside of inverted commas and separated each product with commas. Finally, we have enclosed the whole list with the second brackets.
- Finally, the following results will appear after pressing ENTER.
Example 2: Create Two Dimensional Array Formula with Constant Values in Excel
In this section, we will create a two-dimensional array formula to enter a list of products with their corresponding sales values.
- Write down the following formula in cell C3:
={"Apple", "Orange", "Kiwi", "Tomato", "Banana";3981,3849,3003,2652,1275}
- Here, the name of the products and the sales values are separated with commas. But the final product name is separated by a semicolon from the sales value of the first product.
- Press ENTER.
- In this way, you will get a list of products with their corresponding sales values.
Example 3: Using TRANSPOSE Function with Array Formula to Reverse Rows and Columns
Here, we will be inverting the columns of the first dataset into rows in the following table. For this purpose, we will use the TRANSPOSE function.
- Type the following formula in cell C11.
=TRANSPOSE(B4:C9)
- Here, B4:C9 is the range of the rows which we want to transform into columns.
- After pressing ENTER, you will get the following converted table.
Example 4: Create Array Formula with SUM Function in Excel
In the following dataset, we will first multiply the sales values with their corresponding discounts and then add up the discounted sales values. This work can be done with a single formula using an array formula with the SUM function.
- Type the following formula in cell D10:
=SUM(C5:C9*D5:D9)
Here, C5:C9 is the range of the sales, and D5:D9 is the range of the discounts.
- After pressing ENTER, we have got the total discounted sales value.
Example 5: Create Array Formula with MAX Function in Excel
In this section, we will use the MAX function with an array formula to get the maximum profit value after subtracting the sales values from the cost prices.
- Write down the following formula in cell D10.
=MAX(D5:D9-C5:C9)
- Here, C5:C9 is the range of Cost Prices, and D5:D9 is the range of Sales values.
- Press Enter after this.
- After pressing Enter, we can see that the Maximum Profit of the sales is now showing in cell D11.
Example 6: Create Array Formula for Multiple Rows in Excel
In this example, we will be calculating the bonus earned by the employees by multiplying the profits of the products with their corresponding % Bonuses.
- Use the following formula in cell F4.
=(D4:D8-C4:C8)*E4:E8
- Here, C4:C8 is the range of Cost Prices, D4:D8 is the range of Sales values, and E4:E8 is the range of the %.
- After pressing ENTER, you will get bonuses for each product finally.
Example 7: Create Array by Summing up Nth Largest or Smallest Numbers by Using Formula in Excel
Here, we will be summing up the largest two of the sales values using the LARGE function in an array formula. Also, for calculating the smallest of the two sales values we will be using the SMALL function in an array formula.
- Enter the following formula in cell E4.
=SUM(LARGE(C4:C8,{1,2}))
- Here, C4:C8 is the range of the sales values, and {1,2} is for extracting the largest two values.
- Type the following formula in cell E6 to get the sum of the lowest two sales values.
=SUM(SMALL(C4:C8,{1,2}))
- Here, C4:C8 is the range of the sales values, and {1,2} is for extracting the smallest two values.
Example 8: Create Array with Multiple Criteria in Formula in Excel
Here, we will count the total number of times the products Mango and Orange sold by Lily by using the COUNTIFS function in an array formula.
=SUM(COUNTIFS(B4:B12,"Lily",C4:C12,{"Mango","Orange"}))
Formula Breakdown
- COUNTIFS(B4:B12,”Lily”,C4:C12,{“Mango”,”Orange”}) → becomes
Output → {0,1}
- SUM(COUNTIFS(B4:B12,”Lily”,C4:C12,{“Mango”,”Orange”})) → becomes
SUM({0,1})
Output → 1
Example 9: Combine IF and SUM to Create an Array Formula in Excel
In this section, we will be calculating the total sales value for Mango sold by David using the IF function and the AND operator within an array formula.
- Write down the following formula in cell D15.
=SUM(IF(((B4:B12="David")*(C4:C12="Mango")),(D4:D12)))
- When both of the conditions B4:B12=”David” and C4:C12=”Mango” will be fulfilled for a row, then the corresponding sales values will be extracted and then the values will be added up.
- After pressing ENTER, you will have the following result.
Example 10: Combine IF Function with OR Operator to Create Array Formula in Excel
In this example, we will be calculating the total sales value for Mango sold by David using the IF function and the OR operator within an array formula.
- Write down the following formula in cell D15.
=SUM(IF(((B4:B12="David")+(C4:C12="Mango")),(D4:D12)))
- When any of the conditions B4:B12=”David” and C4:C12=”Mango” will be fulfilled for a row, then the corresponding sales values will be extracted and then the values will be added up.
- Press ENTER.
- Finally, you will get the following result.
Example 11: Use of ROW Function to Create Array Formula in Excel
We can combine the ROW function and the SUM function altogether to get the average of the values in a range of cells.
- In this process we can enter the following formula in cell F5:
=SUM(D5:D13/ROW(D5:D13))
- After pressing Enter we can see that the average of the values in the range of cell D5:D13 is now present in cell F5.
Example 12: Create Array Formula to Count Cell in a Range of Cells
In this example, we can count the number of cells that contain specific values using formulas like the ISNUMBER function and the SEARCH function.
- Select cell E5 and enter the following formula:
=SUM(--(ISNUMBER(SEARCH("Apple",C5:C13)))*1)
- After pressing Enter we can see that the number of cells that contain Apple in the Product category are present in cell E5.
Example 13: Create Array Formula to Average a Range of Cell
Here we can combine the COUNT and SUM functions altogether to create an array formula using which we can get the average of the sales values.
- Select cell F5 and enter the following formula:
=SUM(D5:D13)/COUNT(D5:D13)
- After pressing Enter we can see that Cell F5 is now showing the Average of the values
How to Create a Dynamic Array Formula in Excel: 6 Suitable Examples
Now, we will discuss the uses of the six dynamic functions which are an extension of the Microsoft Excel 365 version.
Example 1: Using UNIQUE Function to Create Array Formula in Excel
Here, we will extract the unique names of the SalesPerson column using the UNIQUE function.
- Enter the following formula in cell B15.
=UNIQUE(B4:B12)
- Finally, you will have unique names.
Example 2: Use of FILTER Function to Create Array Formula in Excel
Here, we will extract the data for the SalesPerson Katy using the FILTER function.
- Type the following formula in cell B15.
=FILTER(B5:D13,B5:B13="Katy")- Here, B4:D12 is the range, and B4:B12=”Katy” is the criterion based on which we are filtering.
- Press ENTER.
- In this way, you will get the filtered table.
Example 3: Applying SORT and SORTBY to Create Array Formula in Excel
Here, we will be sorting data using the SORT and SORTBY functions.
- Use the following function in cell B15.
=SORT(B4:D12,1,-1)
- Here, B4:D12 is the range of the dataset, one is the column number on the basis of which we are sorting, and -1 is for descending order.
- After pressing ENTER, you will get the sorted dataset like the following figure where it is sorted in descending order depending on the SalesPerson.
- The same work can be done also by using the SORTBY function.
=SORTBY(B4:D12,C4:C12,1)
- Here, B4:D12 is the range of the dataset, C4:C12 is the column range on the basis of which we are sorting, and 1 is for ascending order.
- After clicking OK you will see that the salespeople are now sorted by their product by order.
Example 4: Applying SEQUENCE Function to Create Array Formula in Excel
Here, we will put some serial numbers from 1 to 9 in a sequence by using the SEQUENCE function.
- Enter the following formula in cell B4.
=SEQUENCE(9,1,1,1)
- Here, 9 is the total rows, 1 is the column number, 1 is the starting number, and 1 is the step number.
Example 5: Utilizing RANDARRAY Function to Create Array Formula in Excel
For creating random serial numbers, here we will be using the RANDARRAY function.
- Enter the following formula in cell B4.
=RANDARRAY(9,1,1,9,TRUE)
Note:
The serial numbers generated by this function will be automatically changed after each time you refresh in Excel.
Example 6: Using XLOOKUP Function to Create Array Formula in Excel
We can use the XLOOKUP function to create an array formula to search values in a range of cells and then return desired values.
- For this, select cell F5 and enter the following formula:
=XLOOKUP("Katy",Table1[Salesperson],Table1[Sales],1,0)
- Press Enter after this.
- After pressing Enter you will notice that the desired values from the table are now showing.
Note:
Users need to convert their data to a table before applying this method.
Why is the Array Formula Not Working in Excel?
Array formulas can be a bit tricky in Excel, and there are a few reasons why they might not work as expected. Here are some common issues that can cause problems with array formulas:
- Not entering the formula correctly: Array formulas require that you press Ctrl+Shift+Enter instead of just Enter after typing the formula. If you forget to do this, the formula will not work properly.
- Incorrectly referencing cells: Array formulas can be sensitive to the order in which you reference cells. If you reference cells in the wrong order, or if you reference a cell that contains a formula that does not return an array, the formula may not work correctly.
- Using incompatible formulas: Not all formulas can be used in array formulas. Some formulas are designed to work with single values, while others are designed to work with arrays. If you try to use a formula that is not compatible with arrays, the formula will not work.
- Too much data: If you are trying to use an array formula on a large amount of data, it may be too much for Excel to handle. This can cause the formula to fail or cause Excel to crash.
- Calculation mode: Sometimes, Excel’s calculation mode can cause array formulas to not work as expected. Make sure that your calculation mode is set to Automatic, as this will ensure that the formula is recalculated whenever the data changes.
- Conflicting add-ins: Sometimes, add-ins can interfere with array formulas. Try disabling any add-ins that you have installed and see if that resolves the issue.
Excel Array Constants
There are two types of array constants that can be seen in Excel. First one is
Horizontal Array Constants
we can enter the following formula in the cell and get the maximum of the array values.
=MAX({5,7,2,9})
Longitudinal Array Constants
We can enter the following formula in the workspace, and we can see the longitudinal array average of the array values.
=AVERAGE({10;20;30;40})
Limitations of the Array Formulas in Excel
Despite its wide usage, array formula usage has some lacking, they are described below.
- Slower calculation times: Array formulas can take longer to calculate than regular formulas, especially if they are used on large amounts of data. This can result in slower performance in your spreadsheet.
- Limited compatibility: Not all Excel functions and formulas can be used within an array formula. Some functions, such as SUMIFS and COUNTIF, cannot be in array formulas.
- Complexity: Array formulas can be more complex to write and understand than regular formulas. This can make it more difficult to troubleshoot if there are errors in the formula.
- Limited editing: Once an array formula has been entered into a cell, it cannot be edited in the same way that a regular formula can. Instead, you must edit the formula in its entirety, which can be time-consuming.
- User knowledge: Array formulas are not well-known among average Excel users. This means that other users may not understand your formulas and could accidentally overwrite them, leading to errors in your spreadsheet.
- Limitations on number of elements: Excel has limitations on the number of elements that can be used in an array formula. For example, in Excel 2010 and later, the maximum number of elements in an array formula is 1,048,576.
Frequently Asked Questions
What does {} do in Excel?
In Excel, the curly braces {} are used to enclose an array formula. An array formula is a formula that can perform calculations on multiple values in a range of cells and return a single result.
How do I create an array list in Excel?
You can create a Named range and then can create a list from there. After you create the named range, you can execute various operations using that named list.
Can they be entered into a Merged cell?
Normally, array formulas are not for the merged cells. If you try to enforce it, it might give you some error.as shown in the below image.
Things Need to Remember
- Array formulas must be entered using Ctrl + Shift + Enter instead of just Enter. This tells Excel that you are entering an array formula, and it will display the formula in curly braces {}.
- Moreover, array formulas can only be entered in a single cell, and the formula result will be displayed in that cell. However, the formula can reference a range of cells.
- The curly braces {} must be typed in manually when entering the formula, but you don’t need to include them when editing the formula later.
- Array formulas can be used with a wide range of functions in Excel, such as SUM, AVERAGE, MIN, MAX, and more. These functions will perform the calculation on each individual cell in the array, and then return the result as a single value.
- However, It’s important to make sure that the range of cells referenced in the array formula is the same size and shape as the result you want to return. If the ranges are different, the formula may return an error.
- Array formulas can be a powerful tool in Excel, but they can also be complex and difficult to understand. It’s important to test your formulas thoroughly to make sure they are producing the expected results.
Download Practice Workbook
Download the following workbook to practice by yourself.
Conclusion
In this article, we have discussed various ways to create an array formula in Excel. Among all of these examples, the ones with the dynamic array are the most useful ones. We also showed the array of constants that are visible in Excel. We also showed how the Excel array formulas can be deployed in the single-cell and multi-cell systems. Hope these examples will help you a lot.
Related Articles
<< Go Back to Array Formula | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
