Excel Tutorials
Auto-complete a pattern with Flash Fill, or copy a value straight down a column with Fill Down, two different shortcuts for two different situations. This lesson uses the Event Planner workbook from Excel Foundations, free to download below.
Follow along in the same file used in the video. No email required.
Both auto-complete cells for you, but they solve different problems. Flash Fill watches what you type, recognizes a pattern, and fills the rest of the column to match it, useful for splitting, combining or reformatting text. Fill Down simply copies one cell's exact value or formula into the cells below it, no pattern recognition involved, just a direct copy.
If you want Excel to figure out a pattern, use Flash Fill. If you already have the exact value or formula you want repeated, Fill Down is faster and more predictable.
Step-by-step, matching the video above.
Flash Fill needs an empty column beside the data it's learning the pattern from.
In the first row, manually type exactly what you want the result to look like, based on the data in the row next to it.
Begin typing the next value the same way. Excel usually recognizes the pattern and shows a grayed-out preview of the rest of the column.
If the preview looks right, press Enter and the whole column fills in automatically.
Select the range including your one typed example, go to the Data tab, and click Flash Fill (or press Ctrl+E).
Flash Fill is a smart guess, not a formula, scan the filled column for anything that doesn't match the pattern you intended.
| A: Full Name | B: You Type | Flash Fill Completes |
|---|---|---|
| Maria Gonzalez | Maria | Maria |
| David Chen | (pattern learned) | David |
| Priya Patel | (pattern learned) | Priya |
Typing "Maria" next to "Maria Gonzalez" teaches Flash Fill the pattern: take everything before the space. It applies that same logic down the rest of the column automatically, splitting each full name into just the first name.
| Row | C: Qty | D: Price | E: Formula |
|---|---|---|---|
| 2 | 4 | 12.50 | =C2*D2 → 50.00 |
| 3 | 2 | 8.00 | =C3*D3 → 16.00 |
| 4 | 6 | 3.25 | =C4*D4 → 19.50 |
Write =C2*D2 once in row 2, select rows 2 through 4, and press Ctrl+D. The formula copies down with its references shifting automatically, C2 becomes C3, then C4, unlike Flash Fill's static values.
Fill Down doesn't guess a pattern, it copies one cell's exact value or formula into the cells beneath it, adjusting relative references the same way a normal copy would.
Click the cell with the value or formula you want repeated, then extend the selection down to include every cell you want it copied into.
This fills every selected cell below the top one with the same value or formula, instantly, no dragging required.
Click and drag the small square at the bottom-right corner of the source cell down through the cells you want filled.
If there's data in the column right next to it, double-clicking the fill handle automatically fills down to match that column's length, no dragging needed.
Three different ways to fill a column, worth knowing when each is the right one.
It repeats one cell's value or formula down a range. There's no pattern detection, whatever's in the source cell is what gets copied.
Once it fills the column, the results are plain values, not formulas. If the source data changes later, Flash Fill doesn't update automatically.
A formula like a join formula or PROPER recalculates automatically whenever the source cells change.
Use Fill Down to repeat one exact value or formula. Use Flash Fill for a quick, one-off pattern-based cleanup. Use a real formula when the source data is likely to keep changing and you want the result to stay current.
Ctrl+D just repeats the exact same value or formula, it won't detect or reformat a pattern. If you need Excel to interpret and reshape the data, that's Flash Fill's job, not Fill Down's.
Flash Fill guesses based on limited examples, it can misinterpret an edge case partway down a long list. Always scan the results.
Flash Fill results are static values, not live formulas. Editing the original data won't change what Flash Fill already filled in.
An inconsistent or ambiguous first example, like inconsistent spacing or unexpected characters, often leads to Flash Fill guessing the wrong pattern for later rows.
A one-time cleanup trick shouldn't be your only option.
Free 14-day trial. No credit card required.
A feature that detects a pattern from an example you type and automatically fills the rest of the column to match, without writing a formula.
Select the range including your typed example and press Ctrl+E, or go to the Data tab and click Flash Fill.
No. Flash Fill produces static values, not live formulas, so it won't update if the source data changes later.
Flash Fill is a one-time pattern guess based on an example. A formula recalculates automatically every time the source cells change.
It usually needs a clean, unambiguous first example. Inconsistent spacing or formatting in your example can cause it to guess incorrectly.
Yes, it works both directions, splitting a full name into first and last, or combining separate columns into one, based on whatever example you provide.
Select the source cell and the cells below it, then press Ctrl+D, or double-click the fill handle at the bottom-right of the cell to auto-fill down to match an adjacent column's length.
Fill Down copies one cell's exact value or formula into the cells below it, no pattern detection involved. Flash Fill detects a pattern from an example and reformats data to match it.
Every business starts with a spreadsheet. Updoot is where you scale past it, records are already formatted the same way every time.
Start Your Free Trial →