Lesson#133: Auto numbering formulas in Excel
![Lesson#133: Auto numbering formulas in Excel](https://excelabcd.co.in/wp-content/uploads/2019/03/excelabcd-lesson-133.png)
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.
![](https://excelabcd.co.in/wp-content/uploads/2019/03/133-1.png)
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
![](https://excelabcd.co.in/wp-content/uploads/2019/03/133-2.png)
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.
![](https://excelabcd.co.in/wp-content/uploads/2019/03/133-3.png)
Formula to auto number numerical values only
![](https://excelabcd.co.in/wp-content/uploads/2019/03/133-4.png)
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.
![Auto numbering formula](https://excelabcd.co.in/wp-content/uploads/2019/03/133-5.png)
Formula to auto number text values only
Auto numbering text values need a little different type of formula.
![Auto numbering formula](https://excelabcd.co.in/wp-content/uploads/2019/03/133-6.png)
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.
![Auto numbering formula](https://excelabcd.co.in/wp-content/uploads/2019/03/133-7.png)
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.
0 Comments on “Lesson#133: Auto numbering formulas in Excel”