You are reading the article Hyperlinks In Excel (A Complete Guide + Examples) updated in September 2023 on the website Khongconthamnam.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested October 2023 Hyperlinks In Excel (A Complete Guide + Examples)
Excel allows having hyperlinks in cells which you can use to directly go to that URL.
There are many things you can do with hyperlinks in Excel (such as a link to an external website, link to another sheet/workbook, link to a folder, link to an email, etc.).
In this article, I will cover all you need to know to work with hyperlinks in Excel (including some useful tips and examples).
There are many different ways to create hyperlinks in Excel:
Manually type the URL (or copy paste)
Using the HYPERLINK function
Using the Insert Hyperlink dialog box
Let’s learn about each of these methods.
Manually Type the URLWhen you manually enter a URL in a cell in Excel, or copy and paste it in the cell, Excel automatically converts it into a hyperlink.
Below are the steps that will change a simple URL into a hyperlink:
Select a cell in which you want to get the hyperlink
Similarly, when you copy a URL from the web (or some other document/file) and paste it in a cell in Excel, it will automatically be hyperlinked.
Insert Using the Dialog BoxIf you want the text in the cell to be something else other than the URL and want it to link to a specific URL, you can use the insert hyperlink option in Excel.
Below are the steps to enter the hyperlink in a cell using the Insert Hyperlink dialog box:
Select the cell in which you want the hyperlink
Enter the text that you want to be hyperlinked. In this case, I am using the text ‘Sumit’s Blog’
In the Insert Hyperlink dialog box, enter the URL in the Address field.
Press the OK button.
This will insert the hyperlink the cell while the text remains the same.
There are many more things you can do with the ‘Insert Hyperlink’ dialog box (such as create a hyperlink to another worksheet in the same workbook, create a link to a document/folder, create a link to an email address, etc.). These are all covered later in this tutorial.
Insert Using the HYPERLINK FunctionAnother way to insert a link in Excel can be by using the HYPERLINK Function.
Below is the syntax:
HYPERLINK(link_location, [friendly_name])
link_location: This can be the URL of a web-page, a path to a folder or a file in the hard disk, place in a document (such as a specific cell or named range in an Excel worksheet or workbook).
[friendly_name]: This is an optional argument. This is the text that you want in the cell that has the hyperlink. In case you omit this argument, it will use the link_location text string as the friendly name.
Below is an example where I have the name of companies in one column and their website URL in another column.
Below is the HYPERLINK function to get the result where the text is the company name and it links to the company website.
In the examples so far, we have seen how to create hyperlinks to websites.
But you can also create hyperlinks to worksheets in the same workbook, other workbooks, and files and folders on your hard disk.
Let’s see how it can be done.
Below are the steps to create a hyperlink to Sheet2 in the same workbook:
Select the cell in which you want the link
Enter the text that you want to be hyperlinked. In this example, I have used the text ‘Link to Sheet2’.
In the Insert Hyperlink dialog box, select ‘Place in This Document’ option in the left pane.
Enter the cell which you want to hyperlink (I am going with the default A1).
Select the sheet that you want to hyperlink (Sheet2 in this case)
Note: You can also use the same method to create a hyperlink to any cell in the same workbook. For example, if you want to link to a far off cell (say K100), you can do that by using this cell reference in step 6 and selecting the existing sheet in step 7.
You can also use the same method to link to a defined name (named cell or named range). If you have any named ranges (named cells) in the workbook, these would be listed in under the ‘Defined Names’ category in the ‘Insert Hyperlink’ dialog box.
Apart from the dialog box, there is also a function in Excel that allows you to create hyperlinks.
So instead of using the dialog box, you can instead use the HYPERLINK formula to create a link to a cell in another worksheet.
The below formula will do this:
=HYPERLINK("#"&"Sheet2!A1","Link to Sheet2")Below is how this formula works:
“#” would tell the formula to refer to the same workbook.
“Sheet2!A1” tells the formula the cell that should be linked to in the same workbook
“Link to Sheet2” is the text that appears in the cell.
You can also use the same method to create hyperlinks to other Excel (and non-Excel) files that are in the same folder or are in other folders.
For example, if you want to open a file with the chúng tôi which is in the same folder as your current file, you can use the below steps:
Select the cell in which you want the hyperlink
In the Insert Hyperlink dialog box, select ‘Existing File or Webpage’ option in the left pane.
Select ‘Current folder’ in the Look in options
Select the file for which you want to create the hyperlink. Note that you can link to any file type (Excel as well as non-Excel files)
[Optional] Change the Text to Display name if you want to.
You can also do this using the HYPERLINK function.
The below formula will create a hyperlink that links to a file in the same folder as the current file:
=HYPERLINK("Test.xlsx","Test File")In case the file is not in the same folder, you can copy the address of the file and use it as the link_location.
This one also follows the same methodology.
Below are the steps to create a hyperlink to a folder:
Copy the folder address for which you want to create the hyperlink
Select the cell in which you want the hyperlink
In the Insert Hyperlink dialog box, paste folder address
You can also use the HYPERLINK function to create a hyperlink that points to a folder.
=HYPERLINK("C:UserssumitDesktopTest","Test Folder")To use this formula, you will have to change the address of the folder to the one you want to link to.
You can also have hyperlinks which open your default email client (such as Outlook) and have the recipients email and the subject line already filled in the send field.
Below are the steps to create an email hyperlink:
Select the cell in which you want the hyperlink
Enter the E-mail address and the Subject line
[Optional] Enter the text you want to be displayed in the cell.
You can also do this using the HYPERLINK function.
The below formula will open the default email client and have one email address already pre-filled.
Note that you need to use mailto: before the email address in the formula. This tells the HYPERLINK function to open the default email client and use the email address that follows.
In case you want to have the subject line as well, you can use the below formula:
In the above formula, I have kept the cc and bcc fields as empty, but you can also these emails if needed.
Here is a detailed guide on how to send emails using the HYPERLINK function.
If you only have a few hyperlinks, you can remove these manually, but if you have a lot, you can use a VBA Macro to do this.
Manually Remove HyperlinksBelow are the steps to remove hyperlinks manually:
Select the data from which you want to remove hyperlinks.
The above steps would instantly remove hyperlinks from the selected cells.
In case you want to remove hyperlinks from the entire worksheet, select all the cells and then follow the above steps.
Remove Hyperlinks Using VBABelow is the VBA code that will remove the hyperlinks from the selected cells:
Sub RemoveAllHyperlinks() Selection.Hyperlinks.Delete End SubIf you want to remove all the hyperlinks in the worksheet, you can use the below code:
Sub RemoveAllHyperlinks() ActiveSheet.Hyperlinks.Delete End SubNote that this code will not remove the hyperlinks created using the HYPERLINK function.
You need to add this VBA code in the regular module in the VB Editor.
For some people, it’s a great feature that Excel automatically converts a URL text to a hyperlink when entered in a cell.
And for some people, it’s an irritation.
If you’re in the latter category, let me show you a way to prevent Excel from automatically creating URLs into hyperlinks.
The reason this happens as there is a setting in Excel that automatically converts ‘Internet and network paths’ into hyperlinks.
Here are the steps to disable this setting in Excel:
In the AutoCorrect dialog box, select the ‘AutoFormat As You Type’ tab.
Uncheck the option – ‘Internet and network paths with hyperlinks’
Close the Excel Options dialog box.
If you’ve completed the following steps, Excel would not automatically turn URLs, email address, and network paths into hyperlinks.
Note that this change is applied to the entire Excel application, and would be applied to all the workbooks that you work with.
There is no function in Excel that can extract the hyperlink address from a cell.
However, this can be done using the power of VBA.
For example, suppose you have a dataset (as shown below) and you want to extract the hyperlink URL in the adjacent cell.
Let me show you two techniques to extract the hyperlinks from the text in Excel.
Extract Hyperlink in the Adjacent ColumnIf you want to extract all the hyperlink URLs in one go in an adjacent column, you can so that using the below code:
Sub ExtractHyperLinks() Dim HypLnk As Hyperlink For Each HypLnk In Selection.Hyperlinks HypLnk.Range.Offset(0, 1).Value = HypLnk.Address Next HypLnk End SubThe above code goes through all the cells in the selection (using the FOR NEXT loop) and extracts the URLs in the adjacent cell.
In case you want to get the hyperlinks in the entire worksheet, you can use the below code:
Sub ExtractHyperLinks() On Error Resume Next Dim HypLnk As Hyperlink For Each HypLnk In ActiveSheet.Hyperlinks HypLnk.Range.Offset(0, 1).Value = HypLnk.Address Next HypLnk End SubNote that the above codes wouldn’t work for hyperlinks created using the HYPERLINK function.
Extract Hyperlink Using a Formula (created with VBA)The above code works well when you want to get the hyperlinks from a dataset in one go.
But if you have a list of hyperlinks that keeps expanding, you can create a User Defined Function/formula in VBA.
This will allow you to quickly use the cell as the input argument and it will return the hyperlink address in that cell.
Below is the code that will create a UDF for getting the hyperlinks:
Function GetHLink(rng As Range) As String GetHLink = "" Else GetHLink = rng.Hyperlinks(1).Address End If End FunctionNote that this wouldn’t work with Hyperlinks created using the HYPERLINK function.
Also, in case you select a range of cells (instead of a single cell), this formula will return the hyperlink in the first cell only.
If you’re working with a huge dataset that has a lot of hyperlinks in it, it could be a challenge when you want to find the ones that have a specific text in it.
For example, suppose I have a dataset as shown below and I want to find all the cells with hyperlinks that have the text 2023 in it and change it to 2023.
And no.. doing this manually is not an option.
You can do that using a wonderful feature in Excel – Find and Replace.
With this, you can quickly find and select all the cells that have a hyperlink and then change the text 2023 with 2023.
Below are the steps to select all the cells with a hyperlink and the text 2023:
Select any cell which has a hyperlink in it. You will notice that the Format gets visible in the box on the left of the Format button. This indicates that the format of the cell you selected has been picked up.
Enter 2023 in the ‘Find What’ field and 2023 in the ‘Replace with’ field.
In the above data, it will change the text of four cells that have the text 2023 in it and also has a hyperlink.
Note: This technique works as Excel is able to identify the formatting of the cell that you select and use that as a criterion to find cells. So if you’re finding hyperlinks, make sure you select a cell that has the same kind of formatting. If you select a cell that has a background color or any text formatting, it may not find all the correct cells.
While Hyperlinks are useful, there are a few things about it that irritate me.
For example, if you want to select a cell that has a hyperlink in it, Excel would automatically open your default web browser and try to open this URL.
So let me quickly show you how to get rid of these minor irritants.
Select the Cell (without opening the URL)This is a simple trick.
After a second, you’ll notice that the hand cursor icon changes into the plus icon, and now when you leave it, Excel will not open the URL.
Instead, it would select the cell.
Now, you can make any changes in the cell you want.
Neat trick… right?
This is another thing that might drive you nuts.
Here is a quick fix.
This happens when these cells have the wrap text enabled.
There are useful things you can do when working with hyperlinks in Excel.
In this section, I am going to cover some examples that you may find useful and can use in your day-to-day work.
Example 1 – Create an Index of All Sheets in the WorkbookIf you have a workbook with a lot of sheets, you can use a VBA code to quickly create a list of the worksheets and hyperlink these to the sheets.
This could be useful when you have 12-month data in 12 different worksheets and want to create one index sheet that links to all these monthly data worksheets.
Below is the code that will do this:
Sub CreateSummary() 'This code can be used to create summary worksheet with hyperlinks Dim x As Worksheet Dim Counter As Integer Counter = 0 For Each x In Worksheets Counter = Counter + 1 If Counter = 1 Then GoTo Donothing With ActiveCell .Value = x.Name With Worksheets(Counter) .Range("A1").Value = "Back to " & ActiveSheet.Name chúng tôi Sheets(x.Name).Range("A1"), "", _ "'" & chúng tôi & "'" & "!" & ActiveCell.Address, _ ScreenTip:="Return to " & ActiveSheet.Name End With End With ActiveCell.Offset(1, 0).Select Donothing: Next x End SubYou can place this code in the regular module in the workbook (in VB Editor)
This code also adds a link to the summary sheet in cell A1 of all the worksheets. In case you don’t want that, you can remove that part from the code.
You can read more about this example here.
Note: This code works when you have the sheet (in which you want the summary of all the worksheets with links) at the beginning. In case it’s not at the beginning, this may not give the right results).
Example 2 – Create Dynamic HyperlinksBut you can also use a little bit for Excel formula trickery to create dynamic hyperlinks.
By dynamic hyperlinks, I mean links that are dependent on a user selection and change accordingly.
For example, in the below example, I want the hyperlink in cell E2 to point to the company website based on the drop-down list selected by the user (in cell D2).
This can be done using the below formula in cell E2:
When you change the selection using the drop-down list, the VLOOKUP result will change and would accordingly link to the selected company’s website.
This could be a useful technique when you’re creating a dashboard in Excel. You can make the hyperlinks dynamic depending on the user selection (which could be a drop-down list or a checkbox or a radio button).
Here is a more detailed article of using Dynamic Hyperlinks in Excel.
Example 3 – Quickly Generate Simple Emails Using Hyperlink FunctionAs I mentioned in this article earlier, you can use the HYPERLINK function to quickly create simple emails (with pre-filled recipient’s emails and the subject line).
Single Recipient Email Id
Multiple Recipients Email Id
For sending the email to multiple recipients, use a comma to separate email ids. This would open the default email client with all the email ids in the ‘To’ field.
Add Recipients in CC and BCC List
Add Subject Line
You can add a subject line by using the &Subject code. In this case, this would add ‘Excel is Awesome’ in the ‘Subject’ field.
Add Single Line Message in Body
This would add a single line ‘I love Excel’ to the email message body.
Add Multiple Lines Message in Body
To add multiple lines in the body you need to separate each line with %0A. If you wish to introduce two line breaks, add %0A twice, and so on.
Here is a detailed article on how to send emails from Excel.
Hope you found this article useful.
You May Also Like the Following Excel Tutorials:
You're reading Hyperlinks In Excel (A Complete Guide + Examples)
Update the detailed information about Hyperlinks In Excel (A Complete Guide + Examples) on the Khongconthamnam.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!