How To Copy Formula In Excel Without Dragging

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

Download Workbook

10 Ways to Re-create Formula in Excel without Dragging

Here, we will utilise the following dataset to calculate the profit of this company by using the Selling Price and Toll Price of this dataset, and then we will copy this formula by using unlike methods.

Nosotros accept used Microsoft Excel 365 version here, you tin can use any other versions according to your convenience.

Method-one: Double-Clicking to Copy Formula in Excel without Dragging

Hither, 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 cavalcade past double-clicking on the Fill Handle tool.

Steps :
➤ Enter the post-obit formula in jail cell F4

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

➤ Double-click on the Fill Handle tool.

In this way, y'all will get the formulas for all of the cells of the Turn a profit column to summate profits.

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

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

Steps :
➤ Select all of the cells of the Turn a profit column and so type the following formula in jail cell F4

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

➤ Printing CTRL+ENTER.

Subsequently, yous will get the formulas in all of the cells of the Profit column.

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

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

Steps :
➤ Enter the post-obit formula in jail cell F4 and so copy information technology by pressing CTRL+C.

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

➤ Press the Left (←) key to become to the left side by side cell to become downward to the terminal used prison cell as if nosotros attempt to go down to the last prison cell from Column F so it will take u.s.a. to the very terminal cell because this column is empty.

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

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

Finally, y'all will get the formula copied to all of the cells of the Profit cavalcade.

Method-four: Using Name Box to Copy Formula in without Dragging

Here, nosotros volition utilize the Name Box to re-create the formula hands to all of the cells of the Profit column without dragging.

Steps :
➤ Enter the following formula in cell F4 then copy it by pressing CTRL+C.

Hither, D4 is the Selling Cost and E4 is the Cost Toll .

➤ Type the range of cells F5:F14 in the Proper 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 similar the following figure.

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

Method-5: Copy Formula without Dragging Using Fill Selection

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

Steps :
➤ Enter the post-obit formula in cell F4

Hither, D4 is the Selling Toll and E4 is the Cost Price .

➤ Select all of the cells of the Turn a profit column and so become to Home Tab >> Editing Group >> Fill Dropdown >> Down Option.

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

Method-vi: Format equally Table Option to Re-create Formula in Excel without Dragging

Yous tin can utilise the Format as Table choice to re-create the formula without dragging it in the Profit column.

Steps :
➤ Go to Habitation Tab >> Styles Group >> Format every bit Table Dropdown >> select any table style.

And so 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 offset typing the formula

Hither, D4 is the Selling Price and E4 is the Toll Toll .

But, when nosotros start to select the cells D4 and E4, Excel will catechumen them automatically to the structured reference system and modify the formula as follows

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

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

Method-7: Using Table Selection to Re-create Formula without Dragging

Here, we will utilize the Table option to copy the formula throughout all of the cells of the Profit cavalcade.

Steps :
➤ Become to Insert Tab >> Table Selection.

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

And so, you will become the following table.

➤ Select prison cell F4 and start typing the formula

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

Simply, when we kickoff to select the cells D4 and E4, Excel will convert them automatically to the structured reference system and modify the formula as follows

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

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

Method-8: Power Query to Copy Formula without Dragging

Here, we volition be using the Power Query selection to get the formula for all of the cells to get the profit of the products.

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

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

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

Now, we will add a column with a formula to summate the profits of all the products at once.
➤ Go to Add together Column Tab >> Custom Column Option.

Then, the Custom Column wizard will open up.
➤ Write the cavalcade name (for this example Profit) in the New column name box and then write the post-obit formula in the Custom column formula by inserting the required columns from the Available columns list.

=[Selling Toll]-[Toll Price]

After pressing OK, nosotros 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 Ability Query Editor window and loaded the table to a new canvas named Table4 .

Method-nine: Using Dynamic Array to Re-create 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 fourth dimension simply this characteristic will only be bachelor to Microsoft Excel 365 users.

Steps :
➤ Enter the following formula in prison cell F4

Here, D4:D14 is the range of the Selling Price and E4:E14 is the range of the Cost Toll .

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

Method-10: Copy Formula without Dragging Using VBA Code

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

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

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

Subsequently that, a Module will be created.

➤ Write the post-obit lawmaking

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

Here, we have declared Lr, northward equally Long, Lr volition return the last used row number. FOR loop will execute the performance of the formula for all of the cells of Column F from the starting Row iv to the last row.

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

Do Section

For doing exercise by yourself we have provided a Do section like beneath in a sheet named Do. Please practise it by yourself.

Conclusion

In this commodity, nosotros tried to cover the ways to copy formula in Excel without dragging. Hope you will find it useful. If you lot have whatsoever suggestions or questions, feel gratis to share them in the annotate section.