To create a numbered list in Excel, enter 1 and 2 in the first two cells, select both, and drag the fill handle down; Excel continues the sequence. For numbering that recalculates when you add rows, use a formula such as =ROW()-1 when the header is in row 1 and numbering starts in A2. The steps below cover both approaches, plus how to preserve serial numbers that are product codes or identifiers rather than row numbers.
Before you start, decide whether you need an ordered sequence for a list or need to record existing serial-number codes. A sequence such as 1, 2, 3 is easy to generate. A code such as 00047 or AB-0047 should usually be stored as text so Excel does not remove leading zeros or change the value.
What You’ll Need
- A workbook in Microsoft Excel for Microsoft 365 on Windows. The basic fill-handle and formula methods also work in Excel for Mac and Excel for the web.
- A worksheet with a column available for the numbers. Add a header such as Serial Number if the column is part of a data list.
- A clear idea of whether numbers should stay attached to particular records or simply show each record’s current position.
Step-by-Step
- Set up the column. Open the worksheet and choose an empty column beside the records you want to number. If row 1 contains headings, type a heading such as Serial Number in the top cell, for example A1. Keep the numbering column beside its matching names, products, or other data so each number remains associated with the right record when you sort the whole table.
- Enter the first two numbers. Type 1 in A2 and 2 in A3, assuming your heading is in A1. Use consecutive values even if your eventual sequence will increase by a different amount; for a sequence such as 10, 20, 30, enter 10 and 20 as the two starting values. Two examples give Excel a pattern to continue. If you enter only one value and drag the fill handle, Excel may copy that value rather than count upward.
- Select the pattern cells. Select A2:A3, including both numbers. Point to the small square at the bottom-right corner of the selection, called the fill handle. The pointer changes when it is positioned over the handle. Selecting the two starting values is important: it tells Excel both the starting point and the interval between numbers.
- Drag to fill the remaining rows. Drag the fill handle down to the last row that needs a number, then release the mouse button. Excel should fill the selected range with 1, 2, 3, and so on. Check the first few results and the final one before using the list. If your data already occupies a continuous range, you can drag to its last row; avoid dragging far beyond the records, or you will create extra numbered cells.
- Use the Series command for a controlled fill. On Windows desktop Excel, enter the first number in the starting cell and select the range you want to fill, including that cell. Choose Home > Fill > Series. In the Series dialog, choose whether to fill in rows or columns, select Linear for a regular counting sequence, and set the Step value and Stop value. For example, with A2 as the starting cell, a step of 1, and a stop value of 50, the series runs from 1 through 50. Select OK to fill the chosen range. This method is useful when you know the interval or ending number and want to avoid dragging.
- Choose a formula if the numbering should recalculate. With a heading in A1 and the first number needed in A2, enter
=ROW()-1in A2. It returns 1 because A2 is worksheet row 2. Copy the formula down by dragging the fill handle or copying A2 and pasting it into the cells below. Each row calculates its own position, so the values update if rows are inserted above the numbered records. This is a positional numbering method, not a permanent ID for a particular record. - Generate a fixed-length sequence with SEQUENCE. In Excel for Microsoft 365 or Excel 2021 and later, with a heading in A1 and an empty range below it, enter
=SEQUENCE(100)in A2 to return the numbers 1 through 100 in a vertical spill range. Leave the cells beneath A2 empty so the results can spill into them. To create ten numbers starting at 1001 and increasing by one, enter=SEQUENCE(10,1,1001,1)in A2; the results run from 1001 through 1010. SEQUENCE is not available in older Excel versions, so use the fill handle or Series command there. Excel for the web supports SEQUENCE in current Microsoft 365 accounts, but if the function is unavailable in your account, use the fill-handle method. - Check and save the result. Compare the number of filled cells with the number of records. Look for blanks, duplicate values, or numbers extending past the last record. Save the workbook when the sequence is correct. If you plan to sort, select the full data range, including the serial-number column, before sorting so records and their numbers stay together.
Common Mistakes
Dragging a single number and expecting a series. A single value can be copied repeatedly. Start with two consecutive values, or use Home > Fill > Series to specify the step. If you already filled repeated values, undo immediately with Ctrl+Z on Windows or Command+Z on Mac, then repeat with two starting cells.
Filling too far or not far enough. A numbered cell below the final record can look like part of the list, while a missing final number may be overlooked. Check the last data row before filling, and verify that the selected range ends there. When the list will grow often, convert the data range into an Excel table using Insert > Table; tables can expand as you add records, though a manually filled series does not automatically guarantee a new number for every added row.
Using a row formula as a permanent identifier. =ROW()-1 reflects a cell’s current worksheet row. Sorting or moving records can change which record displays a given number, and deleting a row can leave gaps in values that were entered as constants. Decide whether you want a changing position label or a stable ID. For stable IDs, assign values once and keep them with their records rather than recalculating them from row position.
Letting Excel alter an actual code. Excel may remove leading zeros from numeric-looking entries or display long numbers in scientific notation. A serial number is often an identifier, not a quantity for arithmetic. Format the cells as Text before entering or importing codes, or use an appropriate display format when the codes have a fixed length.
Tips
Use a descriptive heading so other people can tell what the column means. If the numbers represent record order, a heading such as Row No. or List Number can distinguish them from manufacturer serial numbers. Keep the numbering column adjacent to the records, and select the entire table when sorting or filtering.
For a one-time list, fill the sequence and then treat the results as values. If you need the formula results to stop changing with row position, copy the formula cells and use Home > Paste > Paste Values. This replaces formulas with their current displayed numbers. Do this only after confirming the numbers are correct, because the cells will no longer recalculate.
For actual inventory codes with leading zeros, select the destination cells and choose Home > Number Format dropdown > Text before typing or pasting. If a code is fixed at six digits and contains only numbers, a custom number format such as 000000 can display 47 as 000047, but the stored value remains the number 47. Text is safer when codes may contain letters, vary in length, or must be preserved exactly. Formatting after Excel has already removed zeros will not restore them automatically.
On Windows, Ctrl+D fills the selected range with the contents of its top cell, but it copies the value or formula; it does not infer a counting pattern from one number. On Mac, Control+D is the corresponding Fill Down shortcut in Excel. If you prefer not to use shortcuts, choose Home > Fill > Down. The Series dialog is available in desktop Excel; Excel for the web may not show the same dialog, so use the fill handle or a supported formula there.
Troubleshooting
The same number appears in every cell. Undo the fill and confirm that you selected two different starting values before dragging. Alternatively, select the destination range and use Home > Fill > Series, then set Linear and a step of 1. If you used a formula, check that you copied it down rather than copying a fixed value.
The formula appears as text instead of calculating. Check whether the cell is formatted as Text or whether the formula has an apostrophe or space before the equals sign. Change the cell format to General, edit the cell, and press Enter. A formula should begin with an equals sign, such as =ROW()-1. Also confirm that Formulas > Show Formulas is not turned on; when enabled, Excel displays formulas in cells instead of their results.
The numbers skip, repeat, or have gaps. Inspect the two seed values and the Series dialog’s Step value. Check whether rows were deleted after numbers were entered as fixed values. A formula based on ROW can recalculate positions, but it will not automatically renumber every kind of manually entered list. If you use filters, remember that ordinary row numbering does not automatically count only visible records. Decide whether gaps are acceptable or rebuild the sequence after the data is finalized.
A new table row has no number. If the column contains manually filled values, Excel may not extend the sequence when you add a row. Use the fill handle to extend the numbers, or use a formula in the table’s number column and check that Excel has filled it into the new row. If the table does not extend when you type directly beneath it, add the row using the last table cell and press Tab, or use Table Design > Resize Table in desktop Excel.
Leading zeros have disappeared. If you have not entered the codes yet, format the cells as Text first. If Excel already converted a code such as 00047 to 47, restore the original code only if you know its required length; for a six-digit code, a custom format of 000000 will display 000047, while converting to text requires rebuilding the zeros. For imported data, use the import options to set that column’s data type to Text before loading it.
Frequently Asked Questions
How do I automatically number rows in Excel?
For a simple sequence, enter 1 and 2 in the first two cells and drag the fill handle down. For formulas that calculate each row’s position, enter =ROW()-1 in A2 when row 1 is a header, then copy it down. The formula result changes with row position, so it is not a permanent record ID.
How can I start the serial numbers at 1001?
Enter 1001 and 1002 in the first two cells and drag the fill handle. In Microsoft 365 or Excel 2021 and later, with an empty spill area and the first result cell at A2, =SEQUENCE(10,1,1001,1) generates ten numbers from 1001 to 1010.
Can I number only the rows that contain data?
Yes. For a short list, fill only as far as the last data row. If you use a table, add numbers as you add records or use a formula in the number column and check that it extends to new rows. A basic row formula follows worksheet position; it does not automatically solve every situation involving blank rows, filters, or visible records only.
How do I keep zeros at the beginning of a serial number?
Format the destination cells as Text before entering or importing the code. For numeric-only codes with a known fixed length, a custom number format can display leading zeros, but it does not change the stored value. Use Text when the identifier must be preserved exactly or includes letters.
Will the numbers change when I sort the worksheet?
When you sort the entire data range, fixed numbers usually move with their records, while a ROW-based formula recalculates according to the new row positions. Select all related columns before sorting. Choose fixed values for IDs that must remain attached to records, and formulas when the numbers are intended to show current row order.
Related
- How to Delete an Excel File Online in OneDrive or Teams
- How to Delete an Excel File on the Web in OneDrive
- How to Delete an Excel File in Windows 11
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Shortcuts for Naming a Range and Managing NamesIn Excel for Microsoft 365 on Windows, press Ctrl+F3 to open Name Manager, then choose New to name…
- Excel Goal Seek: How It Works and When to Use ItIn Excel, Goal Seek is a What-If Analysis tool—not a worksheet function—that finds the input value needed to…
- Clear Outlook Cache on a PC: Safe Steps for WindowsTo clear Outlook cache on a Windows PC, first choose the cache that matches the problem: in classic…
- Embed Video in PowerPoint Using Embed Code: Steps & LimitsYes. In PowerPoint for Microsoft 365 on Windows, go to Insert > Video > Online Videos, paste the…
- How to Get Alternating Color Rows in Excel AutomaticallyTo get alternating color rows in Excel, select your data, choose Home > Format as Table, and pick…
- Excel Data Table Shortcut: What It Does and How to Use ItExcel has no single universal keyboard shortcut for the What-If Analysis Data Table command: in Windows, choose Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.