Auto number column does not start from 1

sahilhq · 06/10/20

Hi,

I have just added a new auto number column in my sheet and although I left the "fill" and "suffix" section blank, when I click on save the numbering in the column starts from "KX BOND 478" rather than "KX BOND 1". Can anyone please clarify why the auto numbering doesn't start from 1? If I am not able to fix this issue by making further updates to the column, is there any way you can suggest me with a formula such that row 1 is KX BOND 1, row 2 is KX BOND 2, row 3 is KX BOND 3, etc...

Paul Newcome · 06/10/20

Drag-filling should work. Make sure you have the $ to lock in the row 1 reference.

Paul Newcome · 06/10/20

There is a specific order that I can never quite remember. I believe it is...

Delete the column. Save the sheet. Refresh your browser then add the column and it should start back over at 1.

If you want to use a formula, you will need to add in another text/number column and use something along the lines of...

="KX-BOND" + COUNTIFS([Other Column]$1:[Other Column]@row, OR(@cell = "", @cell <> ""))

sahilhq · 06/10/20

@Paul Newcome Thanks for the response. Deleting the column, saving the sheet, etc. didn't quite work. I tried the formula and it worked for row 1. Now if I want row 2 to contain KX-BOND2 then do I just need to drag the formula down from the first row or do something else? I dragged it down but the cell in row is still showing as KX-BOND1

Paul Newcome · 06/10/20

Drag-filling should work. Make sure you have the $ to lock in the row 1 reference.

sahilhq · 06/10/20

Your solution worked as always. Thanks @Paul Newcome :)

Paul Newcome · 06/15/20

Happy to help! 👍️

NaomiS · 02/03/22

@Paul Newcome

I am having a similar issue! I need an auto-numbering column, but after deleting some of the column contents, the numbering will not start at "1". I have used the suggestion of deleting the column, saving, and then adding it back in/refreshing, but it is not working.

I used your formula above and I keep getting a circular reference error in the cell. It also removes the brackets from the cell reference for the range.