Wasim Akram
While working in Microsoft Excel, sometimes we need to copy formulas with an array in Excel. You must be thinking it’s simple just select and copy paste the selected formula. Well, it’s not that easy to copy array formulas in a simple way. Today, in this article, I am sharing with you how to copy array formula in Excel.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Array Formula Example in Excel
The array formula is used to calculate multiple calculations. In the following part, I have also explained an example of an array formula for better understanding where you have to enter only one array formula to get the output.
Suppose we have a dataset of some “Sold Products”, “Unit Price”, “Quantity”, and “Delivery Date”. Now will determine the Total Sales collection using both the traditional way and array formula. Stay tuned!
Steps:
- Let’s check the traditional way first. For that, we will need to choose a cell (G5) and apply the below multiplying formula.
- Then we will calculate the total sum by using the SUM function.
- By using the array formula you just need to write down a single formula with multiple criteria. Just choose a cell (C14) and write the below formula down-
=SUM(D5:D12*E5:E12)- Hence pressing CTRL+SHIFT+ENTER will lead to the desired result we are looking for. Simple isn’t it?
- Here we have our desired output with a single array formula in our hands.
Array Formula with Multiple Criteria
Sometimes you might need to add multiple functions inside an array formula with multiple criteria. Just go through the steps below-
Steps:
- Choose a cell (I4) and write the formula from the below-
=SUM(COUNTIFS(C5:C12,{"A","B"}))Where,
- The COUNTIFS function will count cells according to the given specification-”A”, and “B” from the cell range (C5:C12).
- The SUM function will sum up the total counted cells in the range.
- Now, press ENTER, and here we have our result calculated with multiple criteria using an array.
Read More: How to Copy SUM Formula in Excel (6 Easy Methods)
VLOOKUP Array Formula
In order to search for values we often use the VLOOKUP function in Excel. Well, with the VLOOKUP array formula you can provide multiple conditions inside an array. Check out the following screenshot. Here we will search for values according to the Product from the list.
Steps:
- Simply, choose cells (C15:E15) and apply the below formula-
=VLOOKUP(B15,B4:F12,{3,4,5},FALSE)- Hence press CTRL+SHIFT+ENTER to provide an array inside the formula.
- In conclusion, you will get the value in the newly chosen rows according to your product. Simple isn’t it?
Read More: How to Copy Formula in Excel without Dragging (10 Ways)
Similar Readings
- Copy Sheet to Another Workbook with Excel Formulas
- How to Copy Formula in Excel to Change One Cell Reference Only
- Copy a Formula in Excel with Changing Cell References
- How to Copy Formula Down Without Incrementing in Excel
- Creating and Copying Formula Using Relative References in Excel
3 Simple Methods to Copy Array Formula in Excel
In the following, I have described 3 simple and quick ways to copy array formula in Excel.
Imagine we have a dataset with sales of Product-A, B, C, and their Total Sales month-wise. The total sales for all the products are calculated with a simple array formula. Now we will learn a few tricks to copy array formula in Excel.
1. Use Fill Handle Tool
The simplest way of copying and pasting an array formula is by using the “Fill Handle” tool. The fill handle tool is a blessing by which you can not only copy but also fill cells with your desired output. Follow the instructions below.
Steps:
- First, choose a cell (F5) where the array formula is placed.
- Now, moving the cursor to the border of the cell (F5) you will see the “Fill” icon appearing.
- Simply, drag the “Fill Handle” down to copy the array formula for all the cells in the column.
- As you can see, the array formula is copied in the worksheet column wise just like in the following screenshot.
Read More: How to Copy a Formula Down the Column in Excel(7 Methods)
2. Utilize Keyboard Shortcut
Sometimes you might use some keyboard shortcut to copy array formulas from a cell. Follow the steps below-
Steps:
- First, choose the whole range where you want to copy the array formula.
- Second, press CTRL+D from the keyboard.
- Thus, you will see the array formula will be copied in the selected range and display output according to data from the table.
Read More: How to Copy Formula Down with Shortcut in Excel
3. Using Edit Mode
If you want you can also use the Edit Mode to copy the array formula from a cell. You just have to go to Edit Mode and copy the actual formula without an array and paste it to a new location. Follow the steps below to learn this simple technique-
Steps:
- To start with, choose a cell (F5) with an array formula inside it.
- Hence, click the F2 key to go to Edit Mode.
- Now, double click the mouse button to highlight the whole formula. After the formula is highlighted press CTRL+C to copy.
- Then, moving to a new desired cell (B15) where you want your output press CTRL+V to paste.
- Thus, you will see the array formula is copied without an array in the new location.
- In order to get the array hit CTRL+SHIFT+ENTER from the keyboard.
- Finally, we have successfully copied our array formula in Excel. Simple isn’t it?
Read More: How to Copy Formula to Entire Column in Excel (7 Ways)
Conclusion
In this article, I have tried to cover all the methods to copy array formula in Excel. Take a tour of the practice workbook and download the file to practice by yourself. I hope you find it helpful. Please inform us in the comment section about your experience. We, the Exceldemy team, are always responsive to your queries. Stay tuned and keep learning.
Related Articles
- Excel Not Copying Formulas Only Values (4 Issues with Solutions)
- How to Copy a Formula in Excel Without Changing Cell References
- Calculate Data Across Worksheets with Formulas in Excel
- Making Same Change to Multiple Worksheets (9 Examples)
- How to Copy Exact Formula in Excel (13 Methods)
- Copy and Paste Formulas from One Workbook to Another in Excel
- How to Copy Formula to Another Sheet in Excel (4 Ways)
