Google Sheets dynamic hyperlink

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?

Awesome, right?

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.

• Hyperlink to a URL.
• Hyperlink to range of cells.
• Hyperlink to a sheet.

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.

1. Select the cell where you want to insert the hyperlink. You can also do this by double-clicking on the cell

1. Go to the Insert menu.
2. Select the Link option from the menu.

1. After clicking on the Link option from the dropdown menu, you will get a dialogue box that looks exactly like the one in the above image.

1. In the Search and paste a link box, type the link you want to insert or you can copy the webpage’s link from the search bar of your browser and paste it here.

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.

• The anchor text (the text to which the URL or web link is applied) will be automatically highlighted in blue. This is how Google sheets indicate to users of the data that the character in the cell contains a link to an external source.
• There will be a preview of the weblink at the bottom of the cell. This is going to be visible if you have the cell selected, or you place the cursor on the hyperlinked cell.

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.

1. Follow the first 3 steps in the previous example.

1. At this point, instead of pasting a URL into the Search or paste a link box as per the previous example, you will click on the Sheets and named ranges option.

1. A dialogue box opens up showing a list of all the sheets in your workbook, including sheets that have been hidden from view. You can click on any of the sheets to which you want to insert the hyperlink.

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.

• Rename your sheets! By default, your sheets are named Sheet1, Sheet2, and so on. It’s always best practice to give a sheet a name that describes the nature of data it contains or describes its purpose. For instance, a sheet that contains names, addresses, region/country, employment status, etc, can be named Demographics. This makes for easier referencing when looking at the hyperlink quick view.

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.

• Use an anchor text that explains the content of the hyperlink. It’s important to emphasize this. Hyperlinks are meant to make your work in a spreadsheet easier. If the anchor text doesn’t explain what the link contains at a glance, then it will be less useful.

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.

1. Go to the Insert tab and select Link from the menu options.

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.

1. Click on the Sheets and the named ranges option.

1. Click on Select a range of cells to link option

1. After clicking on the Select a range of cells to link option, you will get the Select a data range dialogue box.

1. Inside the search box, you can type the location of the cell range you want to hyperlink if you know the syntax. Otherwise, you can simply navigate to where the cell range is located and select it.

1. Press the OK button to create the link.

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.

1. Select the range of cells you want to name

1. Go to the Name box, double click to highlight the existing text in the box. Use the Backspace or Delete key on your computer to clear the text or simply start typing your preferred name. Press Enter when you’re done.

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

HYPERLINK ( url, label )

• url is the web address to which you would like to create the link. This is a required argument and it must be enclosed in double quote characters.
• label is the anchor text to display in the cell. This argument is optional and if omitted, the cell will display the url as the anchor text.

The label argument takes the text display or anchor text for the hyperlink.

It displays contents in the url argument by default. So, when it’s omitted, the function will return the URL as is. When the argument is used, content must also be enclosed in quotation marks. The label argument takes cell references too.

Example with the HYPERLINK Function

The highlighted cells in yellow show how the HYPERLINK function has been used.

• Row 2 shows the HYPERLINK function used with the optional label argument as a text string.
• Row 3 shows the HYPERLINK function used with the optional label argument referencing a cell that contains a text value.
• Row 4 shows the HYPERLINK function used without the optional label argument.

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.

= HYPERLINK ( "mailto:[email protected]", "Send Email" )

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 url argument section,

= HYPERLINK ( "mailto:[email protected]", "Send Email" )

This command tells the HYPERLINK function to treat any link in the url argument as an email address.

Now when you click on this link, it will open an email addressed to that email address.

= HYPERLINK ( "mailto:[email protected]?subject=your subject text goes here&body=your email body text goes here", "link_label" )

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!

Conclusions

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?

How do I hyperlink multiple links in Google Sheets?

How do you hyperlink in Google Sheets formula?

How do I automatically link data from one sheet to another in Google Sheets?