How to Copy a Formula in Excel Without Dragging - 10 Methods

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.

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.

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.

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.

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.

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.