Drag Number Increase Not Working in Excel (A Solution with Easy Steps)

Get FREE Advanced Excel Exercises with Solutions!

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.

Excel Drag Number Increase Not Working [Fixed]


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.

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.

Excel Drag Number Increase Not Working

  • 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.

Excel Drag Number Increase Not Working [Fixed]

  • 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.

Excel Drag Number Increase Not Working

  • 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.

Excel Drag Number Increase Not Working [Fixed]

  • 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.

Excel Drag Number Increase Not Working

  • 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.

Excel Drag Number Increase Not Working [Fixed]

  • 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.

Excel Drag Number Increase Not Working

  • 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.

Excel Drag Number Increase Not Working [Fixed]

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.


Conclusion

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.


Related Articles


<< Go Back to Excel Autofill not WorkingExcel Autofill | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

10 Comments
  1. you should have started with, remove the filter if selected.

  2. It doesn’t work on my side following your nice explanations. If I have 1, 2, 3, and I try to get 4, 5, 6, I select the 3 cells, or 2, press Ctrl, drag down, and get 1, 2, 3 over and over again.
    I have also activated the Options/ Advanced/ blabla…
    How this could be possible? The second small + comes out with Ctrl, but it is not working.

    • Greetings EDUARDO,
      Yes, you have followed the procedures perfectly. But the thing is that you don’t need to press Ctrl to increase the number, as the numbers are already in an arithmetic progression. So, when you press Ctrl and drag down, you experience something like this:

      So, just drag down the AutoFill tool.

      Please give us feedback if you have any further queries.

      Best Regards,
      Bhubon Costa, ExcelDemy

      • Hey! thanks for the answer. If I don’t press Ctrl, there is no additional little plus on top of the big plus. And what happens is the following: I write in 3 cells, 1, 2, 3, select the 3 cells, drag down and get 1, 2, 3 all over again and again.

        • Greetings EDUARDO,

          Thanks for reaching out again. We are not facing the same issue from our side. But we are providing you with a possible solution to get rid of the issue. There could be problems with versions or with settings. We are using Microsoft 365.

          1. First, check that the Automatic option is enabled.

          2. Check that the Advanced tab options are the same as yours.

          3. Now, if you still face the issue, you can change the Copy Cells command to Fill Series in the AutoFill options.

          Please give us feedback if you have any further queries.

          Best Regards,
          Bhubon Costa, ExcelDemy

  3. Thanks Bhubon, all of these is already in place. I don’t know how to paste pics here to show you, but Automatic calculation is ok, and Drag and drop is already selected in Advanced.
    And still.
    1, 2, 3, select, drag down (no extra small +) and I get 1, 2, 3 all over again.
    If pressing Ctrl, I get the small extra +, but still, 1, 2, 3 again and again.

    I have Microsoft 365 too.

  4. Thanks Shamima, done!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo