In this article:

Google Sheets Generate Unique ID (Easiest Way in 2024)

May 8, 2024

Google Sheets Generate Unique ID

Follow the steps below to generate unique IDs in Google Sheets. 

1. Create Unique ID Column

Begin by selecting the first cell in an empty column next to your dataset where you want the unique IDs to appear. This setup works regardless of your data's nature or volume. For instance, if your data spans columns B to D, starting from row 2 (considering row 1 for headers), you would click on cell A2 to start inputting your unique IDs.

2. Start ID Numbers with Adjusted ROW Function

Input =ROW() - 1 in the chosen cell to create a simple numeric ID, adjusting the formula to account for header rows if necessary. This method ensures that your IDs start at 1, despite the dataset's starting row. In the specific example with the dataset beginning on row 2, the formula in A2 becomes =ROW()-1, making A2's value 1 for the first data entry.

3. Generate Descriptive IDs Using Text and ROW Formula

For more descriptive IDs, combine text with the ROW function in your formula. Use something like ="ID-"&TEXT(ROW()-1, "000") in the first cell of your ID column. This approach prefixes each ID with "ID-" and ensures a consistent numbering format, starting from "ID-001". It’s adaptable to any dataset layout, ensuring that your first data entry gets a neatly formatted, descriptive ID.

4. Extend Unique IDs Down the Column

Drag the fill handle (the small square at the bottom right corner of the formula cell) down to apply the ID formula to the rest of your dataset. This action copies the formula to each cell, automatically adjusting the row number for each entry. In the context of your specific dataset, dragging down from A2 will populate subsequent cells with sequential IDs, maintaining uniqueness across entries.

5. Use ARRAYFORMULA for Full Column Automatic ID Generation

Employ ARRAYFORMULA to fill the entire column with unique IDs in one step. Input =ARRAYFORMULA("ID-"&TEXT(ROW(B2:B)-ROW(B2)+1, "000")) in the first cell of your ID column.

This formula is especially useful for larger datasets or when you frequently add new rows, as it automatically extends IDs to new entries without manual updates. Adjust the range B2:B to match the column adjacent to your ID column to ensure seamless integration with your specific dataset.

6. Ensure Continuous Unique ID Assignment for New Entries

Adopting the ARRAYFORMULA strategy guarantees that each new data entry receives a unique ID automatically. This is crucial for maintaining a robust dataset, particularly when it's subject to frequent additions. This method eliminates the need for manual formula copying which streamlines data management processes across various types of datasets.

We hope that you now have a better understanding of how to set up Google Sheets to generate unique IDs. If you enjoyed this article, you might also like our article on spreadsheet ID in Google Sheets or our article on how to create graphs on Google Sheets. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.
Get your copy of our free Google Sheets automation guide!
  • 27 pages of Google Sheets tips and tricks to save time
  • Covers pivot tables and other advanced topics
  • 100% free

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->