How to Copy Formulas That Contain References in Excel

This updated guide explains how to Copy Formulas That Contain References in Excel through clear steps, practical examples, and important checks that help prevent common errors.

1. Copy Formulas That Contain Relative References or Formulas That Contain Both Absolute and Relative References

With formulas containing relative references, there are 2 ways to copy:

1.1. Copy the Reference Formula Changes to the New Address

- First you should understand what is relative reference. Relative references are references that do not have a fixed address or are simply stated in column and row addresses that do not contain the $ sign. When copying the relative reference formula to another position ? the column and row positions change.

- Formulas that contain both absolute and relative references usually have a fixed reference, which doesn't change when copying the formula.

Example

Copy a formula in a column into money.

After entering the formula for cell F6, you want to Copy the formula for other cells in column F6 with the following ways:

Option 1: click cell F6 -> press Ctrl + C -> Highlight from cells E7 to E12 -> Press Ctrl + V ? Copied the formula for the remaining cells. In this case, right-click and choose Copy and Paste .

Method 2: Highlight the whole formula input box ( F6 ) to cell F12, press Ctrl + D ? Copy the formula to all the other cells.

Method 3: Select the first formula input cell, where cell F6 -> moves down the lower right corner of the cell when the plus sign appears and drag to the remaining cells or double click ? So Copy job formula for the remaining cells.

All 3 ways give the same result:

With formulas that contain both absolute and relative references do the same.

1.2. Copy the Reference Formula as It Is When Moving the New Position

If you may need to copy the relative reference formula but keep the formula you can do the following:

- Method 1: Apply when you may need to copy to 1 data box.

click the cell containing the relative reference formula -> move the formula bar -> highlight the formula copy -> select Ctrl + C (Please do not copy the cell containing the reference formula) -> move to the cell you need paste the formula press Ctrl + V .

Result: The formula remains the same when copied to the cell below:

Note: In this way, besides using the key combination Ctrl + C , Ctrl + V, you can use the right mouse to select Copy and Paste .

Method 2: Copy the unchanged formula for multiple data cells.

Step 1: Select the formula block you may need to copy.

Step 2: Press Ctrl + H -> the dialog box appears, select the Replace tab in the Find What section, enter the "=" sign, the Replace entry enter any characters not in the example formula here, enter & - > Click Replace All .

Step 3: After selecting Replace All , the = sign in the formula is replaced with the & symbol:

Step 4: Press Ctrl + C (or right-click and select Copy ) -> Move the mouse to the position where you need to paste the formula -> press Ctrl + V (or right-click and select Paste ). Result:

Step 5: Press the key combination Ctrl + H -> in the Replace tab, replace the & sign with the = sign. In the Find What section, enter the & symbol, the Replace item, enter =, and then click Replace All .

Result:

So you copied the formula containing relative references but didn't change the formula.

The same goes for formulas that contain both absolute references and relative references you do the same.

2. Copy the Formula Containing Absolute References

When working with formulas that contain absolute references, please note that because they are absolute references, the row and column addresses do not change so when making Copy for other references the value is incorrect. For example, setting the value in cell F6 converts the relative address to an absolute address by pressing F4 ? result:

If you use the Copy operation reference formula with relative results:

Due to the absolute address, when copying the formula does not change.

So when you copy a formula that contains absolute references, you do the following:

Option 1: click cell F6 -> press Ctrl + C -> Highlight from cells E7 to E12 -> Press Ctrl + V ? Copied the formula for the remaining cells. In this case, right-click and choose Copy and Paste .

Method 2: Highlight the whole formula input box ( F6 ) to F12, press Ctrl + D ? Copy the formula to all the other cells.

Method 3: Select the first formula input cell, where cell F6 -> moves down the lower right corner of the cell when the plus sign appears and drag to the remaining cells or double click ? So Copy job formula for the remaining cells.

All 3 ways produce the same results: