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

Sometimes the AutoFill feature in Excel does not work to autoincrement numbers when dragging through rows or columns. Here’s an overview of the problem and how it should work.

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. You want to drag that value to increase and create consecutive ID numbers.

Steps:

• Select cell C5 and put the cursor at the bottom right corner of the cell. You should see a plus (+) sign.
• If the value is a number, you can click and drag the plus icon to auto-increment it for subsequent cells.

• But, if you see a thicker plus sign as shown below, 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 and then go to Options.
• Go to Formulas.
• Under Calculation options, select Automatic for Workbook Calculation.

• Go to the Advanced tab.
• Check Enable fill-handle and cell-drag and drop.
• Hit the OK button.

• When you try to drag the plus icon, 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.

• Suppose you have entered 1 in cell C5. You want to drag it to increase and fill the cells below.

• When you try to drag and increase, you may see the number repeating instead of increasing as shown below.

• The result would be a line of repeating numbers, which might not be ideal.

• To fix this problem, hold Ctrl when hovering over the bottom-right corner of the cell. You will see an additional tiny plus sign (+) as in the following image.

• Drag and increase the number. This time it will work just fine.

• Here’s the result.

• Let’s use consecutive odd numbers to create the IDs. Enter the first two numbers of the series in two adjacent cells. We have entered 1 and 3 in cells C5 and C6.

• If you select only cell C6 and try to drag and increase the numbers to get the desired series, it won’t work.

• The number in cell C6 will be repeated as follows.

• If you use the same solution as before, it won’t work either as it increases by 1 rather than 2.

• The series would end up as the following one.

• To fix this, select both starting values.

• Go to the bottom-right corner of the selection and drag down.

• Here’s the result.

• If you hold Ctrl and drag, you will get a repeating pattern.

• Note that this won’t work with filtered data.

• To remove filters from your data, select the tiny Filter icon in cell B4 and choose Clear Filters From “Names” and hit the OK button. Alternatively, you can select Sort & Filter >> Filter from the Home tab or use the keyboard shortcut Ctrl + Shift + L. The alternatives will completely remove filters from the sheet.

• With filters removed, you can drag the Fill Handle to auto-increment.

Read More: [Fix] Excel Fill Series Not Working

Things to Remember

• Try to hold Ctrl and then drag if just dragging doesn’t work.
• Dragging horizontally works the same as dragging vertically.
• Drag and increase never works with filtered data, so clear filters before trying to drag and increase numbers using autofill.

Download the Practice Workbook

Related Articles

• 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

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