Automatically number rows

Important notice for users of Office 2003    To continue receiving security updates for Office, make sure you're running Office 2003 Service Pack 3 (SP3). The support for Office 2003 ends April 8, 2014. If you’re running Office 2003 after support ends, to receive all important security updates for Office, you need to upgrade to a later version such as Office 365 or Office 2013. For more information, see Support is ending for Office 2003.

Unlike other Microsoft Office programs such as Microsoft Word, Microsoft Excel does not provide a button to automatically number data. However, you can easily add sequential numbers to rows of data by filling a column with a series of numbers or by using the ROW function.

ShowFill a column with a series of numbers

 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.

  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.

ShowHow?

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.

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

ShowIs the fill handle missing?

To display the fill handle on a selection of cells, click Options on the Tools menu, click the Edit tab, and then select the Allow cell drag and drop check box.

ShowUse the ROW function to number rows

 Note   These numbers are updated when you sort them with your data. However, the sequence may be interrupted if you add, move, or remove 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.

  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.

ShowIs the fill handle missing?

To display the fill handle on a selection of cells, click Options on the Tools menu, click the Edit tab, and then select the Allow cell drag and drop check box.

Tips

  • 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 list (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.). Select the range, point to List on the Data menu, and then click Create List. All rows that are added at the end of the list will now be numbered in sequence.
  • 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 would 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.

Autonumbering in Access

If you are looking for a more advanced autonumbering system for your data, and you have Microsoft Access installed, you could 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 Import Excel data to an Access database and Fields that generate numbers automatically in Access.

 
 
Applies to:
Excel 2003