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.

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

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

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

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

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

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.

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.

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

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

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

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.

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

Lastly, this is a very simple method.

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

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.

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

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

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

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

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

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

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.

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

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

• 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 

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.

• 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

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

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

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

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.

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

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

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.

• Write down the formula in cell E5:

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

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

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

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

📄 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

• 6 Ways to Copy a Formula Across Multiple Rows in Excel
• 4 Quick Ways to Copy Formula Down with Shortcut in Excel