In this article:

Google Sheets Generate Unique ID (Easiest Way in 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. 

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started