The Auto Fill Feature is a very handy tool when it comes to automatically detecting 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.
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 of 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 of the screen, click on Options.
Read more: How to AutoFill from List in Excel
Step 03: Choose the Advanced Option
- By clicking on Options, a dialogue box should appear.
- From the menu, click on the Advanced option.
Read More: How to Use Autofill Formula in Excel
Step 04: Enable the 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.
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 the 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 AutoFill.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
That is the end of this article. If you find this article helpful in solving 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.
- AutoFill Cell Based on Another Cell in Excel
- How to AutoFill Sequential Letters in Excel
- How to Turn Off AutoFill in Excel
- How to Create a Custom AutoFill List in Excel
- Fix: Excel Autofill Not Working
- [Solved:] Excel Double Click AutoFill Not Working