Guide Formulas
I need this formula in ten thousand rows
Dragging the fill handle down ten thousand rows is not a plan. There are four faster ways, and one of them means the formula fills itself for every row added in future.
Double-click the fill handle — the small square at the bottom-right of the selected cell — and the formula fills down to the end of the adjacent data. Or select the range and press Ctrl+D. Best of all, format the range as a table with Ctrl+T: a formula entered in one cell of a table column fills the entire column automatically, including rows added later.
Which method to use, and when each one fails
All four work. They fail in different ways, and knowing which is which saves you discovering it on a 50,000-row sheet.
Double-clicking the fill handle stops at the first gap in the adjacent column. If column A has a blank at row 400, your formula in column B fills to row 399 and stops. On a long sheet the gap is invisible and you will not notice.
Ctrl+D fills exactly the range you selected, so it never stops early — but you have to select the range, which on 10,000 rows means using the Name Box rather than scrolling.
A table fills automatically and keeps filling for rows added later. This is the only method that stays correct over time.
A spill formula on Microsoft 365 produces the whole column from a single cell, with no fill at all.
| What you see | What actually happened | What fixes it |
|---|---|---|
| Fill stopped part-way down | A gap in the adjacent column ended the double-click fill | Use Ctrl+D over an explicit range |
| New rows have no formula | The range is not a table | Convert with Ctrl+T |
| Every result identical | The references were absolute when they should be relative | Remove the unneeded $ signs |
#SPILL! | Something is blocking the spill range | Clear the cells below |
| Workbook slowed to a crawl | A million formulas over whole-column references | Limit ranges, or use a spill formula |
How to fill a formula down a column
Double-click the fill handle
Select the cell, then double-click the small square at its bottom-right. Excel fills down as far as the neighbouring column has data. Fastest method, and the one that silently stops at a gap — check where it ended before moving on.
Select a precise range and press Ctrl+D
For an exact range, use the Name Box to the left of the formula
bar. Type B2:B10000, press Enter to select it, then Ctrl+D. The
formula in the top cell fills the whole selection, and gaps in neighbouring columns are
irrelevant.
Ctrl+R does the same thing rightwards.
Convert the range to a table
Select any cell in the data and press Ctrl+T. Now type your formula once in a cell of an empty column: Excel fills the entire column immediately, and every row added afterwards inherits it.
This is the only method that stays correct as the data grows. It also gives you structured
references like =[@Quantity]*[@Price], which need no dollar signs and read far better
than =B2*C2.
Microsoft 365: return the whole column from one formula
Modern Excel evaluates a range argument across the whole range:
=B2:B10000*C2:C10000The result spills down automatically. There is only one formula in the workbook, which
recalculates far faster than 10,000 copies and cannot be partly overwritten by someone editing a
single row. Wrap in FILTER or IF to skip blanks.
Everything on this page, as a workbook you can use on your own data
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.
Do not use whole-column references
=SUM(B:B) and =VLOOKUP(A2,Sheet2!A:Z,3,FALSE) are convenient and they are
the main cause of slow workbooks. A whole-column reference asks Excel to consider 1,048,576 rows
whether or not they contain anything.
One is harmless. Ten thousand lookups over whole-column ranges is tens of billions of cell evaluations on every recalculation. Use a table, whose references cover exactly the rows in use, or state a realistic bound.
Selecting a large range without scrolling
- Ctrl+Shift+↓ selects to the last non-empty cell in the column
- Ctrl+Shift+End selects to the last used cell on the sheet
- The Name Box selects any range you can name: type
B2:B10000
Copy and Paste Special is the other reliable route
Copy the formula cell, select the destination range, and use Paste Special → Formulas. This pastes the formula without the source's formatting — useful when the target column is already styled and you do not want the fill to overwrite it.
Google Sheets
Sheets has the same fill handle and Ctrl+D. It also has ARRAYFORMULA, which does what
spill formulas do in Excel and has been available far longer:
=ARRAYFORMULA(B2:B*C2:C).
Questions people ask
How do I apply a formula to an entire column?
Double-click the fill handle at the bottom-right of the cell, or select the range and press Ctrl+D. Converting the range to a table with Ctrl+T fills the column automatically, including future rows.
Why did my fill stop part-way down?
Double-clicking the fill handle fills only as far as the adjacent column has data, so a gap there ends the fill early. Use Ctrl+D over an explicitly selected range instead.
How do I make new rows get the formula automatically?
Format the range as a table with Ctrl+T. A formula in one cell of a table column applies to the whole column and is inherited by every row added afterwards.
Why is my workbook so slow?
Usually whole-column references such as SUM(B:B) repeated thousands of times. Each one considers over a million rows. Use a table, or bound the range to the rows in use.
What does #SPILL! mean?
A spill formula could not write its results because something is in the way. Clear the cells below and to the right of the formula.
Is there an equivalent in Google Sheets?
Yes — ARRAYFORMULA does what Excel's spill formulas do, and it has been available in Sheets for much longer.
Related guides
Take the workbook with you
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.