In this tutorial, we will describe how to copy a formula in excel.
Step-1: Prepare a simple data table
Step-2: Set a formula in cell C2, outlined below: 
Step-3: Select cell C-2, outlined below:
Step-4: Follow the below process to copy formula from cell C2 to C3 and C4
- Select cell C2
- Press CTRL+C to quickly copy formula
Or Select copy from home menu, outlined below
- Click the cell where you want to paste the formula. Click C3
- To quickly paste the formula with its formatting, press CTRL+V.
Or Select paste option from home menu, outlined below
Result outlined below
How to Copy Formula to entire column (by double click plus sign (+) in the cell)?
Step-1: Prepare a data table:
Step-2: Set a formula in cell C2, outlined below
Step-3: Position the cursor to the lower right corner of the cell C2, wait until it turns into the plus sign, and then double-click the plus
Result outlined below
How to Copy Formula to entire column using Fill Handle?
Step-1: Prepare a data table
Step-2: Set a formula in cell C2
Step-3: Position the cursor to the lower right corner of the cell C2, wait until it turns into thick black cross. which is called the Fill handle.
- Hold and drag the fill handle down the column over the cells where you want to copy the formula. Drag C2 to C5, outlined below:
Result outlined below
How to Copy Formula with Absolute Reference and Relative Reference?
Relative Reference: When you duplicate a cell using a formula, you’ll see that the cell references in the formula also move an equivalent number of cells up and down. A relative reference is the name given to this kind of cell reference.
Step-1: Formula for Relative reference:
Step-2: Formula output for Relative reference:
Step-3: Copy formula with Relative reference:
When we copy (Ctrl + C) the formula in the image above from cell B8, and paste (Ctrl + V) it into another cell D8, you’ll notice that the cell references change from the B column to the D column, so the total is different. See the Result outlined below:
Note: you can use plus sign to drag the formula or double-click the plus to move the relative reverence formula to target cell. Also, you can use Fill handle to copy formula in targeted cell.
Absolute Reference: Use absolute references if you don’t want Excel to alter the cell references when you copy cells. Insert a dollar sign ($) before both sections of the cell reference in the formula you want to freeze to generate an absolute reference. See the process outlined below:
Step-1: Formula for Absolute reference:
Step-2: Formula output for Absolute reference:
Step-3: Copy formula with Absolute reference:
When we copy (Ctrl + C) the formula in the image above from cell B8, and paste (Ctrl + V) it into another cell D8, you’ll notice that the cell formula change from the B column to the D column, but total result is same. See the Result outlined below:
Note: you can use plus sign to drag the formula or double-click the plus to move the relative reverence formula to target cell. Also, you can use Fill handle to copy formula in targeted cell.
How to Copy Exact Formula in Excel?
Step-1: Use ENTER to Copy Exact Formula in selected Cells, outlined below:
The formula is copied exactly to cell D8.
Use CTRL+ENTER to Copy Exact Formula in Multiple Cells?
Pressing CTRL+ENTER we can copy the same formula in multiple cells at the same time. See the process outlined below:
- Go to Cell D5.
- Copy the formula from the formula bar using CTRL+C.
- Now, select multiple cells by pressing the CTRL button.
- After the selection of cells, press the F2 button.
Cells are in editable mood now. Paste the formula now, by pressing CTRL+V, outlined below:
- Now, press CTRL+ENTER instead of the ENTER only. See the Result outlined below:
All the selected cells are exactly copied with the copied formula.
How to move formula in Excel?
Moving a formula: No matter the sort of cell reference you employ, the references within a formula remain the same when you relocate or move it.
Step-1: prepare a table with formula, outlined below:
Step-2: Select the cell that contains the formula that you want to move. In the Clipboard group of the Home tab, click Cut, outline below:
Paste in cell D8, press CTRL+V, result outline below:
Formula moves to cell D8 from B8.
Step-3: You can also move formulas by dragging the border of the selected cell to the upper-left cell of the paste area. This will replace any existing data. outlined below:
Drag to cell D8, result outlined below:
Formula moves to cell D8 from B8.
Copy a formula to non-adjacent cells / ranges?
Naturally, the fill handle is the quickest method for copying a formula in Excel. But what if you want to transfer your Excel formula into a range of cells that aren’t adjacent to one another or past the end of the source data? Simply copy and paste like you always have:
- Click the cell with the formula to select it. Select Cell C1 outlined below:
- Press Ctrl + C to copy the formula.
- Select a cell or a range of cells where you want to paste the formula (to select non-adjacent ranges, press and hold the Ctrl key). See the selection outlined below:
- Press Ctrl + V to paste the formula.
- Press Enter to complete the pasted formulas. See the result outlined below:
Using Find and Replace to copy formula in Excel:
Step-1: Prepare a data table with information and formula outlined below:
Formula result outlined below:
Step-2: Select the cells that have the formulas that you want to copy, outlined below.
Step-3: Go to Home –> Find & Select –> Replace. Or Press Control + H to open find and replace window:
Step-4: In the Find and Replace dialog box:
- In the ‘Find what’ field, enter =
- In the ‘Replace with’ field, enter #, outlined below:
- Click OK. This will replace all the equal to (=) sign with the hash (#) sign. outlined below:
- Copy these cells D1 to D10, use CTRL + C to copy.
- Paste it in the destination cells F1, use CTRL + V to paste. Result outlined below:
- Go to Home –> Find & Replace –> Replace or press Control + H to open Find and Replace dialog box.
- In the Find and Replace dialog box:
- In the ‘Find what’ field, enter #
- In the ‘Replace with’ field, enter =, outlined below:
- Click OK. This will replace all the equal to (#) sign with the hash (=) sign. This will convert the text back into the formula and you will get the result, outlined below:
You may be interested:
