You may be used to copying formulas in Excel by dragging them over to all the cells, but if you have a humongous dataset to copy the formula then it will become easier to copy formula in Excel without dragging. By going through this article you will know various ways of doing this and so let’s start our article.

**Table of Contents**hide

## Download Workbook

## 10 Ways to Copy Formula in Excel without Dragging

Here, we will use the following dataset to calculate the profit of this company by using the *Selling Price *and *Cost Price *of this dataset, and then we will copy this formula by using different methods.

We have used *Microsoft Excel 365* version here, you can use any other versions according to your convenience.

__Method-1__: Double-Clicking to Copy Formula in Excel without Dragging

Here, we will calculate the profits of different products in the **Profit **column and after using the formula in the first cell of this column we will copy it throughout other cells of this column by double-clicking on the **Fill Handle **tool.

** Steps**:

➤ Enter the following formula in cell

**F4**

`=D4-E4`

Here, **D4 **is the ** Selling Price **and

**E4**is the

**.**

*Cost Price*➤ Double-click on the **Fill Handle **tool.

In this way, you will get the formulas for all of the cells of the **Profit **column to calculate profits.

**Read More: How to Copy Formula in Excel (6 Quick Methods)**

__Method-2__: Pressing CTRL+ENTER to Use a Formula Simultaneously for All of the Cells

Here, we will show a way to enter the formula for all of the cells while typing only in the first cell by pressing a **CTRL+ENTER**.

** Steps**:

➤ Select all of the cells of the

**Profit**column and then type the following formula in cell

**F4**

`=D4-E4`

Here, **D4 **is the ** Selling Price **and

**E4**is the

**.**

*Cost Price*➤ Press **CTRL+ENTER**.

Afterward, you will get the formulas in all of the cells of the **Profit **column.

**Read More: Copy Formula in Excel by Changing Only One Cell Reference**

__Method-3__: Shortcut Keys to Copy Formula in Excel without Dragging

In this section, using some shortcut keys we will copy the formula to all of the cells of the **Profit **column.

** Steps**:

➤ Enter the following formula in cell

**F4**and then copy it by pressing

**CTRL+C**.

`=D4-E4`

Here, **D4 **is the ** Selling Price **and

**E4**is the

**.**

*Cost Price*➤ Press the **Left** (←) key to go to the left adjacent cell to go down to the last used cell as if we try to go down to the last cell from **Column F **then it will take us to the very last cell because this column is empty.

➤ Now, to go down to the last cell of this column press **CTRL + Down **key (↓).

➤ Press **Right **key (→) and then **CTRL + SHIFT + Up **key (↑) to select all of the cells of the **Profit **column and then paste the formulas by pressing **CTRL + V**.

Finally, you will get the formula copied to all of the cells of the **Profit **column.

**Read More: Shortcut to Copy Formula Down in Excel (7 Ways)**

__Method-4__: Using Name Box to Copy Formula in without Dragging

Here, we will use the ** Name Box **to copy the formula easily to all of the cells of the

**Profit**column without dragging.

** Steps**:

➤ Enter the following formula in cell

**F4**and then copy it by pressing

**CTRL+C**.

`=D4-E4`

Here, **D4 **is the ** Selling Price **and

**E4**is the

**.**

*Cost Price*➤ Type the range of cells **F5:F14 **in the** Name Box **to paste the formula in these cells.

After pressing **ENTER **the ranges which we have written in the ** Name Box **will be selected like the following figure.

➤ Press **CTRL + V **to paste the formula in the cells of the range **F5:F14**.

**Related Content: How to Copy a Formula in Excel with Changing Cell References**

__Method-5__: Copy Formula without Dragging Using Fill Option

You can use the **Fill **option also to copy the formula in all of the cells without dragging.

** Steps**:

➤ Enter the following formula in cell

**F4**

`=D4-E4`

Here, **D4 **is the ** Selling Price **and

**E4**is the

**.**

*Cost Price*➤ Select all of the cells of the **Profit **column and then go to **Home **Tab >> **Editing **Group >> **Fill **Dropdown >> **Down **Option.

Eventually, you will get the formula for all of the remnant cells of the **Profit **column.

**Read More: How to Copy Formula to Entire Column in Excel (7 Ways)**

__Method-6__: Format as Table Option to Copy Formula in Excel without Dragging

You can use the **Format as Table **option to copy the formula without dragging it in the **Profit **column.

** Steps**:

➤ Go to

**Home**Tab >>

**Styles**Group >>

**Format as Table**Dropdown >> select any table style.

Then the **Create Table **dialog box will appear.

➤ Select the range of your dataset.

➤ Check the **My table has headers **option and click **OK**.

In this way, we will convert our range to the following table.

➤ Select the **F4** cell and start typing the formula

`=D4-E4`

Here, **D4 **is the ** Selling Price **and

**E4**is the

**.**

*Cost Price*But, when we start to select the cells **D4 **and **E4**, Excel will convert them automatically to the structured reference system and modify the formula as follows

`=[@[Selling Price]]-[@[Cost Price]]`

After pressing **ENTER**, we will get the formula for all of the cells of the **Profit **column.

**Related Content: How to Copy a Formula Down the Column in Excel(7 Methods)**

__Method-7__: Using Table Option to Copy Formula without Dragging

Here, we will use the **Table** option to copy the formula throughout all of the cells of the **Profit **column.

** Steps**:

➤ Go to

**Insert**Tab >>

**Table**Option.

Afterward, the **Create Table **dialog box will appear.

➤ Select the range of your dataset.

➤ Check the **My table has headers **option and click **OK**.

Then, you will get the following table.

➤ Select cell **F4 **and start typing the formula

`=D4-E4`

Here, **D4 **is the ** Selling Price **and

**E4**is the

**.**

*Cost Price*But, when we start to select the cells **D4 **and **E4**, Excel will convert them automatically to the structured reference system and modify the formula as follows

`=[@[Selling Price]]-[@[Cost Price]]`

After pressing **ENTER**, you will get the formula for all of the cells of the **Profit **column.

**Related Content: How to Copy Formula to Another Sheet in Excel (4 Ways)**

__Method-8__: Power Query to Copy Formula without Dragging

Here, we will be using the **Power Query** option to get the formula for all of the cells to get the profit of the products.

** Steps**:

➤ Go to

**Data**Tab >>

**From Table/Range**Option.

Then, the **Create Table **dialog box will appear.

➤ Select the range of your dataset.

➤ Check the **My table has headers **option and click **OK**.

After that, you will be taken to the **Power Query Editor **window.

Now, we will add a column with a formula to calculate the profits of all the products at once.

➤ Go to **Add Column **Tab >> **Custom Column** Option.

Then, the **Custom Column **wizard will open up.

➤ Write the column name (for this case **Profit**) in the **New column name **box and then write the following formula in the **Custom column formula **by inserting the required columns from the **Available columns **list.

`=[Selling Price]-[Cost Price]`

After pressing **OK**, we will get our created **Profit **column with the profits by using the formula for all of the cells.

➤ Go to **Home **Tab >> **Close & Load **Dropdown >> **Close & Load **Option.

In this way, we have closed the **Power Query Editor **window and loaded the table to a new sheet named ** Table4**.

**Related Content: Copy and Paste Formulas from One Workbook to Another in Excel**

__Method-9__: Using Dynamic Array to Copy Formula in Excel without Dragging

In this section, we will use ** Dynamic Array **to enter formulas for all of the cells of the

**Profit**column at a time but this feature will only be available to

*Microsoft Excel 365*users.

** Steps**:

➤ Enter the following formula in cell

**F4**

`=(D4:D14)-(E4:E14)`

Here, **D4:D14 **is the range of the ** Selling Price **and

**E4:E14**is the range of the

**.**

*Cost Price*After pressing **ENTER**, we will have the results by using the formula in the **Profit **column.

**Read More: How to Copy Formula and Paste as Text in Excel (2 Ways)**

__Method-10__: Copy Formula without Dragging Using VBA Code

In this section, we will get the profits easily without dragging the formula by using a **VBA **code.

** Steps**:

➤ Go to

**Developer**Tab >>

**Visual Basic**Option.

Then, the **Visual Basic Editor **will open up.

➤ Go to **Insert **Tab >> **Module **Option.

After that, a **Module** will be created.

➤ Write the following code

```
Sub formula()
Dim Lr As Long
Dim n As Long
Lr = Cells(Rows.Count, "D").End(xlUp).Row
For n = 4 To Lr
Cells(n, "F").Value = Cells(n, "D").Value - Cells(n, "E").Value
Next n
End Sub
```

Here, we have declared **Lr**, **n **as **Long**, **Lr **will return the last used row number. **FOR** loop will execute the operation of the formula for all of the cells of **Column F **from the starting *Row 4 *to the last row.

➤ Press **F5**.

Then, you will get the formula for all of the cells of the **Profit **column.

**Read More: VBA to Copy Formula from Cell Above in Excel (10 Methods)**

## Practice Section

For doing practice by yourself we have provided a** Practice** section like below in a sheet named **Practice**. Please do it by yourself.

## Conclusion

In this article, we tried to cover the ways to copy formula in Excel without dragging. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.