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.

**Table of Contents**Expand

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

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

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

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

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

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

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

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

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