How do you copy a formula in Excel to another cell?

One of the more tedious tasks in Excel is to copy a formula down an entire column of a report. Not only is it mind-numbing, but it also leaves the opportunity for error if you don’t copy the formula down to exactly the correct cells. For best results, try one of the methods below.

Table of Contents Show

Option 1: Drag the Plus
Option 2: Double-click the plus
Option 3: Copy and Paste
Watch for Green Warning Triangles
Fill Handle
Absolute Reference

Option 1: Drag the Plus

First create your formula in one cell.

After you are satisfied that it is correct, place your mouse cursor at the lower right-hand corner of the cell. You’ll know you have hit it when the cursor changes to a plus sign. Click the plus and drag it down, filling the cells with a copy of the original formula.

Option 2: Double-click the plus

What if you want to copy the formula down a four-hundred-line report? Option 1, dragging the plus down four hundred rows, would burn up your time—and your temper.

Instead, you can accomplish the same copy with a double-click instead of a drag. Set up your formula in the top cell, position the mouse in the lower right-hand corner of the cell until you see the plus, and double-click.

Note that this option can copy the formula down as far as Excel finds data to the left. If you have row headers or other data in place, Excel continues to copy the formula. The fill stops when Excel sees that you have no headers or data to the left.

Option 3: Copy and Paste

What if you want to copy the formula but not the formatting? What if you want to copy beyond the end of the data? Or if you have some other need for flexibility in copying the formula?

You can always use the good ole’ copy and paste method.

Set up your formula in the top cell.
Either press Control + C or click the “Copy” button on the “Home” ribbon.
Select all the cells to which you wish to copy the formula. You can select cells either by dragging the mouse or by using keyboard shortcuts.
Either press Control + V or click the “Paste” button on the “Home” ribbon. For more paste options, click the arrow below the “Paste” button.

Watch for Green Warning Triangles

What should you do if, after copying your formulas, you see a green triangle in the upper left-hand corner of a cell?

This indicates a possible error in the formula. In this example, the correct formula was copied to the surrounding cells but not to this cell, resulting in a formula that differed from its neighbors. Excel noted this and marked it for review. You can fix it by recopying the correct formula to this cell or fixing it in the formula bar.

Otherwise, click the exclamation point for other options.

Next Steps

One of Excel’s strengths is that it offers you many ways to do the same task, leaving you to choose the one that is most convenient. This is true for copying formulas down. This post shows three options for copying a formula down a column, but as you continue to work in Excel, you’ll find other ways to do the same thing, such as through tables or through special range names.

Courses in Customer Service, Excel, HR, Leadership, OSHA and more. No credit card. No commitment. Individuals and teams.

amazing! thanks

saved me 20 min. of work…

THIS IS AWESOME! – THE # = TRICK. GENIUS!
THANK U VERY MUCH!

I have a cell reference issue I hope someone can help me with. I have a cell outside of a range that I always want to refer to a specific cell inside of the range, even when cells are inserted or deleted from the range. For example, cell A10 refers to C10 in the range B1:D200. If someone inserts cells B7:D13, I still want A10 to refer to C10, not C17. I think I need a helper column that has the text “C10” in cell E10. What is the Function that gets A10 to use the static text in E10 to refer to cell C10?

You can use the INDIRECT function. This should work =INDIRECT(“C10”). If you have text C10 in cell E10, just use =INDIRECT(E10)
I’ve made a step in the right direction. ADDRESS(10,3) results in $C$10 and it does not change when cell C10 is moved. CELL(“contents”,$C$10) gives me the proper result. However, CELL(“contents”,ADDRESS(10,3)) is not even accepted. What is wrong with the nested formula?

I think Bansal’s point was that sometimes you can have a range of dynamic formulae that you want to replicate elsewhere
I’ve had this situation occur before but I never thought of using the Notepad method – thanks for that, another weapon in my arsenal

Absolute cell reference is the best. i.e. =A$1$ + B$1$ this cell is locked in that way.

I use absolute/Dynamic references for doing this

Comments are closed.

When you copy a formula, Excel automatically adjusts the cell references for each new cell the formula is copied to.

Copy 101

Simply 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 in the formula bar to clearly see that the formula references the values in column B.

Fill Handle

Use the fill handle in Excel to quickly copy a formula to other cells.

1. For example, select cell A3 below, click on the lower right corner of cell A3 (the fill handle) and drag it across to cell F3.

Result.

You can also use the fill handle to quickly copy a formula down a column.

2. For example, select cell C1 below, click on the lower right corner of cell C1 (the fill handle) and drag it down to cell C6.

Result.

Tip: instead of dragging the fill handle down, simply select cell C1 and double click the fill handle. If you have hundreds of rows of data, this can save time!

Absolute Reference

Create an absolute reference to fix the reference to a cell or range of cells. When you copy a formula, an absolute reference never changes.

1. For example, fix the reference to cell E2 below by placing a $ symbol in front of the column letter and row number.

2. Select cell C2, click on the lower right corner of cell C2 and drag it down to cell C7.

Check:

Explanation: the absolute reference ($E$2) stays the same, while the relative reference (B2) changes to B3, B4, B5, B6 and B7. Visit our page about absolute reference to learn more about this topic.

To move a formula in Excel, simply use cut (CTRL + x) and paste (CTRL + v). Excel pros use the following trick to move a formula.

1. Select a cell with a formula.

2. Hover over the border of the selection. A four-sided arrow appears.

3. Click and hold the left mouse button.

4. Move the formula to the new position.

5. Release the left mouse button.

Exact Copy

To make an exact copy of a formula, without changing the cell references, execute the following easy steps.

1. Click in 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 exact same formula.

Copy Magic

To make an exact copy of multiple formulas, repeat the previous steps for each formula. You can also use the following magic trick.

1. Select multiple formulas.

2. Replace all equal signs with xxx.

Result.

3. Use CTRL + c and CTRL + v to copy and paste the text strings.

4. Select the range B6:B10, hold down CTRL, 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 exact same formula, cell B7 and cell E7 contain the exact same formula, etc.