Do you need to generate a GUID in Excel?

**GUID** stands for **Globally Unique Identifier**, but it might also be referred to as a **UUID** which stands for **Universally Unique Identifier**. These are the same thing.

A GUID is a 32-character code consisting of both numbers and letters that are commonly used to identify items in a database.

GUIDs are generated randomly, but the probability of duplicating a randomly generated GUID is so infinitesimally small that you can be sure that it will be unique. This means there is no need for a central registry to ensure their uniqueness.

This post is going to show you how you can generate random GUIDs in Excel. Get your copy of the file used in this post and follow along!

## Generate a GUID with the RANDBETWEEN Function

GUIDs are usually 128 bits long and will have the below format where the **x**‘s are hexadecimal digits.

`xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx`

This format organizes the 32 hexadecimal digits into groups of 8-4-4-4-12 characters. The dash character is purely for ease of reading.

A common way to generate these random hex digits in the GUIDs is to generate random decimal numbers and convert them into hexadecimal.

`= RANDBETWEEN ( min, max )`

This can be achieved using the **RANDBETWEEN** function in Excel. This function allows you to generate a random integer number between a given upper `min`

and lower `max`

bound.

`= DEC2HEX ( number, [digits] )`

The **DEC2HEX** function will convert any decimal `number`

to its equivalent hexadecimal representation. It also allows you to specify the number of `digits`

to return. This way you can always return an 8 or 4 character length hexadecimal.

`= DEC2HEX ( RANDBETWEEN ( 0, 4294967295 ), 8 )`

A random sequence of **8** hex digits can be generated by generating a random number between **0** and **4,294,967,295** and converting the result to a hex value using the **DEC2HEX** function in Excel.

`= DEC2HEX ( RANDBETWEEN ( 0, 65535), 4 )`

Similarly, a random sequence of 4 hex digits can be generated by generating a random number between **0** and **65535** and converting it to hexadecimal.

Then you can generate a sequence of 12 hex digits by combining an 8 and 4 sequence.

`=LOWER(CONCATENATE( DEC2HEX(RANDBETWEEN(0,4294967295),8),"-", DEC2HEX(RANDBETWEEN(0,65535),4),"-", DEC2HEX(RANDBETWEEN(0,65535),4),"-", DEC2HEX(RANDBETWEEN(0,65535),4),"-", DEC2HEX(RANDBETWEEN(0,4294967295),8), DEC2HEX(RANDBETWEEN(0,65535),4)))`

The above formula will generate the full GUID value including the joining dash characters.

The **CONCATENATE** function joins all the hex values together along with the dash to create the full GUID.

The **LOWER** function can also be used in the formula to create a GUID that returns lowercase instead of uppercase letters.

📝 **Note**: The **RANDBETWEEN** function is volatile so this will recalculate any time you edit the spreadsheet. You can force the recalculation and generate a new GUID by pressing the `F9` key.

## Generate a GUID with the WEBSERVICE Function

**WEBSERVICE** is a very interesting function. It allows you to call a web address and return data from that address.

When combined with a service such as uuidtools, the **WEBSERVICE** function will give you the ability to create your GUIDs.

`https://www.uuidtools.com/api/generate/v4/count/5`

The uuidtools online GUID generator is a free API you can use to generate GUIDs and UUIDs. The above URL will return a list of 5 generated GUIDs. You can change the 5 in the URL to any number you like.

`= WEBSERVICE ( "https://www.uuidtools.com/api/generate/v4/count/5" )`

The above formula will return a list of GUIDs.

`["736f07e7-12e1-4d9f-aa83-762916761eba","c516d5f6-f816-448c-a2f7-dcb308c02393","04ce3d1b-d4f0-4c41-88cd-b90f7deacde4","960445e7-4e04-4bc1-a220-a7e6eb45fde0","d34c0e8b-11fa-4410-a673-2412aea39569"]`

The function returns a single text string in square brackets which is a comma-separated list of GUIDs.

This comma-separated list of GUIDs can easily be parsed into separated cells using the **TEXTSPLIT** function.

`= TEXTSPLIT ( C2, , {""",""","[","]",""""}, TRUE )`

The above formula will split the list of comma-separated GUIDs based on these sets of characters `","`

, `[`

, `]`

, `"`

in that order.

**TEXTSPLIT** splits these GUIDs into rows and then also removed any blank cells to leave only the GUIDs each in its own cell.

## Generate a GUID with Power Query

Power Query is loading and transforming your data. It also has a fairly easy way to create GUIDs.

The Power Query formula language includes a **Text.NewGuid** function which will generate a GUID for you.

This means when you are importing or transforming a data set, you can add a column of GUIDs.

You can import your data into Power Query through these steps.

- Select your table.
- Go to the
**Data**tab. - Click on the
**From Table/Range**command.

Now the dataset is in the Power Query editor. Go to the **Add Column** tab and press the **Custom Column** button in the **General** section.

This will open the **Custom Column** formula editor.

`#table({"GUID"},{{Text.NewGuid()}})[GUID]{0}`

Give your new column a name and enter the above formula into the **Custom column formula** input, then press the **OK** button.

This part `#table({"GUID"},{{Text.NewGuid()}})`

creates a table with a single column named **GUID** and a single row containing a GUID value.

Then `[GUID]{0}`

will get the zeroth row in the GUID column. In other words, it gets the GUID value out of the table that was created.

⚠️ **Warning**: Unfortunately, you can’t just use the formula `=Text.NewGuid()`

, this will result in each row having the same GUID when it’s loaded to Excel. Creating a table `#table()`

with the formula seems to work and results in different GUIDs across the rows.

This creates a new column in the data preview with a different GUID value in each row.

This can then be loaded back into Excel. Go to the **Home** tab of the Power Query editor and press the **Close and Load** button.

## Generate a GUID with Power Automate

Power Automate has the ability to connect to Excel spreadsheets saved in SharePoint or OneDrive. This means you can automate processes involving Excel and other cloud apps.

Power Automate comes with its own workflow expression language which contains a **guid** function.

This can be used to create GUIDs and add them to an Excel table.

You generally won’t want your GUIDs to change, so this method is great because it will add static values to Excel. Whereas the previous methods will produce GUIDs that will change when you refresh Excel.

You can easily make a simple flow that will add GUIDs to an Excel table with these steps.

- Go to https://make.powerautomate.com/ and create a new flow using the button trigger.
- Add the
**Add a row into a table**action to the flow. - Select the Excel file and table to which you want to add your GUIDs from the
**Location**,**Document Library**,**File**, and**Table**dropdowns.

When you select the **Table**, a list of columns in the table will appear in the action.

- Click in the column to add the GUID.
- Click on the
**Expression**tab in the popup. - Enter
`guid()`

in the formula bar. - Click the
**Update**button. **Save**the flow.

Now when you run the flow it will add a GUID to the table!

## Generate a GUID with VBA

You might not want to save your spreadsheet in the cloud to use Power Automate, but still want a way to create static GUIDs.

This is where VBA might be useful.

You can create a macro to create and enter GUIDs in your workbook without the need for an external tool.

Go to the **Developer** tab and click on the **Visual Basic** command to open the visual basic editor. Alternatively, you can press `Alt` + `F11` to open the editor.

Inside the visual basic editor, go to the **Insert** menu and select the **Module** option. This is where you can add the macro code.

The code relies on two functions and a procedure. You will need to copy and paste all of them into the module.

`Function randBetween(ByVal min As Long, max As Long) randBetween = Int(Rnd() * (max - min + 1)) + minEnd Function`

This function allows you to generate a random number between a min and a max value.

`Function GUID()Dim guid1, guid2, guid3, guid4, guid5, guid6, guid7, guid8 As Stringguid1 = LCase(Hex(randBetween(0, 65535)))guid2 = LCase(Hex(randBetween(0, 65535)))guid3 = LCase(Hex(randBetween(0, 65535)))guid4 = LCase(Hex(randBetween(0, 65535)))guid5 = LCase(Hex(randBetween(0, 65535)))guid6 = LCase(Hex(randBetween(0, 65535)))guid7 = LCase(Hex(randBetween(0, 65535)))guid8 = LCase(Hex(randBetween(0, 65535)))guid1 = Right(String(4, "0") & guid1, 4)guid2 = Right(String(4, "0") & guid2, 4)guid3 = Right(String(4, "0") & guid3, 4)guid4 = Right(String(4, "0") & guid4, 4)guid5 = Right(String(4, "0") & guid5, 4)guid6 = Right(String(4, "0") & guid6, 4)guid7 = Right(String(4, "0") & guid7, 4)guid8 = Right(String(4, "0") & guid8, 4)GUID = guid1 & guid2 & "-" & guid3 & "-" & guid4 & "-" & guid5 & "-" & guid6 & guid7 & guid8End Function`

The above function allows you to create a GUID and it relies on the previous `randBetween()`

VBA function to generate the hex values.

`Sub GenerateGUID()Dim rng As RangeFor Each rng In Selection rng.Value = GUID()Next rngEnd Sub`

The subroutine then loops through each cell in the selected range and adds a GUID generated from the `GUID()`

VBA function.

All you need to do to use this is select the range of cells to which you’d like to add GUID values, then run the **GenerateGUID** macro.

## Generate a GUID with Office Scripts

Another way to add static GUIDs in a range is with Office Scripts.

Go to the **Automate** tab and click on the **New Script** command.

`function main(workbook: ExcelScript.Workbook) { //Create a range object from selected range let selectedRange = workbook.getSelectedRange(); //Get dimensions of selected range let rowHeight = selectedRange.getRowCount(); let colWidth = selectedRange.getColumnCount(); //Loop through each item in the selected range for (let i = 0; i < rowHeight; i++) { for (let j = 0; j < colWidth; j++) { selectedRange.getCell(i, j).setValue(guid()); } }};function randBetween(min: number, max: number) { return Math.floor(Math.random() * (max - min + 1)) + min;};function guid() { let guid1 = randBetween(0, 4294967295).toString(16).padStart(8, '0'); let guid2 = randBetween(0, 65535).toString(16).padStart(4, '0'); let guid3 = randBetween(0, 65535).toString(16).padStart(4, '0'); let guid4 = randBetween(0, 65535).toString(16).padStart(4, '0'); let guid5 = randBetween(0, 4294967295).toString(16).padStart(8, '0'); let guid6 = randBetween(0, 65535).toString(16).padStart(4, '0'); let guid = guid1 + '-' + guid2 + '-' + guid3 + '-' + guid4 + '-' + guid5 + guid6; return guid;};`

This will open up the **Code Editor** and you can paste in the above code.

This code consists of three functions.

`randBetween()`

is a function that allows you to generate a random number between a minimum and a maximum.`guid()`

uses the`randBetween()`

function to generate the GUID in a similar way to the first method with the**RANDBETWEEN**Excel function.`.toString(16)`

will convert the random number to its hex value.`.padStart(8, '0')`

ensures the hex value is 8 characters.

The `main()`

then loops through the selected range and populates a GUID in each cell using the `guid()`

function.

All you need to do is select a range of cells in which you’d like to add GUID values and then run the script.

## Conclusions

GUIDs are very commonly used for uniquely identifying items in a database. When working with data in Excel, there are a number of ways to generate a GUID in Excel.

If you need to generate a GUID quickly and don’t mind using an external service, the **WEBSERVICE** function may be the best option.

For those who prefer working entirely within the **RANDBETWEEN** or **Power Query** methods will be the way to go. However, these will create volatile GUIDs that change when the spreadsheet is refreshed.

Using **Power Automate**, **VBA**, or **Office Scripts** all provide a way to generate GUIDs in Excel that are static and won’t continuously change.

Have you ever needed to create GUIDs in Excel? How did you create them? Let me know in the comments below!

## FAQs

### How do I generate a GUID? ›

To Generate a GUID in Windows 10 with PowerShell,

Type or copy-paste the following command: **[guid]::NewGuid()** . This will produce a new GUID in the output. Alternatively, you can run the command '{'+[guid]::NewGuid(). ToString()+'}' to get a new GUID in the traditional Registry format.

**How do I auto generate unique ID in Excel? ›**

You can generate a unique value using a formula in the spreadsheet. An ID must be a value, not a formula, though, so **copy (Ctrl+C) and paste as plain text (Shift+Ctrl+V) the result of the formula calculation into the cell meant to contain the new ID**. That's all there is to it!

**What is GUID generator? ›**

A GUID (globally unique identifier) is a 128-bit text string that represents an identification (ID). **Organizations generate GUIDs when a unique reference number is needed to identify information on a computer or network**. A GUID can be used to ID hardware, software, accounts, documents and other items.

**How is Microsoft GUID generated? ›**

Basically, a a GUID is generated using a combination of: **The MAC address of the machine used to generate the GUID** (so GUIDs generated on different machines are unique unless MAC addresses are re-used) Timestamp (so GUIDs generated at different times on the same machine are unique)

**What is an example of a GUID? ›**

A GUID is a 128-bit value consisting of one group of 8 hexadecimal digits, followed by three groups of 4 hexadecimal digits each, followed by one group of 12 hexadecimal digits. The following example GUID shows the groupings of hexadecimal digits in a GUID: **6B29FC40-CA47-1067-B31D-00DD010662DA**.

**What are the different types of GUIDs? ›**

The general types of GUIDs are: Random: A random-number generator creates a 128-bit number. Time-based: Based on the current time. Hardware-based: Certain parts of the GUID are based on the hardware features, although this removes part of the anonymity of the number.

**What does GUID stand for? ›**

**Global Unique Identification number** show sources.

**How many unique GUIDs are there? ›**

While each generated GUID is not guaranteed to be unique, the total number of unique keys (**2 ^{128} or 3.4×10^{38}**) is so large that the probability of the same number being generated twice is very small.

**Can Excel generate unique sequential numbers? ›**

**The SEQUENCE function in Excel is used to generate an array of sequential numbers** such as 1, 2, 3, etc. It is a new dynamic array function introduced in Microsoft Excel 365. The result is a dynamic array that spills into the specified number of rows and columns automatically.

**How do you generate unique identifiers? ›**

The simplest way to generate identifiers is **by a serial number**. A steadily increasing number that is assigned to whatever you need to identify next. This is the approached used in most internal databases as well as some commonly encountered public identifiers.

### How do I create unique values in Excel? ›

In Excel, there are several ways to filter for unique values—or remove duplicate values: To filter for unique values, **click Data > Sort & Filter > Advanced**. To remove duplicate values, click Data > Data Tools > Remove Duplicates.

**What is a GUID Microsoft? ›**

A GUID is **a 128-bit integer (16 bytes) that can be used across all computers and networks wherever a unique identifier is required**. Such an identifier has a very low probability of being duplicated.

**Which data type is used for assigning GUID value? ›**

The GUID data type is a **16 byte binary data type**. This data type is used for the global identification of objects, programs, records, and so on. The important property of a GUID is that each value is globally unique.

**How many GUID combinations are there? ›**

Question: How many GUID combinations are there? Answer: There are 122 random bits (128 – 2 for variant – 4 for version) so this calculates to 2^122 or **5,316,911,983,139,663,491,615,228,241,121,400,000 possible combinations**.

**What is a GUID based on? ›**

A GUID is **a series of hexadecimal numbers**. Hexadecimal numbers include the digits 0-6 plus letters A-F. It's 32 digits long and arranged in an 8-4-4-4-12 series. Technically speaking it's a 128-bit integer number.

**What are the uses of GUID? ›**

GUIDs are used in software development as database keys, component identifiers, or just about anywhere else a truly unique identifier is required. GUIDs are also used to identify all interfaces and objects in COM programming.

**How do I find my office GUID? ›**

**To view the GUIDs for the Office 2016 suites and programs that are installed on a computer, follow these steps:**

- Click Start, click Run, type regedit, and then click OK.
- Locate the following registry subkey: HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Uninstall.

**Can Excel generate random codes? ›**

Remarks. If you want to use RAND to generate a random number but don't want the numbers to change every time the cell is calculated, you can **enter =RAND() in the formula bar, and then press F9 to change the formula to a random number**.

**How do I generate a random number in Excel without repeating? ›**

**Generate Random Number List With No Duplicates in Excel**

- Select cell B3 and click on it.
- Insert the formula: =RANDBETWEEN(10,30)
- Press enter.
- Drag the formula down to the other cells in the column by clicking and dragging the little “+” icon at the bottom-right of the cell.

**How do I randomly generate a list of names in Excel? ›**

One way is to **use the RAND function**. The RAND function will return a random number between 0 and 1. You can use this function to generate a list of random numbers, and then use those numbers to select a random name from a list. Another way to select random names in Excel is to use the RANDBETWEEN function.

### How do I randomly select a winner in Excel? ›

To pull random winners in a drawing using Excel:

In the empty column to the left of your data, assign a random number to each student by using the Random Number formula: “=RAND()” Drag the formula down the column to create random numbers for each participant.

**How to generate random numbers in Excel with mean and standard deviation? ›**

**Use the formula "=NORMINV(RAND(),B2,C2)"**, where the RAND() function creates your probability, B2 provides your mean and C2 references your standard deviation. You can change B2 and C2 to reference different cells or enter the values into the formula itself.