Auto-number rows

Use an auto-number system column to generate an automatically incremented counter for each row that contains data.

Who can use this?

Plans:

  • Smartsheet
  • Pro
  • Business
  • Enterprise

Permissions:

  • Owner
  • Admin

Find out if this capability is included in Smartsheet Regions or Smartsheet Gov.

An auto-number system column is useful when you want to automatically include a row ID, a part number, an invoice number, or a customer number without having to type the number in.

You can have only one Auto-Number column per sheet. If the auto-number option isn't available, your sheet already has an auto-number column.

Create an auto-number column

  1. Add a new column using the column options menu   or the add column shortcut  Add row or add column plus sign .
  2. Select Auto number as the column type.
  3. Format the autonumber pattern in the display format section, or leave the fields blank if you want the starting number to be one. 
  • Prefix: Appears before the automatically generated number.
    Use numbers, text, symbols (*, -, _ ), and date tokens.
  • Suffix: Appears after the automatically generated number.
    Use numbers, text, symbols (*, -, _ ), and date tokens.
  • Numerical places: Insert the number of 0s to represent the total number of desired digits between the Prefix and the Suffix. 0s will be inserted into the front of each number on the sheet so that each one contains the desired number of digits.
  • Starting number: Determine where the count will start. By default, it starts at one.
  1. The Auto-Number Preview field in the form will display the format of the starting number.
  2. Select Apply  

The next time you save the sheet, the Auto-Number System column will include a value for each data row.

autonumber column settings

Turn an existing column into an auto-number column

You can also change an existing column type to Auto-Number. You can do this if you have row IDs, employee numbers, or part numbers on the sheet.

For example, when an Excel file containing a row ID column is imported to Smartsheet, that column is recognized as a regular text/number column, and no automatic generation of numbers would occur when new rows are added. Changing the text/number column to an Auto-Number column will preserve any existing column values, so you won't lose any row IDs that have already been assigned.

To change an existing column to Auto-number: 

  1. Open the column options menu  .
  2. Select Edit Column Properties.
  3. Change the column type to Auto number.
  4. Use the steps described in the previous section to adjust the Display Format.
  5. Select Apply.

You won’t see immediate changes since the existing values are preserved. However, the next time you insert a new row and save the sheet, a number will be automatically generated in the column based on your selected Display Format settings.

Set the starting number in the Display Format section that continues the existing sequence. For example, if you've already numbered rows 1-355, set the starting number to 356.

Advanced date display options

To dynamically generate date values in the Prefix or Suffix fields of the Display Format section, use the following dynamic date tokens:

  • {YY} or {YYYY} Displays the two or four-digit year that the Auto-Number was created.
  • {MM} Displays the numeric month that the Auto-Number was created. For example, 01 for January, 02 for February
  • {DD} Displays the numeric day of the month the Auto-Number was created.

The curly brackets are required. The tokens Y, M, and D are used in all languages. Date tokens reflect Coordinated Universal Time (UTC ) rather than your local time zone. Use the Created (Date) system column to log the timestamp of each row's creation in your time zone if needed.

Reset the auto-number column

If your numbering schema changes or if the column isn't displaying numbers as expected, you can reset the auto-number column:

  1. Open the column options menu  .
  2. Select Delete Column.
  3. Select Save in the top toolbar.
  4. Select File > Refresh.
  5. Create a new auto-number column with your correct values.

When setting the values in your new auto-number column, it's essential to set the starting value to one to ensure that the column increments from one. If not, the value increments from the previous auto-number column's last number.

  1. Save the sheet to populate the column with new numbers.

Keep in mind

  • If you later make changes to the Display Format, the new display settings will only be applied to new rows saved to the sheet. To apply the new settings to existing rows, delete the Auto-Number column and create a new one with the new Display Format. Ensure that the starting number is set appropriately to restart the sequence.
  • The values in the column are display-only; however, you can apply formatting to the column to change the appearance of the values. See Format your data.