Adding Serial no. to a list or table in Excel is not a big deal if the list is Small, Singular & Nonchanging. However sometimes (or most of the times..) we deal with ever-changing lists, and every time we insert a new row or delete a row we have to change the serial numbers. This can be changed by insertion of a simple formula in place of numbers as given below.
Let’s see how the formula works
In this formula, we are checking if one of the cells of the row is blank or not. If the cell is blank then the formula won’t return any value. However, if the cell has a value then it will calculate the number of main cells before it that are not blank.
In our example, We have applied IF statement.
- For the condition part: We are checking if the cell ‘B2’ is empty or not (ISBLANK(B2)).
- For Value-if-True Part: If the cell B2 is blank then the formula will return a blank space(“”).
- For Value-if-False Part: If the cell B2 is not blank i.e. it has a value, then the formula will return the count of all cells from B2 (i.e. a cell from the first row of values) to the current row (COUNTA($B$2:B2))
You might be wondering why we used $B$2:B2. This is because when we will drag the formula down we want the first B2 to be fixed ( Hence $B$2) but the next B2 to change (to B3, B4, B5…) as we drag the formula down.
Let me know what you think of the formula or If you think there is a better way of doing this.