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.
⏷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.
Click on this image for better view
- 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.
Click on this image for better view
- It will convert the dataset into a table.
Click on this image for better view
- Next, type the formula in the first cell of the table, cell G6, and press Enter.
=E6*(1-F6)
Click on this image for better view
- You will see the formula being copied in all the cells in that entire column automatically.
Click on this image for better view
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.
Click on this image for better view
- 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.
Click on this image for better view
- After that, press CTRL+Enter, and the formula will be copied to all selected cells.
Click on this image for better view
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)
Click on this image for better view
- Now, copy a cell containing the formula.
Click on this image for better view
- Next, select the non-adjacent cells holding the CTRL button.
Click on this image for better view
- After that, press the CTRL+V It will paste the formula to the cells instantly.
Click on this image for better view
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.
Click on this image for better view
- First, insert the following formula in cell F8:
=E8*($C$5+1)
Click on this image for better view
- 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.
Click on this image for better view
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)
Click on this image for better view
- Next, copy the formula using the CTRL+C shortcut.
Click on this image for better view
- 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.
Click on this image for better view
- This will paste only the formula without any formatting.
Click on this image for better view
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.
Click on this image for better view
- Now, select the cells in the Discounted Price column.
Click on this image for better view
- Next, press CTRL+`(Tilde icon) to see the formula in the selected cells.
- Now, all the formulas are visible in the cells.
Click on this image for better view
- 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.
Click on this image for better view
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.
Click on this image for better view
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.
Click on this image for better view
- First, enter the following formula in cell F8:
=E8*($C$5+1)
Click on this image for better view
- 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.
Click on this image for better view
- Now, you can see the full method in the following image.
Click on this image for better view
- Finally, repeat the process to copy the formula to all remaining cells.
Click on this image for better view
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)
Click on this image for better view
- Next copy the formula in cell G6:G15 by pressing the CTRL+C shortcut.
Click on this image for better view
- Now, go to Another Worksheet (2) and select the cells to which you want to paste the formula.
Click on this image for better view
- Then, just press CTRL+V and the formula will be in the cells G15:G16 in Another Worksheet (2) sheet.
Click on this image for better view
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.
Click on this image for better view
- 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
Get FREE Advanced Excel Exercises with Solutions!
