In Excel, you can quickly copy a formula to many cells in one row or one column, but the result will not always be as expected. If you do not achieve the desired result or #REF and / DIV0 messages appear in the cells, read what the absolute and relative cell addresses are to find out the cause of the error. Remember that you do not have to make changes in all cells of the table, which consists of 5,000 rows - for this there are methods that allow you to automatically update the formula in a specific cell or copy it without changing the values.
Steps
Method 1 of 4: Copying a Formula Using Drag and Drop
Step 1. Enter the formula in one cell
First enter an equal sign (=), and then enter the desired function or mathematical operation. For example, you need to add the values of columns A and B:
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 10 | 9 | = A1 + B1 |
| line 2 | 20 | 8 | |
| line 3 | 30 | 7 | |
| line 4 | 40 | 6 |
Step 2. Press the "Enter" key to calculate the result using the entered formula
The cell where you entered the formula will display the result (in our example, the calculated sum is 19), but the formula will be stored in the spreadsheet.
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 10 | 9 | 19 |
| line 2 | 20 | 8 | |
| line 3 | 30 | 7 | |
| line 4 | 40 | 6 |
Step 3. Click on the marker in the lower right corner of the cell
Move the pointer to the lower-right corner of the formula cell; the pointer turns into a "+" symbol.
Step 4. Hold the "+" sign and drag it over the desired cells in the column or row
Hold down the mouse button, and then drag the + symbol down a column or to the right in a row to select the cells you want. The entered formula will be automatically copied to the selected cells. Since there is a relative cell address here, the cell addresses (in the copied formulas) will change accordingly. In our example (the formulas that were automatically changed and the calculated values are shown):
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 10 | 9 | = A1 + B1 |
| line 2 | 20 | 8 | = A2 + B2 |
| line 3 | 30 | 7 | = A3 + B3 |
| line 4 | 40 | 6 | = A4 + B4 |
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 10 | 9 | 19 |
| line 2 | 20 | 8 | 28 |
| line 3 | 30 | 7 | 37 |
| line 4 | 40 | 6 | 46 |
Step 5. Double click on the "+" to copy the formula to all cells in the column
Instead of dragging the "+" symbol, move the mouse pointer to the lower-right corner of the formula cell and double-click the "+" sign that appears. The formula will be copied to all cells in the column.
If there are blank cells in the column, automatic copying of the formula will stop as soon as the first blank cell is reached. In this case, repeat the steps above to copy the formula to the cells below the empty cell
Method 2 of 4: Copying a Formula Using Paste
Step 1. Enter the formula in one cell
First enter an equal sign (=), and then enter the desired function or mathematical operation. For example, you need to add the values of columns A and B:
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 10 | 9 | = A1 + B1 |
| line 2 | 20 | 8 | |
| line 3 | 30 | 7 | |
| line 4 | 40 | 6 |
Step 2. Press the "Enter" key to calculate the result using the entered formula
The cell where you entered the formula will display the result (in our example, the calculated sum is 19), but the formula will be stored in the spreadsheet.
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 10 | 9 | 19 |
| line 2 | 20 | 8 | |
| line 3 | 30 | 7 | |
| line 4 | 40 | 6 |
Step 3. Click on the cell with the formula and then press CTRL + C to copy it. 4 Select the cells where the formula will be copied
Click on one of the cells, hold the mouse button and move the pointer over the required cells. Alternatively, you can hold down the Ctrl key and click on non-contiguous desired cells. Step 5. Paste the formula into the selected cells.
To do this, press CTRL + V.
Method 3 of 4: Copying the Formula Exactly
Step 1. Use this method to copy the formula without changing the addresses of the cells that are included in it
Sometimes the spreadsheet is full of formulas, and they need to be copied exactly (that is, without modification). If you change the relative addresses of the cells to absolute addresses in all formulas, you can waste a lot of time (this is all the more unacceptable if you have to change addresses again in the future). This method involves copying a formula with relative cell addresses to other cells so that the formula does not change. For example, let's copy the entire contents of column C to column D:
| Column A | Column B | Column C | Column D | |
|---|---|---|---|---|
| line 1 | 944 | Frogs | = A1 / 2 | |
| line 2 | 636 | Toads | = A2 / 2 | |
| line 3 | 712 | Newts | = A3 / 2 | |
| line 4 | 690 | Snakes | = A4 / 2 |
If you just want to copy one formula, skip to the “Use an alternate method” step in this section
Step 2. Open the "Find" window
In most versions of Excel, click the Home tab at the top of the Excel window, and then click Find and Select in the Editing section. Alternatively, you can press CTRL + F.
Step 3. Find and replace the equal sign (=) with another character
Enter "=", click on "Find All", and then enter any other character in the "Replace" field. In this case, all cells with formulas (beginning with an equal sign) will automatically turn into text cells that start with different characters. Please note that you need to enter a character that is not in the cells of the table. For example, replace the equal sign with # or &; also "=" can be replaced with several symbols, for example, ## &.
| Column A | Column B | Column C | Column D | |
|---|---|---|---|---|
| line 1 | 944 | Frogs | ## & A1 / 2 | |
| line 2 | 636 | Toad | ## & A2 / 2 | |
| line 3 | 712 | Newts | ## & A3 / 2 | |
| line 4 | 690 | Snakes | ## & A4 / 2 |
Do not use the * or? Characters so you don't run into problems later
Step 4. Copy and paste the cells
Select the cells you want to copy and paste them into other cells. Since the selected cells are now text cells and not formula cells, the latter will be copied unchanged.
| Column A | Column B | Column C | Column D | |
|---|---|---|---|---|
| line 1 | 944 | Frogs | ## & A1 / 2 | ## & A1 / 2 |
| line 2 | 636 | Toad | ## & A2 / 2 | ## & A2 / 2 |
| line 3 | 712 | Newts | ## & A3 / 2 | ## & A3 / 2 |
| line 4 | 690 | Snakes | ## & A4 / 2 | ## & A4 / 2 |
Step 5. Use the Find and Replace functions again to revert to the previous formulas
Once you have copied the formulas as text, use the Find All and Replace With functions to revert to your previous formulas. In our example, we will find all the ## & characters and replace them with an equal sign (=) so that formulas appear in the cells.
| Column A | Column B | Column C | Column D | |
|---|---|---|---|---|
| line 1 | 944 | Frogs | = A1 / 2 | = A1 / 2 |
| line 2 | 636 | Toad | = A2 / 2 | = A2 / 2 |
| line 3 | 712 | Newts | = A3 / 2 | = A3 / 2 |
| line 4 | 690 | Snakes | = A4 / 2 | = A4 / 2 |
Step 6. Use an alternative method
If the method described here did not lead to success or you do not want to use it, so as not to accidentally change the characters in other cells, use other methods:
- To accurately copy a single formula, select the formula cell, and then copy the formula that appears in the formula bar (not in the cell) at the top of the window. Press esc to close the formula bar, and then paste the formula in the cells you want.
- Press Ctrl` (usually this symbol is on the key with the ~ symbol) to switch to the formulas view. Copy the formulas and then paste them into a simple text editor such as Notepad or TextEdit. Copy the formulas from a text editor and paste them into the desired cells in the spreadsheet. Press Ctrl` again to return to normal table mode.
Method 4 of 4: Using relative and absolute cell addresses
Step 1. Use the relative cell address in the formula
To do this, enter the address manually or click on the required cell when entering the formula. For example, this table has a formula with the address of cell A2:
| Column A | Column B | Column C | |
|---|---|---|---|
| line 2 | 50 | 7 | = A2 * 2 |
| line 3 | 100 | ||
| line 4 | 200 | ||
| line 5 | 400 |
Step 2. Remember what the relative cell address is
In a formula, this address indicates the relative position of the cell. For example, if cell C2 contains the formula "= A2", that formula indicates the value that is two columns to the left. If you copy the formula into cell C4, this formula will again point to the value that is two columns to the left - that is, the formula "= A4" will be displayed in cell C4.
| Column A | Column B | Column C | |
|---|---|---|---|
| line 2 | 50 | 7 | = A2 * 2 |
| line 3 | 100 | ||
| line 4 | 200 | = A4 * 2 | |
| line 5 | 400 |
This principle applies to cells in different rows and columns. For example, if you copy the same formula from cell C1 to cell D6 (not shown), the formula will display the address of the cell that is located one column to the right (C → D) and five rows below (2 → 7), namely the address "B7" …
Step 3. Use the absolute cell address in the formula
Do this so that the formula does not change automatically. To convert a relative address to an absolute address, enter the $ character in front of the column letter or line number that you do not want to change. The following are examples (original formula in bold; this formula is also shown copied to other cells):
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 50 | 7 | = B $ 3 |
| line 2 | 100 | = A $ 3 | = B $ 3 |
| line 3 | 200 | = A $ 3 | = B $ 3 |
| line 4 | 400 | = A $ 3 | = B $ 3 |
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 50 | 7 | = $ B1 |
| line 2 | 100 | = $ B2 | = $ B2 |
| line 3 | 200 | = $ B3 | = $ B3 |
| line 4 | 400 | = $ B4 | = $ B4 |
| Column A | Column B | Column C | |
|---|---|---|---|
| line 1 | 50 | 7 | = $ B $ 1 |
| line 2 | 100 | $ B $ 1 | $ B $ 1 |
| line 3 | 200 | $ B $ 1 | $ B $ 1 |
| line 4 | 400 | $ B $ 1 | $ B $ 1 |
Step 4. Use the F4 key to convert the relative address to absolute
In a formula, select the cell address by clicking on it, and then press F4 to enter or delete the $ character (s). Press F4 until you create the absolute address you want, then press Enter.
Advice
- If you see a green triangle icon when you copy a formula in a cell, Excel has encountered an error. Take a close look at the formula to figure out what went wrong.
- If you accidentally replace the equal sign (=) with the? or *, searching for these characters will yield nothing. In this case, look for the symbols ~? or ~ *.
- Click in a cell and press Ctrl '(apostrophe) to copy the formula into it from the cell above the selected cell.