6 Ways to Remove Hyperlink for Entire Column in Excel

Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. In cases of data tracking, there are significant examples where you get cell value as a hyperlink. Although it is convenient for the user to understand, hyperlinks can be inconvenient in some cases as well. If you can remove hyperlinks for an entire column with the help of a few shortcuts, the work gets easier. This article will discuss six approaches to how you can remove hyperlink returns for an entire column in Excel. All the methods have step-by-step instructions with a figurative description.


📁 Download Excel File

Download the Excel file below.


How to Create Hyperlink in Excel

In the case of storing data in Excel, you may need to create a hyperlink to redirect the cell value to a specific web address. The steps to create a hyperlink in Excel are described below.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, write a web address or email ID on a cell (say cell C6).
  • Now, hit the Enter key.
  • Finally, your cell will show the web address or email ID as a hyperlink. It might not happen if the web address can’t be redirected to any specific destination.

📕 Read More: 5 Handy Ways to Convert Text to Hyperlink in Excel


Learn to Remove Hyperlink for Entire Column in Excel with These 6 Suitable Approaches

The article is going to introduce six ways to remove hyperlink returns for an entire column in Excel. The following discussion will cover the uses of the Context Menu, the Clear, and the Paste Special features in three ways. Rest three methods are based on the Find & Replace option, the Format Painter option, and the VBA Macro Code. Therefore, users will get a walkthrough of these solutions. You will find a dataset to modify in the following methods below to help you understand.

Dataset for removing hyperlink for entire column


approach

1. Using Context Menu Feature

The simplest way to remove hyperlink returns of cell values for an entire column is to use the Context Menu. With two clicks, you can complete the action.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the entire column C:C.
  • Second, right-click on the selection.
  • Third, choose the Remove Hyperlinks option from the Context Menu.

  • Fourth and finally, you will get your entire column with removed hyperlink returns.

Using Context Menu feature to remove hyperlink for entire column in Excel


approach

2. Applying Clear Feature

The Clear feature has a versatile use in Excel. It provides you with a separate option to remove hyperlinks.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, select the entire column C:C.
  • Next, select Remove Hyperlinks from the Clear drop-down menu, under the Editing group of the Home tab.

  • Therefore, you will get your entire column without hyperlink returns.

Applying Clear feature to remove hyperlink for entire column in Excel


approach

3. Utilizing Paste Special Feature

The Paste Special feature enables you to paste data using various operations or formats.

⬇️⬇️ STEPS ⬇️⬇️

  • In the beginning, type”1” in any adjacent cell.
  • Now, copy the cell by Ctrl+C.
  • Next, select the cell range C6:C8.
  • After that, press Ctrl+Alt+V.
  • Then, from the Paste Special dialogue box, tick the Multiply option in the Operation menu.

Utilizing Paste Special feature to remove hyperlink for entire column

  • Moreover, you will get your entire column with removed hyperlink returns.

Utilizing Paste Special feature to remove hyperlink for entire column


approach

4. Improvising Find and Replace Option

In the case of the extended dataset, manual selection is difficult. The Find and Replace option helps you to select hyperlink cells individually. It makes work easy to remove hyperlink returns in Excel for an entire column.

⬇️⬇️ STEPS ⬇️⬇️

  • To begin with, press Ctrl+F.
  • Here, you should get the Find and Replace dialogue box.
  • Next, select the Choose Format from Cells from the Format drop-down within the dialogue box.

  • Now, select cell C6.
  • After that, click on the Find All option.
  • Then, select all of them as you will get all the hyperlink values here.

Improvising Find & Replace option to remove hyperlink for entire column

  • Next, close the Find and Replace window.
  • Now, go to the Clear drop-down and choose the Remove Hyperlinks option.

  • So, you will get your entire column with removed hyperlink returns in this way.

Trying Find & Replace option for removing hyperlink for entire column


approach

5. Applying Format Painter

The Format Painter feature is easy in case of format changes. Since the hyperlink feature is also a feature, copying a format from a hyperlink cell value to other cells is easy to complete.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select cell C6 and choose the Remove Hyperlinks option like Method 1.
  • Second, select the Format Painter option from the Clipboard group, under the Home tab.
  • Third, click on the rest of the cells.

  • Fourth and finally, you will get your entire column with removed hyperlink returns.

Applying Format Painter option to remove hyperlink for entire column in Excel


approach

6. Employing VBA Code

Using VBA (Visual Basic for Applications), you can employ Macro code to apply on your worksheet. Moreover, it is also the only method that will create a new function to remove hyperlink returns for an entire column in Excel automatically. With the proper instruction, you can make your work easier.

⬇️⬇️ STEPS ⬇️⬇️

  • Initially, press Alt+F11 to open Visual Basic Application (VBA).
  • Next, select the Module option from the Insert You can also press Alt+I+L for the action.
  • Then, write the following code on the Module box.
Sub hlink_remove()
ActiveSheet.Hyperlinks.Delete
End Sub
  • Here, you should see the window like the image below.

Employing VBA code to remove hyperlink for entire column in excel

  • Now, close the VBA window and press Alt+F8 or choose the Macros option from the Developer tab.
  • Next, the Macro window will pop up to confirm the macro.
  • Here, click on the Run button.

  • Therefore, you will get your entire column with removed hyperlink returns automatically.

Employing VBA code for removing hyperlink for entire column in excel


📄 Important Notes

🖊️  All the data formats must be formatted properly. Otherwise, Excel may auto-format to something inappropriate for the user.

🖊️  You can modify the formula if you want something specific if Excel finds a match.

🖊️  Finally, you need to enable Macro Workbook for the VBA to run on your workbook.


📝 Takeaways from This Article

The article lets the readers understand the variety of options available to remove hyperlink returns for an entire column in Excel.

📌  Primarily, you can use the Context Menu feature to remove hyperlink cells easily.

📌  Also, you can apply the Clear feature. Here, you choose the Remove Hyperlinks option from the drop-down.

📌  A complex yet effective method is multiplying 1 with the hyperlink cells. This method needs the help of the Paste Special option.

📌  You can improvise the Find and Replace option as well.

📌  Here, you can also try Format Painter. You need to remove the hyperlink for one of the cells and copy the format to others using the feature.

📌  Finally, you can employ VBA Macro Code to remove hyperlink returns for an entire column in Excel.


Conclusion

Six methods are common to remove hyperlink returns for an entire column in Excel. You can use the Context Menu, the Clear, and the Paste Special features in three ways. Rest three methods are based on the Find & Replace option, the Format Painter option, and the VBA Macro Code. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.


Related Articles

(Visited 26 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo