A dynamic line-numbering formula for Excel

Use this formula to automatically number rows with data, especially if you anticipate lots of change.

Sometimes you need a solution to an uncommon problem, like numbering Excel lines. It isn't a very Excel-like task, but I recently heard from someone that insisted that's exactly what they needed. Why not just use the fill handle I asked. A dynamic list was needed—one that could accommodate change and blank rows. Here's what I used:

=IF(cell<>"",COUNTA(cell:cell))

As you can see in the following figure, this formula works well. If you insert or delete a row, the numbers update accordingly—almost. There's a problem in row 9; if the row is empty, the formula returns FALSE. On the other hand, row 11 displays the formula's flexibility. After deleting a row (ID 5), the formula updates as required. (I purposely left the ID column so you could see these changes.)

The FALSE value is easy to eliminate by updating the IF() function:

=IF(cell<>"",COUNTA(cell:cell),"")

Add any required punctuation to the IF() function as well. If you concatenate the punctuation using the following form, Excel will display the punctuation for empty rows.

=IF(cell<>"",COUNTA(cell:cell),"") & "."

Instead, add the punctuation to the IF() function as follows:

=IF(cell<>"",COUNTA(cell:cell) & ".","")

Although this formula works and seems to accommodate all the possibilities, I'm still not convinced it's the most efficient solution—do you have a better one? I'd also like to know if anyone has a use for such a formula. How might you use it?

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.