autonumber in excel


starstarstarstarstar Excellent

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

Autonumber in Excel

resolvedResolved · Medium Priority · Version 2003

Thu 28 Oct 2010, 09:32 replyReply Delegate Richard said...

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.



For upcoming training course dates see: Pricing & availability

Thu 28 Oct 2010, 10:46 replyReply Trainer Anthony said...

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.



Live dates & prices


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:

How to Spell Check an Excel 2010 Worksheet

Excel 2010 does not automatically spell check a document. So, here's how to manually spell check a worksheet.

Either select the ''Review'' tab in the Ribbon, go to the ''Proofing'' section and click ''Spelling.'' Or, simply press F7.

View all Excel hints and tips