Software

Add autonumbering to your Excel spreadsheet


Unlike Access, Excel does not have an autonumbering feature that increments a number each time you add a new row. With Excel 2003 and 2007, you can use the Row function to add your own autonumbering to a data list.

For example, say you are creating a list of all of your company's products and their prices. Follow these steps to add your own autonumbering for the list:

  1. In A1, enter this formula: =TEXT(Row(A1),"000-000")
  2. Enter the name of the first product in B1.
  3. Enter the price of the first product in C1.
  4. Copy cell A1 to A2.
  5. Enter the name of the second product in B2.
  6. Enter the price of the second product in C2.
  7. Select A1:C2.
  8. Go to Data | List and select Create List. (In Excel 2007, press [Ctrl]T, then click OK.)

Now when you type the name of the next product in B3 and press [Tab], Excel will automatically fill in the next number for you.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

17 comments
Eng-AR
Eng-AR

Thanks very much..

if there any way to use this steps for multiple sheets...

Plz help me :)

noemir
noemir

I know this post is old but never the less it was exactly what I need to auto number a list the I constantly had to either insert or remove rows from. Works perfectly. Thanks rbsmith2000

kura420
kura420

download internet download manager(reg) from www.softwarehomes.com its surprisingly free and no need for registration

mpryke
mpryke

Good Tip, how can you generate consequential serial numbers (Invoice Numbers) from a template invoice

kitedemon
kitedemon

Sweet! Great tip. I will use it tomorrow.

J_M
J_M

I think that's a great idea...just remember that if a row (record) is deleted or added in this example, unlike what happens in Access, the autonumber column value does not stay associated with the data in its row. Otherwise, it's great.

simon.freeman
simon.freeman

Mary Ann - I am confused. When you say, step 8, Go To Data | List do you mean menu item Data then item List because I do not have a list item in my data menu. I use Excel 2003. (Also no list on the Go To dialogue)

rbsmith2000
rbsmith2000

I needed a good autonumber formula recently and only found bad ones that easily get out of synch when entries are inserted, moved or deleted. Here is one for the next poor fool that works whereever you put it and does not get out of synch. =IF(OR(ROW()=1,ISTEXT(INDIRECT(ADDRESS(ROW()-1,COLUMN())))),1,INDIRECT(ADDRESS(ROW()-1,COLUMN()))+1)

ssharkins
ssharkins

That's a keen catch -- thank you for sharing it with us.

rcstan
rcstan

If you click "My List has Headers," the first row of data will automatically be reformatted as Header (Style = Bold; Alignment: Horizontal = Center). After changes are Saved, reformatting Row 1 to match Row 2 can be difficult.

bevg
bevg

Since we're on the subject of numbering, I have semi-related comments. I get frustrated that MS Word does not truly "number" a table, though you can use an outlining feature to number the rows. If you sort, however, the numbers don't move with the associated item/entry. I very often work with data in Word, where I want to sort by various columns, and be able to return the table to its original order afterward. To do this, I do a "workaround" by generating a column of numbers in Excel, specifying the format of the cells as text, and copying that into the Word table. The reason for specifying text format is that Word cannot accurately sort numbers with varying numbers of digits--it has to be uniform (all numbers having 4 digits, for example). You may ask why I don't do the entire project in Excel, but very often the edits I do to a table/spreadsheet cannot be done in Excel. Am I making unnecessary work? Is there a REAL (sort-able) numbering utility for Word tables?

ssharkins
ssharkins

I think the List feature is new to Excel 2003. Please make sure you're really using 2003. If List isn't available on your Data menu, click the triangular arrow at the bottom of the menu to display all the items on your menu. If you're using 2003, it should be there.

J_M
J_M

Thanks for the note!

ssharkins
ssharkins

I probably don't understand your problem, but Word does sort a table numerically. Are you selecting the table first and using the Sort command from the Table menu to sort?

ssharkins
ssharkins

Off the top of my head, I don't have any answers for you, but I'll see what I can come up with.

simon.freeman
simon.freeman

ssharkins - thanks v much. Actually using 2000 at the office so not surprising! -- Simon