• An example of the use case
• Example solution
• Solution and summary

When generating a hyperlink from a formula that includes cell references in Google Sheets, you may wish to convert that formula to a regular hyperlink, getting rid of the cell reference. Doing so for an individual cell is easy, but doing so quickly for a range of cells isn’t so intuitive.

Once I figured out a solution, I found myself using it repeatedly, and wanted to document it for others. It’s a useful trick for making certain spreadsheets more user friendly and less volatile.

An example of the use case

Let’s say we have the following spreadsheet:

Note: This spreadsheet is simplified for the sake of concision—Salesforce record IDs don't actually look like this.

Where the Record URL values are generated from the following formula:

The typical way to remove the cell reference A2 and convert the formula into a static hyperlink is to copy the cell contents and paste as values. Doing so converts the cell into plain text, removing the hyperlink, even if the text is a valid URL. By selecting the cell, and pressing enter twice, if the text is a valid URL, it should be converted into a hyperlink. This works great for an individual cell, but it's time consuming to do for a large number of cells.

Example solution

Instead, we need a simple solution that will work for a whole column or a range of cells. We can rewrite our formula to output valid hyperlink formula syntax as a string. After copy and pasting as values, we can then format the cell as the data type ‘Automatic’ to convert it to a hyperlink. You can do this process with an entire range of cells at once.

Here’s an example

This formula:

would be re-written like this:

where the string "=HYPERLINK("""& is added to the front of the formula, and &""")" is added to the end. Once the formula is copy and pasted as values, it will turn into this text:

=HYPERLINK("salesforce.com/?id=5823")

Then, selecting that cell (or the range of cells) and choosing Format > Number > Automatic, the text will be converted from plain text into an active formula, in this case into a hyperlink. The hyperlink will still be a formula, technically, but it will be a formula without cell references which won’t break when certain types of changes are made to the sheet.

Solution and summary

If you have a column in Google Sheets with a formula with a cell reference that outputs a URL and you want to remove the cell references but keep the hyperlink live, you can follow these steps:

1. Add "=HYPERLINK("""& to the beginning of the formula, and &""")" to the end. Fill this formula through the entire range of cells you’re using.
2. Select the range of cells, and Copy and Paste as Values to turn each cell into plain text.
3. Select the range of cells, and choose Format > Number > Automatic, to convert the plain text into working hyperlinks.

Email signup