Quick answer: If the small square at the corner of the selection is missing or dragging does nothing, go to File > Options > Advanced and, under Editing options, select Enable fill handle and cell drag-and-drop. Microsoft says this is on by default, so a cleared box is worth checking first. If Flash Fill is the problem, make sure Automatically Flash Fill is checked on the same Advanced page, restart Excel, or run it yourself with Ctrl + E.
In this guide
Applies to: Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016 on Windows, the versions Microsoft lists for its fill handle article. Microsoft’s Flash Fill article lists Microsoft 365, Excel 2024 and Excel 2021.
Which fill feature is failing?
As general guidance, “AutoFill” may refer to three separate features in Excel, each with its own setting. The fill handle is the square you drag to copy a value or extend a series. Flash Fill fills a column when it senses a pattern in what you type, according to Microsoft. AutoComplete may offer to finish a text entry based on what is already in the column. Pick the row closest to what you see.
| What you see | Possible cause | Go to |
|---|---|---|
| No square at the corner of the selection, or dragging does nothing | Fill handle and drag-and-drop turned off | Fix 1 |
| Dragging copies 1, 1, 1 instead of 1, 2, 3 | Excel copied the cell instead of filling a series | Fix 2 |
| Formula copied down, but every row shows the first result | Calculation may be set to manual | Fix 3 |
| Flash Fill fills nothing, even with Ctrl+E | Automatically Flash Fill turned off, or no pattern found | Fix 4 |
| Excel no longer offers to finish text you type | AutoComplete turned off, or the entry is a number or date | Fix 5 |
Fix 1: Turn the fill handle back on
Microsoft’s article Display or hide the fill handle states that the fill handle and drag-and-drop editing are turned on by default. The same switch also controls moving cells by dragging their border and copying them by holding Ctrl while dragging, so if those stopped too, this setting is the place to look.
- Select File > Options. As general guidance, on a narrow window you may need to select More first to see Options.
- Select the Advanced category.
- Under Editing options, select the Enable fill handle and cell drag-and-drop check box.
- Select OK and try dragging the fill handle again.
Microsoft also recommends keeping Alert before overwriting cells selected, so Excel warns you before a drag replaces existing data. Microsoft adds that you can clear the check box if you don’t want the message about overwriting non-blank cells.
As general guidance, if the box is already selected and the handle still won’t appear, check whether you selected a whole column, several separate ranges or an object rather than cells. Microsoft’s series article, written for Excel for Mac, notes that you can drag the fill handle down a single column or across a single row, not both at once.
Fix 2: Get a series instead of copies
The fill handle may work but fill the wrong thing, such as the same number in every cell. Microsoft’s series article, written for Excel for Mac, describes an Auto Fill Options button that appears after you drag. Its choices include Copy Cells, Fill Series, Fill Formatting Only and Fill Without Formatting, plus day, weekday, month and year options for dates. As general guidance, the Windows version shows the same button at the end of the filled range.
- Drag the fill handle as usual. These steps follow Microsoft’s Mac article, so labels may differ slightly in Excel for Windows.
- Select the Auto Fill Options button when it appears.
- Choose Fill Series to count up, or Copy Cells to repeat the value.
To set the step yourself, the same Microsoft article suggests typing the first two values, for example 2 and 4, selecting both and then dragging. Excel then continues the pattern as 6, 8 and so on. As general guidance, holding Ctrl while dragging a single number may switch between copying and counting, which can explain why the same drag behaves differently on two attempts.
If the Auto Fill Options button never appears, it may have been hidden. The Mac article turns it off with Show Insert Options smart buttons. As general guidance, on Windows a similarly named option may sit under Cut, copy, and paste on the Advanced page; select it again to bring the button back.
Fix 3: Filled formulas all show the same result
When you drag a formula down and every row shows the value from the first row, the fill itself may have worked. As general guidance, this pattern may mean the workbook is set to manual calculation, so the new copies have not been calculated yet. Pressing F9 may recalculate them; if the numbers change, go to Formulas > Calculation Options and choose Automatic.
Also check that the formula uses relative references where it should. An absolute reference such as $A$2 may stay fixed while you fill, so every row may point at the same cell by design. Our guide to Excel formulas not calculating covers manual mode, text-formatted cells and other reasons formulas don’t update.
Fix 4: Flash Fill not working
Microsoft’s Enable Flash Fill in Excel article notes that on a Windows device you might need to turn the feature on before you can use it.
- Go to File > Options.
- Open the Advanced pane and make sure Automatically Flash Fill is checked.
- Select OK.
- Close and restart Excel.
If Flash Fill is enabled but still isn’t filling, Microsoft says you can start it by hand from Data > Flash Fill or with Ctrl + E. As general guidance, it may help to type one or two examples of the result you want in the column next to your data and select the next empty cell below them before you run it.
As general guidance, Flash Fill may fail to find a pattern when the examples are inconsistent, when there is an empty column between your data and the target column, or when the target column already holds other values. Adding a second or third example may give it enough to work with. As general guidance, Flash Fill writes plain values rather than formulas, so the results may not update if the source data changes later.
Fix 5: AutoComplete stops suggesting entries
As general guidance, AutoComplete finishes a word as you type, based on entries already in the same column. Microsoft’s series article states that Excel automatically completes only entries that contain text or a combination of text and numbers. So numbers, dates and times on their own may not trigger a suggestion.
As general guidance, AutoComplete on Windows is controlled by an option named Enable AutoComplete for cell values under File > Options > Advanced, in the Editing options section. Select it and select OK.
As general guidance, suggestions may also stop when there is a blank row between the cell you’re typing in and the rest of the list, because Excel may only look at the connected block of entries above. Two existing entries that start with the same letters may also hold back the suggestion until you type enough to tell them apart. If you want a fixed list of choices rather than suggestions, a data validation drop-down may suit you better.
Still stuck? Other checks
- Filtered rows: as general guidance, filling across a filtered range may skip or include hidden rows in ways you don’t expect. Clear the filter before filling, or see Excel filter not working if the filter itself is misbehaving.
- Protected sheet: as general guidance, a protected sheet may block filling into locked cells. You may find Unprotect Sheet on the Review tab if the command is available.
- Moving data instead: if you were dragging to move rows rather than fill them, the same setting from Fix 1 applies. Our guide on how to move a row in Excel walks through it.
- Keyboard alternative: as general guidance, Ctrl + D fills the selection down from the top cell and Ctrl + R fills right, which may help when dragging is awkward on a long range.
- Excel freezes during a fill: filling formulas down a very large range may slow Excel down. If it hangs, see Excel not responding.
For more ways to save time once filling works again, see our Excel tips and tricks.
Related guides
- Excel formulas not calculating or updating: how to fix it
- Excel filter not working: how to fix it on Windows 11
- How to move a row in Excel (without overwriting data)
- Excel not responding: how to fix freezes and hangs
- Excel tips and tricks: 10 ways to work faster in Excel
Leave a Reply