Hyperlinks in Google Sheets allow you to create links inside a worksheet.
Create a hyperlink to a website
To create a hyperlink, you have to use the HYPERLINK function.
The link will appear in the exact way it was entered as a parameter.
You can hover over the URL to display the website link.
Click it, to open the site in a new tab.
If you want a friendly name, you can add the second, optional parameter to the HYPERLINK function.
HYPERLINK(url, [link_label]) =HYPERLINK("google.com", "Google")
Hyperlinks for multiple websites
Of course, cell references can be used as parameters. If you have a bunch of links and website names, you can create hyperlinks to a bunch of websites.
Hyperlink to email
With the HYPERLINK function, you can send emails by clicking the link. In this case, the email should be preceded by the “mailto:” string.
=HYPERLINK("mailto:[email protected]", "Google mail")
Clicking this link will execute your default email application.
With the help of ArrayFormula, you can link to multiple email addresses.
Here’s a list of five names and five emails. Enter this formula to cell C2 to populate values to the rest of the cells.
Now, you can click each email to start creating a new email message.
Insert link from a menu
There is an option to insert a link from the menu (Insert >> Insert link). If you like to do it quicker, just use the Ctrl + K keyboard shortcut.
Hyperlink to another sheet
Similarly, you can add a reference to another sheet or a document inside your google drive.
Click Ctrl + K to open the link menu. Next, click Sheets in this spreadsheet.
After you do this, the new link will be displayed inside a cell. Move your cursor over it and press the link.
It will make the “Sheet0” an active sheet.
Linking to another sheet is especially useful when you have a huge number of sheets inside your workbook. You don’t have to click the next button many times, but just a single hyperlink.
To remove a hyperlink, hover over the link you want to remove and click Remove link.
After you delete the link, it will convert it to the link label. If it doesn’t have a label, then to URL.
Another way to remove the link from a cell is to right-click the cell and choose unlink. This method is especially useful if you want to unlink multiple cells.
Hyperlink as a part of a text
For now (2020) you cannot create a link using the HYPERLINK function. So this formula won’t work:
="This is just a "&HYPERLINK("google.com", "link")&"!"
Selecting text and inserting text from the menu also won’t work as your text will be deselected in the process.
The only way you can do it is by using the keyboard shortcut (Ctrl + K).
First, double-click a cell with text (or click and press the F2 key) and select the text you want to change into a link.
Press Ctrl + K.
Here you go. You have a link inside the text.
Before creating a link from a list of URLs, you may want to check, whether indeed you are dealing with the correct URL. Google Sheets offers the validation method to check if a URL is correct without the need of using regular expressions.
Select cells from A2 to A6.
Navigate to Data >> Data validation.
From Criteria choose “Text” and “is a valid URL”.
Choose “Show warning”.
The two incorrect links are marked with the red triangle in the upper-right corner. If you hover the cursor over it, a message informing you about a bad URL appears.
Open hyperlink with one click
In Excel, you can hold Ctrl and click links to open them in a new tab. There is no such thing in Google Sheets.
There is another way you can open links ina new tab with a keyboard. User keyboard cursors to make a hyperlink cell active and use Alt + Enter.
This method will open a link without the need for hovering over it and then clicking.
A dropdown list of hyperlinks
If you want to have multiple hyperlinks in one cell, you can use data validation to create a dropdown menu.
This is an example of three links. What important is that these links are not using the HYPERLINK function. They are formatted as links, but they are normal text. If you use the function it won’t work.
Select a cell where you want the dropdown menu to appear.
Go to Data >> Data validation.
In Criteria, select “List from a range” and select the range (A2:A4).
Now, there is a dropdown list inside a single cell. If you click the triangle icon on the right side of the cell, it will display all values from the list. You can then choose the one you want.