Serial Numbers in Excel: Automatic and Manual Methods

To add serial numbers in Excel, enter 1 and 2 in the first two cells of a numbering column, select both cells, then drag the fill handle down. For a formula that adjusts to the row, put =ROW()-1 in A2 when row 1 is a header and fill it down. Choose a method based on whether you need a quick list, numbering that grows with your data, or fixed numbers that stay attached to each record.

Serial numbers can identify records or simply show their current order. That difference matters: a row-based number changes when records are sorted or moved, while a number you store as a fixed value can serve as a lasting ID. The steps below cover both approaches in Excel for Microsoft 365 on Windows, with notes for Excel for the web and Mac where useful.

What You’ll Need

An Excel workbook with the list or table you want to number.
A blank column for the serial numbers, preferably with a clear heading such as Serial No.
A decision about whether the numbers should show row order or act as permanent record IDs.

You do not need an add-in or a special setting. If you are working with important data, save a copy of the workbook first, especially before sorting or replacing formulas with values.

Step-by-Step

Choose where numbering will go. Add a blank column beside your data, and type a heading such as Serial No. in its first row. For example, if your headings are in row 1, enter the first serial number in A2 and keep the associated record in the same row. Do not put serial numbers inside a merged cell or in the middle of another column’s data.
Use the fill handle for a quick numbered list. Type 1 in A2 and 2 in A3, then select both cells. Point to the small square at the lower-right corner of the selection until the pointer becomes a plus sign. Drag down to the last row you want numbered and release. Excel recognizes the pattern and continues it: the next values are 3, 4, 5, and so on. Starting with two values is important; dragging a single 1 usually copies 1 rather than continuing a series.
Check the last number and the alignment. Confirm that the final number matches the number of records you meant to include. If row 1 contains headings and there are 25 data rows, the last serial number should be 25. Check that each serial number sits beside the correct record, especially if the list already contains blank rows or was previously sorted.
Use the Series command when you know the exact range. Select the cells you want to fill, beginning with the cell for the first number. In Excel for Microsoft 365 on Windows, choose Home > Fill > Series. In the dialog, choose Columns if the selected cells run downward, select Linear, set Step value to 1, and enter a Stop value if you want a limit. For example, a stop value of 25 fills numbers through 25. Select OK. If you select a specific range first, Excel fills that range; the stop value provides an additional limit rather than a reason to include extra rows.
Use a row formula for numbering that follows position. With a heading in A1 and the first record on row 2, put =ROW()-1 in A2. It returns 1 in row 2, 2 in row 3, and 4 in row 5. Use the fill handle to copy the formula down, or double-click the handle if the neighboring data column is filled continuously; Excel usually fills to the end of that adjacent data. If your first data row is not row 2, adjust the subtraction. For example, a list beginning on row 5 would need a formula that makes row 5 return 1.
Generate a set with SEQUENCE. If you are using Microsoft 365 or Excel 2021 and want a specific count, place the formula in the first cell where the numbers should appear. With A1 used for the heading and A2:A11 available, enter =SEQUENCE(10) in A2. It spills the numbers 1 through 10 into the cells below. Make sure the spill area is empty; existing content can block the result. To create a different count, change 10 to the number of serials you need. You can also use SEQUENCE’s optional arguments for a starting number or interval, but the one-argument version is enough for ordinary numbering.
Number only rows that contain records, if needed. If column B contains a value for every record and may have blank rows between records, you can leave the serial cell empty when its corresponding B cell is empty. With the first record in B2 and the serial number in A2, enter =IF(B2="","",COUNTA($B$2:B2)) in A2 and fill it down. For example, if B2 and B4 contain names but B3 is blank, A2 shows 1, A3 stays blank, and A4 shows 2. This is useful for simple lists with genuinely empty cells. If column B contains formulas that display blank text, COUNTA may count those formula cells; use a different source column or adjust the approach for that data.
Decide whether to keep formulas or fixed values. Formulas are convenient when you expect to add rows or change the list. If the numbers are permanent IDs that must not change when rows are inserted or sorted, select the numbered cells, copy them, and choose Home > Paste > Paste Values. In Excel for the web, use the Paste options to choose Values when available. Check the pasted numbers before removing the original formulas. A fixed value is not automatically a unique ID, so verify that no duplicate numbers were introduced.
Sort the complete data range together. If you sort the list, include the serial number column and every related data column in the selection. Use Data > Sort, or select a cell in the data and use a sort command, then confirm that the range expands to include the whole record. Sorting only the serial column separates numbers from their records. A formula based on ROW will recalculate to show the new position after a sort; fixed values will move with the rows if the whole range is sorted together.

Common Mistakes

Dragging one starting value. If you enter only 1 and drag, Excel may repeat 1. Enter 1 and 2, select both, and then drag to establish the increment. If the result still repeats, use the fill options button that appears after dragging and choose Fill Series rather than Copy Cells.

Numbering the header. Starting in row 1 can place a number beside the headings or overwrite a heading. Keep the label in the top cell and begin numbering on the first data row. Adjust a row-based formula so that its first result is 1, not 2 or the physical worksheet row number.

Sorting just one column. This can pair each serial with the wrong name or item. Sort the entire contiguous table, not an isolated serial-number column. If Excel asks whether to expand the selection, choose the option that includes adjacent data.

Expecting a position number to be a permanent ID. A formula based on ROW identifies a position, not a record. After sorting, inserting rows, or deleting records, its result can change. For a permanent ID, store fixed values and avoid reusing an ID after deleting a record.

Filling into the wrong number of rows. Dragging too far can create serial numbers beside empty records; stopping too early leaves some records unnumbered. Use the visible end of the list as a guide, or select the exact range before using Home > Fill > Series.

Tips

Turn a regular list into an Excel table with Insert > Table if you add records often. A table helps keep the data together when it expands, but do not assume every numbering method behaves identically in every table; check the new row after adding it.
Use a descriptive heading and consistent number format. For ordinary serials, General or Number with zero decimal places is usually clear. A custom format can display leading zeros, but the underlying value remains numeric.
If you need codes such as 0001, 0002, and 0003, format the cells with a custom format such as 0000 rather than typing text by hand. Select the cells, open Format Cells, choose Custom, and enter the desired number of zero placeholders. This preserves numeric values for sorting and calculations.
For a long adjacent list, double-click the fill handle after entering a formula or selecting the initial pattern. Excel uses the neighboring column to estimate how far to fill. Review the end of the filled range because blanks in that neighboring column may cause it to stop early.
Keep the record data and its serial number in one row. When sharing or exporting, check that the intended values—not an incomplete or blocked spill formula—are present in the file.

Troubleshooting

The fill handle is missing: The fill handle may be disabled. In Excel for Windows, open File > Options > Advanced and under Editing options make sure Enable fill handle and cell drag-and-drop is selected. Select OK and try again. On Mac, Excel’s preferences and settings are under Excel > Settings or Excel > Preferences, depending on the version; look for the editing or fill-handle option.

The numbers repeat instead of increasing: Undo the fill if needed, enter 1 and 2 in adjacent cells, select both, and drag again. If Excel offers an Auto Fill Options button, choose Fill Series. For the Series dialog, confirm that Step value is 1 and that the selected direction is Columns for a vertical list.

The formula shows a number that is too large: Check the starting row and the formula’s offset. With data starting in row 2, =ROW()-1 returns 1 there. If you placed it in row 4, it returns 3, because the formula counts worksheet rows minus one. Change the offset to match your actual first data row, then fill down again.

SEQUENCE displays a spill error: Clear any values, spaces, or merged cells in the cells where the results need to spill. Confirm the formula is entered in only the first cell of the output range. If your Excel version does not support SEQUENCE, use the two-cell fill method or Home > Fill > Series instead.

Some records have no number: Check whether the fill ended early, whether a blank in the neighboring column interrupted a double-click fill, or whether a formula such as the nonblank-row example is intentionally returning an empty result. Extend the series to the final record and review rows with blank source data.

Numbers no longer match records: Undo the sort immediately if possible, or restore a saved copy. Then sort the entire data range together. If you cannot undo the change, use another identifying field to reconstruct the correct record associations; do not assume the serial column alone can restore them.

Frequently Asked Questions

How do I automatically number rows in Excel?
Enter =ROW()-1 in A2 when row 1 is a heading, then fill the formula down. It gives each row a number based on its current position. For a specific number of results in Microsoft 365 or Excel 2021, =SEQUENCE(10) generates 1 through 10 from the cell where you enter it.

How do I continue numbering after 1?
Enter 1 and 2 in the first two cells, select both, and drag the fill handle down. Excel recognizes the step of 1 and continues it. You can also select the destination range and use Home > Fill > Series with a step value of 1.

Can Excel skip blank rows when numbering?
Yes. If a source column is blank for empty records, use the IF and COUNTA method above and fill it down. It leaves the serial cell blank when the source cell is blank and counts populated source cells. Check first whether the source cells are genuinely empty rather than formulas returning blank text.

Will serial numbers change when I sort the list?
Numbers made with a row formula show the current row position and can change after a sort. Fixed numbers do not recalculate, but they must be sorted with all the related columns so each number stays beside its original record.

How can I show leading zeros in serial numbers?
Select the number cells, open Format Cells, choose Custom, and enter a format such as 0000. The value 7 will display as 0007 while remaining numeric. If the serials must be literal text codes, decide that before using them in calculations or sorting.

Can I use these steps in Excel for Mac or the web?
The fill handle, formulas, and general numbering approach also work in Excel for Mac and Excel for the web. Ribbon layout and keyboard shortcuts can differ, and some desktop settings are in different places. If a command is not visible, use Excel’s search box to find Series or the relevant fill command; SEQUENCE is available only in Microsoft 365 and Excel 2021 or later.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.