So there is more maintenance involved than option #1.Īlso, this option does not allow you to keep your primary key static if you are inserting new rows in the middle. 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). Then copy the formula from cell A2, down as far as you need. Then in cell A2, enter the formula: =OFFSET(A2,-1,0)+1 To create an autonumber field, you could try using the Offset() function.
So in this option, you will not be able to keep your primary key static if you are inserting new rows in the middle.Īlso, you insert a blank row, you'll need a way to fill in the formula. Then in cell A2, enter the formula: OFFSET(A2,-1,0)+1. 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. To create an autonumber field, you could try using the Offset() function. This function returns the current row number. To create an autonumber field, you could try using the row() function. It must be able to stay unique if a row is inserted and must not change if a row is cut/pasted.Īnswer: To create an autonumber field in Excel, you have a few options - each with its own set of limitations. It must automatically update when a row is added. Question: I need help in creating an autonumber (primary key) in Excel like the one in Access. This Excel tutorial explains how to create an autonumber (with screenshots and step-by-step instructions).
