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. 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. 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. 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. 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. 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. 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. The above formula will return a list of GUIDs. 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. 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. 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. 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. When you select the Table, a list of columns in the table will appear in the action. 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. This function allows you to generate a random number between a min and a max value. The above function allows you to create a GUID and it relies on the previous randBetween() VBA function to generate the hex values. 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. 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!
