Type the starting values in one or more cells, such as 1 and 2 for a counting series, select them, and drag the fill handle across the cells to fill. Click Auto Fill Options afterwards if you need to change what was filled. For text patterns like names, Flash Fill (Ctrl+E) can do the filling.
- A counting series: give Excel two numbers to show the step, then drag.
- Steps of two: start with 2 and 4, then drag.
- One value repeated: a single cell is enough, then drag.
- A pattern in text: that's Flash Fill territory, covered further down.
Autofill is the tool that saves you typing the same pattern a hundred times. Microsoft describes it as filling cells with data that follows a pattern or is based on data in other cells, and its page on how to fill data automatically in worksheet cells is where the steps below come from.
The fill handle method, step by step
- Select one or more cells to use as the basis for filling.
- For a series, enter the first two values of the pattern. A count of 1, 2, 3, 4, 5 starts with a 1 and then a 2 in neighbouring cells.
- For a single repeated value, type it in the first cell only.
- Drag the fill handle across the cells you want to fill.
- If needed, click Auto Fill Options and choose the option you want.
Nothing about this needs a special setup, which is what makes it such a time saver on long lists of dates, codes or quantities. The reason a series needs two cells is that Excel works out the step from the pair. Give it 2 and 4 and it carries on with 6 and 8. Give it just a 2 and it assumes you want 2 repeated, which is useful in its own right when you need the same label or code down a whole column. It's a small rule, but it explains most of the times a fill comes out as a row of identical values.
Flash Fill: when the pattern is in the text
Some fills aren't a count at all. Splitting first and last names into two columns, or joining them together, is the classic example. That's what Flash Fill is for, and Microsoft explains it in its guide to using Flash Fill in Excel.
- Type the first result you want and press ENTER.
- Start typing the next one. Excel shows a preview of the rest.
- Press ENTER to accept the preview.
- If no preview appears, go to Data > Flash Fill, or press Ctrl+E to run it manually.
Fill handle or Flash Fill: picking the right one
Can you describe the job as a count or a repeat? Then the fill handle is quick and keeps you in charge. If you're pulling a first name out of a full name, Flash Fill suits better, because nothing about that job is a count.
If you pick the wrong one, nothing is lost. Undo it and try the other route, and use Auto Fill Options to adjust anything the fill handle got wrong.
Mistakes that stop autofill working
- Typing only one cell for a counting series. Excel needs two cells to see the step, though a single cell is enough to repeat a value.
- Skipping the Auto Fill Options button. If Excel filled something different to what you wanted, that button is where you change it.
- Giving Flash Fill nothing to learn from. It needs one finished example before it can guess the rest.
- Assuming a fill will reach other worksheets. That isn't something Microsoft's page covers, so check its help pages first.
Once your data is filled, you might want to tidy it. Our guides to wrapping text in Excel and the TEXT function are the usual next steps.
Frequently asked questions
How do I autofill numbers in Excel?
Give Excel the first two numbers, highlight both, then pull the fill handle across your target cells. The pair tells Excel what step to use.
Want AI doing the heavy lifting in your marketing?
I build the systems that handle the boring 80 percent, so you get your week back. Done properly, with the human kept in.
How do I copy the same value down a column?
One cell is all a repeat needs. Enter the value once, select it, and pull the handle along as far as you need.
Where do I change what autofill produced?
After a drag, use the Auto Fill Options button and pick the result you wanted instead.
Is there a keyboard route to Flash Fill?
Yes: Ctrl+E, or the Flash Fill button on the Data tab.
When is Flash Fill the better choice?
When the pattern lives in the text itself, like pulling surnames out of full names. A count or a repeat is the fill handle's job.
Does autofill work across worksheets?
Microsoft's page stays silent on that one, so look at its help pages before counting on it. Everything above deals with filling inside a single sheet.
