Lesson#133: Auto numbering formulas in Excel

Hello friends. I am back with another interesting lesson. How to make auto numbering formulas in Excel. I named that formula 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 the 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),””,COUNTA(B$2:B2)) in the A2 cell and dragged it up to the bottom. This formula skips any blank cells when numbering.

Formula to auto number numerical values only

Now I have made a formula that will put numbers beside cells where numerical values are present. For that I have used the formula =IF(ISNUMBER(B2),COUNT(B$2:B2),””) in the A2 cell. Then I dragged the formula up to the bottom.

Formula to auto number text values only

Auto numbering text values need a little different type of formula.

Now I have made a formula that will put numbers 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 the A2 cell. Then I dragged the formula up to the bottom.

So you can create more interesting auto numbering formulas with the combination of Information Functions to make serial numbers for a particular type of values in a column.

If you want to make an auto numbering formula for filtered value read this post.

Lesson#128: Formula to make serial no auto arranged when filtered