Unable to extend number sequence in column A (i.e., 1,2,3,4,...) when working in a table. Have done before but must have a formula or ? in cell.

Question

Good morning,

I'm not an Excel guru but this must be very simple. 

I have a list of about 2000 customers, listed in various states.  They are listed in Column A as "1, 2, 3, 4.."  I have converted to a table that allows me to extract the customers location by state (i.e., Washington, Oregon, etc.) 

When I try to renumber the customers as "1, 2, 3 .." by typing the new numbers in cells A1, A2, A3, etc., then high lighting those cells, click and drag down the small plus sign in the lower right corner of the three high lighted cells, then release, the number "1" is duplicated the whole way down.  If I high light A2, A3, A4, then then click and drag the small plus sign down, I get "2" duplicated all the way down.

I don't see a formula in the cell.  I have deleted column A, saved and closed, reopened and the same problem occurs.

I believe it's a simple fix but I'm not sure how to proceed.  Any suggestions ??

Thanks in advance for your time.

Cheers !

Mike K.

Answer 1

Hello,

maybe the Fill Handle is disabled in your Excel options. Click File > Options > Advanced. In the Editing Options section look for "Enable fill handle and cell drag-and-drop" and make sure it is checked.

Let me know if that helped.

Answer 2

Another thing to check is if you have any filters set on the data table. When not all rows are showing, due to filters, then the fill handle will not increment on just the visible rows, but repeat the value instead.

So, click the Data ribbon, click "Clear" in the Sort & Filter group and then try the fill handle again.

Answer 3

Hi Mike, 

Did you see the smart tag right after dragging down?

If you do, click on it, and select Fill Series

Answer 4

Hello Teylyn,

Thank you for your prompt replies.  Unfortunately, neither works.

1.  On your first suggestion, "clear" is in shadow, not allowing that operation.  In the same "Sort & Filter" group I have used the "eraser" to "Clear all" without success. 

2.  On your second, the box you suggest is checked.

Maybe a small clue, when I have A1,A2 and A3 cells filled with 1, 2 and 3 respectively, and I click>drag on the small plus sign, I get a small temp pop up box as I cross each cell.  For example, as above, 1, 2 and 3 are followed with "2.8714" in A4.  A5 reads "3.14285".  A6 reads "4".  A7 reads "4.571428".  And when I release the cursor, all cells return to "1".

Which tells me there is a formula in there somewhere.  Also, there is a little tiny pop up box that carries along as I drag downward, it's titled "quick analysis".  Unfortunately, it means nothing to me.

As I'm apt to tell my son, I'm an analog Dad in a digital world.

Let me know if this helps in your analysis.

Cheers !!!

Mike K.

Answer 5

All,

I decided on going to the local MS store in Bellevue WA.

The techs were stumped and decided I had a corrupted table.  Their solution was to copy and paste only the cells with the data, recopy the header line (customer name, address, contact, phone, etc....).  Add in a new column for the "Line", " 1, 2 , 3, ..".  Then high light the first couple of vertical cells, click and drag the plus sign down as far as needed.

So in the end, maybe not solving the issue as to why but providing a workable solution.

BTW, I also had tried using the snip it tool to copy and paste the picture to show the members what I was up against but it wouldn't paste into this thread.  Maybe just an OE issue.

Thanks for your input and support.

Cheers !

Mike K.