Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How do I create a field that automatically generates a unique number when I enter a new record in a table ?

I have a list of numbers in the B column. I want the numbers in column A to automatically list as 1, 2, 3, 4, .....


When I insert rows so that I can add additional numbers to the B column, I want the A column to AUTOMATICALLY CORRECT its self and AUTOMATICALLY UPDATE the new empty cells in column A with the sequential numbers 1, 2, 3, 4, 5, 6, ......

Is there some sort of CONDITIONAL FORMATTING that I can use? Formulas do NOT work because the new empty cells in column A dont AUTOMATICALLY FILL themselves.

share|improve this question
It would seem that Access is the only way to accomplish what I want. Excel does not have the capability to auto format numbers sequentially after rows are inserted. :-( – user235748 Jul 4 '13 at 15:22

What version of Excel? In 2007 and later you can use an Excel table. All formulas and formatting will automatically be applied to new rows.

Use the formula


to create a unique number.

enter image description here

Then insert a new row above row 3 and you will see:

enter image description here

If you want the first data row in the table to be a 1, not a 2, use


In Excel 2003 you can use a list. I forget which versions earlier than 2003 support lists, but if you use anything older than 2003 you may want to consider upgrading, anyway. The formula is the same.


enter image description here

After inserting a new row:

enter image description here

share|improve this answer

There isn't (adding hooks to Excel, let's face it, is a HUGE security risk and usually isn't worth the trouble). However, you can get reasonably close by filling in more cells than you can reasonably ever use on the spreadsheet. The details for that are available on this SuperUser Question. Just fill the first cell with the formula you mentioned and let the VBA code take care of the rest.

share|improve this answer

What is this for? If it is for a database, mysql provides a feature for generating a unique number each time a new record is entered. Or you can use Microsoft Access that has a feature like that.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .