For Excel how do I create a field that automatically generates a sequential number

Question

How do I create a field that automatically generates a unique number when I enter a new record in a table ?

I have a list of numbers in the B column. I want the numbers in column A to automatically list as 1, 2, 3, 4, .....

BUT

When I insert rows so that I can add additional numbers to the B column, I want the A column to AUTOMATICALLY CORRECT its self and AUTOMATICALLY UPDATE the new empty cells in column A with the sequential numbers 1, 2, 3, 4, 5, 6, ......

Is there some sort of CONDITIONAL FORMATTING that I can use? Formulas do NOT work because the new empty cells in column A dont AUTOMATICALLY FILL themselves.

Solution

What version of Excel? In 2007 and later you can use an Excel table. All formulas and formatting will automatically be applied to new rows.

Use the formula

=Row()

to create a unique number.

Then insert a new row above row 3 and you will see:

If you want the first data row in the table to be a 1, not a 2, use

=row()-1

In Excel 2003 you can use a list. I forget which versions earlier than 2003 support lists, but if you use anything older than 2003 you may want to consider upgrading, anyway. The formula is the same.

Before:

After inserting a new row:

Comments

[Admin]

It would seem that Access is the only way to accomplish what I want. Excel does not have the capability to auto format numbers sequentially after rows are inserted. :-(