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.

Dataset-Remove Hyperlink in Excel for Entire Column

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.

Context Menu-Remove Hyperlink in Excel for Entire Column

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

Context Menu result

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


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.

Clear Feature-Remove Hyperlink in Excel for Entire Column

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

Clear Feature outcome


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.

Paste Special-Remove Hyperlink in Excel for Entire Column

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

Context Menu

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

Paste Special window

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.

Paste Special result


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.

Autocorrect-Remove Hyperlink in Excel for Entire Column

🔺 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.

Selections

🔺 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.

Autocorrect Window

🔼 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.

Autocorrect result

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


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.

vba-Remove Hyperlink in Excel for Entire Column

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

Sub HyperlinkRemoval()
ActiveSheet.Hyperlinks.Delete
End Sub

macro

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.

Outcome

🔺 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

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.

Data with formatting

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


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.

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo