How to copy formulas that contain references in Excel

In daily work, you are often exposed to referenced formulas, but for the convenience of work you want to use the formula or want to copy the formula, which makes some people confused. The following article details how to copy formulas that contain references in excel.

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.

For 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 on 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

In case you want to copy the relative reference formula but keep the formula you can do the following:

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

Click on 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 want 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:

In case 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 on 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:

Above is how to copy the reference formula. Depending on the reference you choose the most optimal copy.

Good luck!