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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

  • 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

Read More: 5 Examples of Using Array Formula 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.


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


How to Copy Array Formula in Excel: 3 Simple Methods

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


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


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


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


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. Stay tuned and keep learning.


Related Articles


<< Go Back to Array Formula | 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.
Wasim Akram
Wasim Akram

Wasim Akram holds a BSc degree in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Over the past 2 years, he has been actively contributing to the ExcelDemy project, where he has authored more than 150 articles. Now, he is working as an Excel VBA and Content Developer. He likes learning new things about Microsoft Office, especially Excel VBA, Power Query, Data Analysis, and Excel Statistics. He is also very interested in machine learning and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo