How to Remove Hyperlink for Entire Column in Excel (5 Ways)

Users usually insert email addresses, and website names in datasets and Excel automatically converts them into Hyperlinks. Therefore, sometimes users need to remove Hyperlink for an entire column in Excel. Excel offers multiple features, Autoformat’s enable or disable options as well as VBA Macro to remove Hyperlinks within the worksheet.

Let’s say we have Customer’s Names, Company Names along with their Email addresses. As you type or insert the Email addresses, Excel auto automatically transforms them into Hyperlinks.

In this article, we demonstrate multiple ways to remove hyperlink in Excel for an entire column.

Download Excel Workbook

5 Easy Ways to Remove Hyperlink for Entire Column in Excel

Removing Hyperlinks is frequent operation users do in worksheets. Inbuilt Excel Features, Option, and VBA Macros efficiently remove hyperlinks from columns or selections. Users can select a couple of rows, columns, or an entire range to remove hyperlinks from. As in this case, we want to remove hyperlinks from the entire column, use CTRL+SPACE or just click on the Column Letter to highlight the entire column. Follow the below methods to remove hyperlinks in Excel.

Method 1: Remove Hyperlink for Entire Column Using Context Menu

🔺 Click on the Column Name or Place the cursor in any cell then Press CTRL+SPACE altogether to select the entire column. You can also highlight the entries as depicted in the picture below. Afterward, right-click on the selection. The Context Menu appears. Choose Remove Hyperlinks from the options.

🔺 Excel removes all the Hyperlinks from the entire column or selection. Removing Hyperlinks also clears the cell formats.

If there is no Remove Hyperlinks option in Context Menu options, that’s because there are no Hyperlinks existing in the selection.

Read More: How to Remove Hyperlink Permanently in Excel (4 Ways)

Method 2: Using Clear Feature to Remove Hyperlink for Entire Column

Excel offers Remove Hyperlinks in the Home tab Editing section’s Clear feature. We can use this Clear feature’s options to remove Hyperlinks from the entire column or selection.

🔺 Highlight the entire column or range then, Go to Home > Click on Clear (from the Editing section) > Select Remove Hyperlinks.

🔺 In a moment, Excel transforms all the entries into mere entries without Hyperlinks. Executing Remove Hyperlinks also removes the cell formats.

Read More: How to Edit Hyperlink in Excel (5 Quick & Easy Ways)

Method 3: Remove Hyperlinks Using Paste Special Feature

Excel’s Paste Special feature allows to perform multiple options and Multiply is one of them. It’s quite been a trick to multiple entries that contain Hyperlinks with 1 to convert them into mere entries removing Hyperlinks.

🔺 Type 1 in any adjacent cell then highlight the entire column or selection as shown in the following picture.

🔺 Right-Click on the selection, The Context Menu appears. From the Context Menu, Select Paste Special (from the Paste options).

🔺 The Paste Special dialog box appears. From the Operation’s option Select Multiply. Then Click on OK.

You can also use Keyboard Shortcuts CTRL+ALT+V to bring up the Paste Special window.

🔺 Multiplying 1 to the entire column or selection results in removing all the Hyperlinks from the entire column or selection.

Read More: How to Remove All Hyperlinks in Excel (5 Methods)

Similar Readings

• [Fixed!] This Workbook Contains Links to One or More External Sources That Could Be Unsafe
• How to Remove Excel Links from Word Document
• Create a Drop Down List Hyperlink to Another Sheet in Excel
• Remove External Links in Excel
• How to Link Picture to Cell Value in Excel (4 Quick Methods)

Method 4: Turning off Autoformat Options to Prevent Hyperlink Insertion

Excel automatically generates Hyperlinks whenever users input emails, website names, and other linkable entries. Therefore, if users disable the Autoformat option, they can input anything without the hassle of inserting Hyperlinks automatically.

🔺 Move to Excel Worksheet’s File > Options.

🔺 Excel brings the Excel Options window as depicted in the latter screenshot. From the window,

⧫ Choose Proofing from the right-side of the options.

⧫ Click on the AutoCorrect Options under AutoCorrect Options section.

🔺 The Autocorrect dialog box opens up. In the dialog box,

⧫ Click on the Autoformat as you type section.

⧫ Untick Internet and network paths with hyperlinks under the Replace as you type option.

⧫ Click on OK.

🔼 Now, return to the worksheet and try to insert the emails by typing or pasting. You see, Excel doesn’t insert Hyperlinks automatically. Also, the format of the cells doesn’t get formatted.

Thus, disabling Autoformat as you type is one of the efficient ways to deal with Hyperlinks.

Read More: How to Remove Email Link in Excel (7 Quick Ways)

Method 5: Using VBA Macro to Remove Hyperlinks

VBA Macros are best when considering subjective outcomes. VBA Macros can remove all the Hyperlinks along with the formatting. Also, it can remove the Hyperlinks but intact the cell formatting.

🔺 Press ALT+F11 to open up Microsoft Visual Basic window. In the window, Select Insert > Click on Module to insert a Module.

🔺 Paste the following macro in the module to remove Hyperlinks along with the cell formatting.

Sub HyperlinkRemoval()
ActiveSheet.Hyperlinks.Delete
End Sub

The macro executes the Hyperlinks.Delete command for the Active Worksheet.

🔺 Use the F5 key to run the macro then return to the Worksheet. In the Worksheet, you see all Hyperlinks get removed along with the cell formatting.

🔺 Alternatively, you can insert the below macro in the Module to remove Hyperlinks without altering the cell formatting.

Sub RemoveHyperlnksIntactFormatting()
Dim mRng As Range
Dim wrkRng As Range
Dim mrfRng As Range
Dim mUsedRng As Range
Dim mLink As Hyperlink
On Error Resume Next
xTitleId = "Removing Hyperlinks"
Set wrkRng = Application.Selection
Set wrkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set mUsedRng = Application.ActiveSheet.UsedRange
For Each mLink In wrkRng.Hyperlinks
Set mrfRng = Cells(1, mUsedRng.Column + mUsedRng.Columns.Count)
Set mRng = mLink.Range
mRng.Copy mrfRng
mRng.ClearHyperlinks
Set mrfRng = mrfRng.Resize(mRng.Rows.Count, mRng.Columns.Count)
mrfRng.Copy
mRng.PasteSpecial xlPasteFormats
mrfRng.Clear
Next
End Sub

Macro assigns the Used Range and Hyperlinks using the VBA Application Object and VBA CELLS property respectively. Then clears the Hyperlinks by Clear command. Also resizes the same range. At last, paste the formatting using the VBA Paste Special method.

🔺 After returning to the Worksheet, you see the same format as previous entries. However, if you place the cursor on any entries that previously contain Hyperlinks, you see no links showing as depicted in the image below.

This macro is quite effective when you want to remove the Hyperlinks without altering the cell formatting.

Read More: VBA to Add Hyperlink to Cell Value in Excel (4 Criteria)

Conclusion

In this article, we demonstrate multiple features and VBA Macros to remove Hyperlink in Excel for an entire column or selection. Disabling Autoformat option and VBA doesn’t alter the cell formation in the process of removing Hyperlinks. Whereas other methods remove Hyperlinks along with the cell formatting. Hope these above methods excel in your case. Comment, if you have further inquiries or have anything to say.

Related Articles

• 7 Solutions for Greyed Out Edit Links or Change Source Option in Excel
• How to Delete Hidden Link in Excel (5 Easy Ways)
• [Fix:] Edit Links in Excel Not Working
• [Solved]: Remove Hyperlink Not Showing in Excel (2 Solutions)
• How to Activate Multiple Hyperlinks in Excel (4 Ways)
• Remove Unknown Links in Excel (4 Suitable Examples)
• How to Hyperlink to Cell in Same Sheet in Excel (5 Methods)