Tips on how to copy formulas acurately in excel

Copy formula in excel cell to another cell

Use the following steps to copy formula in one cell to another cell in excel.

Step 1

Select the Excel cell containing the formula that needs to copy.

Step 2

Press the “Ctrl” and “C” keys in the keyboard. Alternatively, you can right click the cell containing the formula and then select “Copy” from the appeared menu.

Step 3

Select the cell that you are going to copy the formula and then press “Ctrl” and “V” keys to paste copied formula into the new cell.

You will observe that the cell addresses in the copied formula in new cell has been changed to adjust the references to position of new cell. When the formula is copied, it automatically adjusts the cell references so that it operates on the new row or column in excel.

Example for Copy formula in excel cell to another cell

Now let us see a practical example to understand how we can copy a formula in excel to another cell.

You can start with downloading this file as it is already setup to do the exercise.

In this example “Amount” in Column “E” for each item is calculated by multiplying “Price” in column “C” and “Quantity” in column “D”.

Let us type “=C3*D3” in cell “E3” and hit “Enter”.

It will calculate the amount for item A as ‘10”.

Now suppose that you want to copy the formula in excel cell “E3” to “E4”.

As I have above mentioned, select the cell E3 which is the cell that contains the formula that you need to copy to another cell.

Then press the “Ctrl” and “C” keys in the keyboard.

Then select the cell “E4” and press “Ctrl” and “V” keys to paste copied formula into the new cell.

You will see that it will automatically calculate the amount for item B by multiplying column C and column D.

Select the cell E4 again and look carefully at the copied formula in E4 in the formula bar. It will look exactly like “=C4*D4”. As I already mentioned above when the formula is copied, it automatically adjusts the cell references so that it operates on the new row or column in excel.

Copying formulas with absolute and relative cell references to cell address

Sometimes, you would not want the formula to change completely when it is copied to a different cell on a excel worksheet. Let us say that we want to discount the amounts in column E by a percentage given in cell E1. So, the formula in E3 should be “=C3*D3*(100%-E1)” and the formula in “E4” should be “=C4*D4*(100%-E1)”.

Now if you copied the formula from E3 to E4 the formula in E4 become “=C4*D4*(100%-E2)”.

it will not give the intended output as the reference for the cell address for E1 is not there and it also changed relatively.

We must use absolute cell referencing method to copy formula in excel without changing reference. A formula with an absolute cell reference contains dollar signs ($) before either the column reference (the letter) or the row reference (the number) or both.

How to copy a formula down a column in excel

You can copy a formula down a column in excel very easily by dragging the fill handle down the column.

Step 01

Select the cell that contains the formula.

Step 02

Click and hold the little solid square (Fill handle) located at the bottom-right corner of the select cell and drag down to copy the formula down the column as you want.

Step 02 – alternative method

Double click on the above-mentioned solid square (Fill handle)

The given formula will be copied in each cell down the column. Depending on the use of absolute or relative cell references, formula in each cell could be exactly same or changed relative to the row and column of each cell.

Example for how to copy a formula down a column in excel

You can start with downloading this file as it is already setup to do the exercise.

In this example “Amount” in Column E for each item is calculated by multiplying “Price” in column C and “Quantity” in column D.

Let us type “=C3*D3” in cell E3 and hit “Enter”.

It will calculate the amount for item A as ‘10”.

Now suppose that you want to copy the formula in cell “E3” down the column up to “E7”.

Select the cell E3 and then drag down the fill handle up to E7. It will copy the formula down the column up to E7 and operate to output the amount for each item.

Alternative you can select the cell E3 and double click on the fill handle. It will flash fill the column up to E7 with copied formula. In this method excel automatically identifies where is last row with data in the column and decides where to stop at copying the content to cells down the column. If you have empty rows or cells of data, this method might not work as it intended. In such cases you can manually drag and fill the formulas down the column.

Copy formula in excel from one column to another column without changing cell addresses (references)

If you want to copy formulas in one column exactly to another column without changing cell addresses or references do as follows. If you have used absolute cell references (have put dollar ($) signs in your cell address references) this method will not need and in that case simple copy paste might work. If you have any doubt let me hear it in comments section so that I can help you out.

Method 01: Using NotePad

Step 01

Go to Formulas Tab à Formula Auditing à Click “Show Formulas”.

Now you can see the formulas as plain text on each cell containing formulas.

Step 02

Select the column of cell range with required formulas to be copied to another column.

Step 03

Press “CTRL” + “C” keys to copy the content.

Step 04

Open Notepad or any preferred text editor.

Step 05

Paste copied content to Notepad file by pressing “CTRL” + “V”.

Step 06

Select the all the content copied to Notepad by pressing “CTRL” + “A”.

Step 07

Select the first cell of the column that you want to copy the content into and then press “CTRL” + “V”

Method 02: Using Find and Replace method

Step 01

Select the column of cell range with required formulas to be copied to another column.

Step 02

Go to Home –> Find & Select –> Replace.

Step 03

In the Find and Replace dialog box:

 In the ‘Find what’ field, enter “=”

 In the ‘Replace with’ field, enter “#” or any other special character like @, $, !.

Step 04

Click “Replace All”. This will replace all the equal to (=) sign with the hash (#) sign.

Step 05

Press “CTRL” + “C” keys to copy the content in these cells.

Step 06

Select the first cell of the column that you want to copy the content and then press “CTRL” + “V”

Step 07

Now select both columns with original and copied formulas,

Step 08

In the Find and Replace dialog box:

 In the ‘Find what’ field, enter “#” or whatever special character you used in step 03.

 In the ‘Replace with’ field, enter “=”

Step 09

Click “Replace All”.

This will convert the text back into the formula and you will get the result.