How to autonumber rows if adjacent cell not blank in Excel?
In Excel, we can drag the fill handle to fill the sequence numbers in a column quickly and easily, but, sometimes, we need to number the rows based on adjacent cells. For example, if the cells contain value then number them, if there are blank cells, leave the sequence blank as well. How could you solve this job that when you insert or delete values in adjacent cells, the column can be renumbered automatically as following screenshot shown?
To solve this task, here, I will introduce a simple formula, please do as follows:
1. Enter this formula: =IF(B2<>"",COUNTA($B$2:B2),"") into a blank cell which adjacent your data cell, A2, for instance, and then drag the fill handle down to the cells that you want to apply this formula to auto number the column based on adjacent cells, see screenshot:
Note: In the above formula, B2 is the cell that you want to auto number based on.
2. And now, when entering value in the blank cells or deleting the existing data, the sequence numbers in column A will be renumbered automatically.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agoSehr praktische Funktion, allerdings sollte man auf einer deuschsprachigen Seite auch deutschsprachige Excel-Befehle benutzen. Außerdem ist in der Formel ein Fehler. So lautet sie richtig:
To post as a guest, your comment is unpublished.· 1 months agoAs usual, exactly what I was searching for. Thanks
To post as a guest, your comment is unpublished.· 3 months agoanyway to get skip "" with this formula? i have a list with "" hidden in the cell. thanks
To post as a guest, your comment is unpublished.· 7 months agoLol didn't know this formula existed and I did this =IF(B2<>"",(COUNTIF(B:B,"<>"&""))-(COUNTIF(B3:B501,"<>"&"")),"")