Art of the Smart

"Collapsing" HYPERLINK Formula in Google Spreadsheets

Quick pro tip: If you need to convert hyperlink functions into linked text in Google Sheets, copy the cells and paste values, then paste formatting.


Suppose you have the following in Google Sheets.

A B
1 =HYPERLINK("https://google.com", B1) Link Text

The cell appears as a hyperlink like this: Link Text

Right now that cell is a formula, so if you copy and paste it somewhere else, its references will be updated. If you copy and paste values, you only get the unlinked "Link Text".

So if you have a long column of links defined using the hyperlink formula, how would you collapse it into hyperlinked text?

Copy the affected cells, then use Right Click > Paste Special > Paste Values. Then in those same cells use Right Click > Paste Special > Paste Formatting.

Bam.