How To Automatically Number Rows In Excel

Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More...Less

Unlike other Microsoft Office programs, Excel does not provide a button to number data automatically. But, yous tin easily add together sequential numbers to rows of data by dragging the fill handle to make full a column with a series of numbers or past using the ROW part.

Tip:If you are looking for a more advanced automobile-numbering system for your data, and Access is installed on your computer, yous tin import the Excel data to an Admission database. In an Admission database, you tin can create a field that automatically generates a unique number when you enter a new tape in a tabular array.

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

What do yous want to do?

Make full a cavalcade with a series of numbers
Utilise the ROW role to number rows
Brandish or hide the fill handle

Fill a cavalcade with a series of numbers

Select the first cell in the range that you want to fill.
Blazon the starting value for the series.
Type a value in the next cell to found a pattern.

Tip:For example, if y'all desire the series i, two, 3, 4, v..., type 1 and 2 in the first two cells. If y'all want the series two, four, half dozen, viii..., type 2 and 4.
Select the cells that contain the starting values.

Note:In Excel 2013 and later, the Quick Analysis push button is displayed by default when you select more than one prison cell containing data. You can ignore the push button to complete this process.
Elevate the fill handle across the range that you want to fill.

Note:Every bit yous drag the fill handle across each cell, Excel displays a preview of the value. If yous desire a different pattern, drag the fill handle by holding downwards the correct-click push button, then choose a design.

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

Tip:If you do not see the fill up handle, you may take to display it first. For more than data, run across Display or hide the make full handle.

Note:These numbers are non automatically updated when you add, move, or remove rows. Yous tin can manually update the sequential numbering by selecting two numbers that are in the right sequence, and so dragging the make full handle to the terminate of the numbered range.

Use the ROW function to number rows

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

The ROW function returns the number of the row that y'all reference. For example, =ROW(A1) returns the number 1.
Drag the fill handle across the range that yous want to fill up.

Tip:If you do non see the make full handle, you lot may take to display information technology first. For more information, see Display or hide the fill handle.

These numbers are updated when you sort them with your data. The sequence may be interrupted if y'all add together, move, or delete rows. You can manually update the numbering by selecting ii 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 equally you add new rows of data, plough that range of information into an Excel table. All rows that are added at the finish of the table are numbered in sequence. For more than information, come across Create or delete an Excel tabular array in a worksheet.

To enter specific sequential number codes, such as purchase social club numbers, you can use the ROW office together with the TEXT function. For case, to offset a numbered list by using 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 elevate the fill handle to the end of the range.

Display or hide the fill handle

The fill handle displays by default, but you can turn it on or off.

In Excel 2010 and later, click the File tab, and so click Options.

In Excel 2007, click the Microsoft Function Push button , and so click Excel Options.
In the Advanced category, under Editing options, select or clear the Enable fill handle and jail cell drag-and-drop check box to display or hide the fill handle.

Notation:To assist forestall replacing existing data when yous drag the make full handle, ensure the Warning before overwriting cells check box is selected. If you do not want Excel to display a bulletin about overwriting cells, you can articulate this cheque box.