How to Copy Formula Down in Excel Without Incrementing

Sometimes we need to copy formulas down in Excel without incrementing. This is very important while doing any mathematical calculation. In this article, we are going to show you how to copy formula down in excel without incrementing in 4 Approaches. We have also included some relevant concerns in the end of this article. So let’s get started.


📁 Download Excel File

Download the free workbook from here:


Copy Formula Down in Excel Without Incrementing by 4 Quick Approaches

First of all, we’ve considered a dataset that contains approximately 10 columns and 7 rows. In this dataset, we added Name, Quantity, Variable Cost, Fixed Cost, and Total Cost to make you understand better. Here in the G5 cell, the Fixed Cost won’t be changed. But if you want you can change the entities and put the values of your own.

Dataset of copying formula down without incrementing in Excel

  • In the E5 cell of the Total Cost column, we’ve written a formula::

 =C5*(G5+D5)

simple method to copy formula down

  • If you want to copy the formula down in the E6 cell without incrementing, just write the formula

=C6*(G5+D6)

  • Now, if you want the same thing for E7 cell, you need write to write

 =C7*(G5+D7)

  • Similarly, the rest will be continued in the same procedure.

But this is a cumbersome approach. Here, in this article, I will show you 4 easy and quick approaches to copying formula down without incrementing.


Approach

1. Application of Absolute Cell References

One of the easiest ways of copying formulas down without incrementing in Excel is the use of Absolute References. The very first question that comes to our mind is- “What are Absolute Cell References?

What is Absolute Cell Reference? 

Absolute Reference makes you “fix” part or all of a cell reference in a formula. This is one of the time-saver procedures when copying formulas. The concept of Absolute Reference is found in all spreadsheet applications which contain Dollar Sign($). A formula put with an Absolute Reference using Fill Handle Icon doesn’t increment. Moreover, $G$5 is the Absolute Reference of cell G5.

So, if you want to use this formula for cell E5, write

=C5*($G$5+D5)

Use of absolute cell reference of copying formula down in excel.

In Excel, you can use the keyboard shortcuts to make Absolute Cell References as well as perform the procedure.

⬇️⬇️ STEPS ⬇️⬇️

  • On the keyboard, you need to Press F2 to take the formula bar on Edit Mode.
  • Select cell G5, press F4 and you will get $G$5.
  • If you click on F4 further and obtain G$5.
  • Repeatedly, click on F4, and get $G5.
  •  In order to get G5, click on F4 again.
  • For formulas containing multiple cell references, press Ctrl + Shift + Home to make them all absolute. When it selects the entire formula, Click on F4.

When the formula in the selected cell with an Absolute Cell Reference is done, you need to reprint the formula for the remaining cells. You can do this using two methods.

Absolute Cell Reference

1.1 Using Fill Handle Icon Manually

You need to perform the following steps to use the Fill Handle Icon manually for the application of Absolute Cell Reference.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, select the E5 cell which contains Absolute Cell Reference.
  • Then using the Fill Handle icon select cells E5 to E10.

Use of fill handle icon of copying formula down in excel without incrementing.

  • The formula will be copied from cells E5 to E10 as well.

fill handle usage manually to copy formula.

  • Consequently, you will get the desired values without incrementing.

the result of using fill handle icon manually to copy formula down in excel without incrementing.

Finally, using the Fill Handle manually is an easier procedure to copy formulas down in Excel without incrementing.


Absolute Cell Reference

1.2 Using Down Command from Fill Dropdown

There is another method for the application of Absolute Cell Reference which implies the use of the Fill Handle from the Toolbar.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Absolute Cell Reference sheet.
  • Select the cells from E5 to E10.
  • Then go Home and click on the menu named Drop-down.
  • Then click on the Down option.

Using fill handle manually to copy formula down

  • Finally, you will get results that do not increment.

result fill handle icon from toolbar to copy formula down without incrementing

Lastly, this is a very simple method.

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


Approach

2. Adopting Find and Replace Option for Copying Formula Down

While copying formula down in Excel without incrementing, one of the shortest ways are adopting the Find and Replace option from the toolbar. To perform this procedure you have to pursue the following steps.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, go to the Find and Replace sheet.
  • Select Home, then select the Editing option.
  • After that go to the Find & Select option.
  • You will find some options there. Click on the Replace option.

Find and replace option to copy formula down without incrementing in excel.

  • Then you will receive a box named Find and Replace.
  • In Find what option put “=” and in Replace with option put “&&&”.
  • Then click on Replace All.

Find and Replace box

  • After clicking on Replace All, you will see your column E with “&&&” like this

Replace all formula to copy formula down in excel without incrementing.

  • Copy the entire E column and paste it to the F column like below.

Copy and paste option.

  • Again go Home option, select the Editing option, select Find & Select option and then click on Replace.
  • You will get the Find and Replace box. This time insert “&&&” in Find what option and insert “=” in Replace with option.
  • Like before, snap on Replace All now.

Find and Replace box

  • Finally, you will see the formulas are copied to the F column from the E column.

result of find and replace formula to copy formula down without incrementing in excel.

Undoubtedly, adopting the Find and Replace option in Excel is an easier process to copy formula down without incrementing in Excel.


Approach

3. Copying Formula in Excel Without Incrementing Reference Using Notepad

Copying formula in Excel without incrementing reference using Notepad is one of the easiest ways to copy formulas. You have to follow the below steps to perform this procedure.

⬇️⬇️ STEPS ⬇️⬇️

  • Firstly, go to the Formula without Switching sheet.
  • Press Ctrl + ` to view the formulas.
  • Select column E and copy the whole column.

without changing reference

  • Then open a notepad and paste the formulas as text.

copying in notepad

  • Now select the formulas again and copy them from the notepad.
  • Go back to the Excel worksheet and paste the text formulas on column F.

result of copying formula without changing reference.

  • Finally, you will get the copied formulas in the F column without incrementing.

📕 Read More: How to Copy a Formula in Excel with Changing Cell References 


Approach

4. Applying VBA Macro to Copy Formula

Applying VBA macro to copy formula down without incrementing in Excel is one of the beat ways to copy formula. You have to maintain the following steps for this.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the VBA sheet.
  • Press Alt+F11. The VBA window will be opened.
  • Select Insert and then click on Module option.

module fot VBA macro

  • After clicking on the Module, you see a box like below.

  • Furthermore, when you get the box, write down the following code.

VBA Code

Sub Paste_Formula_without_increment()
Dim Var As Variant
Dim S_ran As Range, R_ran As Range, p As Long, q As Long
Set S_ran = Application.InputBox("Select source Range w/formulas", Type:=8)
p = S_ran.Rows.Count
q = S_ran.Columns.Count
Var = S_ran.Formula
    For i = 1 To UBound(Var, 1)
        For j = 1 To UBound(Var, 2)
            Var(i, j) = CStr(Var(i, j))
        Next j
    Next i
Set R_ran = Application.InputBox("Select the first cell of Paste Range", Type:=8)
Set R_ran = R_ran.Resize(p, q)
R_ran.Value = Var
End Sub

VBA macro code

  • Now, click on the Run option.
  • When you click on the Run option, you will get a box named Input.
  • Then select the range on the Input box and click OK.

copying formula using VBA

  • After clicking on OK, you will get another Input box containing Select first cell Paste Range.
  • Now select the range and click OK.

VBA use

  • Consequently, now you will get the values with the copied formula.

result of using VBA

Finally, the VBA macro code will work to copy the formula down without incrementing in Excel.

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


How to Copy Formula Down Entire Column in Excel

There is another method of copying formulas down in Excel which is using the Paste Special option. Paste Special is not used commonly. You have to follow the given steps to continue your work in Excel while using the Paste Special option.

⬇️⬇️ STEPS ⬇️⬇️

  • First, go to the Paste Special option sheet.
  • Select the cell where you want to use that option to copy the formula (we took cell E5).
  • Then Right-Click on that cell.
  • Now, click on the Paste Special option.

using paste special option.

  • After clicking on the Paste Special option, you will get the Paste Special box as given below.
  • Then select Formulas and click OK.

paste special box

  • Finally, you will get the values without incrementing in Excel.

result of paste special option

Undoubtedly, this is a lot easy process to copy formulas in excel.


How to Copy Formula Down Column in Excel Without Dragging

Without dragging means without the use of a Fill Handle. So copying the formula down a column in Excel without dragging without using Fill Handle. You need to follow certain steps to obtain this process.

⬇️⬇️ STEPS ⬇️⬇️

  • Primarily, go to the Formula without Dragging excel sheet.
  • Select the cells from E5 to E10.

procedure of formula without dragging

  • Write down the formula in cell E5:

=C5*($G$5+D5)

  • Then press Ctrl + Enter on the keyboard to fill the remaining cells also.

Formula without dragging

  • After pressing Ctrl + Enter, you will obtain the results without incrementing.

result of formula without dragging.

📕 Read More: How to Copy Excel Sheet to Another Workbook with Formulas


How to Copy Excel Formula and Get Result as Text

Copying formulas in Excel and getting results as text is one of the much-required techniques now. There are some steps to follow the procedure.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, go to the Copy and paste as text sheet.
  • Select the desired cell (we considered E5) and press F2.
  • It will show the formula on the cell as well.
  • Now, press F9.

result in text

  • Finally, you will get the result as text in the Formula Bar for the desired cell.

result of copying formula in text


📄 Important Notes

While working on the above-mentioned procedures you need to keep the following things in your mind.

🖊️  When you use the Absolute cell reference, the cell must contain two Dollar($) signs, like $G$5.

🖊️  You need to use the notepad while copying formulas from Excel Worksheet as texts. 

🖊️  If you want to work on the Paste Special option, you have to select the Formulas option from the Paste Special dialogue box.

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


📝  Takeaway from This Article

If we summarize the whole article, we have some approaches to copy formulas down without incrementing in Excel.

📌  In Approach 1, we have used Absolute Cell Reference which is a special feature of Excel while copying formulas down without incrementing. There are two ways to use this process-

I. Using the Fill Handle icon

II. Applying the Fill Handle from Excel

📌  In Approach 2, we’ve adopted Find and Replace option to copy the formula down, where we have replaced the formulas to another column without incrementing.

📌  In Approach 3, we have applied the process of copying the formula without incrementing reference using Notepad. In this process, we transformed the formulas into texts via Notepad.

📌 In Approach 4, we have tried to show you how to copy the formula down without incrementing using VBA macro where we used a VBA code.

We have also shown the procedure of copying formulas down the entire column without incrementing in Excel. Similarly, we have tried show to you how to copy formulas without dragging and also how to copy Excel formulas and get the results in texts. Finally, we hope that may this article be beneficial to you to copy formulas down without incrementing it in Excel.


Conclusion

We wish that you would be able to copy the formula down without incrementing in Excel using this article. As several methods have been shown here, you can easily follow which one is best suited for you. If there is any skepticism in your mind, 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 Articles

(Visited 94 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