Sometimes the AutoFill feature in excel does not work to drag and increase numbers. This article shows how to fix the problem of drag number increase not working in excel. The following picture highlights the purpose of this article.
Download Practice Workbook
You can download the practice workbook from the download button below.
Step-by-Step Solution: Drag Number Increase Not Working in Excel
Imagine you have the following list of names. You have entered an ID in cell C5. Now you want to drag that value to increase and create consecutive ID numbers.
Now execute the following steps.
- First, select cell C5 and put the cursor at the bottom right corner of the cell. Then you should see a plus (+) sign as shown in the following picture. After that, you can drag and increase the value to get the desired result.
- But, if you see a heavy plus sign as shown below, then you won’t be able to do that.
- To fix this problem, press ALT+F+T (on Windows) or Opt+Comma(,) (on Mac) to open the Excel Options dialog box. You can also open it from File >> Options.
- After that, go to the Formulas Then, select Automatic for Workbook Calculation under Calculation options. Next, go to the Advanced tab.
- Then check Enable fill-handle and cell-drag and drop from the Advanced tab. After that, hit the OK button.
- Now try to drag and you will see a preview of the increased value as shown in the following picture.
- After you drag it all the way through, you will see the following result.
- Now suppose you have entered 1 in cell C5. You want to drag it to increase and fill the cells below.
- But, when you try to drag and increase, you may see the number repeating instead of increasing as shown below.
- The final result will look like the following which is not desired at all.
- To fix this problem, hold CTRL. After that, you will see an extra tiny plus sign (+) as in the following figure.
- Now try to drag and increase the number. This time it will work just fine.
- Then, drag it all the way through. After that, you will get the desired result as follows.
- Now assume you want to get consecutive odd numbers to create the IDs. Then you need to enter the first two numbers of the series in two adjacent cells. Here, I have entered 1and 3 in cells C5 and C6.
- Now, select cell C6 and try to drag and increase the numbers to get the desired series. You will find it not working as desired.
- The number in cell C6 will get repeated as follows.
- So, try to hold CTRL and then drag to increase the numbers. You will see this process not working either.
- Instead, the series will end up as the following one.
- Then, how do you fix this? Well, you need to select both of the numbers first.
- After that, try to drag and increase them. This time it will work perfectly.
- As a result, you will end up with the desired output as follows.
- If you hold CTRL ad drag, then you will get the following result instead.
- You should keep in mind though that drag and increase don’t work with filtered data.
- You must remove filters from your data to be able to drag and increase numbers. It is possible to do this in a few ways. Firstly, select the tiny Filter icon in cell B4 and choose Clear Filters From “Names” and hit the OK button. Or, you can select Sort & Filter >> Filter from the Home tab. Besides clicking on the Filter icon from the Data tab gives the same result. Alternatively, you can use CTRL+SHIFT+L as a keyboard shortcut. But the last three ways completely remove all filters from the worksheet.
- After that, hold CTRL and drag the number. It will increase as shown below.
- How to Fill Down to Last Row with Data in Excel (3 Quick Methods)
- [Fixed!] Excel VLOOKUP Drag Down Not Working (11 Possible Solutions)
- [Solved]: Fill Handle Not Working in Excel (5 Simple Solutions)
Things to Remember
- Always try to hold CTRL and then drag if just dragging doesn’t work.
- The same process works perfectly if you want to drag horizontally and increase numbers.
- Drag and increase never work with filtered data. So always clear filters within your data first before trying to drag and increase numbers.
Now you know how to fix if drag and increase are not working in excel. Hope this helps with your problem. If you have further queries or suggestions, please use the comment section below. You can also visit our ExcelDemy blog to learn more about excel. Stay with us and keep learning.
- How to Drag Formula Horizontally with Vertical Reference in Excel
- How to Drag Formula and Ignore Hidden Cells in Excel (2 Examples)
- How to Use Fill Handle to Copy Formula in Excel (2 Useful Examples)
- Fill Down to Next Value in Excel (5 Easy Methods)
- How to Fill Formula Down to Specific Row in Excel (7 Easy Methods)
- How to Drag Formula in Excel with Keyboard (7 Easy Methods)