While using the internet, you would have come across web pages with links to other web pages. The feature that makes this possible is called a hyperlink. It allows webpage URLs to be embedded within text or other objects in websites.
You might wonder what this has got to do with anything in Google Sheets?
Well, it has everything to do with it!
Think about this for a second. What if you can create a link in your spreadsheet that takes you to any sheet or a cell range within your dataset?
Well, there’s a lot more you can do with hyperlinks.
In this post, you will be exposed to the five methods which you can use to create a hyperlink in Google sheets.
You will see how you can add a hyperlink to a website, sheet, or a range of cells. You will also see how to edit hyperlinks and use the HYPERLINK function.
Get your copy of the example workbook to follow along and find out all about hyperlinks and more too!
Insert a Hyperlink from the Insert Menu
Virtually every task you need to carry out on your spreadsheet can be accomplished by using the correct menu options.
Adding a hyperlink is no exception, as it can be found in the Insert menu.
From the Insert menu, you’ll be able to create all the different types of hyperlinks.
You will see each of these types in the next few sections.
Insert a Hyperlink to a URL
The data contains the titles of other articles you can find on this website. Let’s say you want to access each article directly from the spreadsheet, then you will have to insert their URLs.
This will result in a clickable link that will open the specific post when you click on the title.
Here’s how you can do that from the Insert menu.
After you click on Apply, you should see the above pop-up. There are two things you should note here that will serve as a check to know whether or not you’ve done the right thing.
Taking note of these things will help you know if you’ve correctly added a URL to the data in your selected cell.
To check if you’ve added the correct link or know if the hyperlink works, simply click on the URL preview that appears at the bottom of the cell when you hover your mouse on the hyperlinked cell.
After adding a hyperlink to a cell, you don’t need to recreate the hyperlink for reuse because you can copy and paste it to any area within your worksheet.
However, any additional characters added to the anchor text will not carry the hyperlink. To ensure the hyperlink reflects additional characters, you will have to start from the beginning.
💡 Tip: Now that you have a hyperlinked URL, you might need to get the URL without the anchor text. There are multiple ways to extract the URL from a hyperlink.
Insert a Hyperlink to a Sheet
When working with a spreadsheet that has only a few sheets, going back and forth across sheets may not be much trouble.
But as the number of sheets begins to increase, you might have a real problem navigating across sheets. It becomes even more tedious when you have to call on data stored in different areas.
With the hyperlink feature, this can become a non-issue because you can create a hyperlink linked to a sheet.
The process of inserting a hyperlink to a sheet is similar to the process involved when adding a hyperlink to a URL.
To hyperlink to a sheet follow these steps.
You’ve inserted a hyperlink to a sheet which is indicated by the blue underlined text.
💡 Tip: You can create a table of contents that lists to all your sheets and then hyperlink to them for easy navigation.
Hovering your mouse on top of the cell containing the hyperlink, you will get a quick view of the linked address which in this case is the sheet name.
You can make use of these handy tips can when adding a hyperlink to a sheet.
In the above example, a sheet hyperlink has been added to the text 3 Easy Ways to Transpose Data in Google Sheets.
The quick view of the hyperlink displays Sheet2. That doesn’t convey any information about the nature of the data in Sheet2.
Naming the sheet with something more descriptive such as Articles will be more useful for anyone using the spreadsheet.
This is exactly what has happened in the example above. The sheet has been linked to text, which doesn’t say what the link contains.
Issues like this one will require that the hyperlink be edited. Editing a hyperlink will be shown in a subsequent section of this post.
Nevertheless, you should know that when you’re hyperlinking to a sheet, you can select any cell within the spreadsheet, even a blank one.
Selecting a blank or empty cell is even preferable because once you insert the hyperlink, the cell carries the name of the sheet by default.
See why it’s good practice to always rename your sheets?
Insert a Hyperlink to a Range of Cells
Large sets of data can be a navigational nightmare.
You not only have to move across sheets, but you often need to recall the location of specific datasets within each sheet. If the dataset spans multiple columns and rows, then you will have your work cut out for you.
In this situation, named ranges can be quite useful.
It involves giving a range of cells a preferred name so they can be easily accessed and referenced anywhere inside the spreadsheet.
However, the hyperlink feature takes the efficiency of named ranges to the next level and makes accessing specific data from your large dataset a lot easier.
Adding a hyperlink to a range of cells is pretty easy.
You can select any cell including an empty or blank cell. For inserting a hyperlink to a sheet, a named range, or a range of cells, it’s best to select an empty or blank cell.
When you click OK, you will have successfully inserted a hyperlink to a range of cells. And by placing your mouse above the cell, you get a quick view of the hyperlink’s location.
Notice how the hyperlink references a cell range in Sheet 1, but it was created on a cell in Sheet2. This shows that you can insert a hyperlink to any cell range in any sheet in your spreadsheet.
Now, you can easily move across sheets with fewer clicks. Additionally, you can also go to specific data ranges within any of your sheets by simply creating a link to them!
You may have noticed this Text box atop the Search or paste a link box and wondered what it does.
Its function is simple. This box is where you can add the anchor text for your hyperlink. So, if you don’t want the hyperlink to show Sheet 1’!A2:A7, you can use the Text box to give it a preferred name.
You can also insert a hyperlink to a named range. First, you will need to create a named range.
To create a named range follow these steps.
You should note that named ranges can’t contain spaces. You can use an underscore character (_) in place of a space character to get the same effect of separated words.
To be sure you’ve created a named range, deselect the range of cells you have previously selected. Go to the Name box and click on the small drop-down arrow icon to the right of the box.
You should find the named range in the dropdown list. When you click on it, the corresponding ranges should be selected. You can use the Manage named ranges option to edit or delete any named range.
Now that you have created a named range, you can follow the steps for inserting a link to a range of cells up to the point where you click on the Sheets and named ranges option.
You’d notice that the named range created has been listed under NAMED RANGES. All the named ranges you create in your spreadsheet will appear under this section.
If you don’t want the hyperlink to use the named range title as the display or anchor text, you can use the Text box to give a preferred name to the hyperlink.
The result of using a named range and selecting a range of cells is the same. But using named ranges is a more efficient way to insert hyperlinks to cell ranges, especially when you’re dealing with a large dataset.
With a named range, you will not have to navigate to the sheet that contains the cell range you want to hyperlink to because the named range will appear under the NAMED RANGES section.
Additionally, using a named range would reduce the risk of selecting cells whose data shouldn’t be included in the range. This will prove more efficient if you create the named ranges first.
How to Edit a Hyperlink
You’ve learned how to insert a hyperlink in your spreadsheet, now you will see how to edit a hyperlink.
This comes in handy in many situations, especially when you unintentionally hyperlink to the wrong address.
There are three ways by which you can edit a hyperlink in Google spreadsheets. No method is better than the other because they produce the same result.
For the first method, simply place your mouse over the hyperlinked cell to reveal the quick view box.
From there, you will see three icons. By placing your mouse on the icons, you will get a description of the function each icon performs. To edit your hyperlink, click on the Edit icon in the middle.
For the second method, simply select the cell that contains the hyperlink and right-click on it. From the list of options, select the Edit link option.
You can also use the Edit link option from the Insert menu to edit a hyperlink.
Whichever method you use, you will end up getting this dialogue box, which is the same box you used for inserting the hyperlink.
From here, you can replace both the hyperlink and the anchor text. Make sure to click Apply when you’re done.
Remove a Hyperlink But Keep the Text
First, select the cell containing the hyperlink. Then, from the icons in the quick view box, click the Remove link icon.
After you do this, the cell will no longer be highlighted in blue. Placing your mouse over the cell will also not show you a quick view of the link as before. This way, you know you have removed the hyperlink without deleting the text.
Insert a Hyperlink from the Toolbar
The toolbar is a location on your spreadsheet that contains a few quick access commands. It is situated directly beneath the menu options. You can use the caret on the far right to hide or reveal the toolbar.
Not all the quick access commands will be revealed in the toolbar if your screen is not wide enough. To see the rest, click on the ellipsis icon.
You can run your mouse across each icon to know what function they perform. To insert a hyperlink, use the Insert link icon.
Clicking on this icon opens the same dialogue box you get when you use the Insert menu method.
Insert a Hyperlink with a Keyboard Shortcut
All the clicks involved in inserting a hyperlink can be reduced by using the simple and easy-to-remember keyboard shortcut. Press Ctrl + K on your keyboard to insert a hyperlink.
Select the cell you want to include the hyperlink, and then simply use the keyboard shortcut Ctrl + K. This will open the insert link dialogue box.
Using this shortcut comes with a little perk. It allows you to add multiple hyperlinks to different parts of the text contained in a single cell.
In this example, the article titles are entered into a single cell. using the Insert menu will not allow you to add separate hyperlinks to each article title, but the keyboard shortcut will allow you to achieve this.
Select the part of the text you want to add a hyperlink to.
Press the keyboard shortcut Ctrl + K. You will notice that only the previously selected text remains highlighted in light green. This indicates that only these characters will get the hyperlink.
Notice that the dialogue is missing the Text box that is always present when you use the Insert menu process. Consequently, you will not be able to alter the hyperlink’s anchor text.
So ensure you get it right before adding the hyperlink!
When you click on the Applybutton, only the selected text will get the hyperlink.
The quick view box reveals that there are three different hyperlinks within the cell and you can choose either one to visit.
📝 Note: You can also use the hyperlink icon from the toolbar to achieve this result.
Insert a Hyperlink by Pasting a URL
You may want to paste a URL to a cell without creating or needing an anchor text.
You can simply copy the URL of the webpage and paste it into any cell within your spreadsheet. With a connection to the internet, Google will recognize right away that it’s a link and even provide the headline for the link in the quick view box.
This is perhaps the easiest way of inserting a URL hyperlink into your spreadsheet.
Insert a Hyperlink with the HYPERLINK Function
The HYPERLINK function is super easy to use for inserting hyperlinks. It compiles all the processes we’ve been following into one simple command that requires only two arguments.
Syntax of the HYPERLINK Function
It displays contents in the
Example with the HYPERLINK Function
The highlighted cells in yellow show how the HYPERLINK function has been used.
Unfortunately, the HYPERLINK function cannot be used to link to other sheets, named ranges, or a range of cells.
Create a Mailto Link with the HYPERLINK Function
You can use the HYPERLINK function to create a mailto link. When a mailto link is created, the user can create an email that is ready to send directly from the spreadsheet by simply clicking the link.
The email link works by activating the default email application while also filling in the recipient’s email at the same time.
Use the above syntax to create a mailto link with the HYPERLINK function.
The difference between this syntax and the default syntax is that, instead of entering a traditional URL into the
3 is used.
This command tells the HYPERLINK function to treat any link in the
Now when you click on this link, it will open an email addressed to that email address.
You can also repopulate the subject line and email body by appending the above subject and body parameters to the mailto link.
This can be a great way to dynamically generate emails in a single click!
Overall, the methods used to insert a hyperlink produce almost similar results. However, there are some variations in what they can do.
While using the Insert menu won’t allow you to add hyperlinks to different characters in a single cell, it provides the Text box where you can alter or give a preferred anchor text to display in the cell.
Although the keyboard shortcut or toolbar method won’t provide the text box, you can add different hyperlinks to different parts of the text in a single cell.
The HYPERLINK function is great, but you cannot create hyperlinks to a sheet, a named range, or a range of cells. Nevertheless, it allows you to create some interesting and dynamic solutions such as mailto links.
Ultimately, what method you use depends on what result you want to achieve.
Which method do you prefer the most? Are there any other methods you know? Let us know in the comments below!
How do I automate a hyperlink in Google Sheets?
Link to data in a spreadsheet.
In Sheets, click the cell you want to add the link to..
Click Insert. Link..
In the Link box, click Select a range of cells to link..
Highlight the cell or range of cells you want to link to. ... .
(Optional) Change the link text..
How do I hyperlink multiple links in Google Sheets?
Multiple Hyperlinks in One Cell – Google Sheets Unlike Excel, Google Sheets lets you insert multiple hyperlinks into a cell without using text boxes. Select the text you want to link (in this case COUNTA) and click Insert Link in the menu (or use the keyboard shortcut CTRL + K).
How do you hyperlink in Google Sheets formula?
Here are the steps you need to follow to use HYPERLINK in Google Sheets:.
Click the cell where you want to enter the formula..
Enter the starting part of the formula, which is =HYPERLINK(..
Now enter the first parameter, which is the link parameter. ... .
Add a comma and then write the label parameter..
How do I automatically link data from one sheet to another in Google Sheets?
Use the IMPORTRANGE function.
In Sheets, open a spreadsheet..
In an empty cell, enter =IMPORTRANGE..
In parenthesis, add the following specifications in quotation marks and separated by a comma*: The URL of the spreadsheet in Sheets. ... .
Click Allow access to connect the 2 spreadsheets..