How to Number Rows Automatically in an Excel Spreadsheet

If you're using Microsoft Excel, it's often useful to number the particular rows in a spreadsheet in order to keep track of the order in which you received records and where they go in a list of data. This can also be a useful way to assign a unique identifying number to each record, such as a customer of your business or an expense that you incurred. You can number rows by dragging down a number through the spreadsheet in order to have a column with increasing numbers or by using built-in functions that refer to the current row's number.

How to Number Rows Automatically in an Excel Spreadsheet
Image Credit: Manuel Breva Colmeiro/Moment/GettyImages

Rows and Columns in Excel

Like most other modern spreadsheet programs, Microsoft Excel organizes data into rows and columns. Rows are normally numbered, starting with the number one, while columns are organized by letters, beginning with A.

While Excel and other spreadsheets can be used in a variety of ways, it's common to organize data into rows, where each row represents all the information about a particular person or thing, such as customer of a business or a purchase made. In such systems, columns are usually used to hold particular attributes that exist across each of the data. For instance, the name of each customer might be put in one column and the phone number of each in another.

Often, the first row in a spreadsheet is used as a header to specify what each column contains. In some cases, you may also have header columns specifying some kind of information about what is in each row.

Row Numbering in Excel

Rows in Excel are automatically numbered, but this numbering is stored outside the spreadsheet itself. If you want to create an actual column in the spreadsheet with row numbers, you will need to do it yourself.

One way to do this is to type the first number that you want in the first row that you want to number. You may want this numbering system to be different from the numbers displayed on the side of the spreadsheet, since if you have a header row or rows you may not want to include these in your numbering system. Then, type the next number you want to use in the subsequent row so that you can set up a pattern for Excel to follow in numbering your rows.

Next, select the two numbering cells and click the bottom-right corner of the cell and drag it down through the spreadsheet to create an increasing column of numbers throughout your file. You should see a column of increasing numbers perpetuated throughout your file, and if you later add additional rows, you can repeat this operation to add additional numbers to these new rows. Note that if you reorder rows in your spreadsheet, such as by sorting them, these numbers will persist in place. It is up to you if you wish to renumber the rows after reordering them by labeling the first ones from the starting number and again dragging the numbers down through the spreadsheet.

Using the Row Excel Function

Excel includes a function called Row that will automatically return the number of the current row in the spreadsheet. To use it to number your rows, simply type "=Row()" in a cell. You can use this as part of a formula in order to compute quantities based on the row number.

If you wish to use this function to number multiple rows, you can drag the formula down through a column to each the rows of the spreadsheet that you wish to number. Keep in mind that if you rearrange the rows in your spreadsheet, such as by sorting, and then recalculate formulas, you will see the row numbering change to reflect the new row ordering.

references