autonumber in excel

Public Schedule Face-to-Face & Virtual Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Autonumber in Excel

Autonumber in Excel

resolvedResolved · Medium Priority · Version 2003

Richard has attended:
Excel Introduction course

Autonumber in Excel


Please could you help me understand how to use autonumber in Excel. Sometimes I can get it to work and at other times not very well.

I would be interested to know how it applies to dates as well as numbers.



RE: 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.



Training courses


Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


Excel tip:

Convert Text into Number

Some times numbers maybe imported in as text or you maybe concatenating numbers that form a text string that now are treated, because you had to extract them by Text functions

To convert Text into Number just encase the relevant cell reference or formula in the TEXT function. See Converting American Date to European hint

eg TEXT(Ref) or TEXT(formula)

View all Excel hints and tips

Server loaded in 0.07 secs.

✓ w3speedster