Automatically number rows

Conversely like other Microsoft Office programmes, Excel does not have a built-in function to number data automatically. Although, you can easily insert sequential numbers to dats rows by dragging the fill handle to populate a column with multiple ranges of numbers or by applying the ROW function.

Tip: If Access is installed on your computer and you want a more sophisticated auto-numbering system for your data, then you can simply import the Excel data to an Access database. In an Access database, you can produce a field that generates a unique number by default, when you type a new record in a table.

Fill a column with a series of numbers

  1. Choose the initial cell in the particular range that you want to fill.
  1. Enter the starting value for the series.
  1. Enter a value in the next cell to form a pattern.

Tip: For example, if you seek the series 1, 2, 3, 4, 5…, enter 1 and 2 in the first two cells. To get the series 2, 4, 6, 8…, enter 2 and 4.

  1. Highlight the cells with the starting values.

Note: In Excel 2013 and later, the Quick Analysis button is shown automatically when you choose several cells including data. You can avoid using the button to finish this procedure.

  1. Drag the fill handle throughout the range that you desire to fill.

Note: Whilst dragging the fill handle across each cell, Excel presents a preview of the value. For another pattern, drag the fill handle by holding down the right-click button, followed by selecting a pattern.

To fill in ascending order, drag down or to the right. To fill in descending order, drag up or to the left.

Tip: If the fill handle isn’t visible, you might have to show it first. For further information, see Display or hide the fill handle.

Note: These numbers are not instantly updated when you add, move, or remove rows. You can directly update the sequential numbering by choosing two numbers that appear in the right sequence, and then moving the fill handle to the end of the numbered selection.

Use the ROW function to number rows

  1. In the first cell of the range that you choose to number, enter =ROW(A1). The ROW function yields the number of the row that you reference. For instance, =ROW(A1) gives the number 1.
  1. Drag the fill handle through the preferable range you want to fill.

Tip: If the fill handle isn’t visible, you might have to show it first. For further information, see Display or hide the fill handle.

  • These numbers are updated when they are sorted amongst your data. The sequence could be disrupted if you insert, rearrange, or remove rows. You can manually edit the numbering by highlighting two numbers within in the correct sequence, and then dragging the fill handle to the last part of the numbered range.
  • With the ROW function, you can format numbers to be added by default, as you insert new data rows through changing that data selection into an Excel table. All rows that are inserted at the end of the table are numbered sequentially. For further details, see Create or delete an Excel table in a worksheet.

Display or hide the fill handle

The fill handle appears automatically, but it can be turned on or off.

  1. In Excel 2010 and later, select the File tab, and then press Options.

In Excel 2007, press the Microsoft Office Button, and then select Excel Options.

  1. In the Advanced category, below Editing options, click or clear the Enable fill handle and cell drag-and-drop check box to show or cover the fill handle.

Note:  To help stop replacing existing data when you drag the fill handle, ensure the Alert before overwriting cells check box is chosen. If you do not want Excel to prompt a message about overwriting cells, you can empty this check box.

See Also

Overview of formulas in Excel

How to avoid broken formulas

Find and correct errors in formulas

Excel keyboard shortcuts and function keys

Lookup and reference functions (reference)

Excel functions (alphabetical)

Excel functions (by category)

%d bloggers like this: