How to Copy Formula in Excel without Dragging (10 Ways)

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.

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.

how to copy formula in Excel without dragging

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.

how to copy formula in Excel without dragging

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.

double-click

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

double-click

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.

how to copy formula in Excel without dragging

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.

CTRL+ENTER

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

CTRL+ENTER

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.

how to copy formula in Excel 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.

Shortcut key

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

Shortcut key

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

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

Shortcut key

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

how to copy formula in Excel without dragging

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.

how to copy formula in Excel 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.

using Name Box

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

using Name Box

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

how to copy formula in Excel without dragging

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.

how to copy formula in Excel without dragging

Steps:
➤ Enter the following formula in cell F4

=D4-E4

Here, D4 is the Selling Price and E4 is the Cost Price.

using Fill option

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

using Fill option

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

how to copy formula in Excel without dragging

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.

how to copy formula in Excel without dragging

Steps:
➤ Go to Home Tab >> Styles Group >> Format as Table Dropdown >> select any table style.

Format as Table

Then the Create Table dialog box will appear.
➤ Select the range of your dataset.
➤ Check the My table has headers option and click OK.

how to copy formula in Excel without dragging

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

Format as 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]]

Format as Table

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

how to copy formula in Excel without dragging

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.

how to copy formula in Excel without dragging

Steps:
➤ Go to Insert Tab >> Table Option.

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.

how to copy formula in Excel without dragging

Then, you will get the following table.

Table option
➤ 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]]

Table option

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

how to copy formula in Excel without dragging

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.

how to copy formula in Excel without dragging

Steps:
➤ Go to Data Tab >> From Table/Range Option.

Power Query

Then, the Create Table dialog box will appear.
➤ Select the range of your dataset.
➤ Check the My table has headers option and click OK.

how to copy formula in Excel without dragging

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

Power Query

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.

Power Query

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]

Power Query

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

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

Power Query

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

how to copy formula in Excel without dragging

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.

how to copy formula in Excel without dragging

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.

dynamic array

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

dynamic array

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.

how to copy formula in Excel without dragging

Steps:
➤ Go to Developer Tab >> Visual Basic Option.

VBA Code

Then, the Visual Basic Editor will open up.
➤ Go to Insert Tab >> Module Option.

VBA Code

After that, a Module will be created.

how to copy formula in Excel without dragging

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

VBA Code

➤ Press F5.
Then, you will get the formula for all of the cells of the Profit column.

VBA Code

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.

Practice


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.


Related Articles

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo