Have you ever come across an Excel spreadsheet where the serial numbers are not in sequence? Well I have 😛 It’s not as simple as putting 1 in cell A1, =A1+1 in cell A2, and copying the formula to the subsequent cells. Sometimes you may insert/delete a row, move some rows, insert section headers and all the numbers get out of sequence.
Here’s a formula which I adapted from a Stack Overflow post which will work no matter which cell it is copied into. It requires Excel 2007 and above as I’ve used the IFERROR function.
=1 + IFERROR(LOOKUP(2, 1/(ISNUMBER(INDIRECT(ADDRESS(1, COLUMN(), 1)):INDIRECT(ADDRESS(ROW() - 1, COLUMN(), 1)))),INDIRECT(ADDRESS(1, COLUMN(), 1)):INDIRECT(ADDRESS(ROW() - 1, COLUMN(), 1))), 0)
Here’s a screenshot of how the numbers will run with section headers and blank lines in between:
The formula basically looks for the last number in the cells above it and adds one to it. Text and blank cells are ignored. The formula was tested on 1000 cells with no lag. Go ahead and try it out! Ad huc 🙂