Autonumber in Excel

Hi Richard, many thanks for your question. Autonumbering exists in Access, but it is actually a data type usually used to populate a primary key field as records are inputted into the database. Excel doesn’t have any similar “out of the box” functionality like this, but it can be mimicked using some of its other built-in tools. However, first be sure to distinguish between autonumbering and the Autofill feature.

For example, type today’s date into cell A1, reselect the cell and then drag and drag down from the bottom left hand Fill handle and the column will populate with the subsequent dates. This is the Autofill feature and isn’t quite the same as automatic numbering. Excel uses pattern recognition to populate numerical lists such as this, and its own built-in custom lists for, say, months. You can, of course, build your own custom lists in Excel.

However, with a bit of work a similar Autonumbering effect to that of Access can be achieved in Excel.

For example, in Row 1 of a new worksheet create three column headings: ID, Name, Department

For your first ID enter “=TEXT(ROW(A2),"00")”, and then a name and department
For your second date enter “=TEXT(ROW(A3),"00")” and then another dummy name and department
Then Select the whole table. In Excel 2007 go to the Insert Tab and click on “Insert Table” (In 2003 click on the Data drop down menu and chose List – Create List, then tick “My list has headers”)

Now, every time you enter a new name and department, a two digit ID number will be entered automatically for you. In the 2007 version the table is formatted and can be restyled and controlled using the ribbon, and in 2003 you’ll see an asterisk in the autonumbered cell which should remind you of Access! If you inserted a row in the middle of the table, the ID number should update accordingly.

This solution is, of course, a work around and coming up with your own bespoke autonumbered field code will involve other situational factors, not least the nature of the field itself but see how you get on with these examples and let me know if you need any further assistance.

Anthony