I would like to create a table with each row indexed by auto-increment.

I tried using A2=A1+1 or adding 1 to the previous value. This should be the solution according to this thread. However adding new rows (using the tab key or dragging the corner down) doesn't automatically increment the index column.

What's the solution?

  • Assuming you have a header row, which a table requires, then A2 would contain the initial value, and A3 would contain the formula =A2+1. As long as your table is formatted as such, then that formula should automatically be added whenever you add a row or rows. You might also try the formula =ROW()+1. – BillDOe Oct 20 '15 at 5:07
  • Sorry, that formula should have been =ROW()-1. – BillDOe Oct 20 '15 at 5:27
  • That's what I would expect too, but it doesn't happen. – Benjamin Oct 20 '15 at 18:08
  • Try highlighting as many rows as you need to insert and then use the Insert command on the Home ribbon in the Cells box. That code actually works differently than the insert command using a right click of the mouse. I say this because when I use the right-click method and have complicated borders, the insert command from the Home ribbon duplicates the borders, but a right-click totally messes them up. And I have the formula =ROW()-2 in my auto-increment column (I have two header rows). – BillDOe Oct 20 '15 at 18:22
  • Nope, it inserts blank cells too. – Benjamin Oct 26 '15 at 4:07

If you reference =row(1:1) in the first row of your table, it will automatically fill in 1, 2, 3, 4, 5 etc. for as many rows as you have in your table. The formula will automatically generate to be =row(1:1) or 1, =row(2:2) or 2, =row(3:3) or 3, etc.

I use this to create a unique key in a table for table values.

  • 1
    This assumes that the table will never be sorted. The column will always increment by 1, even if you sort it by largest to smallest. – Gravitate Nov 14 '18 at 11:03

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.