Formula Watch – Autonumber your rows and then convert them to values

If you've ever wanted to have a number column that automatically updated as you inserted new rows, simply use this function:


Place that in your A1 cell and then either cut and paste or autofill down as far as you like. If you insert rows, the numbers will update to match the row number.

If, at some point, you want to lock the numbers in permanently (say, if you're going to sort by another column and want to be able to recover your original order using the numbering you added), you need to convert the formulas to values.

Select the column containing your numbers (or just the cells you want) and then right click one of the sides of the selected area (just not the bottom corner). Now, while still holding the right button, drag the outline of your cells to another column and then back to the original location to replace your formulas. You'll be prompted to select from several options; pick Copy Here As Values Only:

Copy Here as Values Only

Now sort away; you can always use your number column to put things back the way you had them.


Comments (0)

Skip to main content