Auto-Number Rows

To generate an automatically incremented counter for each row that contains data, use an Auto-Number System column. This type of 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.

Required Permissions

You must be the sheet Owner or a licensed user with Admin-level sharing access to the sheet to add or modify columns.

In this article:


Create an Auto-Number Column

  1. To get started, click the drop-down arrow  under any column's header and select Insert Column Right or Insert Column Left.
  2. In the form, click Auto-Number/System > Auto-Number to reveal the Display Format section of the form.


     
  3. To format the number, do either of the following:

    To set up a specific numbering pattern, fill in the fields in the Display Format section of the form:
     
    • Prefix: Appears before the automatically generated number.

      You can use numbers, text, symbols (*, -, _ ), and dynamic date tokens.
       
    • Fill: Insert a number of 0's to represent the total number of desired digits between the Prefix and the Suffix. 0's will be inserted into the front of each number in the sheet such that each one contains the desired number of digits.
       
    • Suffix: Appears after the automatically generated number.

      You can use numbers, text, symbols (*, -, _ ), and dynamic date tokens.
       
    • Starting Number: Determine where the count will start. By default, it starts at 1.
       
    -or-

    Leave the fields blank to assign row IDs that start with 1, and increment by 1 in each row.

    TIP: The Auto-Number Preview field in the form will display the format of the starting number.

The next time you save the sheet, the Auto-Number System column will include a value (as per your specifications in the form) for each row that contains data.

NOTES:

  • You can have only one Auto-Number column per sheet.
  • If you later make changes to the Display Format (e.g., change the Prefix), the new display settings will only be applied to new rows that are 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. Make sure to set the starting number appropriately to re-start the sequence.
  • The values in the column are display-only; however, you can apply formatting to the column to change the appearance of the value it contains. For more information, see the Formatting Options article.

Turn an Existing Column into an Auto-Number Column

You can also change an existing column type to Auto-Number. You may want to do this if you already have row IDs, part numbers, etc, listed in 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 take place 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.

  1. Click the drop-down arrow underneath the column header and select Edit Column Properties.
  2. Change the type listed in part 2 to Auto-Number/System→ Auto-Number.
  3. Use the steps described in the previous section to adjust the Display Format.
  4. Click OK.

Because the existing values are preserved, you won't see any immediate changes. However, the next time you insert a new row, and Save the sheet, a number will be generated automatically into the column based on the Display Format settings you selected.

TIP: Make sure to set the Starting Number in the Display Format section such that it continues the existing sequence. 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 2 or 4 digit year that the Auto-Number was created.
  • {MM} Displays the numeric month (for example 01 for January, 02 for February) that the Auto-Number was created.
  • {DD} Displays the numeric day of the month that the Auto-Number was created.

NOTE: The curly brackets are required. The tokens Y, M, and D, are used in all languages. 

NOTE: Date tokens reflect Coordinated Universal Time (UTC ) rather than your local time zone. If needed, use the Created (Date) system column to log the timestamp of each row's creation in your own time zone.

For more information about other system column types, including the Date column, see Use a System Column to Automatically Add Information to a Row.


Reset the Auto-Number Column

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

  1. Select the drop-down arrow under the column's header and select Delete Column.
  2. Click Save in the top toolbar.
  3. Select File > Refresh.
  4. Create a new auto-number column with your correct values.

    IMPORTANT: When setting the values in your new auto-number column, it's important to set the starting value to 1 to ensure that the column increments from 1 (as opposed to from a value in the prior auto-number column).
     
  5. Save the sheet to populate the column with new numbers.

Back to Top    

Was this article helpful?
YesNo