You are viewing the article How to Copy Formulas in Excel at Tnhelearning.edu.vn you can quickly access the necessary information in the table of contents of the article below.
X
This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.
The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.
This article has been viewed 11,084 times.
The Excel program has a feature that makes it easy for users to copy formulas for whole rows or columns, but you don’t always get the results you want. When you get unexpected results or get #REF and /DIV0 errors, you will probably feel unhappy. Don’t worry, though – you don’t need to edit every cell in a 5,000-line spreadsheet. This article will provide some simple ways to help you copy formulas to other cells.
Steps
Use Find and Replace to copy exact formulas
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/3a/579572-1-3.jpg/v4-728px-579572-1-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/3/3a/579572-1-3.jpg/v4-728px-579572-1-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Open the Excel spreadsheet. Surely there will be times when you have to deal with a large spreadsheet with many formulas, and want to copy all the information. Changing all the information to absolute cell references can be somewhat of a challenge, especially when you want to revisit the original information soon after. Use this method to quickly move formulas with relative cell references elsewhere without changing the reference. [1] X Research Source With the following sample spreadsheet, we will copy the formula from column C to column D without changing any factors.
| Column A | Column OVER | OLD Column | Column EASY |
|---|---|---|---|
| line 1 | 944 | Frog | = A1 /2 |
| line 2 | 636 | Toad | = A2 /2 |
| line 3 | 712 | The lizard | = A3 /2 |
| line 4 | 690 | Snake | = A4 /2 |
- If you want to copy the formula in a cell, skip to the last step (“Try Other Ways”) of this section.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/d/d1/579572-2-3.jpg/v4-728px-579572-2-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/d/d1/579572-2-3.jpg/v4-728px-579572-2-3.jpg”,”smallWidth”:460,”smallHeight”:344 ,”bigWidth”:728,”bigHeight”:545,”licensing”:”<div class=”mw-parser-output”></div>”}
Press Ctrl + H to open the Find window. This shortcut is available on Windows and macOS computers.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/9/9f/579572-3-3.jpg/v4-728px-579572-3-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/9/9f/579572-3-3.jpg/v4-728px-579572-3-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Find and replace the “=” sign with another character. Type “=” in the “Find what” field, then enter another character in the “Replace with” box. Click Replace All to convert the entire formula (usually starting with an equal sign) into text that begins with another character. Always use characters that you have not used in the spreadsheet. For example, replace it with a # or &, or a longer string of characters, such as ##&.
| Column A | Column OVER | OLD Column | Column EASY |
|---|---|---|---|
| line 1 | 944 | Frog | ##& A1 /2 |
| line 2 | 636 | Toad | ##& A2 /2 |
| line 3 | 712 | The lizard | ##& A3 /2 |
| line 4 | 690 | Snake | ##& A4 /2 |
- Do not use the * or ? to avoid difficulties in the following steps.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/f3/579572-4-3.jpg/v4-728px-579572-4-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/f/f3/579572-4-3.jpg/v4-728px-579572-4-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Copy and paste the cell. Highlight the cell you want to copy, and press Ctrl + C (on Windows) or Cmd + C (on Mac) to copy. Next, select the cell where you want to paste the information, and press Ctrl + V (on Windows) or Cmd + V (on Mac) to paste. Since they are no longer considered formulas, they will be copied exactly.
| Column A | Column OVER | OLD Column | Column EASY | |
|---|---|---|---|---|
| line 1 | 944 | Frog | ##& A1 /2 | ##& A1 /2 |
| line 2 | 636 | Toad | ##& A2 /2 | ##& A2 /2 |
| line 3 | 712 | The lizard | ##& A3 /2 | ##& A3 /2 |
| line 4 | 690 | Snake | ##& A4 /2 | ##& A4 /2 |
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/3/3c/579572-5-3.jpg/v4-728px-579572-5-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/3/3c/579572-5-3.jpg/v4-728px-579572-5-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Use Find & Replace again to reverse the change. Now that you have the formulas you want, use “Replace All” again to reverse the change. In the following sample spreadsheet, we’ll find the string “##&” and replace it with a “=” again, so that those cells become the original formulas. You can now continue editing the spreadsheet as usual:
| Column A | Column OVER | OLD Column | Column EASY | |
|---|---|---|---|---|
| line 1 | 944 | Frog | = A1 /2 | = A1 /2 |
| line 2 | 636 | Toad | = A2 /2 | = A2 /2 |
| line 3 | 712 | The lizard | = A3 /2 | = A3 /2 |
| line 4 | 690 | Snake | = A4 /2 | = A4 /2 |
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/8f/579572-6-3.jpg/v4-728px-579572-6-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/8/8f/579572-6-3.jpg/v4-728px-579572-6-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Try another way. If the above doesn’t work, or if you’re worried about accidentally changing content in another cell with the “Replace all” feature, there are a few things you can try:
- To copy a formula in a cell without changing the reference, you would select the cell and then copy the formula that appears in the formula bar near the top of the window (not in that cell). Press Esc to close the formula bar, and then paste the formula wherever needed.
- Press Ctrl and ` (also the ~ key) to view the worksheet in formula view. Copy the formulas and paste them into a text editing tool such as Notepad or TextEdit. Copy them again, and then paste them where you want them in the spreadsheet. Next, press Ctrl and ` again to return to the normal view.
Fill in the formula by column or row
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/82/579572-7-3.jpg/v4-728px-579572-7-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/8/82/579572-7-3.jpg/v4-728px-579572-7-3.jpg”,”smallWidth”:460,”smallHeight”:346 ,”bigWidth”:728,”bigHeight”:547,”licensing”:”<div class=”mw-parser-output”></div>”}
Enter the formula in a blank cell. The Excel program makes it easy for users to duplicate formulas by column or row by “filling” information in cells. As with any other formula, you’ll enter the = sign first, and then enter the function or calculation you want. We’ll use a simple sample spreadsheet, and add the numbers in column A with column B. Press Enter or Return to see the result.
| Column A | Column OVER | OLD Column | |
|---|---|---|---|
| line 1 | ten | 9 | 19 |
| line 2 | 20 | 8 | |
| line 3 | 30 | 7 | |
| line 4 | 40 | 6 |
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/03/579572-8-3.jpg/v4-728px-579572-8-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/0/03/579572-8-3.jpg/v4-728px-579572-8-3.jpg”,”smallWidth”:460,”smallHeight”:346 ,”bigWidth”:728,”bigHeight”:547,”licensing”:”<div class=”mw-parser-output”></div>”}
Click in the lower right corner of the cell with the formula you want to copy. The cursor will become a bold + .
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/c/c6/579572-9-3.jpg/v4-728px-579572-9-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/c/c6/579572-9-3.jpg/v4-728px-579572-9-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Click and drag the pointer over the column or row where you want to duplicate the formula. The entered formula is automatically entered into the cells that you have highlighted. Relative cell references will automatically update by cell in the same relative position instead of staying the same. Here is a sample spreadsheet showing the formula used and the results:
| Column A | Column OVER | OLD Column | |
|---|---|---|---|
| line 1 | ten | 9 | = A1 + B1 |
| line 2 | 20 | 8 | = A2 + B2 |
| line 3 | 30 | 7 | = A3 + B3 |
| line 4 | 40 | 6 | = A4 + B4 |
| Column A | Column OVER | OLD Column | |
|---|---|---|---|
| line 1 | ten | 9 | 19 |
| line 2 | 20 | 8 | 28 |
| line 3 | 30 | 7 | 37 |
| line 4 | 40 | 6 | forty six |
- You can also double-click the plus sign to fill the entire column instead of dragging. The Excel program will stop filling in the column when it sees a blank cell. If the reference data has gaps, you’ll have to repeat this step to populate the column below that gap.
- Another way to duplicate a formula for an entire column is to select the cell directly below the cell containing the formula and press Ctrl + D . [2] X Research Source
Copy formula to multiple cells with paste operation
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/0/03/579572-10-3.jpg/v4-728px-579572-10-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/0/03/579572-10-3.jpg/v4-728px-579572-10-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Enter a formula in a cell. As with any other formula, you’ll enter the = sign first, and then enter the function or calculation to use. In the simple sample spreadsheet below, we will add the numbers in column A with column B. When you press Enter or Return , the results are displayed.
| Column A | Column OVER | OLD Column | |
|---|---|---|---|
| line 1 | ten | 9 | 19 |
| line 2 | 20 | 8 | |
| line 3 | 30 | 7 | |
| line 4 | 40 | 6 |
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/1/1b/579572-11-3.jpg/v4-728px-579572-11-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/1/1b/579572-11-3.jpg/v4-728px-579572-11-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Select the cell and press Ctrl + C (on Windows) or ⌘ Command + C (on Mac). This is a public copy operation to the clipboard.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/20/579572-12-3.jpg/v4-728px-579572-12-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/2/20/579572-12-3.jpg/v4-728px-579572-12-3.jpg”,”smallWidth”:460,”smallHeight”:344 ,”bigWidth”:728,”bigHeight”:545,”licensing”:”<div class=”mw-parser-output”></div>”}
Select the cell where you want to paste the copied formula. Click a cell and drag the mouse up and down or use the arrow keys. Unlike column- or row-by-column formula duplication, the cells that are about to be pasted with the formula are not necessarily adjacent to the cell with the formula you copied. You can press the Contrp key during selection to copy non-adjacent cells and regions.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/4/46/579572-13-3.jpg/v4-728px-579572-13-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/4/46/579572-13-3.jpg/v4-728px-579572-13-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Press Ctrl + V (on Windows) or ⌘ Command + V (on Mac) to paste the information. The formula is now visible in the selected cells.
Use relative and absolute cell references
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/f/fe/579572-14-3.jpg/v4-728px-579572-14-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/f/fe/579572-14-3.jpg/v4-728px-579572-14-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Use relative cell references in formulas. In Excel formulas, “cell reference” is the address of the cell. You can enter this cell reference manually, or click the cell you want to use while entering the formula. For example, the following worksheet has a formula that references cell A2:
| Column A | Column OVER | OLD Column | |
|---|---|---|---|
| line 2 | 50 | 7 | = A2 *2 |
| line 3 | 100 | ||
| line 4 | 200 | ||
| line 5 | 400 |
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/85/579572-15-3.jpg/v4-728px-579572-15-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/8/85/579572-15-3.jpg/v4-728px-579572-15-3.jpg”,”smallWidth”:460,”smallHeight”:344 ,”bigWidth”:728,”bigHeight”:545,”licensing”:”<div class=”mw-parser-output”></div>”}
Learn what relative references mean. In Excel formulas, relative references use the relative position of the cell address. In the sample example, cell C2 has the formula “=A2” which is a relative reference to the second value cell on the left. If you copy this formula into cell C4, the value still refers to the second cell to the left, but is now “=A4”.
| Column A | Column OVER | OLD Column | |
|---|---|---|---|
| line 2 | 50 | 7 | = A2 *2 |
| line 3 | 100 | ||
| line 4 | 200 | = A4 *2 | |
| line 5 | 400 |
- This also works with cells outside the worksheet but in the same row and column. If you copied the formula from cell C1 to cell D6 (not shown), Excel changes the “A2” reference to the cell in the right adjacent column (C→D) and down 5 lines (2→7) , or “B7”.
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/8/87/579572-16-3.jpg/v4-728px-579572-16-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/8/87/579572-16-3.jpg/v4-728px-579572-16-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Use absolute references. Let’s say you don’t want Excel to automatically change your formula. Instead of using a relative cell reference, you can make it absolute by adding a $ symbol before the column or row that needs to stay the same, no matter where you copy the formula from. [3] X Research Source The following are sample spreadsheets that show the original formula in large font size and bold, with the same result when you copy and paste it into another cell:
- Relative column, absolute row (B$3): The formula has an absolute reference to line 3, so it always refers to line 3:
Column A Column OVER OLD Column 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 has an absolute reference to column B, so it always refers to column B.
Column A Column OVER OLD Column line 1 50 7 = $B1 line 2 100 = $B2 = $B2 line 3 200 = $B3 = $B3 line 4 400 = $B4 = $B4 - Absolute row and column ($B$1): The formula has an absolute reference to column B of row 1, so it always refers to column B of row 1.
Column A Column OVER OLD Column 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
{“smallUrl”:”https://www.wikihow.com/images_en/thumb/2/26/579572-17-3.jpg/v4-728px-579572-17-3.jpg”,”bigUrl”:” https://www.wikihow.com/images/thumb/2/26/579572-17-3.jpg/v4-728px-579572-17-3.jpg”,”smallWidth”:460,”smallHeight”:345 ,”bigWidth”:728,”bigHeight”:546,”licensing”:”<div class=”mw-parser-output”></div>”}
Use the F4 key to toggle between absolute and relative. Highlight the cell reference in the formula by clicking on it and pressing F4 to automatically add or remove the $ symbol. Continue pressing F4 until the absolute or relative reference you want is selected, then press Enter or Return .
Advice
- If you copy the formula to a new cell and see a green triangle, Excel has identified a possible error. Take a close look at the recipe to find out the cause. [4] X Research Sources
- If you accidentally change the = sign to a ? or * in the “copy exact formula” method, finding the “?” or “*” will not give you the expected result. You can fix the problem by searching for “~?” or “~*”. [5] X Research Sources
- Select the cell and press Ctrl ‘ (apostrophe) to copy the formula of the cell above into it.
Warning
- Other versions of Excel may look different from the screenshots in the article.
X
This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.
The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.
This article has been viewed 11,084 times.
The Excel program has a feature that makes it easy for users to copy formulas for whole rows or columns, but you don’t always get the results you want. When you get unexpected results or get #REF and /DIV0 errors, you will probably feel unhappy. Don’t worry, though – you don’t need to edit every cell in a 5,000-line spreadsheet. This article will provide some simple ways to help you copy formulas to other cells.
Thank you for reading this post How to Copy Formulas in Excel at Tnhelearning.edu.vn You can comment, see more related articles below and hope to help you with interesting information.
Related Search: