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.

How to Copy Formula in Excel Without Dragging: 10 Creative Ways

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 version according to your convenience.

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

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 Down Without Incrementing in Excel

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.

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: How to Copy Formula and Paste as Text in Excel

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.

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 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: How to Copy Formula to Another Sheet in Excel

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.

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.

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

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.

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: [Fixed] Excel Not Copying Formulas, Only Values

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.

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

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

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.

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

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

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.

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.

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.

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.

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.

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.

Download Practice Workbook

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

• VBA to Copy Formula from Cell Above in Excel 
• Excel VBA to Copy Formula with Relative Reference

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