Hello friends. I am back with another interesting lesson. How to make auto numbering formulas in Excel. I named that formulas auto numbering because it helps to create automatic serial numbers. Now I am showing you these formulas with an example sheet.
In the above picture I have some values in B column. These are some text values, numerical values and there are some blank cells. I want to make an auto numbering formula.
Formula to skip blank cells when numbering
So I have used here the formula =IF(ISBLANK(B2),””,COUNT(B$2:B2)) in A2 cell and dragged it up to bottom. This formula skips any blank cells when numbering.
Formula to auto number numerical values only
Now I have made a formula which will put number beside cells where numerical values are present. For that I have used the formula =IF(ISNUMBER(B2),COUNT(B$2:B2),””) in A2 cell. Then I have dragged the formula up to bottom.
Formula to auto number text values only
Auto numbering text values needs a little different type formulas.
Now I have made a formula which will put number beside cells where text values are present. For that I have used the formula =IF(ISTEXT(B2),COUNTA(B$2:B2)-COUNT(B$2:B2),””) in A2 cell. Then I have dragged the formula up to bottom.
So you can create more interesting auto numbering formulas with the combination of Information Functions to make serial number for particular type of values in a column.
If you want to make an auto numbering formula for filtered value the read this post.