Automatically number rows

Unlike other Microsoft Office programs, such as Word, Excel does not provide a button to automatically number data. However, you can easily add sequential numbers to rows of data by dragging the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) to fill a column with a series of numbers or by using the ROW function.

 Tip   If you are looking for a more advanced autonumbering system for your data, and Access is installed on your computer, you can import the Excel data to an Access database. In an Access database, you can create a field that automatically generates a unique number when you enter a new record in a table. For more information, see Exchange (copy, import, export) data between Excel and Access.

What do you want to do?


Display or hide the fill handle

The fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) Selected cell with fill handle is displayed by default, but you can turn it on or off.

  1. Click the File tab.
  1. Under Excel, click Options.
  2. In the Advanced category, under Editing options, select or clear the Enable fill handle and cell drag-and-drop check box to display or hide the fill handle.

 Note   To help prevent replacing existing data when you drag the fill handle, make sure that the Alert before overwriting cells check box is selected. If you do not want Excel to display a message about overwriting non-blank cells, you can clear this check box.

Top of Page Top of Page

Fill a column with a series of numbers

  1. Select the first cell in the range that you want to fill.
  2. Type the starting value for the series.
  3. Type a value in the next cell to establish a pattern.

 Tip   For example, if you want the series 1, 2, 3, 4, 5..., type 1 and 2 in the first two cells. If you want the series 2, 4, 6, 8..., type 2 and 4.

  1. Select the cells that contain the starting values.
  2. Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) Selected cell with fill handle across the range that you want to fill.

 Note   As you drag the fill handle across each cell, Excel displays a preview of the value that it will fill the cell with. By default, Excel uses a linear growth pattern to determine these values. If you want to apply a different growth pattern, drag the fill handle by holding down the right mouse button, and then choose a pattern from the shortcut menu.

To fill in increasing order, drag down or to the right. To fill in decreasing order, drag up or to the left.

 Tip   If you do not see the fill handle, you may have to display it first. For more information, see Display or hide the fill handle.

 Note   These numbers are not automatically updated when you add, move, or remove rows. You can manually update the sequential numbering by selecting two numbers that are in the right sequence, and then dragging the fill handle to the end of the numbered range.

Top of Page Top of Page

Use the ROW function to number rows

  1. In the first cell of the range that you want to number, type =ROW(A1).

The ROW function returns the number of the row that you reference. For example, =ROW(A1) returns the number 1.

  1. Drag the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) Selected cell with fill handle across the range that you want to fill.

 Tip   If you do not see the fill handle, you may have to display it first. For more information, see Display or hide the fill handle.

 Notes 

  • These numbers are updated when you sort them with your data. However, the sequence may be interrupted if you add, move, or delete rows. You can manually update the numbering by selecting two numbers that are in the right sequence, and then dragging the fill handle to the end of the numbered range.
  • If you are using the ROW function, and you want the numbers to be inserted automatically as you add new rows of data, turn that range of data into an Excel table. All rows that are added at the end of the table will be numbered in sequence. For more information, see Create or delete an Excel table in a worksheet.
  • To enter specific sequential number codes, such as purchase order numbers, you can use the ROW function with the TEXT function. For example, to start a numbered list with 000-001, you enter the formula =TEXT(ROW(A1),"000-000") in the first cell of the range that you want to number, and then drag the fill handle to the end of the range.

Top of Page Top of Page

 
 
Applies to:
Excel 2010