To number rows in Excel, enter =ROW()-1 in cell A2 when row 1 contains a header, then fill the formula down. For a list that should renumber from 1 regardless of its worksheet position, use =ROWS($A$2:A2) in A2 instead; both formulas return 1 in the first data row.
What row numbering means in Excel
Excel does not require a special numbering tool for ordinary lists: a formula can calculate each item’s position. The best formula depends on what “numbering” should mean. It might mean the row’s physical position on the worksheet, a sequence that begins at 1 in your list, a count of visible records, or a position within each group. These meanings can produce different results after you insert rows, filter data, or move the list.
The examples below assume Microsoft Excel for Microsoft 365 on Windows and use a header in row 1 with data beginning in row 2 unless noted. Place each formula in the first numbering cell, then copy or fill it down through the records. If your list begins elsewhere, adjust the starting row or anchor references to match.
Use ROW for worksheet-based numbering
The ROW function returns the row number of a reference. Without a reference, it returns the row number of the cell containing the formula. That makes it a quick choice when the desired numbers should reflect the worksheet’s row positions.
With a header in row 1 and the first record in A2, enter =ROW()-1 in A2. The result is 1 because A2 is row 2; in A3, the filled formula evaluates to 2. If the header is in row 4 and the first record is in row 5, use =ROW()-4 in A5 to begin at 1.
This formula is easy to understand, but it follows sheet rows rather than the list’s position. If you insert a blank worksheet row above the data, the calculated numbers may shift. If you sort or filter the records, the formula still reports physical row positions, so the visible items may not appear numbered consecutively from 1.
Use ROWS for a sequence tied to the list
For a sequence that starts at 1 in the first data row and counts down through a range, ROWS is often the clearest option. It counts how many rows are in a reference. The expanding reference makes the count increase as the formula is filled down.
With the first number in A2 and the list starting on row 2, enter =ROWS($A$2:A2) in A2. It returns 1. Fill the formula into A3 and the reference becomes $A$2:A3, which contains two rows, so the result is 2. In A4, the range contains three rows and returns 3.
The dollar signs keep the starting cell fixed while the second endpoint moves. This is called a mixed or expanding reference: the beginning stays anchored, but the range grows as you copy the formula. If the sequence starts in D7, use =ROWS($D$7:D7) in D7 and fill downward.
Fill the formula down the column
After entering a formula in the first numbering cell, use one of these methods to extend it:
- Drag the fill handle: Select the formula cell and drag the small square at its lower-right corner down the required rows.
- Double-click the fill handle: If a neighboring column has a continuous set of records, double-click the handle to fill alongside that data. Check the end of the filled range, especially if there are gaps in the neighboring column.
- Copy and paste: Copy the formula cell, select the destination cells, and paste. Relative references adjust for each row while absolute references remain fixed.
- Fill Down: Select the formula cell and the cells below it, then choose Home > Fill > Down. On Windows, Ctrl+D is the Fill Down shortcut; on a Mac, use the ribbon command or copy and paste if the keyboard shortcut differs in your setup.
Excel Tables can make this easier for regularly maintained data. If you add a formula to a table column, Excel generally treats it as a calculated column and applies the formula to the other table rows. To create a table, select the data and use Insert > Table. A table can also extend as you add records, although you should verify that its formula column has populated as expected.
Number rows in a dynamic array
If you need a standalone sequence rather than a formula copied into every row, Excel’s SEQUENCE function can spill numbers into neighboring cells. It is available in Microsoft 365 and Excel 2021 or later. This is useful for a planned range, a template, or a helper list whose length is known in advance.
With an empty area beginning in A2 and a requirement for ten numbers, enter =SEQUENCE(10) in A2. Excel spills the values 1 through 10 into A2:A11. The number 10 is the requested number of rows. If any cell in the spill area is occupied, Excel cannot place the full result and displays a spill error; clear the obstructing cells or choose another starting cell.
Unlike a copied ROWS formula, a spilled sequence is generated by one formula. You cannot edit an individual number inside the spill range separately from the formula. Also, a fixed sequence of ten numbers does not automatically know how many records are in a separate data list. Use a formula copied down a table column when numbering needs to accompany records as that table grows.
Number only rows that contain records
A simple ROW or ROWS formula returns a number even when the corresponding record row is blank. To leave the numbering cell blank until a chosen field is populated, use an IF test. This can make an entry sheet easier to read and avoid displaying numbers below the end of the current list.
With record identifiers in B2:B100 and numbering in A2:A100, enter =IF(B2<>"",ROWS($B$2:B2),"") in A2 and fill it down. If B2 contains an identifier, A2 displays 1; if B2 is empty, A2 displays a blank. The next populated record in a later row receives a count based on the number of rows in the reference, so this particular formula counts positions, including blank gaps.
If you want a consecutive count of populated cells even when there are gaps, use a running COUNTA calculation instead. With identifiers in B2:B100 and numbering in A2, enter =IF(B2<>"",COUNTA($B$2:B2),"") and fill down. For example, if B2 and B4 contain identifiers but B3 is blank, A2 returns 1, A3 stays blank, and A4 returns 2. COUNTA counts nonempty cells; a cell containing a formula that returns an empty string may still be counted, so use genuinely empty cells or account for that behavior if your source column contains formulas.
Number visible rows after filtering
Filtering hides records from view but does not change their worksheet row numbers. Consequently, =ROW()-1 will not produce a fresh 1, 2, 3 sequence for the visible results. For a running count that responds to a filter, use SUBTOTAL on a column that has a value for every record.
With an identifier in every populated cell of B2:B100 and the numbering formula in A2, enter =SUBTOTAL(3,$B$2:B2) and fill down. The function number 3 tells SUBTOTAL to count nonempty cells while excluding rows hidden by a filter. If the visible identifiers are on worksheet rows 2, 5, and 8, their displayed running numbers will be 1, 2, and 3.
There is an important distinction between filtered and manually hidden rows. Function number 3 excludes rows removed by a filter but includes manually hidden rows. Use 103 instead of 3 when manually hidden rows should also be excluded. The source column must be populated consistently; blanks in that column can make the displayed count stop increasing for that record.
Restart numbering within groups
Sometimes each category, department, or customer needs its own count beginning at 1. A formula can compare the current group label with the label in the row above. This method assumes the records are sorted so that each group’s rows are together.
With group labels in B2:B100, numbering in C2:C100, and B1 blank and C1 set to 0, enter =IF(B2=B1,C1+1,1) in C2 and fill down. The first record starts at 1 because its label differs from the blank cell above. If B2:B4 all contain “North,” the results in C2:C4 are 1, 2, and 3; when the next row’s label changes to “South,” its number resets to 1.
If you sort the data, keep each group together for the reset logic to remain meaningful. If the previous row’s group label is not the same, the formula begins a new count, even if that group appeared earlier elsewhere in the list. For a separate count of every occurrence of a category regardless of whether records are adjacent, a different counting approach is needed.
Formula numbering versus fixed numbers
Formulas are useful when the numbers should update as you add, remove, or rearrange records. They also make the numbering rule visible and reproducible. However, a formula may recalculate after a sort or row insertion, and different formulas follow different rules: ROW follows worksheet position, while ROWS follows the range’s position in the list.
Fixed numbers are better when each record needs a permanent identifier that must not change after sorting or deleting rows. A formula-generated sequence is not necessarily a durable ID: if you remove a record, the remaining formulas may close the gap or renumber. For stable identifiers, assign values and preserve them as data rather than relying on a formula based on row position. One way to freeze a current sequence is to copy the formula results and use Paste Special > Values, but those values will no longer update automatically.
Common problems and how to fix them
- The first number is not 1: Check the formula’s starting row. For ROW, subtract the row number immediately above the first data row; for ROWS, make the anchored first cell match the first data cell.
- Every row shows the same number: Check whether the range endpoint changes as the formula is filled down. In a ROWS formula, the first endpoint should be anchored and the second should be relative, as in
$A$2:A2. - Numbers skip after filtering: ROW reports physical worksheet positions. Use SUBTOTAL if the visible records need consecutive numbers.
- Blank records still receive numbers: Wrap the sequence formula in IF and test a column that reliably indicates whether a record exists.
- A SEQUENCE formula returns an error: Check for occupied cells in the spill range and make sure the Excel version supports dynamic arrays.
- Numbers change after sorting: That is expected for formulas based on row position. Use fixed values for identifiers that must remain unchanged.
Frequently Asked Questions
What is the simplest Excel formula to number rows?
With a header in row 1 and the first record in row 2, enter =ROW()-1 in A2 and fill down. It returns the worksheet row number minus one.
How do I start row numbering at 1 when my list begins lower down?
With the first number in D7, enter =ROWS($D$7:D7) in D7 and fill down. The first result is 1 regardless of the worksheet row number.
Can Excel number only nonblank rows?
Yes. With record values in B2:B100 and numbering in A2, use =IF(B2<>"",COUNTA($B$2:B2),"") and fill down. It leaves blank rows unnumbered and counts populated entries.
How do I number filtered rows consecutively?
With a value in every record cell in B2:B100 and the formula in A2, use =SUBTOTAL(3,$B$2:B2) and fill down. It counts visible, nonempty cells through the current row.
Can I number rows without dragging a formula?
In Microsoft 365 or Excel 2021 and later, enter =SEQUENCE(10) in an empty cell to spill the numbers 1 through 10. Change 10 to the number of rows you need.
Choosing the right formula
For a basic list, choose ROWS when you want numbering to begin at 1 at the first data row, even if the list is lower on the sheet. Choose ROW when the number should correspond to the worksheet position. Use IF with a record field to suppress numbers on empty rows, SUBTOTAL for visible filtered records, and a group-comparison formula when counts must restart by category. Use SEQUENCE when you need a compact, automatically spilled series rather than a formula repeated down a data column.
Before filling any formula across a large range, test it in the first few records and check how it behaves with a blank row, a sort, or a filter if those actions matter to your workbook. That small check helps ensure the numbers represent the intended order instead of merely looking correct in the original view.
Related
- PHP Backend Data Validation: A Practical Guide
- Protect Sheet in Excel: Allow Editing in Certain Cells
- Jira Merge Table Cells: Steps and Limitations
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Copy a Formula in Excel to Other Cells SafelyTo copy a formula in Excel, select the cell that contains it, then drag its fill handle into…
- SUMIF Excel Formula: Examples, Syntax, and Common FixesTo add values that match one condition, use SUMIF: with regions in A2:A6 and sales in B2:B6, =SUMIF(A2:A6,"East",B2:B6)…
- When VLOOKUP Does Not Work: Common Causes and FixesVLOOKUP usually fails because its lookup value does not exactly match the first column of the selected range,…
- XLOOKUP Case Sensitivity: How Excel Matches Text ValuesNo. In Excel for Microsoft 365, XLOOKUP is not case-sensitive: it treats text such as “Maple,” “MAPLE,” and…
- Excel Formula Bar Disappeared? How to Show It AgainIf the Excel formula bar disappeared, turn it back on from View > Show > Formula Bar in…
- VLOOKUP Formula to Compare Two Sheets in Excel EasilyTo compare values on two Excel sheets by a shared ID, enter this formula on the first sheet,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.