Aniruddah Alam
The Auto Fill Feature is a very handy tool when it comes to automatically detect a pattern or series and filling the rest of the cells in a column or row maintaining the pattern or series. But sometimes it can happen that Auto Fill options not showing in Excel. In this article, I will show you an easy solution to this problem with very straightforward steps.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Step-by-Step Procedures to Solve If Auto Fill Options Are Not Showing in Excel
In this section, we will try to fix the issue [the Auto Fill option not showing in Excel. But before looking into the solutions, let’s see what the problem looks like. In excel, when you use the Fill Handle, you are supposed to see the Autofill option just below the filled-up cells. (see the example below)
Here, we can see the Auto Fill option that has many options such as Copy Cells, Fill Series, Fill Formatting Only, Fill Without Formatting, and Flash Fill. But, sometimes you may find that this Auto Fill option is not showing. (like the illustration below)
This time, we can only see the Quick Analysis Tool, but the Auto Fill option has disappeared. Now, to make this vanished Auto Fill Option appear again, we can follow the easy 4 steps below.
Step 01: Go to File Tab
- From the ribbon, click on the File tab.
Step 02: Click on Options
- From the appeared menu on the left side down of the screen, click on Options.
Read More: [Solved]: If Macro Settings Is Greyed out in Excel
Step 03: Choose Advanced Option
- By clicking on Options, a dialogue box should appear.
- From the menu, click on the Advanced option.
Read More: [Solved!]: Excel Page Layout Being Greyed Out (4 Quick Fixes)
Similar Readings
- [Fixed!] Excel Scrolling Too Many Rows (2 Easy Solutions)
- Excel Data Validation Greyed Out (4 Reasons with Solutions)
- [Fixed] Excel COUNT Function Not Working
Step 04: Enable Show Paste Options Button When Content Is Pasted
- On the Advanced option, scroll down below until you see this option.
- Now, check the ‘Show Paste Options button when content is pasted‘ option and click OK.
Hopefully, it will solve your problem.
Read More: [Fixed!] Merge Cells Button Is Greyed Out in Excel
How to Solve If AutoFill of Series Is Not Working While Dragging Only Last Cell
Like the example given above, sometimes, we may have data only on 2 cells in a column and we want to fill up the rest of the cells with Autofill. See the example below.
But we want to Autofill the remaining cells so that rest of the cells are filled by 15, 20, 25, and so on. But if you try to drag the Fill Handle by selecting only B6, you will get the following result.
You will only get 10 in all those cells. To get a proper arithmetic series, we have to select the range of cells available in the column first. In our example, they are B5 and B6. After selecting the cells, we have to use the Fill Handle option from Cell B6 to drag down to the last Cell B10.
Thus we will be able to get the proper series (5,10,15,20….)
Things to Remember
- You can always use the Fill Option on the ribbon under the Editing group in the Home tab to manually do the task done by Auto Fill
Conclusion
That is the end of this article. If you find this article helpful to solve the problem when the Auto Fill options not showing in Excel. then please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exeldemy for more exciting articles on Excel.
