Fix Excel Autofill and Sorting Not Working in Microsoft 365 Desktop App
Question details
The user is experiencing issues with Excel's Autofill, custom lists, and sorting features failing to work correctly in the desktop version.
- Product
- Microsoft Excel (Microsoft 365 Desktop App)
- Device & OS
- not provided
- Scenario
- Trying to use Autofill, fill options, custom lists, or sorting on data within an Excel workbook.
- Observed behavior
- Autofill fails to fill series correctly and defaults to copying cells. Fill options and the Date Unit section may appear unavailable or disabled in the desktop application, even though they work in the web version.
Before proceeding with system repairs, open a brand new, blank workbook to test the autofill and sorting functions. This helps determine if the glitch is isolated to your current file or affecting the entire Excel application.
Change AutoFill Options to Fill Series
Use this solution if dragging the fill handle duplicates your data instead of continuing the sequence.
Often, Excel defaults the drag-and-drop behavior to 'Copy Cells' instead of recognizing the pattern you want to create. Manually adjusting the fill options will force Excel to increment the data series.
Highlight the cell or cells containing the initial values of the series you want to extend.
Click and hold the small green square at the bottom-right corner of the selection, then drag it across the cells you want to fill.
Release the mouse button. A small 'AutoFill Options' icon will appear near your filled data. Click on this icon to open the dropdown menu.
From the dropdown menu, choose 'Fill Series' instead of 'Copy Cells'. Your data should immediately update to reflect the sequential pattern.
Perform an Online Repair for Microsoft Office
Use this solution if the AutoFill, fill options, and sorting features are completely disabled or broken across all workbooks.
Enjoy Flawless Data Management with WPS Spreadsheet
If Microsoft Excel continues to experience bugs with autofill or sorting, consider using WPS Office. It provides a lightweight, free Spreadsheet tool that seamlessly handles autofill, custom lists, and data sorting without unexpected glitches.
- 1. Download and Install WPS Office: Download WPS Office Free from the official website and install it on your computer. Setup takes only a few minutes.
- 2. Open Your Excel Workbook: Launch WPS Spreadsheet, go to 'Menu' > 'Open', and select your existing .xlsx file to continue working without formatting loss.
- 3. Use AutoFill Seamlessly: Enter your starting data, click and drag the fill handle at the bottom right of the cell, and use the AutoFill Options icon to choose 'Fill Series' perfectly every time.
- 4. Sort Your Data Flawlessly: Highlight your data range, navigate to the 'Data' tab on the top ribbon, and click 'Sort' to organize your information without any disabled buttons.
Frequently Asked Questions
Why does Excel copy cells instead of filling a series?
This usually happens because Excel's predictive AutoFill defaults to 'Copy Cells' when it doesn't immediately recognize a clear pattern. You can fix this by dragging the fill handle, clicking the small AutoFill Options icon that appears, and selecting 'Fill Series' manually.
How do I enable the fill handle and drag-and-drop in Excel?
If the fill handle square is completely missing, go to File > Options > Advanced. Under the 'Editing options' section, ensure the checkbox for 'Enable fill handle and cell drag-and-drop' is checked, then click OK.
Does the web version of Excel have the same autofill bugs as the desktop app?
Generally, no. Users often find that the web version of Microsoft 365 handles fill options and Date Unit sections correctly even when the desktop application experiences local glitches. Using Excel for the Web can serve as a reliable temporary workaround.
What does an Online Repair do for Microsoft Office?
An Online Repair downloads and replaces all corrupted or missing Office application files that might cause features like autofill and sorting to malfunction. It requires an active internet connection and takes longer than a Quick Repair, but is highly effective at resolving deep-rooted software glitches.
