autonumber in excel
Request a callback

We'll call during UK business hours

0207 987 3777

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:
No courses

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.



Please browse our web site to find out more about
excel consultancy and other Microsoft training courses.

Excel tip:

Adding multiple rows in an Excel 2010 Worksheet

If you want to add more than one row to an Excel Worksheet, drag select the number or rows you want added to the spreadsheet.

Then right click on these selected rows, choose Insert from the menu, and the new rows will be added above the rows you first selected.

View all Excel hints and tips

Excel training
Course rating
4.7 stars - based on 24710 reviews
Microsoft Certified Partner Institute of Leadership and Management - Certified Courses Learning and Performance Institute - Accredited Training Provider Security Seal verified by visa, mastercard securecard

Connect with us: Google+ · Facebook · Twitter · LinkedIn · Pinterest

2nd Floor, CA House, 1 Northey Street
Limehouse Basin, London, E14 8BT
United Kingdom