How to Auto Number Rows in Excel: Formulas and Fill

Formulas & functions · Updated November 20, 2026

To auto number rows in Excel, enter a row-based formula in the first cell of your numbering column and fill it down. For a worksheet with headers in row 1 and data beginning in row 2, enter =ROW()-1 in the first numbering cell. Excel then returns 1, 2, 3, and so on. You can also create a fixed sequence with the fill handle or generate one with a dynamic-array formula. These steps create numbers in worksheet cells; they do not change Excel’s built-in row headings on the left.

What You’ll Need

  • Excel for Microsoft 365 on Windows, or Excel for the web or Mac. The main formula and fill methods work in all three.
  • A worksheet with a column available for the numbers. Decide whether the numbers should identify each record permanently or simply show its current position in the list.
  • For a formula that skips blank records, a separate column that is filled in for every record, such as a name or item description.

Step-by-Step

  1. Choose where the numbers will go. Add a heading such as “No.” or “ID” in the top cell of an empty column. For example, if your headings are in row 1, put the heading in A1 and keep your records in the columns beside it. Make sure the numbering column does not contain data you need to keep; entering a formula into an occupied cell replaces its contents.
  2. Use a formula when numbering should follow worksheet position. With the heading in A1 and the first record in row 2, enter =ROW()-1 in A2. The ROW function returns the number of the row containing the formula, so this returns 1 in row 2, 2 in row 3, and 3 in row 4. Press Enter. The formula refers to the cell’s position, not to the record’s contents.
  3. Fill the formula down the list. Select A2 and point to the small square at the lower-right corner of the cell, called the fill handle. Drag it down to the last row you want numbered, then release. Alternatively, select A2 and the empty cells below it, then use Home > Fill > Down. On Windows, Ctrl+D fills the selected cells down; in Excel for Mac, use Command+D. Excel for the web has the fill handle and the Home > Fill > Down command, though the ribbon layout can vary with window size.
  4. Check the last few results. Confirm the numbers begin at 1 and increase by exactly 1. If you filled 20 data rows starting in row 2, the last number should be 20. Click a numbered cell to see whether it contains a formula. A formula-based number changes when the row position changes; it is not a permanent ID attached to a particular record.
  5. Adjust the starting number if your data begins lower down. If the first record is in row 7 and you want it numbered 1, use =ROW()-6 in the first numbering cell, then fill down. The general idea is to subtract the worksheet row number immediately above the desired starting number. For example, when the first data row is 7, 7 minus 6 gives 1; the next row gives 2. Do not use the row-2 version unchanged if your list starts elsewhere.
  6. Use a blank-aware formula if your list has empty rows. Suppose the record description is in B2 and the number belongs in A2. Enter =IF(B2="","",ROW()-1) in A2, then fill it down. If B2 is blank, the numbering cell stays visually blank; if B2 contains a value, the row gets its position number. This works best when column B is reliably completed for every real record. If the first record is not in row 2, adjust the subtraction as described above.
  7. Use the fill handle for a fixed sequence instead. If you want ordinary numbers rather than formulas, type 1 in the first cell and 2 directly below it. Select both cells, point to the fill handle at the lower-right corner of the selection, and drag down. Excel recognizes the two starting values as a pattern and continues 3, 4, 5, and so on. If you select only the cell containing 1 and drag, Excel may copy 1 rather than continue the series, so include both starting values.
  8. Generate a short sequence with a dynamic-array formula. In an empty worksheet area, with no content in the cells where the results will appear, enter =SEQUENCE(10). It spills the numbers 1 through 10 into ten cells. SEQUENCE is available in Microsoft 365 and Excel 2021 or later; it is not available in some older perpetual versions. In Excel for the web, it also works in a clear spill area. If Excel shows a spill error, clear the cells blocking the output.
  9. Choose the method that matches how you use the list. Use ROW-based numbering when the number should represent a record’s current position. Use typed values and fill when the numbering should stay fixed as values. Use SEQUENCE when you want a quick, automatically sized block of consecutive numbers and have an empty area for the results. Save the workbook after confirming the numbers and the records beside them are correct.

For example, if A1 is “No.”, A2 contains the row formula, and B2:B4 contain three product names, the results in A2:A4 will be 1, 2, and 3. If you sort the entire data range by product name, the formula-based values recalculate according to the rows the products now occupy. A sequence pasted as values, by contrast, travels with its record when the whole row is sorted. That difference matters if the number is meant to be a stable reference rather than a display of list order.

Common Mistakes

  • Starting with the wrong row offset. The formula =ROW()-1 assumes the first numbered record is in row 2. If your data starts in row 5, that formula starts at 4, not 1. Set the offset to match the first data row or use the start-row method above.
  • Dragging one starting value and expecting a series. Excel may copy a single value repeatedly. Enter two consecutive values, select both, and drag to establish the pattern.
  • Numbering only part of a record. When sorting, select the full data range or click inside a properly formed table before sorting. Sorting just the number column can separate labels from their records.
  • Expecting formula numbers to be permanent IDs. A ROW formula reflects location. Inserting, deleting, or sorting rows can change the displayed values. If numbers must never change, use fixed values and keep a backup before reorganizing the records.
  • Filling far beyond the data. This leaves numbers beside empty rows and can make the worksheet appear to contain more records than it does. Fill only as far as needed, or use a blank-aware formula tied to a required field.

Tips

  • Convert formulas to fixed values when appropriate. Copy the numbered cells, then use Home > Paste > Paste Values to replace formulas with their current results. In some ribbon layouts, Paste Values appears in the paste menu as a values-only clipboard option. Check that the numbers are correct first, because the formulas will be removed.
  • Use a proper Excel table for changing lists. Select the data, then choose Insert > Table and confirm whether the range has headers. Tables expand as you add records, and Excel commonly carries a calculated-column formula into new rows. Check the new row after adding it rather than assuming the numbering formula filled automatically in every workbook setup.
  • Keep the numbering column inside the data range. A heading and numbers adjacent to the records make it easier to sort and filter all fields together. Freeze panes or adjust column width if the numbers are hard to see, but do not place them outside the range you intend to manage.
  • Remember what happens with filters. A ROW-based formula numbers worksheet rows, including rows hidden by a filter. The visible list may therefore show gaps, such as 1, 3, and 6. That does not mean records are missing; it means other rows are currently hidden. Clear the filter to see the full sequence.
  • Use a clear destination for SEQUENCE. Dynamic-array results need empty cells below the formula. Leave room for the entire output and avoid putting notes, totals, or other values in its spill area.

Troubleshooting

The formula appears as text instead of a number. The cell may be formatted as Text, or the formula may have been entered with an apostrophe before the equals sign. Change the cell format to General using Home > Number Format > General, edit the cell, and press Enter. If necessary, delete the entry and type it again.

Every filled cell shows the same number. You may have copied a value instead of the formula, or dragged only a single starting number. Check a cell below the first one: a formula-based series should contain a row formula adjusted for its location. For a manually created series, start again with two values selected together.

The first value is not 1. Check which worksheet row contains the first record. The subtraction in the formula must make that row return 1. If the first record is in row 7, for example, the formula needs to subtract 6, not 1.

There are gaps in the visible numbers. Check whether a filter is hiding rows. A row-based formula continues to reflect the original worksheet positions, so filtered results can skip values. Clear the filter if you want to inspect all rows. If you need a compact sequence only for visible records, ordinary ROW numbering will not provide that behavior by itself.

SEQUENCE displays a spill error. Clear any values, formulas, or merged cells in the cells where the sequence needs to appear. Also check that the formula is not inside an Excel table, because spilled array results cannot spill within table cells. Put it in a blank area outside the table.

New table rows are not numbered. Check whether the numbering formula was copied into the new row and whether calculated-column formula filling is enabled for the workbook. You can copy the formula from the row above or extend the table by typing in the row directly below it. Then verify the result and confirm the table includes all the intended record columns.

Frequently Asked Questions

Can Excel number rows automatically when I add new records?
Often, yes. In an Excel table, a formula column may extend its formula to newly added rows. Check the new row after adding data. If it does not fill, copy the formula down or enter it in the new row.

How do I start numbering at 100 instead of 1?
Type 100 and 101 in the first two cells, select both, and drag the fill handle. If you use a formula, base its offset on the first row and desired starting value; then check the next row increments by one.

Will auto numbering update after I sort my data?
A ROW formula updates to show each record’s new position when the full data range is sorted. Fixed values generally remain with their records when the whole range is sorted. Always include the entire record in the sort selection.

Can I number only rows that contain data?
Yes. Use a blank-aware IF formula that checks a required field, such as the record name, and returns an empty string when that field is blank. It will leave blank rows unnumbered, but it still uses worksheet row positions, so gaps can remain if there are empty rows within the list.

Why does a filtered list show skipped numbers?
The hidden rows still have worksheet positions and numbers. A row-based formula does not renumber only the records currently visible after filtering. Clear the filter to view the complete sequence and confirm the rows are present.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.