Wednesday, July 18, 2007

Automatically number rows in Excel

Fill 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. 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.
4. Select the cells that contain the starting values.
5. 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.) 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.

Is 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.

Use 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.

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.) n i d o k i d o s Across the range that you want to fill.

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.

3 comments:

  1. Anonymous3:14 PM

    How to make it wihtout dragging?

    ReplyDelete
  2. Anonymous3:20 PM

    For that you need to go for macros.

    ReplyDelete
  3. Anonymous12:44 PM

    Hi. Just wanted to say thanks for a tip in your column that helped me. I was not being able to see the fill handle, got it back using the stpes u mentioned. Thanks.

    ReplyDelete