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.
Drag Number Increase Not Working in Excel: A Solution with Easy Steps
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 1 and 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 be 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 and 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 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 the CTRL and drag the number. It will increase as shown below.
Read More: [Fix] Excel Fill Series Not Working
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 using autofill.
Download Practice Workbook
You can download the practice workbook from the download button below.
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. Stay with us and keep learning.
- How to Autofill Numbers in Excel Without Dragging
- How to Auto Number or Renumber after Filter in Excel
- How to AutoFill Numbers in Excel with Filter
- How to AutoFill Ascending Numbers in Excel
- How to Auto Number Cells in Excel