To drag serial numbers in Excel, enter 1 and 2 in the first two cells, select both cells, then drag the small square at the selection’s lower-right corner down the column. Excel recognizes the pattern and continues it as 3, 4, 5, and so on. If you drag just one number, Excel may copy it instead of creating a series, so starting with two numbers is the dependable method.
What You’ll Need
- A Microsoft Excel workbook and a blank column or row for the numbers.
- A starting number and, if you want a consistent increment, the next number in the sequence. For example, use 10 and 20 for a series that increases by 10.
- The fill handle enabled. It is usually on by default; troubleshooting instructions below explain how to turn it on if it is missing.
Step-by-Step
- Choose where the numbering should begin. Click the first cell in the intended list. For example, to put serial numbers in column A with a header, click A2, not A1. Keeping the heading outside the numbered range makes it easier to sort and work with the data later. Make sure there is enough room below the starting cells for the numbers you want to add.
- Enter the first number. Type
1in the starting cell and press Enter. If your numbering should begin at a different value, use that instead. For instance, enter 100 if your list should start at serial number 100. - Enter the next number in the pattern. Click the cell directly below the first number and enter the value that establishes the interval. For ordinary numbering, type
2. For a sequence that rises by 5, enter 105 after 100. The difference between these seed values tells Excel how much to add each time. - Select both starting cells. Drag across the two cells, or click the first and Shift-click the second, so both are highlighted. Check that the selection contains the two values in the intended order. If you select only the cell containing 1, Excel might fill every destination cell with 1 because it has only one value from which to infer a pattern.
- Find the fill handle. Move the pointer to the small square at the bottom-right corner of the selected range. The pointer changes to a thin black plus sign when it is in the right place. This is different from the larger white plus sign used to select cells; wait for the small fill-handle cursor before dragging.
- Drag through the cells you want to number. Hold the left mouse button and pull the fill handle down to the last row to be numbered. For a horizontal sequence, drag it to the right. The destination cells are outlined as you move. Release the mouse button when the outline reaches the final cell. If you started with 1 and 2, the filled cells should continue with 3, 4, 5, and so forth.
- Check the result and choose a fill option if needed. After the drag, Excel may show the Auto Fill Options button near the filled range. Click it to see available choices, such as filling the series or copying the cells. The wording and available choices can vary with the selection. For serial numbers, choose the series option rather than copying the initial values if Excel has repeated them.
- Use the exact range when you know how many numbers you need. If you need, for example, 50 serial numbers starting in A2, select A2:A51 after entering the first values and use the fill command instead of estimating a long drag. In Excel for Microsoft 365 on Windows, select Home > Fill > Series. In the dialog, choose Columns for a vertical list, Linear for a steady increase, and a Step value of 1. Enter a Stop value of 50 if you want the series to end at 50, then choose OK. With this example, A2 contains 1 and the final cell, A51, contains 50.
- Use the same method for other increments. For a list that goes 10, 20, 30, enter 10 and 20 in the first two cells, select them, and drag the handle. For odd numbers, seed the pattern with 1 and 3. Excel continues the difference between the two values, so check the first few results before relying on a long sequence.
On Excel for Mac, the fill handle works in the same basic way: select the seed cells and drag the small square at the selection’s lower-right corner. Excel for the web also supports dragging the fill handle, though the available Auto Fill options may differ from the Windows desktop version. If you cannot drag accurately, use the Series dialog on desktop Excel or fill a shorter section and continue from the last two values.
Common Mistakes
- Dragging a single number and expecting a sequence. One selected number does not always tell Excel whether you want a copy or a series. Enter two values, select both, and drag. This is the clearest way to establish the pattern.
- Selecting the wrong starting cells. If the cells are not adjacent, or if the second value does not match your intended interval, Excel may infer an unexpected pattern. Confirm the seed values before filling, especially when numbering by 2, 5, or 10.
- Grabbing the cell border instead of the fill handle. Dragging a cell border can move or select cells rather than extend the numbering. Position the pointer precisely over the small square at the bottom-right corner until the fill cursor appears.
- Including a heading in the fill selection. If you select a label such as Serial No. along with your seed numbers, the results may be confusing or the heading may be copied. Begin the selection with the numeric cells only.
- Filling farther than the data list. A drag can number blank rows below the records you meant to label. Decide the final row first, then stop at that row. If you overshoot, select the extra numbered cells and press Delete; this clears their contents without deleting the rows.
- Assuming serial numbers update to match a sorted list. Numbers filled as values do not automatically renumber when rows are sorted, filtered, or removed. Decide whether the numbers are fixed identifiers or just row positions before choosing a method.
Tips
- Use the Series dialog for a long or precisely limited list. It avoids the need to drag to a distant row. Choose Home > Fill > Series, then set the direction, type of series, step, and stop value. For a simple list beginning at 1, choose a step of 1 and a stop value equal to the last serial number you need.
- Keep a header separate from the sequence. Put a clear label such as Serial Number in the row above the numbers. A header helps Excel identify the data range and makes the sheet easier to understand when someone else opens it.
- Check the difference, not just the first value. When the sequence is not meant to increase by 1, verify that the first several results follow the intended increment. For seed values 4 and 7, for example, Excel should extend a pattern that increases by 3: 10, 13, 16.
- Use a formula when numbering should follow row positions. With the first serial number in A2 and the header in row 1, enter
=ROW()-1in A2, then use the fill handle to copy the formula down. A2 returns 1, A3 returns 2, and A51 returns 50. This formula adjusts to the row where it is placed, but it does not automatically close gaps after you delete rows; it simply calculates from each row’s position. - Distinguish identifiers from display formatting. If your serials need leading zeros, such as 001, 002, and 003, fill the numbers first and apply a custom number format, or use text if the zeros are part of a code that must remain exact. Formatting changes how a number looks; it does not change its underlying numeric value.
Troubleshooting
- The fill handle is not visible. On Excel for Microsoft 365 for Windows, go to File > Options > Advanced. Under Editing options, make sure Enable fill handle and cell drag-and-drop is checked, then click OK. On Mac, open Excel > Preferences or Settings, depending on the release, and look under the editing options for the fill-handle setting. Menu labels can differ slightly between Mac releases.
- Every filled cell contains the same number. Undo the fill with Ctrl+Z on Windows or Command+Z on Mac. Then enter two seed values, select both, and drag again. Alternatively, choose the series option from Auto Fill Options if it appears after filling.
- The sequence increases by the wrong amount. Check the difference between the two selected seed values. If you want 1, 2, 3, enter 1 and 2; if you seed 1 and 3, Excel will continue by 2. Replace the second seed with the correct next number, select the pair again, and refill the destination range.
- The drag stops too soon or is difficult to control. Zoom in, scroll to bring the destination into view, or use Home > Fill > Series to specify a stop value. You can also fill a manageable block first, then select its final two numbers and drag to extend the same pattern.
- Numbers show as dates or another unexpected format. Select the affected cells and choose Home > Number Format, then select General or Number. If Excel has already interpreted entries as dates, undo the fill and set the destination cells to General or Number before entering the seed values again.
- Some rows do not receive numbers. Check for merged cells, protected sheets, or gaps in the selected range. Unmerge cells if appropriate, unprotect the sheet if you have permission, and verify that the intended destination cells are included. If the sheet is filtered, remember that dragging into the underlying range may fill hidden rows too; check the result before using the numbers as visible row labels.
Frequently Asked Questions
Why does Excel repeat the same serial number when I drag?
Excel may interpret a single selected number as something to copy. Enter the next number in the series, select both seed cells, and drag the fill handle. If you have already dragged, undo and refill, or use the Auto Fill Options button to select a series where available.
Can I start serial numbers at a value other than 1?
Yes. Enter your preferred starting value and the next value that defines the interval. For example, enter 25 and 26 for consecutive numbers beginning at 25, or 25 and 30 for a sequence that increases by 5. Select both cells before dragging.
How do I drag serial numbers across a row instead of down a column?
Enter the first two values in neighboring cells in the same row, select both, and drag the fill handle to the right. For the Series dialog in desktop Excel, choose Rows rather than Columns.
Can Excel number only the visible rows in a filtered list?
A normal fill can also populate hidden rows in the filtered range, so do not assume it numbers only the visible records. For a filtered list, check which cells were filled and use a method designed for your layout if the numbering must correspond only to visible rows. The right approach depends on whether the numbers should remain fixed identifiers or recalculate with the displayed rows.
Will the serial numbers change when I sort the table?
Numbers filled as ordinary values move with their rows when you sort, so each record keeps its assigned number. A formula based on ROW calculates from the row’s current position and may therefore show different numbers after sorting. Choose fixed values for record IDs and row-based formulas for position labels.
Can I fill a series without using the mouse?
Yes. On Windows, select the destination range and use Home > Fill > Series to define the sequence. The dialog is useful when you need an exact stop value or increment. On Mac, look for the Fill Series command in the Home tab or ribbon; its location can vary by Excel release.
Related
- How to Delete Blank Cells in Excel Safely
- How to Delete an Excel File Online in OneDrive or Teams
- How to Delete an Excel File on the Web in OneDrive
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Find and Replace: Steps, Options, and Common FixesTo find or replace content in Excel for Microsoft 365 on Windows, press Ctrl+H to open Find and…
- Excel GIS Data Analysis: Maps, Methods, and Limits ExplainedExcel can organize, check, summarize, and map geographic data, but it is not a full geographic information system…
- Excel Insert Page Break Shortcut: Windows and MacIn Excel for Microsoft 365 on Windows, press Alt, P, B, I in sequence to insert a page…
- How to Set an Out of Office Reply in OutlookTo set an out-of-office reply in Outlook, open Automatic Replies, turn them on, write your message, and—if available—choose…
- Clear Email Cache in Outlook 365: Safe StepsTo clear the email cache in classic Outlook for Microsoft 365 on Windows, close Outlook, then rename the…
- Excel Merge Multiple Cells Into One Cell: A Clear GuideTo merge cells in Excel, select the range, then choose Home > Merge & Center ▼ > Merge…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.