How to Make Serial Numbers in Excel: 4 Easy Methods

To make serial numbers in Excel, enter 1 and 2 in the first two cells, select both, then drag the fill handle down to continue the series. For numbering that updates with row positions, use a formula instead. The right method depends on whether you want fixed numbers or a sequence that changes as your worksheet changes.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows, or Excel for Mac or the web. The basic fill methods work in all three.
A worksheet with a clear place for the serial-number column. If your data has headers, leave the header row for a label such as “No.” or “Serial Number.”
For a formula that skips numbering blank records, a column that reliably indicates whether each row contains a record, such as a name or item description.

You do not need an add-in or special formatting for ordinary sequential numbers. Save a copy first if the worksheet contains important data or complex formulas.

Step-by-Step

Choose whether the numbers should be fixed or dynamic. Fixed numbers are stored as values and generally stay the same when rows are moved or sorted with the rest of the record. They work well for a completed list, printed checklist, or inventory sheet where each existing row needs a simple label. Formula-based numbers recalculate from the current row or generate a changing sequence; they are useful when your list will grow, but the number associated with a record can change after a sort or row insertion. If you mean a permanent product identifier that must never change or be reused, an ordinary row-numbering sequence may not be suitable; assign and preserve identifiers as values instead.
Make a quick fixed sequence with the fill handle. Click the first cell in the serial-number column, such as A2 if row 1 contains headings, and type 1. In the cell directly below, type 2. Select both cells. Point to the small square at the lower-right corner of the selection until the pointer becomes a plus sign, then drag down to the last row you want numbered. Excel recognizes the 1, 2 pattern and continues it as 3, 4, 5, and so on. Check the last value before moving on. If you drag only the cell containing 1, Excel may copy 1 repeatedly rather than infer a series.
Use Fill Series when you want a specified range. Enter 1 in the first cell where numbering should begin. Select the full range to fill, including that first cell. On Windows, choose Home > Editing > Fill > Series. In the Series dialog, choose Columns if the selected cells run down a column, leave the series type as Linear, and set the step value to 1. Set a stop value if you want a particular final number, then select OK. For example, selecting A2:A21 and setting a step of 1 and a stop value of 20 fills the 20 cells with 1 through 20. The fill handle is usually faster for a short list; Fill Series is handy when you already know the range or want a different step, such as 5.
Create row-based numbers with a formula. With a heading in A1 and the first record on row 2, click A2 and enter =ROW()-1. This layout returns 1 in A2 because the formula uses the worksheet row number, 2, then subtracts 1. Copy the formula down the column by selecting A2 and dragging its fill handle, or select the destination range and use Home > Fill > Down. On Windows, Ctrl+D fills the selected cells down from the top cell; Excel for Mac also supports Control+D in many keyboard layouts. If that shortcut is unavailable on your Mac, use the ribbon command. This formula follows row positions: a record moved to another row can receive a different number.
Keep blank records unnumbered, if needed. If column B contains a required item name and A is the serial-number column, put this formula in A2, with headers on row 1 and records starting on row 2: =IF(B2="","",ROW()-1). The formula returns a blank when B2 is empty and returns 1 when B2 contains an item. Copy it down as far as you expect the list to extend. Replace B2 with the cell in your own record-indicator column. This prevents empty rows from displaying numbers, but the number still reflects the worksheet row; it does not close gaps in numbering after a record is deleted.
Generate a whole sequence with SEQUENCE. In Microsoft 365, click the first cell where the sequence should appear and enter =SEQUENCE(10). This layout assumes you want ten numbers beginning at 1 in the cells below the formula; it spills the values 1 through 10 into the cells underneath. Change 10 to the number of values you need. To start at a different number or use a different increment, the function can take additional arguments: rows, columns, start, and step. For example, =SEQUENCE(5,1,10,2) spills five values down one column, starting at 10 and increasing by 2, so the results are 10, 12, 14, 16, and 18. SEQUENCE is available in Microsoft 365 and Excel 2021 or later, including Excel for the web. If the cells in the spill area are not empty, Excel cannot display the full result.
Check the result and keep the data together. Confirm that numbering begins where intended, increases by one (unless you chose another step), and ends at the right record. If you sort the worksheet, select the entire data range or use a proper Excel table so each serial number stays with its associated record. Sorting only the serial-number column can mismatch the numbers and the data. Save the workbook after checking it.

For many everyday lists, the two-cell fill-handle method is the simplest and produces fixed values. Use ROW when numbering should reflect physical row positions, and use SEQUENCE when you want Excel to generate a known number of results from one formula.

Common Mistakes

Dragging one starting value and getting repeats: Excel may copy a single 1 instead of extending a series. Enter 1 and 2, select both, and drag; or use the fill options to choose a series.
Numbering the heading: Start the fill or formula in the first data row, not in the header cell. If row 1 is a heading, A2 is usually the first number cell.
Sorting only the serial numbers: This separates numbers from their records. Select the full table before sorting, and confirm that Excel recognizes the correct header row.
Expecting a formula to create permanent IDs: ROW-based formulas depend on position and can change when rows are inserted, deleted, or sorted. Convert results to values only when appropriate, or use a process that assigns identifiers once and preserves them.
Overwriting an existing sequence: Check the destination range before filling. A fill operation can replace cells that already contain data, so undo immediately with Ctrl+Z on Windows or Command+Z on Mac if you overwrite something by mistake.

Tips

Use a meaningful heading. Label the column “No.” or “Serial Number” so readers know it is a row label, not a quantity or item code.
Turn a growing list into a table. Select the data and choose Insert > Table, ensuring the header option is set correctly. Tables make it easier to sort and filter complete records. They do not turn row numbers into permanent identifiers, so decide separately how new records should be numbered.
Choose a formula based on the desired behavior. ROW is easy to fill down but follows the worksheet row. SEQUENCE is convenient for a fixed-length generated list but may spill into cells you need for other data. For a small, finished list, fixed values avoid recalculation surprises.
Format numbers only if the display needs it. Select the serial-number cells and use Home > Number to choose a number format. A custom format can show leading zeros, but the underlying value remains numeric. If identifiers must retain leading zeros when exported or used as codes, consider storing them as text and check the requirements of the destination system.
Extend formula numbering carefully. If you enter a formula in a table column, Excel may offer to fill the calculated column automatically. Review the suggested fill and check a few records; do not assume a formula has reached rows outside the table.
Keep a reusable blank template tidy. If you prefill formulas far below the current data, blank-record logic can keep the cells looking empty. However, the formula still exists in those cells, so consider whether that affects printing, file size, or other calculations.

Troubleshooting

The fill handle is missing or hard to use. Check Excel’s fill-handle setting in File > Options > Advanced under editing options, and make sure Enable fill handle and cell drag-and-drop is selected. On Mac, check Excel settings or preferences for editing options; the exact label can vary by release. You can use Home > Fill > Series instead of dragging.

Excel repeats the same number. If you started with one selected cell, choose the fill options button that appears after dragging and select Fill Series, or undo and start again with two seed values, 1 and 2. Also check that you did not select a range containing only the first value when expecting Excel to infer a pattern.

The formula displays as text or does not calculate. Make sure the cell is not formatted as Text. Change its format to General or Number, then edit the cell and press Enter to recalculate it. Check that the formula begins with an equals sign and that there is no apostrophe before it. If formulas display throughout the sheet, the worksheet may have Show Formulas enabled; toggle it from the Formulas tab or check the workbook’s formula-display setting.

SEQUENCE returns a spill error. Clear any values, formulas, merged cells, or other obstruction from the cells where the results should appear. A ten-row sequence needs ten available cells, starting with the formula cell. If your Excel version does not recognize SEQUENCE, use the fill handle or Fill Series instead.

There are gaps or the numbering changes after editing. A deleted row can leave a gap in fixed numbers, while ROW formulas can change as the worksheet layout changes. Decide whether gaps are acceptable. If you need a refreshed 1, 2, 3 sequence after filtering or editing, use an approach designed for that specific layout; do not treat a row-position formula as an immutable identifier.

Frequently Asked Questions

How do I make serial numbers automatically in Excel?
Enter 1 and 2 in consecutive cells, select both, and drag the fill handle to extend a fixed sequence. For a formula-based sequence, enter =ROW()-1 in A2 when row 1 is the heading, then fill it down. The formula updates according to row position.

Can I start serial numbers from 100 or another number?
Yes. For fixed values, enter 100 and 101 in the first two cells, select both, and drag. With Microsoft 365 or Excel 2021 or later, a one-column sequence of five values starting at 100 is =SEQUENCE(5,1,100,1). It returns 100 through 104.

How do I number only rows that contain data?
Use an IF formula that checks a required field, such as the item name in column B. The blank-skipping example above leaves the serial cell empty when B is empty. Copy it down to cover the rows you may use; it does not automatically remove gaps in the row-based numbering.

Will serial numbers update when I insert a row?
Fixed values do not necessarily renumber themselves just because a row is inserted. A ROW formula recalculates based on the new worksheet position, and a SEQUENCE formula updates according to its arguments and available spill area. Choose based on whether you want stable values or a changing sequence.

Can I use these steps in Excel for Mac or Excel for the web?
The fill handle works in Excel for Mac and the web, although ribbon labels or dialog placement can differ slightly from Windows. SEQUENCE is available in current Microsoft 365 versions of Excel for Windows, Mac, and the web, and in Excel 2021 or later. If a feature is missing from your version, use the two-value fill-handle method.

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.