Copy a formula in Excel means to replicate a formula from one cell to another or multiple cells. It allows you to use the same formula for other cells or datasets without retyping it manually.

In this Excel tutorial, you will learn how to copy formulas in Excel with various cases using different methods. You will also learn what to do when Excel is copying only values, not formulas.

The following image shows a dataset containing some **products **from different **sellers**. Each product has some discount ongoing. The formula to calculate the discounted price is given in the cell **G4**. We double-clicked on the **Fill Handle** tool to copy the formula to other cells in the **Discounted Price **column.

Here, you will learn how to copy formula in Excel in different cases like how to copy a formula down a column using the **Fill Handle** tool; fill in an Excel formula for the entire column; copy a formula using keyboard shortcuts into multiple cells or rows; copy formula to non-adjacent cells; copy as formulas, copy the formula and paste it as text; creating and copying formula using cell references; copy the formula using the find and replace tool. You will also learn to copy formula in Excel to another sheet or another workbook.

**Note:**We have used**Excel for Microsoft 365**to prepare this article.⏷Copy a Formula Down

⏷Fill in Formula for Entire Column

⏵Double-Clicking Fill Handle Icon

⏵Using Excel Table

⏷Copy Formula Multiple Cells

⏷Copy Formula to Non-Adjacent Cells

⏷Copy Formula Across Multiple Rows

⏷Copy a Formula Without Formatting

⏵Copy as Formulas

⏵Paste As Text

⏷Copy Formula with Cell References

⏵Relative References

⏵Change One Cell Reference

⏷Shortcuts to Copy a Formula

⏵Whole Column

⏵Copy Rightward/ Downward

⏷Copy Exact Formula

⏷Copy to Another Sheet

⏷Copy to Another Workbook

⏷Copying Only Values Not Formulas

## 1. How to Copy a Formula Down in Excel?

Here we will show how to copy a formula down in an Excel column. This means we will copy a formula from a cell to other remaining cells in the same column. We will drag down the **Fill Handle** tool to do that.

- First, we have a dataset containing the
**Retail Price**and**Discount Rate**of some products.

- Insert the following formula in cell
**G6**and hit the**Enter**button in Excel:

`=E6*(1-F6)`

- Now, select the
**G6**cell and drag down the**Fill Handle**tool by using your mouse up to cell**G15**. - You will see that the formula is copied to the cells down the
**Discounted Price**column.

## 2. How to Copy in an Excel Formula for the Entire Column?

Now we will show how to copy formula in Excel for the entire column using two different methods. In these methods, you can copy formula in Excel without dragging.

– Double-clicking on the **Fill Handle** icon

– Using Excel table

### 2.1 Double-Clicking on the Fill Handle Icon

In this section, you will learn to copy the formula to the entire column by double-clicking the** Fill Handle** icon. But if you have any discontinuity down the column, you may have to repeat the double-clicking each time.

- First, type the following formula in cell
**G6**:

`=E6*(1-F6)`

- Next, double-click on the
**Fill Handle**tool in, the bottom right corner of cell**G6**. - It will copy the formula till cell
**G15**.

### 2.2 Using Excel Table

In this method, we will learn how to copy a formula in Excel using the table. It’s a great idea to convert the entire data set into a table if you have a large data set and want to copy a formula to an entire column.

- First, we have the dataset containing product
**Retail Price**and**Discount Rate**columns.

- Now, select any of the cells in your dataset >> press
**CTRL+T**(shortcut for converting into table). The following**Create Table**dialogue box will appear. - After that, click on
**OK**.

- It will convert the dataset into a table.

- Next, type the formula in the first cell of the table, cell
**G6**, and press**Enter**.

`=E6*(1-F6)`

- You will see the formula being copied in all the cells in that entire column automatically.

## 3. How to Copy a Formula into Multiple Cells?

Here, we will show how to copy a formula into multiple cells. Now in our dataset, we already have some cells with **discounted prices**. We will calculate the** Discounted Price **in other empty cells by copying the formula.

- First, we will press
**CTRL**and hold the key >> Select the cells where you want to copy the formula.

- Then, select a cell containing the formula holding the
**CTRL**button.

- Now, leave the
**CTRL**button and press**F2,**which will switch to editing mode.

- After that, press
**CTRL+Enter,**and the formula will be copied to all selected cells.

## 4. How to Copy Formula to Non-Adjacent Cells in Excel?

In this part, you’ll learn how to copy formula to non-adjacent cells in Excel. You can use a keyboard shortcut to do that. There are some non-adjacent blank cells in the below dataset, we’ll copy the formula there.

- First, we have the following formula in cell
**G6**:

`=E6*(1-F6)`

- Now, copy a cell containing the formula.

- Next, select the non-adjacent cells holding the
**CTRL**button.

- After that, press the
**CTRL+V**It will paste the formula to the cells instantly.

## 5. How to Copy Formula Across Multiple Rows in Excel?

Here, we will show how to copy the formula across multiple rows in Excel. In this dataset, we will calculate the **price **of any product with **7% VAT **based on the previous price. We will use the** Fill Handle** tool to copy formula in Excel.

- First, insert the following formula in cell
**F8**:

`=E8*($C$5+1)`

- Now, drag the
**Fill Handle**tool to the right and then down to the last cell of the dataset. - As a result, you will see the formula copied to the remaining multiple rows.

## 6. How to Copy a Formula Without Formatting?

In this section, you will learn to copy a formula to other cells without formatting. We will show two methods below.

– Copy as formulas

– Copy Formula And Paste As Text

### 6.1 Copy as Formulas

Here, we will show how you can easily copy the formula to different cells using the context menu.

- There are some formattings with the below formula in cell
**G6**:

`=E6*(1-F6)`

- Next, copy the formula using the
**CTRL+C**shortcut.

- Now, select the other empty cells where you want to copy the formula.
- After that, right-click on the mouse for the
**context menu**. - Next, select the marked icon named
**Formulas (F)**from**Paste Options**.

- This will paste only the formula without any formatting.

### 6.2 Copy Formula And Paste As Text

In this method, you will learn to copy a formula without formatting and paste it as text.

- First, create another column named
**Formula**.

- Now, select the cells in the
**Discounted Price**column.

- Next, press
**CTRL+`(Tilde icon)**to see the formula in the selected cells.

- Now, all the formulas are visible in the cells.

- After that, go back to the Windows desktop and right-click on it.
- From the pop-up menu, choose
**New**. - Then follow the arrow direction and select
**Text Document**. This will open the**Notepad**.

- Now press
**CTRL+V**to paste the copied formulas into the**Notepad**. - After that, select all the formulas again in
**Notepad**and press**CTRL+C**to copy them all.

- Finally, paste the formula in the Formula column using the
**CTRL+V**shortcut.

## 7. How to Copy Formula with Cell References?

In this section, we will see how to copy formula with relative or absolute cell references. Here the following two cases are explained with images.

– Creating And Copying Formula Using Relative Cell References

– Only Change One Cell Reference

### 7.1 Creating And Copying Formula with Relative Cell References

Here, we will learn how to create and copy formula using relative cell references. That means you will copy a formula in Excel with changing cell references.

Here we have the **Retail Price** of some **products **and their different **discounts**. We will find the **discount price** using a simple formula.

- First, insert the following formula in cell
**G6**:

`=E6-F6`

- Next, drag down the
**Fill Handle**It will copy the formula to the next cells.

- The formulas are shown beside the cells. You can see, the references have been updated because of relative reference.

### 7.2 Only Change One Cell Reference

Now you will learn how to copy a formula in Excel changing only one cell reference. In the dataset, we have a fixed **rate of discount**. We kept the discount in cell **H6**. We will now copy a formula in Excel without changing cell references (**$H$6**). That means we’ll use absolute cell reference.

- First, insert the formula in cell
**F6**:

`=E6*$H$6`

- Here the
**H6**is a fixed or absolute reference. - Now, drag down the
**Fill Handle**tool and it will copy the formula to the next cells changing only one cell reference.

## 8. What Are the Keyborad Shortcuts to Copy a Formula in Excel?

In this part, you will learn about a few keyboard shortcuts to copy a formula in Excel. Here we will show you these:

– Shortcut for the Whole Column

– Shortcut to Copy Rightward/ Downward

### 8.1 Keyboard Shortcut to Copy Formula for Whole Column

Here you will learn how to copy a formula for the whole column using **CTRL+Enter** together.

- First, type the following formula in cell
**G6**:

`=E6*(1-F6)`

- Next, select the cells where you want to copy the formula including the cell containing the formula.

- Now, press
**F2**to open Edit mode.

- Then, press
**CTRL+Enter**, and the formula will be copied to the other cells.

### 8.2 Shortcut to Copy Rightward/ Downward

In this section, we will learn to copy a formula rightward or downward using a keyboard shortcut. The dataset contains a fixed **7% VAT** and it will be added to the price of **January **month, then will be added to the next price based on the previous month’s price.

- First, enter the following formula in cell
**F8**:

`=E8*($C$5+1)`

- Next, select the cell right beside the cell containing the formula.
- After that, press
**CTRL+R**together.

- And you will see your formula copied to cell
**G8**. - Now, you will also copy the formula down. Select cell
**F9**and press**CTRL+D**. - The formula will be in the cell
**F9**.

- Now, you can see the full method in the following image.

- Finally, repeat the process to copy the formula to all remaining cells.

## 9. How to Copy Exact Formula in Excel?

In this part, you will learn to copy the exact formula from a cell in Excel. Here in the dataset, we have a similar product in the next cell. So we want to keep the previous formula. We can do that using a keyboard shortcut **CTRL+’(upper quotation mark)**. In this way, you can copy a formula down without incrementing it in Excel.

- First, insert the following formula in cell
**G6**:

`=E6*(1-F6)`

- Next, select the immediate cell
**G7**and press**Ctr+’(single upperquote)**shortcut. - It will copy the formula of the above cell.

- Then, you will see the formula in cell
**G7**.

This method comes with a limitation because of its unchanged cell references. It will lead to potential errors when applied to different rows or columns. You may need to adjust references manually.

## 10. How to Copy Formula in Excel From One Sheet to Another Sheet?

In this method, we will see how to copy formula in Excel from one sheet to another sheet.

- First, insert the formula in cell
**G6**and copy till cell**G15**:

`=E6*(1-F6)`

- Next copy the formula in cell
**G6:G15**by pressing the**CTRL+C**shortcut.

- Now, go to
**Another Worksheet (2)**and select the cells to which you want to paste the formula.

- Then, just press
**CTRL+V**and the formula will be in the cells**G15:G16**in**Another Worksheet (2)**sheet.

## 11. How to Copy And Paste Formulas in Excel from One Workbook to Another?

Here, you will learn how to copy and paste formulas in Excel from one workbook to another very easily.

- First, copy the cells by pressing the
**CTRL+C**shortcut.

- Next, go to the
**View**tab and click on**View Side by Side**and**Synchronous Scrolling**.

- It will show both workbooks side by side.

- Then copy the formula in cells
**G6:G15**in the**‘Copy Formula in Excel’**workbook. - Now select the same cells in the
**‘Another workbook’**workbook and paste the formula.

## What to Do When Excel Is Copying Only Values Not Formulas?

Sometimes you may face problems copying formula in Excel. You will see it only copying values, not formulas. You can resolve this issue by following some simple steps.

- We have the below formula in cell
**G6**:

`=E6*(1-F6)`

- Then, while trying to copy the formula to other cells, it is only copying the same value to other cells. It happens when you keep the calculation mode
**‘Manual’**.

- To fix it, go to
**Formulas**tab and select**Calculation Options**from the ribbon. - You will see that the
**Manual**option is selected in the dropdown menu.

- Now, change this to
**Automatic**.

- It will automatically resolve the issue and copy the formula to the cells.

**Download Practice Workbook**

In this article, we have learned how to copy formula in Excel in different methods: using the Fill Handle tool, excel table, keyboard shortcuts, etc. Now you can copy formula in different cases like copying a formula into multiple cells or rows, copying a formula to non-adjacent cells, copying a formula across multiple rows, copying as formulas, copying the formula and pasting it as text, creating and copying the formula using relative references, only change one cell reference, etc.

## Copy Formula in Excel: Knowledge Hub

- How to Copy SUM Formula in Excel
- VBA to Copy Formula from Cell Above in Excel
- Excel VBA to Copy Formula with Relative Reference

**<< Go Back to Excel Formulas | Learn Excel**