What You’ll Learn
- ✓ How to find and use the Excel fill handle to drag-fill any sequence
- ✓ How to AutoFill numbers, dates, days, months, and mixed text-number series
- ✓ Speed shortcuts: Ctrl+D, Ctrl+R, and the double-click fill handle trick
- ✓ How to use Flash Fill (Ctrl+E) to split, reformat, and combine data instantly
- ✓ When to use AutoFill vs. Flash Fill — and what to do when Flash Fill fails
AutoFill and Flash Fill in Excel are two of the most time-saving features a beginner can discover — and most people stumble across them by accident, if at all. You are typing “January” in a cell. Then “February.” Then “March.” Somewhere around “September” you start wondering whether there is a better way. There is. Once you know how both features work, you will wonder how you ever filled a spreadsheet without them.
Here’s the thing: most people discover these features after months of unnecessary manual work. This lesson means you will not have to wait that long. By the end of it, you will know how to fill an entire column of dates, months, or numbered sequences in seconds, and how to reformat a messy data column with a single keyboard shortcut.
Two features. One lesson. A lot of time saved.
What Is AutoFill in Excel?
AutoFill is Excel’s way of continuing a pattern you have already started. You type one or two values, and Excel looks at them, figures out the sequence, and completes it — as far down or across as you want to go.
Think of it like this: if you start writing the alphabet and hand the pen to a friend after the letter C, a sensible friend keeps going with D, E, F. AutoFill is that friend — except it never gets distracted, never skips a letter, and works on thousands of rows without complaint.
The Fill Handle — The Small Green Square That Changes Everything
Before you can use AutoFill, you need to find the fill handle. It is a small solid green square in the bottom-right corner of any selected cell. Click any cell containing a value, look at the very bottom-right corner of its blue border, and you will see it.
When you hover your mouse over it, your cursor changes from the usual white arrow to a thin black crosshair. That change is your signal: you are ready to click and drag to fill.
If you cannot see the fill handle at all, it may be turned off. Go to File → Options → Advanced and make sure “Enable fill handle and cell drag-and-drop” is ticked.
How to Drag to Fill (Excel Drag to Fill Basics)
The standard way to use AutoFill is to grab the fill handle and drag. Here is how:
How to drag-fill a sequence:
- Type your first value in a cell — for example,
1in cell A1. - Type your second value in the cell below —
2in A2 — to establish the pattern. - Select both cells.
- Hover over the fill handle in the bottom-right corner until the black crosshair appears.
- Click and hold, then drag down (or across) as far as needed.
- Release the mouse. Excel fills the sequence automatically.
Always give Excel at least two starting values when filling sequences — one value alone may cause it to copy rather than continue the pattern.
What Can AutoFill Do? (More Than You Think)
Most beginners assume AutoFill is only for numbers. It goes much further than that.
Filling a Number Sequence
Type 1 in A1 and 2 in A2, select both, and drag. Excel continues: 3, 4, 5, and so on. You can set any interval — type 10 and 20 and AutoFill continues: 30, 40, 50. Whatever gap you establish in the first two cells becomes the rule.
💡 PRO TIP
You do not have to start from 1. If you need a sequence that starts at 50 — for invoice numbers or reference IDs continuing from an existing list — just type 50 and 51 in the first two cells, select both, and drag. AutoFill picks up wherever you start.
Filling Dates, Days, and Months — The Excel AutoFill Series
This is the part that makes people’s eyes light up. Type “Monday” in a cell, grab the fill handle, and drag down. Excel fills in Tuesday, Wednesday, Thursday, Friday — all the way through the week and back to Monday if you keep going. It knows the sequence because Monday is part of Excel’s built-in series list.
You’ve probably seen colleagues do this and assumed it was some kind of advanced skill. It is not. It is a drag.
| Type Into First Cell | AutoFill Continues With… |
|---|---|
| Monday | Tuesday, Wednesday, Thursday… |
| Mon | Tue, Wed, Thu, Fri… |
| January | February, March, April… |
| Jan | Feb, Mar, Apr… |
| Q1 | Q2, Q3, Q4, Q1… |
| 01/01/2025 | 02/01/2025, 03/01/2025… (use AutoFill Options to change to months or years) |
Filling Mixed Text and Numbers (e.g. Invoice 1, Invoice 2…)
AutoFill handles text-number combinations too. Type Invoice 1 in one cell and Invoice 2 in the next. Select both and drag. Excel continues: Invoice 3, Invoice 4, Invoice 5. Also works for Week 1 / Week 2, Item 1 / Item 2, Q1 Report / Q2 Report, and so on.
⚠️ COMMON MISTAKE
Typing only Invoice 1 in a single cell and dragging without a second value. Excel has no pattern to work from and will likely just copy “Invoice 1” down the whole column. Always provide two cells to establish the direction and interval.
The AutoFill Options Button — Copy vs. Fill Series
After you complete an AutoFill drag, a small button appears near the last filled cell — it looks like a clipboard icon with a dropdown arrow. Most beginners never click it. That is a missed opportunity.
| Option | What It Does |
|---|---|
| Copy Cells | Repeats the original value — no sequence, just copies |
| Fill Series | Continues the pattern (numbers, dates, months) |
| Fill Formatting Only | Copies the cell’s formatting without its value |
| Fill Without Formatting | Continues the sequence but ignores the source cell’s styling |
The most useful scenario: you filled dates by day but needed months. Click AutoFill Options and choose Fill Months. Done — January 1st, February 1st, March 1st instead of January 1st, January 2nd, January 3rd. Always glance at that button after a fill. It takes one second and can save you from quietly wrong data.
Faster AutoFill — Keyboard Shortcuts and the Double-Click Trick
Dragging with the mouse is intuitive, but once you are working with larger spreadsheets, the mouse slows you down. Here are the faster alternatives.
Fill Down with Ctrl+D and Fill Right with Ctrl+R
Trust me on this — Ctrl+D and Ctrl+R are two shortcuts worth learning today.
Fill Keyboard Shortcuts
Ctrl + D
Fill Down — copies the top cell into all selected cells belowCtrl + R
Fill Right — copies the leftmost cell into all selected cells to the rightCtrl + E
Flash Fill — detects a pattern from your example and fills the columnHow to fill down with Ctrl+D:
- Type your value or formula in the top cell of a column.
- Select that cell and all the cells below it that you want to fill (click the top cell, hold Shift, use the down arrow to extend).
- Press Ctrl+D.
Formulas adjust their row references as they fill down — exactly like dragging would. Use Ctrl+R for the same operation running horizontally.
The Double-Click Fill Handle Method
This is genuinely underrated, and almost nobody knows it exists until someone shows them.
If there is data in an adjacent column, you can double-click the fill handle instead of dragging it. Excel automatically fills down as far as the adjacent column has data.
So if column A has 500 rows of customer names and you have a formula in B1 that needs to apply to all 500 rows — do not drag 500 rows. Just double-click the fill handle in B1. Excel fills down to B500 instantly. This is the Excel drag to fill method taken to its logical, fastest conclusion — no dragging needed at all.
What Is Flash Fill in Excel?
Flash Fill is a completely different kind of tool. Where AutoFill continues a sequence, Flash Fill recognises a transformation pattern from a single example you give it — then applies that transformation to the rest of your data automatically.
It was introduced in Excel 2013 and has been one of the most beloved beginner features ever since.
How Flash Fill Differs From AutoFill
The clearest way to put it: AutoFill continues things. Flash Fill transforms things.
If AutoFill is a friend who finishes your sentences, Flash Fill is a colleague who glances at your messy spreadsheet, understands in a second what you are trying to achieve, and fixes the whole thing while you make a cup of tea. You show it the result you want once. It handles the rest.
How to Trigger the Excel Flash Fill Feature (Ctrl+E)
The fastest way to use Flash Fill is with the keyboard shortcut Ctrl+E. Here is the step-by-step for the most common scenario:
How to use Flash Fill (Ctrl+E):
- Put your source data in one column — for example, full names in column A.
- Click the first empty cell in the adjacent column — cell B1.
- Type the result you want for the first row. If A1 says “Emma Johnson” and you want just the first name, type
Emmain B1. - Press Enter to confirm and move to B2.
- Press Ctrl+E.
Excel reads your example, figures out the pattern, and fills the rest of column B automatically. The whole operation takes about five seconds.
💡 PRO TIP
Sometimes Flash Fill shows a grey preview of the filled column automatically before you even press Ctrl+E — as you start typing in B2, Excel guesses what you are doing. If the preview looks right, just press Enter to accept it. If it does not appear or looks wrong, finish your example and press Ctrl+E manually. You can also find Flash Fill under the Data tab → Data Tools group on the ribbon.
What Can Flash Fill Do? Real-World Examples
Flash Fill is more versatile than most people expect. Here are the three most practical use cases.
Splitting Full Names Into First and Last Name
This is the classic Flash Fill use case — and it comes up constantly in real work. You have a CRM export with a single “Full Name” column and you need first and last names split into separate columns.
For first names: in B1 next to your full names in A1, type the first name from the first row. Press Ctrl+E. Flash Fill extracts every first name down the column.
For last names: go to C1, type the last name from A1. Press Ctrl+E. Flash Fill gets all the last names. Two operations, about twenty seconds, regardless of whether you have 20 rows or 2,000.
Reformatting Dates and Codes
You’ve probably seen this: a list of dates formatted as 01012025, or phone numbers written without spaces when they need consistent formatting. Type the first entry in your preferred format in the adjacent column. Press Ctrl+E. Flash Fill reads the transformation you applied and applies it uniformly to every other row.
Works for product codes, reference numbers, postal codes, ID numbers — any situation where you need to impose a consistent format on data that currently has none.
Changing Text Case and Combining Columns
Flash Fill can change text case without a formula. Type the first entry in the capitalisation style you want — all caps, title case, lowercase — and press Ctrl+E. It applies the same style throughout the column.
It can also combine columns. If column A has first names and column B has last names, click into C1, type the first full name combining both (e.g. Emma Johnson), and press Ctrl+E. Flash Fill reads both source columns and combines them for every row automatically.
AutoFill and Flash Fill in Excel — Side-by-Side Comparison
The simplest decision rule: if you are continuing something, use AutoFill. If you are changing the shape of something, use Flash Fill.
| Use AutoFill when you want to… | Use Flash Fill when you want to… |
|---|---|
| Continue a number sequence (1, 2, 3…) | Extract part of a text string (first name from full name) |
| Fill days, months, or dates forward | Reformat data into a consistent style (dates, codes, phone numbers) |
| Repeat a value or formula down a column | Combine data from two columns into one |
| Continue a mixed text-number pattern (Invoice 1, Invoice 2…) | Change text case without using a formula |
When Flash Fill Doesn’t Work (And How to Fix It)
Flash Fill is powerful but not infallible. Here is what to do when it lets you down.
⚠️ COMMON MISTAKE — Pressing Ctrl+E Before Typing an Example
Flash Fill has nothing to learn from if you have not given it at least one manually typed result. Always type the first entry yourself, press Enter, and then press Ctrl+E. Skipping this step is the single most common reason Flash Fill appears to “not work.”
Flash Fill Troubleshooting Guide
Pattern not clear from one example
Fix: Type a second or third example manually in the rows below your first entry, then press Ctrl+E again. More examples give Flash Fill more context.
Flash Fill is filling the wrong thing
Fix: Press Ctrl+Z to undo, check you are in the correct column and starting row, retype your example carefully, and press Ctrl+E again.
Flash Fill appears to be turned off
Fix: Go to File → Options → Advanced. Under “Editing options,” make sure “Automatically Flash Fill” is ticked.
No adjacent column with data
Fix: Make sure your source data and your Flash Fill column are directly next to each other — no empty columns between them.
Practice Exercise — Try It Yourself
Here is a short exercise that lets you practice both features on the same dataset.
🧪 TRY IT YOURSELF
Set up column A with this data:
| Cell | Column A — Full Name |
|---|---|
| A1 | Full Name |
| A2 | Emma Johnson |
| A3 | Liam Williams |
| A4 | Olivia Brown |
| A5 | Noah Davis |
| A6 | Ava Wilson |
Complete these four tasks:
- AutoFill — number sequence: Type
101in C1 and102in C2. Select both. Drag the fill handle down to C6. Expected: 101, 102, 103, 104, 105, 106. - AutoFill — days of the week: Type
Mondayin D1. Drag the fill handle down to D5. Expected: Monday, Tuesday, Wednesday, Thursday, Friday. - Flash Fill — first names: Type
First Namein B1. TypeEmmain B2. Press Enter. Press Ctrl+E. Expected: Emma, Liam, Olivia, Noah, Ava. - Flash Fill — last names: Type
Last Namein E1. TypeJohnsonin E2. Press Enter. Press Ctrl+E. Expected: Johnson, Williams, Brown, Davis, Wilson.
All four correct? You’ve used AutoFill and Flash Fill in Excel back-to-back on real data. That is the whole lesson in four tasks.
Frequently Asked Questions
What is the difference between AutoFill and Flash Fill?
AutoFill continues a sequence you have already started — numbers, dates, days, months. Flash Fill transforms data based on a single example you provide. AutoFill is for extending series. Flash Fill is for reformatting or reshaping data. The names sound similar, but the jobs they do are quite different.
Can AutoFill fill formulas, not just values?
Yes. If you have a formula in a cell, drag the fill handle (or press Ctrl+D) and Excel copies the formula into every filled cell, adjusting cell references automatically for each row. For a refresher on how that works, see Lesson 5: Basic Formulas in Excel.
Does Flash Fill work with numbers, not just text?
Yes — Flash Fill works on numbers too, including reformatting numerical strings and standardising formats. For purely mathematical operations, formulas tend to be the more reliable choice.
Can I create my own custom AutoFill sequence?
You can. Go to File → Options → Advanced, scroll to the General section, and click “Edit Custom Lists.” Add your own sequence — your company’s product lines, regional offices, team names — and AutoFill will cycle through it just like built-in lists.
Further Reading
📚 Authoritative Excel Resources
Microsoft’s official documentation covering AutoFill, fill series options, and every control available in the fill handle dropdown — the definitive reference for this feature.
A detailed Flash Fill reference page with advanced pattern examples that go well beyond what this beginner lesson covers — excellent next reading once the basics are locked in.
What to Learn Next
You now have both features working for you — AutoFill for sequences, Flash Fill for transformations, keyboard shortcuts for speed, and a clear sense of which one to reach for in any situation.
If you are filling columns of formulas and getting unexpected results, the cause is almost always cell references behaving differently than you expected. Head back to Lesson 5: Basic Formulas in Excel for a refresher on relative references before combining them with fills.
When you are ready to move forward, the next step is learning to sort and filter your data. Once you have a clean, consistently filled dataset, you will want to slice and search it — and that is exactly what Lesson 7 covers. The more you use AutoFill and Flash Fill in Excel, the more natural they become. Until one day you realise you have not typed a sequence by hand in months.