Use the following dataset to calculate the profit of this company.

### Method 1- Double-Clicking to Copy Formula in Excel without Dragging

** Steps**:

- Enter the following formula in
**F4**

`=D4-E4`

**D4 **is the *Selling Price *and **E4 **is the *Cost** Price*.

- Double-click the
**Fill Handle**tool.

You will see the formulas in all cells of the **Profit **column to calculate profit.

**Read More: **How to Copy Formula Down Without Incrementing in Excel

### Method 2 – Pressing CTRL+ENTER to Use a Formula Simultaneously for All of the Cells

** Steps**:

- Select all of the cells in the
**Profit**column and enter the following formula in**F4**.

`=D4-E4`

**D4 **is the *Selling Price *and **E4 **is the *Cost Price*.

- Press
**CTRL+ENTER**.

You will see the formulas in all cells in the Profit column.

**Read More: **How to Copy Formula and Paste as Text in Excel

### Method 3 – Using Shortcut Keys to Copy a Formula in Excel without Dragging

** Steps**:

- Enter the following formula in
**F4**and copy it by pressing**CTRL+C**.

`=D4-E4`

**D4 **is the *Selling **Price *and **E4 **is the *Cost **Price*.

- Press the
**Left**(←) key to go to the left adjacent cell.

- Go down to the last cell of this column and press
**CTRL + Down**key (↓).

- Press
**Right**(→) and then**CTRL + SHIFT + Up**(↑) to select all the cells in the Profit column. - Paste the formulas by pressing
**CTRL + V**.

You will see the formula copied to all cells in the **Profit **column.

**Read More:** How to Copy Formula to Another Sheet in Excel

### Method 4 – Using the Name Box to Copy a Formula without Dragging

** Steps**:

- Enter the following formula in
**F4**and copy it by pressing**CTRL+C**.

`=D4-E4`

**D4 **is the *Selling Price *and **E4 **is the *Cost Price*.

- Enter
**F5:F14**in the**Name Box**to paste the formula in these cells.

- Press
**ENTER.** - The ranges will be selected.

- Press
**CTRL + V**to paste the formula in**F5:F14**.

**Read More: **How to Copy and Paste Formulas from One Workbook to Another in Excel

### Method 5 – Copy a Formula without Dragging Using the Fill Option

** Steps**:

- Enter the following formula in
**F4**.

`=D4-E4`

**D4 **is the *Selling Price *and **E4 **is the *Cost Price*.

- Select all cells in the Profit column.
- Go to the
**Home**Tab >>**Editing**>>**Fill**>>**Down**.

This is the output.

**Read More: **[Fixed] Excel Not Copying Formulas, Only Values

### Method 6 – Using the Format as Table Option to Copy Formula in Excel without Dragging

** Steps**:

- Go to
**Home**Tab >>**Styles**>>**Format as Table**>> select any table style.

In the **Create Table **dialog box:

- Select the range.
- Check
**My table has headers.** - Click
**OK**.

This is the output.

- Select
**F4**and enter the formula.

`=D4-E4`

**D4 **is the *Selling Price *and **E4 **is the *Cost Price*.

By selecting **D4 **and **E4**, Excel will convert them automatically into the structured reference system and modify the formula:

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

- Press
**ENTER**.

This is the output.

### Method 7 – Using the Table Option to Copy a Formula without Dragging

** Steps**:

- Go to
**Insert**Tab >>**Table**.

In the **Create Table **dialog box:

- Select the range.
- Check
**My table has headers.** - Click
**OK**.

This is the output.

- Select
**F4**and use the formula.

`=D4-E4`

**D4 **is the ** Selling Price **and

**E4**is the

**.**

*Cost Price*By selecting **D4 **and **E4**, Excel will convert them automatically to the structured reference system and modify the formula:

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

- Press
**ENTER.**

This is the output.

### Method 8 – Using the Power Query to Copy a Formula without Dragging

** Steps**:

- Go to
**Data**Tab >>**From Table/Range**.

In the **Create Table **dialog box:

- Select the range.
- Check
**My table has headers.** - Click
**OK**.

In the **Power Query Editor **window:

Add a column with a formula to calculate the profit of all products.

- Go to
**Add Column**Tab >>**Custom Column**.

In the **Custom Column **wizard:

- Enter the column name (
**Profit**) in**New column name.** - Use the following formula in
**Custom column formula**by inserting the columns from the**Available columns**list.

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

- Click
**OK**. - This is the output.
- Go to
**Home**Tab >>**Close & Load**>>**Close & Load To**.

The **Power Query Editor **window is closed and the table is loaded to a new sheet: ** Table4**.

### Method 9 – Using a Dynamic Array to Copy a Formula in Excel without Dragging

** Steps**:

- Enter the following formula in
**F4**.

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

**D4:D14 **is the *Selling Price *and **E4:E14 **is the *Cost Price*.

Press **ENTER.**

This is the output.

### Method 10 – Copy a Formula without Dragging Using a VBA Code

** Steps**:

- Go to
**Developer**Tab >>**Visual Basic**.

In the **Visual Basic Editor:**

- Go to
**Insert**>>**Module**.

A **Module** will be created.

- Enter 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
```

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

- Press
**F5**.

This is the output.

## Practice Section

**Practice** here.

**Download Practice Workbook**

## Related Articles

**<< Go Back to Copy Formula in Excel | Excel Formulas | Learn Excel**