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.
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.
FAQs
Fill formulas into adjacent cells
Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.
How do you copy a formula down a column? ›
Fill formulas into adjacent cells
Click Home > Fill, and choose either Down, Right, Up, or Left. Keyboard shortcut: You can also press Ctrl+D to fill the formula down in a column, or Ctrl+R to fill the formula to the right in a row.
What are the two ways of copying a formula? ›
In the Clipboard group of the Home tab, click Copy. Do one of the following: To paste the formula and any formatting, in the Clipboard group of the Home tab, click Paste. To paste the formula only, in the Clipboard group of the Home tab, click Paste, click Paste Special, and then click Formulas.
What three methods can be used to copy or move cells in Excel? ›
Use Cut, Copy, and Paste to move or copy cell contents.
How do I copy data from one column to another using formula? ›
You can also edit and select cell data in the formula bar. Select the row or column that you want to move or copy. In the cell, click where you want to paste the characters, or double-click another cell to move or copy the data. or press Ctrl+V.
What is the method of copying formula in Excel? ›
First, we need to select the Excel cell with a formula that we want to copy. After selecting the formula, we must click the shortcut 'Ctrl + C' to copy the formula. We can also cut the formula using the shortcut 'Ctrl + X' when we need to move the formula from one cell to another with the exact cell reference.
How to copy formula in Excel down a column with changing references? ›
Copying Excel formulas with Copy and Paste
- Click in cell B10.
- From the Home tab, choose Copy from the Clipboard group.
- Select range C10:D10.
- Choose Paste from the Clipboard group. Excel pastes the formula from cell B10 and adjusts the cell references accordingly.
A file or folder can be copied or moved to a new location by dragging and dropping with the mouse, using the copy and paste commands, or by using keyboard shortcuts.
What are the three different ways to copy a worksheet in the same workbook? ›
Copy a worksheet in the same workbook
- Right-click the worksheet tab and select Move or Copy.
- Select the Create a copy checkbox.
- Under Before sheet, select where you want to place the copy.
- Select OK.
Select Home > Format Painter. Drag to select the cell or range you want to apply the formatting to. Release the mouse button and the formatting should now be applied.
Select the multiple columns in a sequence with the left key of your mouse by the column header. Next, right-click on the selected columns. Click on the “Copy” option from the dialog box to select the entire data. Now, you'll see that column is highlighted, and then paste it by using the Control + V.
How do I automatically copy value from one cell to another cell in Excel? ›
Fill data automatically in worksheet cells
- Select one or more cells you want to use as a basis for filling additional cells. For a series like 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. ...
- Drag the fill handle .
- If needed, click Auto Fill Options. and choose the option you want.
Grab the fill handle: Click the cell containing the formula, and locate the small blue square (fill handle) in the bottom-right corner of the cell. Drag the fill handle: Click and hold the fill handle, then drag it down to the last cell of the column where you want to apply the formula.
How do you copy a formula down a column without changing it? ›
Here are the steps to copy formulas without changing the cell references:
- Select the cells that have the formulas that you want to copy.
- Go to Home –> Find & Select –> Replace.
- In the Find and Replace dialog box: ...
- Click OK. ...
- Copy these cells.
- Paste it in the destination cells.
- Go to Home –> Find & Replace –> Replace.
Select an argument within the function. Select the cells you want to include in the calculation by doing one of the following: Add values in noncontiguous cells: Click each cell you want to include. Select a range of cells across multiple rows and columns: Drag across the range of cells you want to include.
How do I copy a cell down an entire column in Excel? ›
Move your mouse pointer to the bottom right corner of the selected cell. You should see a small black square, which is the fill handle. Double-click the fill handle. This will automatically copy the formula down to the end of your data, matching the rows in adjacent columns.
