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:

table

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.

table

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):

table

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 |

table

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:

table

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.

table

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:

table

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, ## &.

table

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.

table

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.

table

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:

Cell relative addresses

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.

Cell relative addresses

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):

Relative Column, Absolute Row (B $ 3):

The formula contains the absolute line "3", which means that the formula will always refer to the third line.

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 |

Absolute Column, Relative Row ($ B1):

The formula contains an absolute column "B", which means that the formula will always refer to column "B".

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 |

Absolute Column and Row ($ B $ 1):

The formula contains an absolute column "B" and an absolute row "1", that is, the formula will always refer to column "B" and the first row.

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.