How to Add Serial Numbers in Excel Automatically

How-to basics · Updated November 20, 2026

To add serial numbers in Excel, enter 1 and 2 in the first two cells of a number column, select both cells, then drag the fill handle down. Excel continues the sequence. For numbering that adjusts with rows, use a formula; for a quick generated list in Microsoft 365, use SEQUENCE. These steps are for Excel for Microsoft 365 on Windows.

What You’ll Need

  • An Excel worksheet with a column available for the serial numbers. If your data has headings, put a heading such as Serial No. in the top cell of that column.
  • A clear idea of whether the numbers should be fixed identifiers or should change when rows are inserted, deleted, or reordered. This determines whether to use filled values or a formula.
  • For the SEQUENCE method, Microsoft 365 or Excel 2021 or later. The fill handle and the basic ROW and ROWS formulas also work in older desktop versions of Excel.

Step-by-Step

  1. Prepare the serial-number column. Open the worksheet and identify the rows that need numbering. If row 1 contains headings and your first record is in row 2, enter Serial No. in A1, or use another empty column if A is already in use. Keep the serial number in the same row as the record it identifies. If you plan to sort the data, make sure the serial number and all related columns are part of the same data range.
  2. Use the fill handle for a simple, fixed sequence. Type 1 in A2 and 2 in A3, assuming A1 is the heading and the first record is on row 2. Select A2:A3. Point to the small square at the lower-right corner of the selection until the pointer becomes a thin black plus sign, then drag down to the last row you want to number. Excel recognizes the difference between 1 and 2 and continues 3, 4, 5, and so on. When you release the mouse, check the last number and confirm that each number lines up with the intended record.
  3. Use the Fill command when you know the exact range. Enter 1 in A2, then select A2:A101 if you need 100 serial numbers and row 1 is a heading. On the ribbon, choose Home > Fill > Series. In the Series dialog, choose Columns, keep Linear selected, set Step value to 1, and set Stop value to 100. Select OK. Excel fills the selected cells with 1 through 100. The first selected cell supplies the starting value, so do not leave A2 blank. If you need a different starting number, enter that number in A2 and use the matching stop value.
  4. Use a formula when numbering should follow worksheet rows. With a heading in A1 and the first record on row 2, enter this in A2: =ROW()-1. It returns 1 because A2 is on worksheet row 2. Copy the formula down by dragging its fill handle, or select the destination cells and use the Fill command. Each row calculates its own position, so the results continue as 2, 3, 4, and so on. If your first record starts on a different row, change the subtraction: for example, in A5 with the first record on row 5, use =ROW()-4.
  5. Choose a formula that starts at 1 regardless of the worksheet row. If you may move the numbering column or start the list at a different row, use a relative range instead. With the first serial number in A2, enter =ROWS($A$2:A2) in A2 and copy it down. The first formula counts one row; when copied to A3, its end reference changes and it counts two. This makes the first result 1 even if the number column is not positioned at the top of the worksheet. Keep the first reference anchored at A2 when copying.
  6. Generate a list with SEQUENCE. For a list of 100 numbers beginning at 1, leave A2:A101 empty and enter =SEQUENCE(100) in A2. Excel spills the results into the cells below, displaying 1 through 100. Change 100 to the number of serials you need. SEQUENCE can also start elsewhere: with the output starting in A2 and a need for 100 numbers beginning at 501, use =SEQUENCE(100,1,501). This function is available in Microsoft 365 and Excel 2021 or later. Do not type into the cells where the results need to spill.
  7. Decide whether the serials should remain fixed. Filled numbers are values, so they do not recalculate when the rows change. Formula results can change when the list is edited or reordered. If formula-based numbering is correct but you need to preserve the current numbers, copy the numbered cells and use Paste Special > Values. The ribbon path is Home > Paste > Paste Values. Keep a copy of the workbook before replacing formulas if you may need to restore them.

On Excel for Mac, the fill handle and these formulas work in much the same way. Ribbon wording or layout can vary slightly by release, so use the Home tab and look for Fill or Series. Excel for the web supports the fill handle and formulas such as ROW and SEQUENCE, but some dialog layouts differ from the Windows desktop version.

Common Mistakes

  • Dragging only one number. If you enter 1 in one cell and drag its fill handle, Excel may copy 1 rather than create a sequence. Enter 1 and 2, select both, and then drag. Alternatively, use the Series dialog or a formula.
  • Starting from the wrong row. The formula =ROW()-1 assumes the first numbered cell is in row 2. If you put it in row 3, the first result is 2. Adjust the subtraction to match the starting row, or use the ROWS method shown above.
  • Including the heading in the series. Select only the cells that need numbers. If you include the heading cell, Excel may replace it or create an unwanted value. Keep the heading separate from the fill range.
  • Sorting only one column. Sorting the serial-number column by itself separates numbers from their records. Select the full table or accept Excel’s prompt to expand the selection before sorting. If serial numbers are intended as fixed IDs, do not renumber them just because the display order changes.
  • Overwriting cells in a SEQUENCE spill range. A dynamic array needs empty cells below or beside the formula cell. Clear the obstructing cells, or choose a different starting cell before entering the formula.

Tips

For a short list that will not change, the fill handle is usually the quickest choice. For a maintained list, a formula is easier to extend and can reduce manual errors. SEQUENCE is convenient when you want a specific count in one step, but remember that its output depends on the space available for the spill range.

Serial numbers are not automatically the same as permanent record IDs. A row-based formula describes a position in the current list; it may produce a different number after rows are inserted, deleted, or sorted. If a number must remain attached to a particular customer, order, or item permanently, use fixed values and avoid regenerating them when the rows move.

If your data has blank rows and you only want a number beside populated records, base the numbering on a column that is always filled for each record. With the first record in row 2 and its required data in B2, enter =IF(B2="","",COUNTA($B$2:B2)) in A2 and copy it down. This displays a blank when B is blank and counts populated entries in B for numbered rows. If column B is not consistently filled, choose a more reliable column or the formula may skip records.

Before filling a long list, check the starting cell, last row, and expected final number. For example, 100 records numbered from 1 should end at 100. If the worksheet is a formatted Excel Table, adding records may extend its formatting and formulas, but verify the numbering behavior when you append rows. A standard filled-value sequence does not automatically create new numbers in rows added later.

Troubleshooting

  • The fill handle copies the same number. Make sure you selected two starting cells containing 1 and 2, not just the cell containing 1. If you want to keep one starting cell, enter it, drag down, and use the Auto Fill Options button to choose Fill Series if that option appears. The Series dialog is another dependable way to specify a linear sequence.
  • The formula displays as text instead of a number. Select the affected cells and check Home > Number Format. Change Text to General, then re-enter the formula in the cell; changing the format alone may not convert text already entered. Also check that the formula begins with an equals sign and that there is no apostrophe or leading space before it.
  • The sequence has gaps or the final number is wrong. Look for blank cells or missing records in the range, and compare the selected range with the number of records. A formula based on worksheet rows counts positions, not necessarily filled records. If using Series, verify the starting value, Step value, and Stop value in the dialog.
  • SEQUENCE returns a spill error or does not fill down. Clear any values, formulas, or merged cells in the intended output area, then re-enter the formula in the top-left cell. Confirm that the Excel version supports SEQUENCE. If it does not, use the fill handle, the Series dialog, or a ROWS formula instead.
  • Numbers change after sorting or deleting rows. This is expected for row-based formulas: the formula calculates a position, not a permanent ID. If the displayed numbers should be preserved, copy the results and paste values before changing the list. When sorting, always include the entire data range so records stay together.

Frequently Asked Questions

How do I add serial numbers automatically in Excel?
Enter 1 and 2 in adjacent cells, select both, and drag the fill handle. For a formula-driven list, enter =ROWS($A$2:A2) in A2, assuming the first serial number belongs in A2, and fill it down.

How can I number rows without dragging?
Enter the first number in the top cell of the range, select the full range, and use Home > Fill > Series. Set the series to Columns, Linear, and a step of 1. For a fixed count in a supported version, enter =SEQUENCE(100) in the first cell to generate 100 numbers.

Can Excel number only rows that contain data?
Yes. Use an IF formula that checks a consistently populated data column and returns a number only when that cell is not blank. For example, the formula in the Tips section checks column B. Copy it down far enough to cover the rows you expect to use.

Will serial numbers update when I add or remove rows?
Filled values do not update by themselves. A row-based formula recalculates according to the current positions, and a SEQUENCE formula produces the number of results specified in its arguments. For a list that grows, extend the formula or increase the SEQUENCE count, then check the results.

Can I keep serial numbers unchanged when I sort?
Yes. Use fixed numbers and sort the whole data range, not just the serial-number column. If your numbers come from formulas and must become permanent, copy them and paste values before sorting or editing the list.

Why does my sequence start at 0 or at the wrong number?
Check the first value and the formula’s starting row. In SEQUENCE, the first argument is the number of rows and the third argument sets the starting value; without a third argument, the sequence begins at 1. With ROW, the subtraction must match the row where numbering starts.

Related

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.