We all know the fill handle feature is the most useful tool in Microsoft Excel. If you are facing problems with the fill handle not working in Excel, then you are at the right place. I will share some easy solutions to fix the fill handle not working problem in this article. Stay tuned!
Fill Handle Not Working in Excel: 5 Easy Solutions
Fill Handle feature in excel is used to fill cells with numbers, dates, and even text too so that you don’t have to type the values several times. It appears with a cross symbol (+) after putting data in a cell. Then dragging in different sides the fill handle will fill the cells with proper values.
Sometimes the Fill Handle does not work as we want. Mostly it copies the cell, but it doesn’t show up in the selected cell. In this article, I will cover 5 easy solutions to solve fill handle not working in Excel.
1. Enable Fill Handle Option
While working with a dataset you may face that the fill handle is not visible to fill the cells. In that case, you will not be able to use it. For that, you have to enable the fill handle to drag formulas or values in Excel.
Problem:
In the following screenshot, you will see the fill handle is not enabled. Now I will enable the fill handle with the help of “Advanced Options”.
Solution:
To get the fill handle visible follow the steps below-
Step 1:
- Click the “File” option from your worksheet.
- Choose “Options” from the downside.
- A new window will appear with “Excel Options”.
- Choose “Advanced” and check to mark the “Enable fill handle and cell-drag-and-drop” to enjoy the fill handle feature.
- Press OK to continue.
Step 2:
- Now select cells from your dataset you will see the fill handle icon is visible.
- Drag down the Fill Handle to get the column filled with values.
- Well, here we got our sequence by getting the fill handle enabled from advanced options.
Read More: How to Enable Drag Formula in Excel
2. Select Proper Sequence of Data
Sometimes the fill handle won’t work if we don’t select the proper sequence from the dataset. This happens because Excel can’t capture the sequence which you want to fill.
Problem 1:
If you select only one cell from your workbook and drag it, Excel will copy the cell and fill the cells with the same value as the selected cells. You can drag cells in Excel using keyboard too. Go through the screenshot to learn about the problems.
- Here I have selected a cell (B6). And then dragged down to fill other cells.
- As you can see, we didn’t get the sequence we wanted. The column is filled with the selected value from the chosen cell.
Problem 2:
Sometimes you might select an extra blank cell and then drag it down to fill.
- From my workbook, I have chosen a few cells (B5:B7) and dragged them below to fill.
- Thus you will get a blank cell between the sequence which is not required.
Solution:
To avoid these types of problems follow my instructions below.
Steps:
- Choose cells (B5:B6) where the proper sequence appears.
- Drag the Fill Handle down.
- Simply, we will get our proper sequence in the column.
Read More: How to Use Fill Handle to Copy Formula in Excel
3. Check Calculation Option
The fill handle is used for different purposes. You can change the purpose of your filling from the “Calculation Options”.
Problems:
- Select cells (B5:B6) to fill the other cells.
- Drag down the “fill handle” icon to cover cells with data.
- You will see after dragging down the cells are filled with the same data from those two cells which we don’t want to see in our filled columns.
Solution:
Follow these steps below to solve this problem.
Steps:
- Go to Formulas > Calculation Options > Automatic.
- Now drag down the Fill Handle.
- Thus you will get the proper sequence in the filled column.
4. Use Keyboard Shortcut
This method is the quickest solution to solve the fill handle not working problem in Excel. Just by pressing the F4 key several times, you will get your desired result.
Steps:
- Choose cells from the workbook. Here I have chosen cells (B5:B6).
- Press F4 key 3 times.
- Now drag the Fill Handle down to get the results in the cells.
- You will get your output in the cells (B5:B13) as we dragged till cell (B13).
Read More: How to Drag Formula in Excel with Keyboard
5. Clear Filter from Data Table
In this method, I will describe a crucial method that you will face many times while working with a fill handle in a filtered data table.
Suppose we have a filtered table of a dataset of some employees, their salary, and bonus amount in a workbook. Now we will show you some proper steps so that you won’t face problems with the fill handle within a filtered table.
Problem:
- Let’s filter the employee names from the dataset.
- To filter, click the filter option and uncheck some names from the list.
- Press OK to continue.
- After filtering you will see the hidden rows.
- Now we will calculate the bonus amount with the following formula.
- Apply the formula in the cell (E5)-
=D5*0.05
- Hit the Enter button to get the result.
- Drag down the Fill Handle to get the output in all cells.
- Here you will see we have our calculated bonus amount in the column.
- Now let’s clear the filter by clicking the ‘Clear Filter From “Employee”.
- Click OK.
- You will see the formula did not work for the hidden rows which we filtered before. Though we learned fill handle won’t work for the hidden rows. Now we will solve this problem. Get ready for the solution.
Solution:
The solution to this problem is given below!
Steps:
- Without filtering the dataset we will apply the following formula in the selected cell. Here I have selected cell (E5) to apply the formula.
- Put the formula in the cell.
=D5*0.05
- Press Enter.
- Pull the Fill Handle down to get the result in all cells.
- Thus we will get our results in all cells.
- Now let’s filter the dataset by clicking the filter option from the employee column.
- Uncheck some names from the list and click OK to continue.
- Next, you will see some hidden rows in the table as we filtered the dataset.
- Now by clicking “Select All” we will get all the data in the table.
- This time we will get all our data in the table because we applied the formula before filtering the dataset.
Read More: How to Use Fill Handle in Excel
Things to Remember
- In method 4, after selecting cells we pressed the F4 keys 3 The reason is when we press the F4 keys the last action is repeated in those cells. The last action can be anything like filling data, changing shape, formatting, or deleting values. So when we press the F4 key it will repeat the last action. It will not always solve the Fill Handle issue.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Conclusion
In this article, I have tried to cover the simple and quickest steps to solve the share workbook option not showing in Excel. Take a tour of the practice workbook and download the file to practice by yourself. Hope you find it useful. Please inform us in the comment section about your experience. We are always responsive to your queries. Stay tuned and keep learning.
Related Articles
- Excel Fill Down to Next Value
- How to Fill Formula Down to Specific Row in Excel
- How to Drag Formula and Ignore Hidden Cells in Excel
- How to Drag Formula Horizontally with Vertical Reference in Excel
<< Go Back to Fill Handle in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
