How to Copy Array Formula in Excel (3 Easy Methods)

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!

Array Formula Example in Excel

Steps:

  • Let’s check the traditional way first. For that, we will need to choose a cell (G5) and apply the below multiplying formula.

Array Formula Example in Excel

  • 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?

Array Formula Example in Excel

  • Here we have our desired output with a single array formula in our hands.

Array Formula Example in Excel


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.

Array Formula with Multiple Criteria

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

VLOOKUP Array Formula

  • In conclusion, you will get the value in the newly chosen rows according to your product. Simple isn’t it?

VLOOKUP Array Formula

Read More: How to Copy Formula in Excel without Dragging (10 Ways)


Similar Readings


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.

Use Fill Handle Tool

  • As you can see, the array formula is copied in the worksheet column wise just like in the following screenshot.

Use Fill Handle Tool

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.

Utilize Keyboard Shortcut

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.

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

Using Edit Mode

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

Using Edit Mode

  • Finally, we have successfully copied our array formula in Excel. Simple isn’t it?

Using Edit Mode

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

Wasim Akram

Wasim Akram

Hi! my name is Wasim Akram. I am a graduate in mechanical engineering from Ahsanullah University of Science & Technology. I am passionate about learning new things and writing articles. I write article about Microsoft Excel and it's my favourite thing to do.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo