When you copy a formula, Excel will automatically adjust the cell references for each new cell the formula is copied to.
| Join the channel Telegram of the AnonyViet 👉 Link 👈 |
How to copy formulas in Excel
Just use CTRL + c and CTRL + v to copy and paste a formula in Excel.
1. For example, to copy a formula, select cell A3 below and press CTRL+c.
2. To paste this formula, select cell B3 and press CTRL + v.
3. Click the formula bar to clearly see that the formula refers to the values in column B.
Autofill
1. For example, select cell A3 below, click in the lower right corner of cell A3, and drag it to cell F3.
Result:
You can also use autofill to quickly copy formulas to columns.
2. For example, select cell C1 below, click the lower right corner of cell C1 and drag it down to cell C6.
Result:
Absolute reference
Create an absolute reference to freeze a reference to a cell or range of cells. When you copy a formula, the absolute reference never changes.
1. For example, fix the reference to cell E2 below by placing the $ symbol in front of the column letter and the row number.
2. Select cell C2, click the lower right corner of cell C2 and drag it down to cell C7.
Result:
Explanation: the absolute reference ($E$2) stays the same, while the relative reference (B2) changes to B3, B4, B5, B6 and B7.
Move formula
To move a formula in Excel, simply use cut (CTRL + x) and paste (CTRL + v).
1. Select a cell with a formula.
2. Hover the mouse over the border of the cell. A four-sided arrow appears.
3. Click and hold the left mouse button.
4. Move the formula to a new location.
5. Release the left mouse button.
Make a copy of the recipe
To create an exact copy of the formula without changing the cell references, perform these simple steps.
1. Click the formula bar and select the formula.
2. Press CTRL + c and press Enter.
3. Select another cell and press CTRL + v.
Result:
Conclusion: cell A3 and cell B3 contain the same formula.
More about copying formulas
To copy multiple formulas, follow these steps.
1. Select multiple formulas.
2. Enter “xxx” in the “Replace with” box and click “Replace All”.
Result:
3. Use CTRL + c and CTRL + v to copy and paste text.
4. Select the range B6:B10, hold down the CTRL key, select the range E6:E10 and replace all occurrences of ‘xxx’ with equal signs (the exact opposite of step 2).
Result:
Conclusion: cell B6 and cell E6 contain the same formula, cell B7 and cell E7 contain the same formula, etc.
