9 Tricks to Copy Formula in Excel Without Dragging

Sometimes we need to copy formula in Excel without dragging. In this article, we will show you how to copy formula in Excel without dragging with 9 easy approaches. We have also included some relevant concerns at the end of this article. So, let’s get started.


📂  Download Excel File

Download free workbook from here:


Learn to Copy Formula in Excel Without Dragging with These 9 Approaches

While writing this article, “How to Copy Formula in Excel without Dragging”, we’ve considered a dataset that contains approximately 5 columns and 14 rows. In this dataset, we added Name, Income, Expenditure, and Savings to make you better understand. But if you want, you can change the entities and put your own values.

Sample dataset containing monthly income and expense

APPROACH

1. Double-Clicking Fill Handle Icon

You can DoubleClick on your mouse to copy the formula in Excel without dragging. It is a very easy step. You can do this easily. Follow the given steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell E6.
  • Secondly, write the following formula on that cell.

=C6-D6

  • Now press Enter.
  • Then place the fill handle just like the below picture and Double-Click on that.

Using Excel formula on the dataset with the fill handle

  • Finally, you will see the result as well.

Savings after using the Excel formula

Undoubtedly, this is a very easy way to copy formula in Excel without dragging.

📕 Read More: 3 Ways to Copy Formula with Relative Reference Using Excel VBA

APPROACH

2. Pressing Ctrl+Enter

To copy the formula in Excel without dragging, you can also tap Ctrl+Enter. It is very simple. You just need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, you need to select the entire E
  • Secondly, write the following formula on cell E6.

=C6-D6

Using the Excel formula on the dataset after selecting the entire column

  • Thirdly, press Ctrl+Enter.
  • Consequently, you will get the result you need.

Savings after using the Excel formula

Truly, you can achieve this technique easily.

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

APPROACH

3. Using Keyboard Shortcuts

If you want, you can use the keyboard shortcuts to copy formulas in Excel without dragging. You just follow the below steps to achieve this approach.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, you need to select cell E6.
  • Secondly, write the following formula on that cell.

=C6-D6

  • Now press Enter.
  • Thirdly, press Ctrl+C to copy cell E6.

Copying the cell after using the Excel formula

  • Then press Ctrl+Left (⬅️) key to go to the left cell.
  • Afterward, press Ctrl+Down (⬇️) key to go to the last cell.
  • After that press Ctrl+Right (➡️) key to go to the right cell.
  • Sixthly, tap Ctrl+Shift+Up (⬆️) key to select the entire column.

Pressing the Ctrl+Shift+Up key to select the column

  • Finally, press Ctrl+V.
  • Eventually, the result will appear to you.

Savings after using the keyboard shortcuts

Indeed, this is a very easy and handy way to work.

📕 Read More: 4 Quick Ways to Copy Formula Down with Shortcut in Excel

APPROACH

4. Using Name Box

You can use Name Box to copy formula in Excel without dragging. Follow the below procedures.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, you need to select cell E6.
  • Secondly, write the following formula on that cell.

=C6-D6

  • Now press Enter.
  • Thirdly, press Ctrl+C to copy cell E6.
  • Fourthly, write the below range to the Name Box.

E6:E14

Giving the range of the dataset on the Name Box

  • Again press Enter.
  • You will see the entire E column selected.

Selecting the whole column

  • Afterward, tap Ctrl+V.
  • Consequently, you will find the desired result.

Savings after using the Excel formula

Finally, you must say that this is a simple step to copy formula in Excel without dragging.

📕 Read More: 6 Ways to Copy a Formula Across Multiple Rows in Excel

APPROACH

5. Applying Fill Down Feature

Applying the Fill feature to copy formula in Excel without dragging is a lot easier than you think. You just need to maintain the following steps as well.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, you need to select cell E6.
  • Secondly, write the following formula on that cell.

=C6-D6

  • Now press Enter.
  • Thirdly, select the whole column.
  • Then go to the Home tab.
  • Fourthly, select the Editing option and then click on Fill Down.

Selecting the Fill Down option from the Editing tool

  • Finally, your desired result will appear to you.

Savings after using the Excel formula

Indeed this step is one of the simplest procedures.

📕 Read More: Copy Formula and Change Only One Cell Reference in Excel

APPROACH

6. Adopting Table Feature

If you want to copy formula in Excel without dragging then you can adopt the Table feature as well. You can do this easily if you follow the given steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, go to the Insert tab.
  • Then select the Table option.

Inserting Table on the dataset

  • After that, you will get a dialogue box name Create Table containing the range.
  • Then you need to press OK.

Selecting range on the Create table dialogue box

  • Afterward, select cell E6 and write the following formula there.

=[@Income]-[@Expenditure] 

  • Finally, tap Enter.

Applying the formula to the dataset

  • Eventually, you will see the result as well.

Savings after using the formula

Hardly you can find an easier technique than this one.

APPROACH

7. Applying Power Query Method

The Power Query method is one of the unique features of Excel. You can copy formula without dragging using the Power Query Editor. For this, you need to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Data tab on the worksheet.
  • Secondly, choose the From Table/Range.

Choosing From Table/Range from the Data tab

  • Then you will see a dialogue box name Create Table
  • Thirdly, select the range:

$B$5:$D$14

  • Check the My table has headers.
  • Fourthly, click on OK.

Selecting range on the Create table dialogue box

  • After that, you will get the Power Query Editor.
  • Belatedly, select Add Column and then click on the Custom Column.

Selecting the Custom Column from the Power Query Editor

  • Again you will get a window name Custom Column.
  • Write “Savings” on the New column name.
  • Sixthly, write the following formula on the Custom column formula:

=[Income]-[Expenditure]

  • Then click on OK.

Custom Column dialogue box

  • Seventhly, go to the Home tab and select Close & Load.

Choosing the Close & Load option from the Power Query Editor

  • Finally, you will get the result with the help of Power Query Editor.

Savings after using the Power Query Editor

Indeed, this approach is very simple for you.

APPROACH

8. Using Dynamic Array

Using a dynamic array is one of the easiest approaches to copying formula down without dragging in Excel. You just need to follow the instructions given below.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select cell E6.
  • Secondly, write the given formula on that cell.

=(C6:C14)-(D6:D14)

  • Thirdly, press Enter.

Inserting a dynamic array

  • Eventually, you will find your required result as well.

Savings after using the dynamic array

Undoubtedly, this one is very helpful for you.

APPROACH

9. Creating VBA Macro

Using VBA code is one of the best ways to copy formula down without dragging in Excel. It is a very simple and handy approach. You have to maintain the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the worksheet.
  • Secondly, press Alt+F11.
  • A VBA window will appear to you.
  • Thirdly, insert a Module.

VBA module

  • Fourthly, paste the following code on that module.

Code

Sub FormulaWithoutDrag()
Dim Lr1 As Long
Dim r As Long
Lr1 = Cells(Rows.Count, "C").End(xlUp).Row
For r = 6 To Lr1
Cells(r, "E").Value = Cells(r, "C").Value - Cells(r, "D").Value
Next r
End Sub
  • Now tap Run.

VBA code

  • Eventually, you will get the result you wanted to have.

Savings after using the VBA code

Undoubtedly, this is a very effortless way to work on.

📕 Read More: 9 Examples to Copy Formula from Above Cell Using VBA in Excel


How to Copy Formula in Excel to Another Sheet

If you want to copy formula to another sheet in Excel then this portion is for you. You can do this easily if you follow the given steps properly.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, you need to select cell E6.
  • Secondly, write the following formula on that cell.

=C6-D6

  • Now press Enter.
  • Thirdly, press Ctrl+C to copy cell E6.

Copying cell after using the Excel formula

  • Fourthly, go to another worksheet in Excel.
  • Then select cell E6 of that sheet and tap Ctrl+V.

Pasting the formula

  • Consequently, you will find that your formula has been copied to another sheet.

Savings after copy-pasting the formula

Truly, this procedure is simple enough to copy formula to another sheet in Excel.


How to Copy Formula in Excel Without Changing References

You can also copy formula without changing references in Excel. It is also very easy to achieve. Follow the given instructions.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, you need to select cell E6.
  • Secondly, write the following formula on that cell.

=C6-D6

  • Now press Enter.
  • Thirdly, go to the formula bar, select the formula, and press Ctrl+C to copy the formula as well.

Copying the formula

  • Fourthly, select another cell and press Ctrl+V.
  • Eventually, you will see that your formula has been copied to that cell without changing references.

Savings after pasting the formula without changing the reference

Undoubtedly, this approach is very handy to work.


📄 Important Notes

You should be aware of the following things while performing the processes mentioned above:

🖊️  You need to be careful while selecting the range for the dynamic array.

🖊️  You have to be conscious when you intend to use the Power Query Editor.


📝 Takeaways from This Article

If we summarize the whole article, we have got some points.

📌  Primarily, we used the double-clicking method to copy formula in Excel without dragging.

📌  Secondly, we used the Ctrl+Enter keys.

📌  Thirdly, we used the Name Box in Excel.

📌  Fourthly, we applied the Fill feature in Excel.

📌  Fifthly, we adopted the table feature.

📌  Sixthly, we applied the Power Query method.

📌  Seventhly, we used a dynamic array,

📌  Afterward, we applied VBA code to copy formula without dragging.

📌  Belatedly, we copied formula to another sheet in Excel.

📌  Finally, we copied formula without changing references in Excel.


Conclusion

We wish that you would be able to copy formula in Excel without dragging using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If you have any skepticism, feel free to ask me in the comment. In the end, to learn more about Excel tactics, we recommend you to visit our website www.ExcelDen.com.


Related Article

(Visited 37 times, 1 visits today)
Jonayed

Jonayed

Hello! My name is Jonayed. I've completed my graduation from BUET in Naval Architecture & Marine Engineering. As Excel is very interesting to me, I like to do various types of research regarding Excel. My inquisitive nature drives me to find solutions to different types of problems with Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo