The following image shows a dataset containing products from different sellers. Each product has a discount. The formula to calculate the discounted price is given in G4. Double-click the Fill Handle to copy the formula to other cells in the Discounted Price column.
⏷Copy a Formula
⏷Filling in a Formula in an Entire Column
⏵Double-Clicking the Fill Handle Icon
⏵Using an Excel Table
⏷Copy a Formula to Multiple Cells
⏷Copy a Formula to Non-Adjacent Cells
⏷Copy a Formula Across Multiple Rows
⏷Copy a Formula Without Formatting
⏵Copy as Formulas
⏵Paste As Text
⏷Copy a Formula with Cell References
⏵Relative References
⏵Change a Cell Reference
⏷Shortcuts to Copy a Formula
⏵Whole Column
⏵Copy Rightward/ Downward
⏷Copy an Exact Formula
⏷Copy to Another Sheet
⏷Copy to Another Workbook
⏷Copying Values Only, Not Formulas
1. How to Copy a Formula in Excel?
- The dataset contains the Retail Price and the Discount Rate of some products.
- Enter the following formula in G6 and press Enter.
=E6*(1-F6)
- Select G6 and drag down the Fill Handle.
- The formula is copied to the other cells in the column.
2. How to Copy an Excel Formula to the Entire Column?
To copy a formula in Excel to the entire column, you can use two different methods.
– Double-clicking the Fill Handle icon (copy the formula without dragging).
– Using an Excel table.
2.1 Double-Clicking the Fill Handle Icon
- Enter the following formula in G6:
=E6*(1-F6)
- Double-click the Fill Handle.
- It will copy the formula till G15.
2.2 Using an Excel Table
- The dataset contains two columns: Retail Price and Discount Rate.
Click on this image for better view
- Select a cell in your dataset >> press CTRL+T (to convert it into a table).
- In the Create Table dialog box, click OK.
Click on this image for better view
- It will convert the dataset into a table.
Click on this image for better view
- Enter the formula in the first cell of the table: G6, and press Enter.
=E6*(1-F6)
Click on this image for better view
- The formula is automatically copied to all the cells in the column.
Click on this image for better view
3. How to Copy a Formula into Multiple Cells?
To calculate the Discounted Price in empty cells by copying the formula:
- Press CTRL and hold the key >> Select the cells to copy the formula.
Click on this image for better view
- Select a cell containing the formula holding CTRL.
- Release CTRL and press F2 to switch to editing mode.
Click on this image for better view
- Press CTRL+Enter, and the formula will be copied to the selected cells.
Click on this image for better view
4. How to Copy a Formula to Non-Adjacent Cells in Excel?
- Enter the following formula in G6:
=E6*(1-F6)
Click on this image for better view
- Copy a cell containing the formula.
Click on this image for better view
- Select the non-adjacent cells holding CTRL.
Click on this image for better view
- Press CTRL+V. It will paste the formula into the cells.
Click on this image for better view
5. How to Copy a Formula Across Multiple Rows in Excel?
Copy a formula across multiple rows to calculate the price of products with 7% VAT based on the previous price.
Click on this image for better view
- Enter the following formula in F8:
=E8*($C$5+1)
Click on this image for better view
- Drag down the Fill Handle to see the result in the rest of the cells.
- The formula is copied to multiple rows.
Click on this image for better view
6. How to Copy a Formula Without Formatting?
6.1 Copy as Formulas
- Enter the formula in G6:
=E6*(1-F6)
Click on this image for better view
- Copy the formula using CTRL+C.
Click on this image for better view
- Select the other empty cells to copy the formula.
- Right-click to display the context menu.
- Select Formulas (F) in Paste Options.
Click on this image for better view
- This will paste the formula only, without formatting.
Click on this image for better view
6.2 Copy Formula And Paste it As Text
- Create another column: Formula.
Click on this image for better view
- Select the cells in the Discounted Price column.
Click on this image for better view
- Press CTRL+~(Tilde icon) to see the formula in the selected cells.
- All the formulas are visible.
Click on this image for better view
- Go back to the Windows desktop and right-click.
- Choose New.
- Follow the arrow direction and select Text Document. This will open the Notepad.
- Press CTRL+V to paste the copied formulas into the Notepad.
- Select all the formulas in the Notepad and press CTRL+C to copy them.
- Paste the formula in the Formula column using CTRL+V.
Click on this image for better view
7. How to Copy a Formula with Cell References?
7.1 Creating And Copying a Formula with Relative Cell References
Create and copy a formula using relative cell references.
To find the discount price:
- Enter the following formula in G6:
=E6-F6- Drag down the Fill Handle to see the result in the rest of the cells.
- The formulas are shown beside the cells. The references were updated.
Click on this image for better view
7.2 Change a Cell Reference Only
Copy a formula in Excel changing a cell reference only.
The dataset showcases a discount rate in H6.
Copy a formula without changing cell references ($H$6) – use an absolute cell reference.
- Enter the formula in F6:
=E6*$H$6- H6 is an absolute reference.
- Drag down the Fill Handle to see the result in the rest of the cells.
8. What Are the Keyborad Shortcuts to Copy a Formula in Excel?
8.1 Keyboard Shortcut to Copy a Formula for the Whole Column
- Enter the following formula in G6:
=E6*(1-F6)
- Select the cells to copy the formula (include the cell containing the formula).
- Press F2 to open the Edit mode.
- Press CTRL+Enter and the formula will be copied to the other cells.
8.2 Shortcut to Copy the formula Rightward/ Downward
Copy a formula rightward or downward using a keyboard shortcut.
The dataset contains a fixed 7% VAT to be added to the price in January and to the next prices based on the previous month.
Click on this image for better view
- Enter the following formula in F8:
=E8*($C$5+1)
Click on this image for better view
- Select the cell beside the cell containing the formula.
- Press CTRL+R.
- The formula is copied to G8.
- Copy the formula down. Select F9 and press CTRL+D.
- The formula will be copied into F9.
Click on this image for better view
- Observe the GIF.
Click on this image for better view
- Repeat the process to copy the formula to the other cells.
Click on this image for better view
9. How to Copy an Exact Formula in Excel?
Copy the exact formula from a cell without incrementing it.
- Enter the following formula in G6:
=E6*(1-F6)
- Select G7 and press Ctr+’(single upperquote).
- It will copy the formula.
- You will see the formula in G7.
10. How to Copy a Formula in Excel From One Sheet to Another?
Copy a formula from one sheet to another.
- Enter the formula in G6 and copy it till G15:
=E6*(1-F6)
Click on this image for better view
- Copy the formula in G6:G15 by pressing CTRL+C.
Click on this image for better view
- Go to Another Worksheet (2) and select the cells to paste the formula.
Click on this image for better view
- Press CTRL+V and the formula is copied to G15:G16 in Another Worksheet (2).
Click on this image for better view
11. How to Copy And Paste Formulas from One Workbook to Another?
Copy and paste formulas from one workbook to another:
- Copy the cells by pressing CTRL+C.
- Go to the View tab and click View Side by Side and Synchronous Scrolling.
- It will show both workbooks side by side.
- Copy the formula in G6:G15 in the ‘Copy Formula in Excel’ workbook.
- Select the same cells in ‘Another workbook’ and paste the formula.
What to Do When Excel Is Copying Only Values, Not Formulas?
If Excel is copying values only, not formulas:
- Enter the formula in G6:
=E6*(1-F6)
- While trying to copy the formula, it is only copying the same value to the other cells: the calculation mode is set to ‘Manual’.
- Go to Formulas and select Calculation Options.
- The Manual option is selected in the dropdown menu.
Click on this image for better view
- Change it to Automatic.
- It will automatically copy the formula to the other cells.
Download Practice Workbook
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!
