How to Auto Number in Excel 2007

By Petra Wakefield

Avoid the tedium of manually entering a list of numbers by using one of two functions in Excel 2007 that automatically enter the numbers for you. Using Excel functions creates a list of numbers that update themselves if you move a row or column to a different location, and also allows you to format those numbers. The fill handle method will not update the numbers, but quickly creates a basic numbered list.


Step 1

Click the first cell in the row or column you want to number.

Step 2

Type "=TEXT(ROW(A1), "000")" to number the rows beginning with "1" and with the format 001, 002, etc. Replace "A1" with a different cell reference to begin at a different number; use "A2" to begin with 002, for example. In this formula, the cell reference returns the row number of the cell. You can use any cell reference in the formula, not just the reference for the cell the formula is in. Replace "000" with "$0.00" for dollar format, "0%" for percent format or use your own format.Only type "=ROW(A1)" if you want numbers in standard format -- 1, 2 and so on.

Step 3

Press "Enter" on your keyboard and Excel will calculate the formula. Select the same cell again and hover your pointer over the bottom right corner of the cell until a black cross appears. Click and drag down to cover as many cells as you want to number.

Fill Handle

Step 1

Click to select the cell where you want to begin numbering and type your first number.

Step 2

Select the next cell in the row or column you are numbering and type the second number in your series. For standard numbering, type "1" in the first cell and "2" in the second. Use "2" and "4" to number by twos, "10" and "20" to number by tens, and so forth.

Step 3

Click the first cell and hold the left mouse button down. Drag down or right to the second cell so both are selected at once.

Step 4

Hover the pointer over the bottom right corner of the second cell until you see a black cross. Click and drag down to number rows or to the right to number columns. Let go of the mouse button when you reach the cell where you want the numbering to end.

Tips & Warnings

  • Use "COLUMN" in place of "ROW" in the TEXT function or on its own to number columns instead of rows.
  • If the black cross does not appear when you hover over the bottom right corner of a cell, click the Office icon in the top left corner of your screen. Select the "Excel Options" button at the very bottom of the drop-down menu. Choose "Advanced" in the left-hand menu of the pop-up window, and check the box next to "Enable fill handle and cell drag-and-drop" under "Editing options."