Why does typing 'Jan' and dragging give you 'Feb', 'Mar', but typing 'Q1' gives you 'Q1', 'Q1', 'Q1'? Why does autofill jump from 100 to 200 instead of 101? Why does it ignore your custom list unless you restart Excel?
The Problem
You’ve typed data in A1:A4 hoping Excel will extend the pattern. It doesn’t. You drag the fill handle anyway — and get garbage.
This happens because autofill doesn’t ‘guess’. It matches against built-in lists or detects arithmetic/linear trends — and only if your starting selection gives it enough clues. Below is what a typical failed attempt looks like:
| Cell | Value | Intended Pattern | What Autofill Actually Produced (A5:A8) |
|---|---|---|---|
| A1 | Q1 FY24 | Quarterly fiscal labels | Q1 FY24 |
| A2 | Q2 FY24 | Q3 FY24 → Q4 FY24 → Q1 FY25 | Q1 FY24 |
| A3 | $45,200 | +12% growth per quarter | $45,200 |
| A4 | $50,624 | (45,200 × 1.12) | $50,624 |
| A5 | — | — | $45,200 |
| A6 | — | — | $50,624 |
| A7 | — | — | $45,200 |
| A8 | — | — | $50,624 |
The Solution
Autofill works — but only when Excel can classify your input. Do this instead:
- Select A1:A2 (not just A1). That gives Excel two points to infer sequence direction and increment.
- Hover over the bottom-right corner of the selection until the cursor becomes a thin black cross (
+). Click and drag down to A8. - Release. Excel now sees “Q1 FY24 → Q2 FY24” and extends to Q3, Q4, Q1 FY25, Q2 FY25, Q3 FY25.
- For numbers: select A3:A4 ($45,200 and $50,624), then drag. Excel calculates the difference (+$5,424) and adds it repeatedly: $56,048, $61,472, etc.
Result after correct selection and drag:
| Cell | Value | Notes |
|---|---|---|
| A1 | Q1 FY24 | Original |
| A2 | Q2 FY24 | Original |
| A3 | Q3 FY24 | Autofilled |
| A4 | Q4 FY24 | Autofilled |
| A5 | Q1 FY25 | Autofilled |
| A6 | Q2 FY25 | Autofilled |
| A7 | $45,200 | Original |
| A8 | $50,624 | Original |
| A9 | $56,048 | +5,424 |
| A10 | $61,472 | +5,424 |
| A11 | $66,896 | +5,424 |
| A12 | $72,320 | +5,424 |
Going Further
Autofill has modes. Right-click while dragging to choose:
- COPY CELLS: Paste exact values (no pattern).
- FILL SERIES: Default behavior — detect and extend.
- FILL FORMULAS: Copy formulas, updating relative references.
- FILL WITHOUT FORMATTING: Values only, no fonts/colors/borders.
Custom lists? Yes — but only if defined first. Go to File > Options > Advanced > General > Edit Custom Lists…. Add "Q1 FY24,Q2 FY24,Q3 FY24,Q4 FY24" as one list. Then type Q1 FY24 in A1, select A1, and drag — it’ll cycle correctly.
Surprising tip: Type 1 in A1, 3 in A2, select both, then drag. Excel infers step = +2, not +1. It always uses the delta between first two cells — never assumes +1 unless you give it identical increments.
When NOT to Use This
Autofill fails silently in four cases:
- Text-only single cell: Type "Project Alpha" in A1 and drag → copies, never increments.
- Mixed data types: A1 = "Jan", A2 = 100 → Excel abandons pattern detection and copies.
- Dates with inconsistent formatting: A1 = 01/01/2024 (as date), A2 = "Feb-24" (as text) → no series.
- Formulas returning errors: If A1 contains
=B1/C1and C1=0, dragging repeats the #DIV/0! error — no calculation occurs.
Also: Autofill ignores leading zeros. Type "001" and "002" → it fills "3", "4", "5". To preserve, format column as Text first — or prefix with apostrophe: '001.
Keyboard Shortcuts
| Action | Windows Shortcut | Mac Shortcut | Notes |
|---|---|---|---|
| Fill Down (same as dragging) | Ctrl+D | Cmd+D | Select source cell + destination range first (e.g., A1:A10) |
| Fill Right | Ctrl+R | Cmd+R | Same selection rule — select B1:E1, then Ctrl+R |
| Open Series Dialog (manual control) | Alt+H+F+I+S | — | Lets you set step value, stop value, series type (date/linear/growth) |
| Force Fill Series (bypass default) | Alt+E+I+S | — | Legacy shortcut — still works in Excel 365 |
