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:

`=C4+C4*D4`

- 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:

`=C5+C5*D4`

- Similarly, cell
**E6**will have:

`=C6+C6*D4`

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

`=C4+C4*$D$4`

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**

thanks for helping and explaining with example for excel absolute reference