How to Copy Formula Down Without Incrementing in Excel

We have the price record of various items of a company. The dataset contains Item Names, their prices, tax, and prices with tax in columns B, C, D, and E, respectively.

• In the first cell of column E, prices with tax, we have written a formula:

• We want to copy down this formula to the rest of the cells without incrementing the tax, D4. That means the cell E5 will have:

• Similarly, cell E6 will have:

Part 1 – Use an Absolute Cell Reference to Copy Formula Down Without Incrementing

An Absolute Cell Reference is a cell reference with a Dollar Sign ($) before the row and column number. When we drag a formula with an Absolute Cell Reference through the Fill Handle, it does not increment. The Absolute Cell Reference of cell D4 is $D$4.

• Use this formula in the Formula Bar for cell E4.

For Excel version 2013 or higher, you can use a keyboard shortcut to create an Absolute Cell Reference.

Steps:

• Double-click in the Formula Bar or press F2 on your keyboard. The formula will be in Edit mode.
• Put the cursor after D4 and press F4 on your keyboard. It will turn D4 into $D$4.
• If you press F4 again, it will turn $D$4 into D$4.
• Press F4 again and you will get $D4.
• If you press F4 again, you will go back to D4.
• If your formula contains more than one cell reference and you need to make all of them Absolute, press Ctrl + Shift + Home first. It will select the whole formula. Then press F4.
• In the Formula Bar, the mouse cursor remains at the end by default. If it is not, you can press Ctrl + End on your keyboard to bring it to the end.

After entering the formula of the first cell with Absolute Cell Reference in the Formula Bar, you have to copy the formula to the rest of the cells.

Method 1 – Dragging the Fill Handle

• Drag the Fill Handle (The Small Plus(+) Sign in the bottom-right corner) from the cell with the formula down to the cell in which you want to copy the formula. We dragged the Fill Handle from cell E4 to E13.

• The formula is copied to all the cells without incrementing D4.

Read More: How to Copy Formula in Excel Without Dragging 

Method 2 – Using the Fill Option from Excel Toolbar

• Select the cell with the formula and the rest of the cells where you want to copy down the formula. We selected the range E4:E13.

• Go to Home and select the Fill option under the Editing section.

• Click on the drop-down menu.
• You will get a few options. Click on Down.

• The formula is copied to all cells without changing the cell reference to D4.

Read More: How to Copy Formula and Paste as Text in Excel

Part 2 – Using the Find and Replace Box to Copy Formula Down Without Incrementing

We’ll copy column E, price with tax, to column F, keeping all the formulas intact.

Steps:

•  Go to Home and choose the Find and Select option in the Editing group.

• Select Replace. Alternatively, press Ctrl + H.

• You will get the Find and Replace dialogue box.
• In Find What, insert ‘=’.
• In Replace With, insert ‘&&&’.

• Click on Replace All.

• Select all cells in column E, copy them with Ctrl + C, then paste them in column F.

• Go to Home and choose Find and Select.
• Select Replace. (Or press Ctrl + H)
• In the Find What option, insert ‘&&&’.
• In the Replace With option, insert ‘=’.

• Click on Replace All.
• You will find the formulas from column E copied to column F without any change.

Read More: How to Copy Formula to Another Sheet in Excel

Part 3 – Applying VBA Macro to Copy Formula Down Without Incrementing

Steps:

• Press Alt + F11 to open the VBA window.
• Go to the Insert option in the VBA Toolbar.
• Select Module.

• You will get a Module window like this.

• Insert the following code to create the Macro.

Code

Sub PasteExactFormulas()
Dim Vrnt As Variant
Dim sRang As Range, rRang As Range, s As Long, y As Long
Set sRang = Application.InputBox("Select source Range w/formulas", Type:=8)
s = sRang.Rows.Count
y = sRang.Columns.Count
Vrnt = sRang.Formula
    For i = 1 To UBound(Vrnt, 1)
        For j = 1 To UBound(Vrnt, 2)
            Vrnt(i, j) = CStr(Vrnt(i, j))
        Next j
    Next i
Set rRang = Application.InputBox("Select the first cell of Paste Range", Type:=8)
Set rRang = rRang.Resize(s, y)
rRang.Value = Vrnt
End Sub

• Your code will look like this in the module window.

• Press Ctrl + S to save the Macros. You will get an Error Box like this.

• Click on No. Excel will automatically open the Save As window for you.
• Give the File a Name.
• Click on the drop-down menu for Save As Type.

• Choose Excel-Macro-Enabled Workbook.
• Click Save. Your workbook is now saved with the Macros.

• Go back to the Excel Worksheet and press Alt + F8. You will get a box named Macros.
• Select the Macro you want to run and click Run. We need PasteExactFormulas.

• You will get an Input Box.
• Select the range of the cells from which you want to copy the formulas. We selected cells E3 to E13.
• Click OK.

• You will get another Input Box.
• Select the first cell of the range where you want to paste the formulas. We selected F3.
• Click OK.

• Here are the results. The macro doesn’t copy the formatting.

Download the Practice Workbook

Related Articles

• How to Copy and Paste Formulas from One Workbook to Another in Excel
• [Fixed] Excel Not Copying Formulas, Only Values
• 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