Apply a Formula to an Entire Column in Excel

To apply a formula to an entire column in Excel, write it once in the first row, then copy it down. The fastest way is to double-click the fill handle, and the most reliable is to select the range and press Ctrl+D (Mac: ⌘D). This guide shows five methods, when each one fits, and how to keep shared cells locked so every row calculates correctly.

The quickest way: double-click the fill handle#

The fill handle is the small green square at the bottom-right corner of the selected cell. Double-clicking it copies the formula down to the last row of your data in one move.

Here is a small order list. Column D needs =B2*C2 in every row:

1. Click D2 and type =B2*C2, then press Enter. The result is 50.
2. Click D2 again so it is selected.
3. Move the pointer over the fill handle until it becomes a thin black cross.
4. Double-click. Excel fills D3:D6 with 170, 120, 54 and 60.

Excel decides where to stop by looking at the column next to it. If column C has a blank cell in row 4, the fill stops at row 3. See how the fill handle works for dragging, series and the Auto Fill Options button.

Fill down with Ctrl+D (Mac: ⌘D)#

Ctrl+D copies the top cell of a selection into every cell below it. It works even when the next column has gaps, because you choose the range yourself.

1. Click the formula cell, D2.
2. Hold Shift and click the last cell you need, such as D500. Don't use Ctrl+Shift+Down Arrow here: in an empty column it selects all the way to row 1,048,576.
3. Press Ctrl+D on Windows or ⌘D on a Mac.

Selecting a long column is easier with keyboard shortcuts; the guide to selecting an entire column or row lists them all. Ctrl+R (Mac: ⌘R) does the same thing to the right, across a row.

Type once into many cells with Ctrl+Enter#

If the column is empty, you can enter the formula in every cell at the same time. This is useful when you know the exact range, such as rows 2 to 1,000.

1. Click the Name Box (the box left of the formula bar), type D2:D1000 and press Enter. Excel selects the range.
2. Type the formula for the first row: =B2*C2.
3. Press Ctrl+Enter on Windows or Control+Return on a Mac.

Each cell gets its own version: D3 holds =B3*C3, D4 holds =B4*C4, and so on. The same trick fills empty cells in a list with a value from above, which the article on filling blank cells explains step by step.

Lock shared cells with $ before you copy#

When a formula is copied down, every cell reference moves down by the same number of rows. That is what you want for B2 and C2, but not for a single input cell that every row shares, such as a tax rate.

Say G2 holds a VAT rate of 20% (0.2). The formula in D2 is:

=B2*C2*(1+$G$2)

D2 shows 60 (4 × 12.50 × 1.2). The dollar signs make $G$2 an absolute reference: it stays on G2 in every row. Without them, row 3 would read G3, which is empty, and the totals would quietly drop the VAT. Press F4 on Windows or ⌘T on a Mac while the cursor is on the reference to add the dollar signs.

Use an Excel Table so new rows fill themselves#

An Excel Table copies a formula to the whole column for you and keeps doing it as the list grows. This is the best choice for data you add to every week.

1. Click any cell in the data and press Ctrl+T (Mac: ⌘T), then click OK.
2. Type the formula in the first row of the new column.
3. Excel fills the whole column. This is called a calculated column.

When you type a new row under the Table, the formula appears in it automatically. Formulas inside a Table often use column names instead of cell addresses, such as =[@Qty]*[@Price]. They work the same way.

Spill one formula down a column (Microsoft 365)#

In Excel 2021 and Microsoft 365, one formula can return a result for every row at once. This is called a spill (or dynamic array) formula. In D2, type:

=B2:B6*C2:C6

Excel shows all five totals in D2:D6, from a single formula in D2. If you click D3, the formula bar shows the D2 formula in grey, because the result belongs to D2. If something already sits in D3:D6, you get a #SPILL! error until you clear those cells.

Avoid whole-column references like =B:B*C:C for this. They calculate more than a million rows, slow the file down and include the header row.

Common mistakes when filling a formula down a column#

Most problems come from the data around the formula, not the formula itself:

1. The fill stops early. A blank cell in the next column ends a double-click fill. Use Ctrl+D on a selected range.
2. Results are all the same. Calculation may be set to Manual. Go to Formulas > Calculation Options > Automatic.
3. Formatting gets copied too. Fills copy borders and colours. Click Auto Fill Options and choose Fill Without Formatting.
4. A shared cell moved. Lock it with $ and fill again.
5. The column is too narrow. Results show as ##### when they don't fit. See how to AutoFit column width.

If you're filling numbers or dates rather than formulas, AutoFill series explains how Excel continues patterns like 1, 2, 3 or Jan, Feb, Mar.

Practise it#

Filling formulas quickly is one of the first things a job test checks. Practise Fill Down and Fill Right in a live spreadsheet: you copy formulas down a column and across a row, and each answer is checked instantly. Then lock shared cells in the relative vs absolute references lesson. Start with the VAT exercise on this page.