tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities DigMinecraft

Excel Functions

(Scroll to see more)

Share this page:

MS Excel: How to Autonumber in Excel

Question: I need help in creating an autonumber (primary key) in Excel like the one in Access. It must automatically update when a row is added. It must be able to stay unique if a row is inserted and must not change if a row is cut/pasted.

Answer: To create an autonumber field in Excel, you have a few options - each with its own set of limitations.

Option #1

To create an autonumber field, you could try using the row() function. This function returns the current row number.

Microsoft Excel

The limitations of this option is that if you insert a new row, your row() function will return a different value in the lines below the inserted line. So in this option, you will not be able to keep your primary key static if you are inserting new rows in the middle.

Also, you insert a blank row, you'll need a way to fill in the formula.

Option #2

To create an autonumber field, you could try using the Offset() function.

In cell A1, enter the number 1.
Then in cell A2, enter the formula:

=OFFSET(A2,-1,0)+1

Then copy the formula from cell A2, down as far as you need.

Microsoft Excel

The limitations of this option is that if you insert a new row, you will need to readjust all of your formulas (below the row inserted). So there is more maintenance involved than option #1.

Also, this option does not allow you to keep your primary key static if you are inserting new rows in the middle.