While working in **Excel**, we often have to copy down one formula to another group of cells without incrementing. Today I will be showing three easy ways on how to copy formula down without incrementing in excel. Let’s get started.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

**3 Quick Ways to Copy Formula Down Without Incrementing in Excel**

- Let us have a look at this data set. We have the price record of various items of a company named
**APEX Garments**. There are the**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`

- Now 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`

- And so on. How can you achieve that? Here are the three methods you can utilize to copy formula down without incrementing in Excel.

**1. Use of Absolute Cell Reference to Copy Formula Down Without Incrementing**

The best way to achieve this is by using **Absolute Cell Reference. **An **Absolute Cell Reference** is a cell reference having **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**. So use this formula in the **Formula Bar** for cell **E4.**

`=C4+C4*$D$4`

For Excel version 2013 or higher, you can use your keyboard shortcut to create an **Absolute Cell Reference**. Follow the steps below.

**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 get**D4**. - Again press
**F4**, and you will get**$D$4.**And the cycle goes on. - 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. You can implement this in two methods.

**Method 1: By Dragging Fill Handle**

- Drag the
**Fill Handle**(The Small**Plus(+) Sign**in the Bottom Right Corner) from the cell having the formula with**Absolute Cell Reference**up to the cell in which you want to copy the formula. Here I drag the**Fill Handle**from cell**E4**to**E13**.

- As a result, I get the formula copied to all the cells without incrementing
**D4**.

**Method 2: Using Fill Option from Excel Toolbar**

- Select the cell with the formula having the
**Absolute Cell Reference**and the rest of the cells where you want to copy down the formula. I select cells**E4**to**E13**.

- Then go to
**Home>Fill**Option in Excel Toolbar under the**Editing**section.

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

- You will get the formula copied to all cells without incrementing cell reference
**D4**.

**Read More: Copy Formula in Excel by Changing Only One Cell Reference**

**Similar Readings**

**How to Copy Formula to Another Sheet in Excel (4 Ways)****How to Copy a Formula Down the Column in Excel(7 Methods)****Excel VBA to Copy Formula with Relative Reference (A Detailed Analysis)**

**2. Using Find and Replace Box to Copy Formula Down Without Incrementing**

This method is very handy when you want to **copy formulas** from one range of cells to another range of cells without changing the cell reference. Let us think that we want to copy column **E**, price with tax to column **F**, keeping all the formulas intact. How can we do that? Follow the steps below.

**Steps:**

- Go to
**Home > Find and Select**Option on the**Editing**group of the**Home**tab from the Excel Toolbar.

- Click on the Drop Down Menu. You will get some options. Select
**Replace…**.

- You will get the
**Find and Replace**dialogue box. You can also press**Ctrl + H**to get that. In**Find What**option, insert ‘**=**’. And in**Replace With**option, insert ‘**&&&**’.

- Click on
**Replace All.**You will get all cells in column**E**having ‘**&&&**’ like this.

- Then select all cells of column
**E**, copy them with**Ctrl +C**and then paste them in column**F**.

- Again go to
**Home>Find and Select**. Then select**Replace.**(Or press**Ctrl + H**) This time, in the**Find What**option, insert ‘&&&’. And 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 a Formula in Excel with Changing Cell References**

**3. Application of VBA Macro to Copy Formula Down Without Incrementing**

You can use **VBA** code to create a Macro to do the same thing as I did earlier. Follow the steps below.`

**Steps:**

- First, press
**Alt + F11**in your Excel File. It will open the**VBA**window. - Then go to the
**Insert**option in the**VBA**Toolbar. Select**Module.**

- You will get a Module window like this.

- Write down the following code here to create the Macros.

**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 + C**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 Name**anything. Then click on the drop-down menu with**Save As Type**.

- You will find a lot of options. Choose
**Excel-Macro-Enabled Workbook.**Then click**Save.**Your workbook is now saved with the**Macros**.

- Then 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**. Here I want to run**PasteExactFormulas.**

- If you run the recently created
**Macro**, the**PasteExactFormulas,**you will get an**Input Box**like this. Select the range of the cells from which you want to copy the formulas. Then click OK. Here I select cells**E3 to E13**.

- You will get another
**Input Box**like this. Select the first cell of the range where you want to paste the formulas. Then click**OK**. Here I select**F3**.

- And you will find the formulas of column
**E**copied beautifully to column**F**. Obviously this does not copy the format of the cells, only the formula. If you want, you can change the format manually.

**Read More: VBA to Copy Formula from Cell Above in Excel (10 Methods)**

**Conclusion**

I hope the **3** methods shown above will be helpful when you try to copy formula down without incrementing in excel. If you like the article please share it with your friends. For more articles like this visit our **EXELDEMY.com** site

## Further Readings

**How to Copy a Formula across Multiple Rows in Excel (5 Ways)****Copy Formula in Excel by Changing Only One Cell Reference****How to Copy Excel Sheet with Formulas to Another Workbook (5 Ways)****Shortcut to Copy Formula Down in Excel (7 Ways)****Copy and Paste Formulas from One Workbook to Another in Excel****How to Copy Formula and Paste as Text in Excel (2 Ways)****Making Same Change to Multiple Worksheets (9 Examples)**

thanks for helping and explaining with example for excel absolute reference