MrExcel Consulting
Your One Stop for Excel Tips & Solutions

Any idea how to do an Autonumber?


Posted by Jeremy on January 09, 2001 1:14 PM

Is there anyway within excel to do an autonumber like you do in Access?? I'm trying to populate a database, and would like to automatically assign a number to each entry. Any ideas?

Posted by Mark w. on January 09, 2001 1:19 PM

Put the value '1' in a column in your data set.
In the cell beneath this value type =, click on
the cell above, type +1, and [Enter]. Copy this
formula down to the last row of your data.

Posted by dd on January 09, 2001 1:26 PM


Or if you want the numbers to adjust automatically when rows are deleted and for blank rows to be un-numbered try this one:
=IF(A1<>"",COUNTA($A$1:A1),"")


Posted by Mark W. on January 09, 2001 2:49 PM

Or even simplier ...

1. Enter '1' in cell A1
2. Enter =OFFSET(A2,-1,)+1 in cell A2, and Copy
Down.

Posted by Mark W. on January 09, 2001 2:56 PM

Or... if you have a column label in cell A1, then
use =SUM(OFFSET(A2,-1,),1) in cell A2 and below.
That'll allow you to even delete the 1st row of
data without any consequences!

Posted by dd on January 09, 2001 3:10 PM


But these last two formulas don't work if a row is inserted.

Posted by Mark W. on January 09, 2001 3:24 PM

I does if you using a Data Form... If you're not
just use Ctrl-' (single quote) to fill in the
new, empty cell.

Posted by dd on January 09, 2001 3:30 PM

But this doesn't update the rest of the numbers, does it?

Posted by Mark W. on January 09, 2001 3:32 PM

Uhhg, the Ctrl-' suggestion wasn't a good one.
Actually, what I like to do, but didn't want to
elaborate before is:

1. With cell A2 active, define a name (e.g, "counter")
with a reference of "=SUM(OFFSET(Sheet1!A2,-1,),1)".
2. Then use "=counter" in cell A2 and below.
3. ...then you can use Ctrl-' to reliably fill-in
a missing value.

Posted by Mark W. on January 09, 2001 3:33 PM

You beat me to the punch. See my comment right
before yours.

Posted by dd on January 09, 2001 3:45 PM


Sorry to be such a nuisance, but what if you want to insert a blank row (and being blank, it should not have a number) ?

Posted by Mark W. on January 09, 2001 4:07 PM

It was my understanding that the original poster
wanted to number all records; therefore, that
field should be filled in at all times as it would
if you were using the Data Form... menu command.