To put the same formula down a whole column, double-click the fill handle, use Ctrl + Enter, let a dynamic array spill, or turn the data into an Excel Table so new rows get the formula automatically.
Table of Contents
1. Double-click the fill handle
Type the formula in the first cell, then double-click the small square at its bottom-right corner. Excel copies it down as far as the column next to it has data.
2. Ctrl + Enter
- Select all the cells that need the formula.
- Type the formula for the first (active) cell.
- Press Ctrl + Enter instead of Enter.
For thousands of rows, type the range in the Name Box (such as F3:F5000) and press Enter to select it first.
3. Ctrl + D
Select the cell with the formula and the empty cells below it, then press Ctrl + D to fill down. Ctrl + R fills right.
4. Dynamic array formula (Excel 365)
Refer to the whole range once, and the results spill:
=D3:D12*E3:E12
There’s only one formula to edit. To follow data of any length, use a Table reference or =TRIMRANGE(D3:D1000)*TRIMRANGE(E3:E1000) in current Excel 365.
5. Calculated column in an Excel Table
Convert the data to a Table with Ctrl + T. Type the formula once and Excel fills the whole column, and every new row, automatically.
The formula uses names like [@Qty]*[@Rate] instead of cell addresses, which makes it easier to read.
Which method to use
| Method | Best for |
|---|---|
| Double-click fill handle | Quick one-off fills next to existing data |
| Ctrl + Enter | Filling a selection, including non-adjacent cells |
| Dynamic array | One formula for the whole column, Excel 365 |
| Excel Table | Data that grows every week |