Automatically Adding Serial Numbers in Excel

Serial numbers are an essential part of many datasets because you can use them to identify specific entries in your sheet. Adding them manually can take time and result in errors, but there are several ways you can add these numbers automatically. 

Save yourself some time at work by learning how to add serial numbers to your spreadsheets. We’ll cover four popular methods.

Fill Series Method

If you want to add a series of number rows without doing so manually, use the Fill Series tool to generate a column with a list of numbers in it.

Follow these steps:

  • Select the cell you want to use and insert the number “1” into it
  • Click the Home tab, then hover over to Editing, then Fill, then Series.
  • The Series window will populate, and when it does, key in the following information:
    • Series In = Column
    • Step Value = 1
    • Stop Value = the number you want to end your sequence with (ex: 1,000)
    • Click OK

ROW Function Method

For this method, click on the cell A1, then edit it. Key in the =ROW() formula. You’ll then select the formula and drag it to the number you want to end your sequence with.

Adding One Method

This is another quick method to use if you want to add serial numbers in an Excel spreadsheet.

  • Click the cell you want to use to start your sequence
  • Enter the number “1” into the cell
  • In the next cell down, key in the formula =C1+1 (assuming C1 is your starting cell)
  • Click the formula and then drag it down to the number you want to end your sequence with

Pivot Table Method

If you want to add serial numbers to a pivot table, it’s important to insert an extra column in your source data. This number column will appear after the row item columns.

To accomplish this task, follow these steps:

  • Add a new column in your source data, and place a “1” in each cell
  • Create a pivot table with the data therein
  • Add the columns as values afterward
  • Right-click the column in the values section and select Value Field Settings
  • Click on Show Value As and select the running total
  • Click OK

We hope these tools help save time and enhance the quality of your data!

If you want to take your Excel skills to the next level and explore a wide range of formulas and functions, check out our workshop.

And, we’ve got you covered with an entire library of solutions that will empower you to work smarter!

Like Learn Excel Now? Follow us on social media and share our content with your networks!