Part of the free Module 2: Fill, Auto Fill and Flash Fill · Lesson 2 of 6 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 unless noted.
AutoFill in Excel extends a pattern when you drag the small square fill handle at the bottom-right corner of a selection. Days, months, numbers, dates, quarters and text with trailing numbers all continue automatically, and a custom list teaches Excel your own sequences such as regions or team names. This lesson explains how AutoFill decides what to fill, what the Auto Fill Options button changes, and how to build a custom list.
How AutoFill decides what to fill
Select one or more cells and point at the fill handle until the cursor becomes a thin black cross, then drag down, up, left or right. Excel looks at the selection and applies a rule. A single text value is copied. A recognised list item such as Monday or Jan continues the list. A single number is copied unless you hold Ctrl or select two cells, in which case the difference between them becomes the step. A date increments by one day. Text ending in a number, such as Item 1 or Q1, increments the number. A formula is copied with its relative references adjusted. Double-clicking the handle fills down as far as the column beside it has data.
How to AutoFill a list of days
- Type Monday in the first cell and press Enter, then select that cell again.
- Drag the fill handle down as far as you need. A tooltip previews the value that will land in the last cell.
- Release the mouse. Tuesday, Wednesday and the rest appear, restarting at Monday after Sunday.
- Click the Auto Fill Options button that appears at the corner of the range to switch to Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting or, for days, Fill Weekdays.
What AutoFill produces from different starting values
The table shows what you type in the first one or two cells, what dragging the handle produces, and how to get the other result.
| Starting value(s) | Drag result | How to change it |
|---|---|---|
| North (plain text) | North, North, North | Only a custom list can make text continue |
| Monday | Tuesday, Wednesday, Thursday | Auto Fill Options > Fill Weekdays skips Sat and Sun |
| Jan or January | Feb, Mar, Apr | Auto Fill Options > Copy Cells to repeat |
| 5 (one number) | 5, 5, 5 | Hold Ctrl while dragging to get 6, 7, 8 |
| 1 and 2 (two cells) | 3, 4, 5 | Hold Ctrl to copy 1, 2, 1, 2 instead |
| 5 and 10 (two cells) | 15, 20, 25 | Change the two seed values to change the step |
| 01-Jan-2026 | 02-Jan, 03-Jan, 04-Jan | Auto Fill Options > Fill Weekdays, Months or Years |
| Q1 | Q2, Q3, Q4, Q1 | Cycles back to Q1 after Q4 |
| Item 1 | Item 2, Item 3, Item 4 | Hold Ctrl to copy Item 1 |
| 9:00 | 10:00, 11:00, 12:00 | Select 9:00 and 9:30 to step by 30 minutes |
=A2*2 |
=A3*2, =A4*2 |
Use $ to lock references, see chapter 5 |
| Ctrl+drag of 100 | 101, 102, 103 | Ctrl reverses the default behaviour |
| East (custom list entry) | West, North, South | Continues your own list in its stored order |
Drag the fill handle with the right mouse button for a menu with extra choices: Fill Days, Weekdays, Months, Years, Linear Trend, Growth Trend and Series. Linear Trend and Growth Trend extend two or more seed numbers along a best-fit line, which is a quick forecast for a column of sales figures.
How to create a custom list
Excel knows days and months, but not your product names, regions or team members. A custom list teaches AutoFill your own sequence and also appears in the Sort dialog under Custom Sort.
- Go to File > Options > Advanced, scroll to the General section and click Edit Custom Lists.
- In the Custom Lists dialog select NEW LIST, type the entries one per line in the List entries box and click Add. Alternatively import the entries from cells as shown next.
- To import, type the list in a worksheet column first, click the Import list from cells box, select the range and click Import.
- Click OK twice. Type any entry from the list in a cell and drag the fill handle; Excel continues your custom sequence from that entry.
To edit a list, open the same dialog, select it, change the entries and click Add; to remove it click Delete. Custom lists are stored in your Excel installation, not in the workbook, so a colleague must import the same list to use it.
Worked example: build a weekly roster
A five-person roster needs days across the top, staff names down the side and a serial number for each row. The staff names are stored as a custom list in the order Asha, Ben, Chen, Dana, Eli.
| A: No. | B: Staff | C | D | E | F | G |
|---|---|---|---|---|---|---|
| 1 | Asha | Mon | Tue | Wed | Thu | Fri |
| 2 | Ben | |||||
| 3 | Chen | |||||
| 4 | Dana | |||||
| 5 | Eli |
- Type Mon in C1 and drag the fill handle to G1. Excel fills Tue to Fri.
- Type Asha in B2 and drag to B6. The custom list supplies Ben, Chen, Dana and Eli.
- Type 1 in A2, hold Ctrl and drag to A6 to number the rows 1 to 5.
- Type Off in C2 and drag across to G2; plain text copies, so every day shows Off until you overwrite it.
Four drags built the frame of the roster. Because the list is stored in Excel, next week’s roster starts from any name and continues in the same order.
Tips and common mistakes
- Numbers copy instead of counting. Hold Ctrl while dragging a single number, or select two cells such as 1 and 2 to define the step.
- Dates jump by month. Use the Auto Fill Options button or right-drag and choose Fill Days, Weekdays, Months or Years.
- Double-click stops early. It fills to the first blank in the adjacent column. Fill the gap or use Ctrl+D on a selected range instead.
- Fill handle missing. Enable it under File > Options > Advanced > Enable fill handle and cell drag-and-drop.
- Custom lists travel with Excel, not the file. Keep the list in a sheet so anyone can import it.
- Formulas AutoFill too. Dragging a formula adjusts relative references; lock the parts that must not move with $ or press F4.
- Formatting comes along. Choose Fill Without Formatting from Auto Fill Options if the target cells already have their own borders or colours.
Errors and how to fix them
| Problem | Cause | Fix |
|---|---|---|
| Same number repeats down the column | A single number is copied by default | Ctrl+drag, or seed two cells and drag both |
| Dates increase by a month or a year | Auto Fill Options changed on an earlier drag | Click Auto Fill Options and choose Fill Days |
| No fill handle on the selection | Handle disabled in Options, or the sheet is protected | Enable it in Options > Advanced or unprotect the sheet |
| Double-click fills only part of the column | Blank cell in the neighbouring column | Select the full range and press Ctrl+D |
| Custom list does not work on another PC | Lists are stored per Excel installation | Import the list from cells on that PC |
| Text with a number increments when you wanted a copy | AutoFill treats a trailing number as a series | Hold Ctrl while dragging, or choose Copy Cells |
Practice exercise
- Type Jan in a cell and drag to build a 12-month header, then repeat with a right-drag and Fill Months on a date.
- Type 1 and 2, select both and drag to number 50 rows; then do the same with 5 and 10.
- Import your regions or departments as a custom list and fill them down a report.
- Type
=A2*2beside a column of numbers and double-click the fill handle; note where it stops if column A has a blank. - Enter 100 and 110 in two cells, right-drag ten rows further and choose Linear Trend.
Key takeaways
- AutoFill reads the selection and picks a rule: copy text, continue lists, step numbers, increment dates and adjust formulas.
- Ctrl reverses the default for numbers and text-with-numbers; two seed cells define the step.
- The Auto Fill Options button and right-drag menu switch between copy, series, weekdays, months, years and trends.
- Double-clicking the fill handle fills to the end of the adjacent data.
- Custom lists make AutoFill and Sort follow your own order, and they live in Excel rather than the workbook.
Related lessons
- Fill, AutoFill and Flash Fill course hub
- Fill Down, Up, Right, Left and Justify
- AutoFill formulas: relative and absolute references
- SEQUENCE function: the dynamic alternative
- Data sorting with custom lists
- Microsoft Support: Sort data using a custom list
Frequently asked questions
Why does AutoFill copy the same number instead of a sequence?
A single number is copied by default because Excel cannot tell what step you want. Hold Ctrl while dragging to count by one, or enter the first two numbers, select both and drag so Excel calculates the step from the difference. You can also click Auto Fill Options after dragging and choose Fill Series.
How do I AutoFill without the mouse?
Select the range starting with the seed cell and choose Home > Fill > Series with the AutoFill type, or press Alt+E+I+S. Ctrl+D copies the top cell down and Ctrl+R copies it to the right. Ctrl+E runs Flash Fill for text patterns. In Excel 365 the SEQUENCE function generates a numbered list with no dragging at all.
Can I edit or delete a custom list?
Yes. Open File > Options > Advanced > Edit Custom Lists, select the list, change the entries in the List entries box and click Add, or click Delete to remove it. The built-in day and month lists cannot be edited or deleted. Changes apply to your Excel installation, so other users must make the same change.
Why does double-clicking the fill handle stop before the last row?
Double-click fills down only as far as the adjacent column is continuous. The first blank cell beside your column ends the fill. Fill the gap, or select the whole range you need and press Ctrl+D, which does not depend on neighbouring data.
Does AutoFill work inside an Excel Table?
Yes, and Tables go further: a formula typed in one cell of a Table column becomes a calculated column and fills every row automatically, including rows added later. Dragging the handle in a Table works the same way as on a normal range for text, numbers and dates.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.