Sequential Numbers Formula

A ROWS by any other name

Getting a single formula to create a series of sequential numbers is most commonly done with the ROWS function.

The formula in cell A5 in the image below shows the typical solution for creating sequential numbers.

An example file can be downloaded via the button at the bottom of the post.

This formula works if rows are inserted or deleted above or within the range. See images below.

 

 

 

Even though the formula in cell A5 refers to itself it doesn’t trigger a circular reference error because the ROWS function returns a count of how many rows are in a range, not a calculation as such. The value in cell A5 is not used in the calculation so no circular reference exists.

Using the $ signs fixes the starting reference at $A$5 and the second reference being relative changes as the formula is copied down to create the expanding range and hence increasing numbers of rows.

There is also a COLUMNS function if you want to create a horizontal sequence.

In cell D1 you could use and copy the following formula across.

=COLUMNS($D$1:D1)

Download example file

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.