Excel hyperlink tutorial: create, change, use and remove in Excel

As far as we know, hyperlinks can be used in internet to navigate between websites. In Excel, you can create hyperlink to not only open a web page, but also link to a cell, worksheet, workbook and so on. In this tutorial, we explain how to create, change, use as well as remove hyperlinks gradually to help you quickly master hyperlink in Excel.

---

1.What is hyperlink in Excel?

An Excel hyperlink is a reference, which helps to open one of the following items with just a mouse click away:

1. A file
2. A certain location in a file
3. A web page
4. An email massage

Normally, an Excel hyperlink displays with text highlighted in blue underlined font in a cell as the below screenshot shown.

---

2. Types of hyperlinks

There are two types of hyperlinks in Excel: absolute hyperlinks and relative hyperlinks. This section will talk about the difference between these two types of hyperlinks.

2.1 Absolute hyperlinks in Excel

An absolute hyperlink contains a full address including the form as: protocol://domain/path.

See the below examples:

2.2 Relative hyperlinks in Excel

A relative hyperlink always contains less information than an absolute hyperlink.

See the below examples:

For a web page, using relative hyperlinks helps the pages loading more quickly than using absolute hyperlinks.

In Microsoft Excel, when creating external links, you can use either absolute hyperlink or relative hyperlink to achieve it, but using relative paths is highly recommended in this case. With relative hyperlink, you can move the workbooks without breaking the links between them in the future. However, this depends on the locations of the linked file and the source data file:

1. The linked file and the source data file are in the same folder.
2. The source data file is located in a folder that is nested in the same root folder as the linked file.

For example, the linked file locates on C:\Users\linked.xlsx and the source data file locates on C:\Users\info\source.xlsx. In this case, with relative hyperlink, you can move both linked file and source data file to any location without breaking and updating the hyperlink as long as the source data file is still located in the subfolder called “info”. If you are using absolute hyperlink in this case, the path should be updated every time the file is moved.

---

3. Basic approach to create hyperlink in Excel

Excel provides two methods for users to create hyperlinks in Excel.

3.1 Create hyperlink with the Excel Hyperlink feature

The most traditional method to create hyperlink is using the Insert Hyperlink command. In this section, we will demonstrate how to apply the Insert Hyperlink command to create hyperlinks in Excel.

1. Select a cell you want to add hyperlink, and then click Insert > Hyperlink.

Notes:

2. Then the Insert Hyperlink dialog box pops up, you can configure as follows depending on what sort of hyperlink you want to create.

3.1.1 Create a hyperlink to another file

In the Insert Hyperlink dialog box, if you want to create an external link to another file, you need to configure as below:

1. Keep the Existing File or Web Page option selected in the Link to pane;

2. In the Look in list box, specify a file you want to link.

Tips: You can directly choose a file in the list box, click the Look in drop-down arrow to display all drop-down items for selecting, or click the Browse for File icon to open the Link to File dialog box for file selecting.

3. In the Text to display box, if the selected cell has value, the value will display here, you can change it if you need.

4. It is optional to display a screen tip while hover the cursor over the hyperlink. You can click the ScreenTip button, enter the screen tip text and click OK.

5. Click OK.

Now you have created a hyperlink to a certain file. When clicking the hyperlink, the file will be opened at once.

3.1.2 Create a hyperlink to website

In the Insert Hyperlink dialog box, you can also create a hyperlink to a web page as follows.

1. Keep the Existing File or Web Page option selected in the Link to pane;

2. Fill in the Address and Text to display boxes.

1. If you remember the linked web page, enter it into the Address box directly.
2. If you don’t remember the linked web page clearly but you have saved it in the favorites of your web browser, click the Browse the Web button in the Look in section to open the web browser. In the web browser, open the web page you will link to, then go back to the Insert Hyperlink dialog box without closing the web browser. You will see the Address and the Text to display fields are filled with currently opened web page automatically.

Tips: You can change the Text to display text or add ScreenTip as you need.

3. Click OK.

3.1.3 Create a hyperlink to specific location of current workbook

To create a hyperlink to a specific location of current worksheet or workbook, you can configure the Insert Hyperlink dialog box as follows.

1. Keep the Existing File or Web Page option selected in the Link to pane;

2. Click the Bookmark button.

3. Then the Select Place in Document dialog box pops up, type a cell address where you want to locate in the Type in the cell reference box, select the worksheet or named range as you need, and then click OK.

4. Click OK to finish the settings when it returns to the Insert Hyperlink dialog box.

Another solution: In the Insert Hyperlink dialog box, select the Place in This Document option in the Link to pane, specify the worksheet/named range and cell you will link to, and then click OK. See screenshot:

3.1.4 Create a hyperlink to an email address

In this section, we are going to show you how to create a hyperlink in the Insert Hyperlink dialog box to create an email message.

1. Select the E-mail Address option in the Link to pane;

2. In the Text to display box, if the selected cell has value, the value will display here, you can change it if you need.

3. In the E-mail address field, enter an email address or multiple email addresses separated with semicolons.

4. In the Subject field, enter the email subject directly.

Tips: After adding the email subject, if you want to include the email body, please add &body=body content at the end of the subject, such as FAQs of Kutools for Excel&body=I want to know more about your product.

3. Click OK.

From now on, when clicking the hyperlink, an Outlook email will open, you can see the To, Subject and Email body fields are filled with certain email address and contents we specified above.

Note: To force this mailto hyperlink to open in Outlook, please make sure that Outlook has been set as the default mail client in your computer.

3.2 Create hyperlink with the HYPERLINK function

Apart from the Insert Hyperlink command, you can also apply the HYPERLINK function to create a hyperlink in Excel.

Syntax

HYPERLINK(link_location, [friendly_name])

Arguments

Here take some examples to demonstrate how to apply the HYPERLINK function to create hyperlinks in Excel.

Example 1: Link to another file with HYPERLINK function

Supposing you want to create a hyperlink to a txt file named “test” that locates in path: C:\Users\Win10x64Test\Documents\My files, you can apply the HYPERLINK function as follows to create a hyperlink to it.

1. Select a cell to place the hyperlink, enter the below formula into it and press the Enter key.

=HYPERLINK("C:\Users\Win10x64Test\Documents\My files\test.txt","Click to open the txt file named test")

If the linked file and the source data file are in the same folder, use relative hyperlink (the file path contains only the file name and file extension) in the HYPERLINK function as follows:

=HYPERLINK("test.txt","Click to open the txt file named test")

If the source data file is located in a folder that is nested in the same root folder as the linked file, use relative hyperlink (the file path contains only the root folder name, file name and file extension) in the HYPERLINK function as follows:

=HYPERLINK("My files\test.txt","Click to open the txt file named test")

After creating, click the hyperlink will open the test.txt file directly.

Example 2: Link to a website with HYPERLINK function

The HYPERLINK function can also support creating hyperlinks to websites.

1. Select a blank cell to place the hyperlink, enter the below formula into it and press the Enter key.

=HYPERLINK("https://www.extendoffice.com/","Extendoffice")

Example 3: Link to a specific location of current workbook with HYPERLINK function

Supposing you want to link to cell D100 of Sheet4 in current workbook, you can apply the below formula to get it done.

1. Select a blank cell to output the hyperlink, enter the below formula into it and press the Enter key.

=HYPERLINK("#Sheet4!D100","Go to D100 of Sheet4")

Example 4: Link to an email address with HYPERLINK function

To create hyperlink to email address, you can concatenate the HYPERLINK function with “mailto”.

=HYPERLINK("mailto:"&"email address","text to display")

1. Select a blank cell, enter the below formula into it and press the Enter key.

=HYPERLINK("mailto:"&"","email to")

To include two email addresses in the formula, apply this formula:

=HYPERLINK("mailto:"&"Email address A"&","&"Email address B","Text to display")

From now on, when clicking the hyperlink, an Outlook email message is created with the specified email addresses listing in the To field.

---

4. Create hyperlinks under different circumstances

While using Excel, you may need to create hyperlinks under special circumstances which the Insert Hyperlink command and the HYPERLINK function can’t achieve, such as create multiple hyperlinks at the same time, create one hyperlink to each worksheet of a workbook in bulk, create dynamic hyperlink based on cell value and so on.

In this section, we list different circumstances for creating hyperlinks and the corresponding methods to achieve.

4.1 Create multiple hyperlinks in one or multiple cells

Using the traditional methods above can only create one hyperlink per time in a cell, if you want to create multiple hyperlinks in one or multiple cells, the below methods can do you a favor.

4.1.1: Create multiple hyperlinks in one cell with shapes

By default, Excel only allows creating one hyperlink in one cell per time. However, there is a little trick for you: using shapes to create multiple hyperlinks in one cell.

Supposing there is a sentence in B1 as the below screenshot shown, and you want to separately add different hyperlinks to texts “Extendoffice”, “Kutools for Excel”, “Kutools for Outlook” and “Kutools for Word”, you can do as follows.

1. Firstly, separately format these texts as hyperlink texts appearance by changing the font color to blue and adding underlines. After that, you can see the results as follows.

2. Click Insert > Shapes > Rectangle.

3. Draw a rectangle to cover the text to be hyperlinked. In this case, I draw a rectangle to cover text “Extendoffice”. See screenshot:

4. Right click the rectangle and select Format Shape from the context menu to open the Format Shape pane.

5. In the Format Shape pane, change the Fill and Line options to No fill and No line under the Fill & Line tab.

Now the shape is transparent.

6. Keep the shape selected, right click and select Hyperlink from the context menu.

7. In the Insert Hyperlink dialog, specify an address and then click OK. See screenshot:

Tips: In the Insert Hyperlink dialog box, you can configure different sort of hyperlinks as you need, click to know how.

8. Repeat the step 2 to 7 until the other texts in the cell are added hyperlinks.

After adding multiple hyperlinks in a cell, you can see the result as the below demo shown.

4.1.2 Create multiple hyperlinks for multiple cells with an amazing tool

To add multiple hyperlinks for many cells, here highly recommend the Convert Hyperlink feature of Kutools for Excel.

Supposing there are two columns separately containing texts and corresponding hyperlink addresses, to add hyperlinks to all texts based on the corresponding hyperlink addresses in bulk, you can do as follows.

1. Click Kutools > Link > Convert Hyperlinks. See screenshot:

2. In the Convert Hyperlinks dialog box, you need to configure as follows.

So far, you have added different hyperlinks to selected cells in bulk as the below screenshot shown.

4.2 Create hyperlink to each worksheet of a workbook

Supposing you received a sales report workbook contains 12 worksheets from Jan to Dec, and you need to navigate between the worksheets frequently to find and analyze data. In this circumstance, creating a hyperlinked index of worksheets will definitely help and save a lot of time. This section demonstrates two methods in details to help quickly create a hyperlinked index of sheets in current workbook.

4.2.1 Create hyperlink to each worksheet of a workbook with VBA code

You can apply the below VBA code to create index of sheets with hyperlinks in a workbook.

1. In the workbook you want to create sheet index, press the Alt + F11 keys simultaneously.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module window.

VBA code: Create hyperlinked index of sheets in current workbook

Sub CreateIndex()
'Updateby Extendoffice 20210825
    Dim xAlerts As Boolean
    Dim I  As Long
    Dim xShtIndex As Worksheet
    Dim xSht As Variant
    xAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("Index").Delete
    On Error GoTo 0
    Set xShtIndex = Sheets.Add(Sheets(1))
    xShtIndex.Name = "Index"
    I = 1
    Cells(1, 1).Value = "INDEX"
    For Each xSht In ThisWorkbook.Sheets
        If xSht.Name <> "Index" Then
            I = I + 1
            xShtIndex.Hyperlinks.Add Cells(I, 1), "", "'" & xSht.Name & "'!A1", , xSht.Name
        End If
    Next
    Application.DisplayAlerts = xAlerts
End Sub

3. Press the F5 key to run the code.

Then a new worksheet named “Index” has been inserted before all worksheets. All sheet names with hyperlinks are listed in a column, and you can click on any hyperlinked sheet name to open the corresponding worksheet immediately.

4.2.2 Easily create hyperlink to each worksheet of a workbook with an amazing tool

If you are not good at handling VBA code, here highly recommend the Create List of Sheet Names feature of Kutools for Excel. With this feature, you can easily create a hyperlinked index of sheets in a workbook with only several clicks.

1. In the workbook you want to create hyperlinked index of sheets, click Kutools Plus > Worksheet > Create List of Sheet Names.

2. In the Create List of Sheet Names dialog box, you need to do the below settings.

Tips: Displaying Sheet Index into: It is optional to display the sheet index into 2 or more columns if the list is too long to display on one screen.

Then the hyperlinked index of sheets is created in current workbook as the below screenshot shown.

4.3 Create dynamic hyperlinks based on cell value

As the below demo shown, there are two worksheets, one contains a drop-down list, another contains the source data of the drop-down list. Now you need to create a hyperlink next to the drop-down list, after selecting an item from the drop-down list, click the hyperlink will jump to the cell containing this item in the source data worksheet directly.

Click to know how to create dynamic hyperlinks based on cell value in Excel.

4.4 Create hyperlink from plain text

Assuming that you have a list of URLs displaying as plain texts that needs to be submitted to your supervisor. Before handing it, you need to convert all the URL plain text to clickable hyperlinks. How can you achieve it? This section provides two methods to help you get it done.

4.4.1 Convert URL text to clickable hyperlink with VBA code

You can run the following VBA code to create hyperlink from URL plain texts in Excel.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module window.

VBA code: Convert URL text to clickable hyperlink in Excel

Sub ConvertToHyperlinks()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    Application.ActiveSheet.Hyperlinks.Add Rng, Rng.Value
Next
End Sub

3. Press the F5 key to run the code. In the pop up Kutools for Excel dialog box, select the range containing the plain texts you want to convert to hyperlinks and then click OK.

Then all plain texts in selected cells have been converted to hyperlinks as the below screenshot shown.

4.4.2 Convert URL text to clickable hyperlink with an amazing tool

This section introduces a handy feature – Convert Hyperlinks feature of Kutools for Excel to help easily convert URL texts to clickable hyperlinks in Excel.

1. Select the range containing the URL plain texts you want to convert to hyperlinks, and then click Kutools > Link > Convert Hyperlinks.

2. In the Convert Hyperlinks dialog box, you need to configure as follows.

Note: The selected range is displayed in the Input range box automatically, you can change it as you need.

Then all plain texts in selected cells have been converted to hyperlinks at once.

4.5 Create hyperlinks to all files in a folder

As the below screenshot shown, there are different kinds of files in the folder and you want to create hyperlinks to all of them or only certain type of them in a worksheet, the below methods can help you get it done.

4.5.1 Auto list all file names in a folder and create hyperlinks with VBA code

You can apply the below VBA code to list all file names in a certain folder and create hyperlinks automatically at the same time. Please do as follows.

1. In a workbook, create a new sheet to output the hyperlinked file names.

2. Press the Alt + F11 keys at the same time to open the Microsoft Visual Basic for Applications window.

3. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module window.

VBA code: List all file names in a folder and create hyperlinks

Sub ListFileNames()
'Update by Extendoffice 20210827
    Dim xFSO As Object
    Dim xFolder As Object
    Dim xFile As Object
    Dim xFiDialog As FileDialog
    Dim xPath As String
    Dim I As Integer
    Set xFiDialog = Application.FileDialog(msoFileDialogFolderPicker)
    If xFiDialog.Show = -1 Then
        xPath = xFiDialog.SelectedItems(1)
    End If
    Set xFiDialog = Nothing
    If xPath = "" Then Exit Sub
    Set xFSO = CreateObject("Scripting.FileSystemObject")
    Set xFolder = xFSO.GetFolder(xPath)
    For Each xFile In xFolder.Files
        I = I + 1
        ActiveSheet.Hyperlinks.Add Cells(I, 1), xFile.Path, , , xFile.Name
    Next
End Sub

4. Press the F5 key to run the code. In the popping up Browse window, select the folder containing the files you want to create hyperlinks in Excel, and then click OK.

Then all file names in selected folder are listed in column A starting from cell A1 in the new worksheet. At the same time, hyperlinks are created for each file. You can click on any hyperlinked file name in the cell to open the file directly.

Note: If there are subfolders in the specific folder, the filenames in the subfolders will not be listed.

4.5.2 Easily list files in a folder and create hyperlinks with an amazing tool

The above VBA code only allows you to list all file names in a folder. Here recommend the Filename List feature of Kutools for Excel. With this feature, you can easily list all file names or certain types of file names with hyperlinks in not only a folder but also its subfolders.

1. Click Kutools Plus > Import & Export > Filename List.

2. In the Filename List dialog box, please do the below settings.

Then you can see all filenames in specified folder(s) are listed with hyperlinks in a new created worksheet as the below screenshot shown.

In the worksheet, you can click the filename to open the file, or click the folder path to open the folder.

---

5. Change hyperlinks in Excel

After creating a hyperlink, you may need to modify it, for example, change the link text or location, change the link appearance, or change the link path. Here will guide you to solve the problems one by one.

5.1 Change link text or location with the Edit Hyperlink feature

You can apply the Edit Hyperlink feature to change the link text, link location or both as you need.

1. Select the hyperlink cell, right click and select Edit Hyperlink from the context menu, or Press Ctrl + K keys.

2. In the Edit Hyperlink dialog box, change the options as you need and then click OK to save the changes.

For example, to change the link text, modify the texts in the Text to display box.

5.2 Change appearance of hyperlinks in current workbook

By default, Excel displays hyperlink as underlined blue formatting. This section demonstrates how to change the appearance of hyperlinks in Excel.

If you want to change the appearance of hyperlinks that have not been clicked yet in current workbook, please configure as follows.

1. Under the Home tab, right click Hyperlink in the Styles box, and then click Modify in the context menu.

2. In the Style dialog box, click the Format button.

3. Then it gets into the Format Cells dialog box, change the options as you need under the Font tab, and then, click OK to save the changes.

In this case, I change the font style and font color for hyperlinks. See screenshot:

4. When it returns to the Style dialog box, click OK.

Then you can see the hyperlinks that have not been clicked yet in current workbook are changed to specified formatting as the below screenshot shown.

Note: To change the appearance of hyperlinks that have been clicked, right click Followed Hyperlink in the Styles box under the Home tab, select Modify from the context menu, and then do the same operations as above step 2-4 shown.

5.3 Change multiple hyperlink paths at once in Excel

Supposing you have created the same hyperlink for multiple cell data in a worksheet, to change the hyperlink path to another new path in bulk at once, you can try one of the below methods.

5.3.1 Change multiple hyperlink paths at once with VBA code

The below VBA code can help you change the same hyperlink path in active worksheet at once. Please do as follows.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module window. See screenshot:

VBA code: Change multiple hyperlink paths at once

Sub ReplaceHyperlinks()
'Update by Extendoffice 20210831
Dim Ws As Worksheet
Dim xHyperlink As Hyperlink
Dim xOld As String, xNew As String
xTitleId = "KutoolsforExcel"
Set Ws = Application.ActiveSheet
xOld = Application.InputBox("Old text:", xTitleId, "", Type:=2)
xNew = Application.InputBox("New text:", xTitleId, "", Type:=2)
Application.ScreenUpdating = False
For Each xHyperlink In Ws.Hyperlinks
    xHyperlink.Address = Replace(xHyperlink.Address, xOld, xNew)
Next
Application.ScreenUpdating = True
End Sub

3. Press the F5 key to run the code.

4. In the popping up KutoolsforExcel dialog box, enter the hyperlink address text you want to replace, and click OK.

5. In the second popping up KutoolsforExcel dialog box, enter the new hyperlink address text you want to replace with, and then click the OK button.

In this case, I replace all “addin” with “extendoffice” in hyperlinks in current worksheet. You can see the result as the below screenshot shown.

5.3.2 Change multiple hyperlink paths at once with an amazing tool

With the Find and Replace in Multiple Workbooks feature of Kutools for Excel, you can easily change multiple hyperlink paths at once in not only a selected range, but also in multiple selected sheets, all opened workbooks or active workbook.

1. Click Kutools > Find > Find & Replace in Multiple Workbooks. See screenshot:

2. Then the Find and Replace pane is displayed on the left side of the workbook, you need to configure as follows.

After replacing, the results are listed in the below list box.

---

6. Tips for using hyperlinks in Excel

This section provides some tricks for using hyperlinks.

6.1 Select a cell without opening the hyperlink

Sometimes, when selecting a cell with hyperlink, the hyperlink may be opened at once. How can we select a cell without opening the hyperlink? The little trick in this section will do you a favor.

Click the cell without releasing the left mouse until the cursor turns to .

You can see the cell has been selected without affecting the hyperlink as the below demo shown.

6.2 Open multiple hyperlinks in bulk with VBA code

Here provide a VBA code to help you open multiple hyperlinks in bulk in Excel.

1. Press the Alt + F11 keys.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module window.

VBA code: Open multiple hyperlinks in bulk

 Sub OpenHyperLinks()
'Update by Extendoffice 20210831
      Dim xHyperlink As Hyperlink
      Dim WorkRng As Range
      On Error Resume Next
      xTitleId = "KutoolsforExcel"
      Set WorkRng = Application.Selection
      Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
      For Each xHyperlink In WorkRng.Hyperlinks
         xHyperlink.Follow
      Next
End Sub

3. In the KutoolsforExcel dialog box, select the cells containing the hyperlinks you want to open in bulk, and then click OK.

Then all hyperlinks in selected range are opened immediately.

6.3 Change default browser when opening hyperlink

When clicking a hyperlink, it will be opened with window’s default browser. It is very common for someone to have more than one web browser on his or her computer. In this section, we are going to show you the steps to change the default browser, so that any Excel links you click in the future will open in your preferred browser.

1. In windows 10, click the Type here to search button besides the Start button in the bottom-left corner of the window, type control panel into the search box, and then click Control Panel when it searched out.

Tips: In windows 7, click the Start button in the bottom-left corner of the window, and then find and click Control Panel from the menu.

2. In the Control Panel window, click Programs.

3. In the Programs window, click Default Programs. See screenshot:

4. In the Default Programs window, click Set your default programs.

5. In the Settings window, you can see the current default browser is displayed in the Web browser section, click on the default browser to expand the browser list, and then choose a browser from the list that you would like to use to open your Excel links.

6. Now the default browser is changed to the specified one. Close all Control Panel related windows.

From now on, all links will open in the specified web browser.

6.4 Extract URLs from hyperlinks

Supposing you have a list of cells containing hyperlinks in a column, to extract real URL addresses from these hyperlinks, how can you do? Actually, there is no built-in feature can achieve. In this section, we gather 4 methods to help solve this problem.

6.4.1 Extract an URL from one hyperlink by manually copying

The most common method to get the URL from a hyperlink is copying from the Edit Hyperlink dialog box.

1. Right click the cell containing hyperlink you want to extract the URL, and then press Ctrl+ K keys simultaneously to open the Edit Hyperlink dialog box.

2. In the Edit Hyperlink dialog box, go to the Address box, press Ctrl + A keys to select the entire URL, press Ctrl + C keys to copy it, and then click OK to close the dialog box.

3. Select a blank cell, press Ctrl+ V keys to paste the copied URL.

Note: To extract URL from multiple cells, you need to repeat the above steps back and forth.

6.4.2 Extract URL from one hyperlink with User-defined function

Here provide the user-defined function to extract the URL from one hyperlink, after that, you can apply the AutoFill Handle to get all URLs of the adjacent cells based on the first result. Please do as follows.

1. Press the Alt + F11 keys.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below code into the Module window.

Function GetURL(pWorkRng As Range) As String
'Update by Extendoffice 20210901
    GetURL = pWorkRng.Hyperlinks(1).Address
End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a blank cell next to the first cell from which you want to extract the URL, enter the below formula into it and press the Enter key. Select the result cell, drag its AutoFill Handle over the below cells to get the other URLs.

=GetURL(A2)

Note: A2 is the reference cell from which you want to extract URL. Please change it based on your needs.

6.4.3 Extract URLs from hyperlinks with VBA code

Apply the below VBA code will replace all cell contents in selected range with the hyperlink URLs.

Note: Before the operation, please backup the original data. In this case, I want to extract all URLs from hyperlinks in range A2:A10, so I copy this range and paste to B2:B10, and then handle the new range.

1. Press the Alt + F11 keys.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module window.

VBA code: Extract an URL from a hyperlink

Sub Extracthyperlinks()
'Update by Extendoffice 20210901
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
For Each Rng In WorkRng
    If Rng.Hyperlinks.Count > 0 Then
        Rng.Value = Rng.Hyperlinks.Item(1).Address
    End If
Next
End Sub

3. Press the F5 key to run the code.

4. In the popping up Kutools for Excel dialog box, select the range of cells you want to extract URLs from, and then click OK.

Then all cell contents are replaced with hyperlink URLs as the below screenshot shown.

6.4.4 Easily extract URLs from multiple hyperlinks in bulk with an amazing tool

Here introduce the Convert Hyperlinks utility of Kutools for Excel, with this feature you can easily extract URLs from a range of hyperlink cells in bulk with several clicks only.

1. Click Kutools > Link > Convert Hyperlinks. See screenshot:

2. In the Convert Hyperlinks dialog box, configure as follows.

Then all URLs are extracted from hyperlinks of selected cells in bulk as the below screenshot shown.

---

7. Remove hyperlinks in Excel

This section guides you to remove hyperlinks from not only a range, but also the active sheet, selected sheets or the entire workbook. Besides, you can choose whether to keep the hyperlink formatting in cells after removing hyperlinks.

7.1 Remove hyperlinks from a range with the Remove Hyperlinks command

You can easily remove all hyperlinks from a selected range or an active sheet with the Excel built-in feature – Remove Hyperlinks.

1. Select the range that you want to remove all hyperlinks, right click any cell in the range and then select Remove Hyperlinks from the context menu.

Or you can click Home > Clear > Remove Hyperlinks (this step does not exist in Excel 2007 and the earlier versions).

Tips: To remove all hyperlinks from the active sheet, press the Ctrl + A keys to select the whole sheet, right click any cell and select Remove Hyperlink from the context menu.

7.2 Remove all hyperlinks from an active worksheet with VBA code

You can apply the below VBA code to remove all hyperlinks at once from an active sheet.

1. In the worksheet containing the hyperlinks you want to remove, press the Alt + F11 keys simultaneously.

2. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Code window.

VBA code: Remove all hyperlinks from current sheet

Sub RemoveHyperlinks()
'Update by Extendoffice 20210901
ActiveSheet.Hyperlinks.Delete
End Sub

3. Press the F5 key to run the code. Then all hyperlinks are remove from active sheet.

7.3 Remove hyperlinks without losing formatting with Clear Hyperlinks feature

The above methods remove both the hyperlinks and formatting from cells. Sometimes, you may need to remove hyperlinks but keep the formatting in cells. In this case, the Excel built-in feature – Clear Hyperlinks can help you solve the problem.

1. Select the range you will clear hyperlinks but keep formatting, click Home > Clear > Clear Hyperlinks.

To clear hyperlinks from current sheet, press the Ctrl + A keys to select the whole sheet and then apply the feature.

2. Then all hyperlinks are removed from selected cells but the formatting is not cleared.

As the below screenshot shown, it seems that nothing has been changed before and after, actually the hyperlinks has been removed already.

Notes:

7.4 Several clicks to remove hyperlinks from ranges, sheets or workbook without losing formatting

Kutools for Excel provides a handy feature – Remove Hyperlinks Without Losing Formatting to help you easily remove hyperlinks from:

1. A selected range;
2. Active sheet;
3. Multiple selected sheets;
4. The entire workbook.

1. Click Kutools > Link > Remove Hyperlinks Without Losing Formatting, and then select an option as you need.

Note: To remove hyperlink from a range or selected sheets, you first need to select the range or sheets and then apply the feature.

2. In the popping up Kutools for Excel dialog box, click Yes to go ahead.

Then only hyperlinks are removed without clearing the formatting.

---

8. Disable automatic hyperlinks in Excel

By default, when typing an email address or a web page (URL) format in a cell, it will be converted to hyperlink automatically. This section provides a couple ways to disable automatic hyperlink in one cell or the whole workbook in Excel. Please do as follows.

8.1 Disable automatic hyperlink in one cell in Excel

The below shortcut keys can help prevent automatic hyperlink in one cell in Excel, please do as follows.

1. Type the URL or email address format text into a cell and then press the Enter key.

Now the text is converted to hyperlink as the below screenshot shown.

2. Press the Ctrl + Z keys simultaneously, then the clickable hyperlink text is turned to plain text.

8.2 Disable automatic hyperlinks across the entire Excel application

You can disable automatic hyperlinks across the entire Excel application.

1. Click File > Options.

2. In the Excel Options window, click Proofing in the left pane, and then click the AutoCorrect Options button in the right window.

3. In the AutoCorrect dialog box, go to the AutoFormat As You Type tab, uncheck the Internet and network paths with hyperlinks box in the Replace as you type section, and then click OK.

4. Click OK to save the changes when it returns to the Excel Options window.

From now on, when typing URL or email address format text into cells, the texts will not be converted to hyperlinks.

Note: This change will impact every workbook on your computer.