Instant Rows Numbers

Enter this formula anywhere and sequential numbers 1, 2, 3 .... will appear in sequence in each row. Note: This won't work if the above row is not blank but you start in the top row (A1, B1, C1)

Here's a "short" version (Thanks Frank Brutsaert)

=IF(ROW()=1,1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1)

A quick way to enter row numbers is to enter the formula in the top cell of a selection, then push Ctrl and Enter at the same time. This formula also works if there is text above such as column header. (Thanks Nick Osdale-Popa)

=IF(ROW()=1,1,IF(ISTEXT(INDIRECT(ADDRESS(ROW()-1,COLUMN()))),1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1))

This formula enables you to add to add text to the numbers (in this case it is "T")

=IF(ROW()=1,"T1",IF(INDIRECT(ADDRESS(ROW()-1,COLUMN()))="","T1","T"&SUBSTITUTE(INDIRECT(ADDRESS(ROW()-1,COLUMN())),"T","")+1))

This will work whether the above row is blank or not (the above formulas were actually designed that way)

Enter =ROW(INDIRECT("1:10")) for 10 sequential numbers in 10 cells as an array formula (Push Ctrl, Shift and Enter simultaneously)



© Copyright andrewsexceltips.net All Rights Reserved.