How to Quickly Copy a Formula Down in Excel?

Do you need to apply the same formula to multiple rows in an Excel spreadsheet? There’s no need to manually type or copy-paste the formula into each individual cell. Excel provides several quick and easy ways to auto-fill formulas down a column. In this article, we’ll show you how to quickly copy formulas in Excel using fill handle, Fill Down command, keyboard shortcuts, Ctrl-Enter, and absolute cell references.

Using Fill Handle to Copy Formula Down

The fill handle is the small square at the bottom right corner of a selected cell or range. When you hover over the fill handle, the cursor changes to a black plus sign (+).

To use the fill handle to copy a formula down:

1. Select the cell with the formula you want to copy down
2. Move your cursor over the fill handle until it changes to a black (+)
3. Click and drag the fill handle down to extend the formula to adjacent cells below
4. Release the mouse to fill the formula down

The fill handle automatically adjusts the cell references in the formula as it copies down. For example, if the formula in cell B2 references cell A2, then when copied to B3 it will reference A3, in B4 it references A4, and so on.

Fill Down Command

Another way to quickly copy a formula down in Excel is using the Fill Down command. To do this:

1. Select the cell with the formula and the range of cells you want to fill
2. Go to the Home tab on the ribbon
3. In the Editing group, click the Fill button and select Down

This copies the formula from the top cell down to the selected range below, adjusting the cell references accordingly. You can also access Fill Down from the right-click context menu.

Keyboard Shortcuts to Copy Formulas

Excel provides some handy keyboard shortcuts to speed up copying formulas down a column:

• To fill the formula down to adjacent cells, select the cell and press Ctrl + D
• To fill the formula up to adjacent cells, select the cell and press Ctrl + U
• To fill the formula down and across to adjacent cells, select the range and press Ctrl + R

Ctrl-Enter to Fill Formula Down

Here’s a quick way to copy a formula down a whole column in Excel:

1. Select the cell with the formula
2. Press Ctrl + Shift + Down Arrow to select all cells below in the column
3. Press Ctrl + Enter to fill the formula down to the selected cells

Ctrl-Enter fills the formula down and Enter moves the selection to the cell below.

Copy Formula Down with Absolute Cell References

By default, Excel uses relative cell references in formulas, where the column and row references change based on the relative position of the cell containing the formula.

Sometimes you may want a cell reference to remain constant when copying a formula down. To do this, use an absolute cell reference by placing a dollar sign ($) before the column and/or row reference, like $A$1.

To quickly toggle between relative, mixed, and absolute cell references:

1. Double-click the cell to edit the formula
2. Select a cell reference in the formula
3. Press F4 to cycle through the reference types:

• A1 (relative)
• $A$1 (absolute)
• A$1 (mixed with absolute row)
• $A1 (mixed with absolute column)

Using absolute references anchors those cells so they don’t change when auto-filling the formula down.

Example: Copy SUM Formula Down

Let’s walk through an example of using these methods to quickly copy a SUM formula down in Excel.

Suppose you have a worksheet with sales data like this:

To calculate the total sales for each rep:

1. In D2, enter the formula =SUM(B2:C2)
2. Double-click the fill handle to copy the formula down column D
3. The formula will auto-adjust to =SUM(B3:C3) in D3, =SUM(B4:C4) in D4, etc.

The completed table will look like:

You can also use the Fill Down, Ctrl+D or Ctrl+Enter methods explained above to quickly copy the SUM formula down after entering it once in D2.

Copy Formula Down to Filtered Data

When you have a filtered dataset, Excel can auto-fill formulas down only to the visible cells.

To copy a formula down to filtered rows:

1. Enter the formula in the first visible row under the header row
2. Select the cell with the formula
3. Go to the Home tab and find the Editing group
4. Open the Fill drop-down menu and choose Down

This fills the formula down only to the visible rows after applying the filter, skipping the hidden ones.

Paste Only Formulas Down a Column

When you copy cells in Excel, it copies over the contents and the formatting by default. But sometimes you may want to paste only the formulas down without overwriting the existing data or formatting.

Here’s how to paste only the formulas down a column:

1. Select and copy the cells with the formulas you want to paste down
2. Right-click the first cell where you want to paste
3. From the context menu, go to Paste Special > Formulas

This pastes down only the formulas to the destination cells without changing their data or formatting.

Final Thoughts

Learning how to auto-fill formulas in Excel can help you work more efficiently when building spreadsheets. The fill handle, Fill Down command, keyboard shortcuts and Ctrl+Enter technique all provide quick ways to copy a formula down to multiple rows.

For more precise control over formula copying behavior, you can use absolute and mixed cell references where needed. Excel also allows you to copy formulas down to only the visible cells in a filtered list and paste only formulas to a column without overwriting existing data.

By applying these methods, you’ll be able to quickly populate worksheet columns with formulas and calculations, saving time and effort in Excel.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.