How to Number Rows Automatically in an Excel Spreadsheet

Excel 2013 automatically creates row numbers on the left hand side of any spreadsheet, but these are static and can't be included in the spreadsheet's calculations. To create automatic numbers within your workbook, you can use a number of Excel's tools. These include the "IF" Function, click-and-drag extrapolation, the "ROW" function and program settings.

Businesspeople analyzing documents
credit: Ridofranz/iStock/Getty Images

Clicking and Dragging

Excel is very good at identifying patterns. You can use this to your advantage to quickly create row-by-row sequential numbers. First, enter a "1" and a "2" in separate rows in the column you wish to have numbered, then highlight these two cells. Highlighting can be done by clicking and dragging over the two. Once selected, click on the tiny box in the lower-right corner of the selection border, called the Fill handle, and drag downward through the column. Every new, blank cell you drag over will generate a new number in sequential order. If you drag over five new rows, for example, you'll end up with row numbers from 1 to 7. This is particularly useful when your row numbering is separate from the built-in row numbers in Excel.

Using "IF" to Auto-Generate

Clicking and dragging forces that row number to appear, even if there's no other data in that row. This can be problematic if you use Count or similar functions on that column. Another option is to use an "IF" statement to only generate the number when data appears next to it. Start with a "1" in your first row. Immediately below it, enter the formula =IF([Cell to the right]<>"", [Cell above]+1, "").

For example, if your row number starts at A4, the formula would be =IF(B4<>"", A3+1, "").

You can then use the click and drag function with the box in the lower-left corner to copy this formula as far as you think you'll need to go. Numbers will only appear in the A column when something is entered in the corresponding B column.

Using the "ROW" Function

Select the cell where your row numbering will being, and type the formula =ROW(A1).

This should return the number "1." Now select that cell and click-and-drag the fill handle downward to fill in your other row numbers.

Hiding and Showing Row Headers

You have the ability to turn off and on the automatically generated row and column headers in Excel. If you find yourself in a spreadsheet with no row numbers at the far left, go to the View tab. Look for a series of check boxes that includes Formula Bar, Gridlines and Headings, and ensure there is a check mark next to "Headings."