Guides

Excel – Automatic Update of Sequence Number / Current Number in a Table

394

When working in Excel I sometimes have tables of data that need to have a current number (CUR. No) or sequence number (SN/SQN) column.

But as it goes, I sometimes have to delete or insert rows in the middle of the table and my numbering column is messed up and have to redo the numbering correctly:

excel_ldf2hcyvhd

Instead of the above I use Tables (select range, press CTRL+T) because of their great features, and I use a formula in the table to do the numbering for me (=IF(ISNUMBER(A1);COUNTA($A$1:A1);1)), but upon deleting or inserting a row, I have to update the formula to include the new row or fix numbering if row was deleted:

excel_fgakoaumut

 

As you might have guessed, there’s a way for Excel to do the correct numbering by itself when I delete or insert a row. You must use an Excel table for your data (picture above).

I’m gonna give you 2 examples of such a formula, one similar to the above, and one that addresses a value in another row and column in a table.

Example 1 – works in any table for simply numbering and not with conditioning from a value in another column

=IF(NOT(ISNUMBER(INDIRECT(ADDRESS(ROW()-1;COLUMN()))));1;INDIRECT(ADDRESS(ROW()-1;COLUMN()))+1)

Let me explain it a bit before I demonstrate how it works:

If the value of the cell above (INDIRECT(ADDRESS(ROW()-1;COLUMN()))) is not a number (in the example table is SQN, so not a number), then display the value 1. Otherwise display the number of the row above + 1 (INDIRECT(ADDRESS(ROW()-1;COLUMN()))+1).

For example for SQN 4 in the example below (which is on row 5 of Excel), the formula tests if the value on row 4 of current column is a number (SQN=3, so it is a number) and then returns the value of the previous row cell on the current column (which is SQN=3) and adds 1 to it, which results in 4.

Regardless if you insert a row or delete one, the formula will react to actual Excel rows and not to cell references, therefore you will get no error or bad numbering. See how it works:

excel_yq1esyorr2

Example 2 – works in any table but with specific conditioning – this formula must be changed according to your specific table conditions

=IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1;COLUMN()+3)));ROW()-10;1)

This assumes our numbering starts from Row 11, that’s why there’s a ROW()-10 value returned. On Row 11 it will show the value 11-10 = 1, then on Row 12 it will show the value 12-10 = 2 and so on. But all under the condition that the value from row above and 3 columns to the right is a number.