How do I convert a column of text URLs into active hyperlinks in Excel?

I have a column in excel, wherein I have all the website url values. My question is I want to turn the url values to active links. There are about 200 entries in that column with different urls in all cells. Is there a way I can create active hyperlinks to all the cells without writing a macro.

15.9k 25 25 gold badges 74 74 silver badges 117 117 bronze badges asked Apr 7, 2010 at 20:30 5,378 11 11 gold badges 49 49 silver badges 72 72 bronze badges

25 Answers 25

If you don't want to make a macro and as long as you don't mind an additional column, then just create a new column alongside your column of URLs.

In the new column type in the formula =HYPERLINK(A1) (replacing A1 with whatever cell you are interested in). Then copy the formula down the rest of the 200 entries.

NOTE: This solution does not work if the cell A1 contains a string longer than 255 characters. It results in a #VALUE! error

1,705 3 3 gold badges 19 19 silver badges 30 30 bronze badges answered Apr 7, 2010 at 20:47 15.9k 25 25 gold badges 74 74 silver badges 117 117 bronze badges

If my column A contains 200 link values, is it possible to write a formula that would hyperlink the 200 values and rewrite it in column A, instead of creating a new column?

Commented Apr 7, 2010 at 20:50

Excellent! The best thing is, this step is so simple that I can pass CSV files to a layperson and he can generate URLS from it easily.

Commented May 25, 2015 at 1:29

To make it so it's one column just copy the hyperlink column and "paste value" into whichever column you want to keep.

Commented May 4, 2017 at 17:52 This should be accepted answer, since it solves most of the use cases. Commented Jan 15, 2018 at 12:27 Hide the raw column that's not a hyperlink Commented Sep 28, 2018 at 1:48

Create the macro as here:

On the Tools menu in Microsoft Excel, point to Macro, and then click Visual Basic Editor. On the Insert menu, click Module. Copy and paste this code into the code window of the module. It will automatically name itself HyperAdd.

Sub HyperAdd() 'Converts each text hyperlink selected into a working hyperlink For Each xCell In Selection ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula Next xCell End Sub

When you're finished pasting your macro, click Close and Return to Microsoft Excel on the File menu.

Then select the required cells and click macro and click run.

NOTE Do NOT select the whole column! Select ONLY the cells you wish to be changed to clickable links else you will end up in a neverending loop and have to restart Excel! Done!

5,772 25 25 silver badges 36 36 bronze badges answered Nov 2, 2012 at 19:10 Steve Keenan Steve Keenan 1,167 1 1 gold badge 7 7 silver badges 5 5 bronze badges

Add Dim xCell As Range if you are using the explicit option. +1 for a solution that changes the cell (as asked) and providing the code that does it.

Commented Dec 22, 2014 at 23:52

This worked well for me, granted I had about 50,000 links in a spread sheet that needed updating and had to do it in chunks or excel would crash.

Commented Jun 15, 2015 at 22:06

To make email addresses into hyperlinks, change Address:=xCell.Formula to Address:="mailto:" & xCell.Formula

Commented Oct 21, 2015 at 21:30

Also, for URLs not starting with http:// this needs to be added (use the above change, but substitute mailto: with http:// . Otherwise the hyperlinks don't work.

Commented Oct 21, 2015 at 21:55

Works great. FYI Excel 2016 has a limit of 66,530 hyperlinks per worksheet, so expect an eventual run-time error if you exceed that. support.office.com/en-us/article/…

Commented Jun 1, 2017 at 21:46

Pretty easy way for rather short lists:

  1. Double click on the box where the url is
  2. Enter

You have your link ;)

199k 63 63 gold badges 531 531 silver badges 1.1k 1.1k bronze badges answered Jan 18, 2014 at 19:43 user3210679 user3210679 487 4 4 silver badges 2 2 bronze badges I use F2 to edit a cell, then enter, repeatedly to quickly run down my list. Simple. Thank you! Commented Feb 11, 2014 at 17:50

This plus the comment about F2 just got me to a column of 160 hyperlinks in about a minute. So yeah, this is the way to go if the column is short enough.

Commented Oct 21, 2016 at 16:27 I don't think this could be a solution for someone with hundreds of cells to convert. Commented Oct 11, 2017 at 15:00 I have 287,000 rows. Cry. Commented May 18, 2019 at 2:02

@thejohnbackes For newer versions of Excel it is enough to covert a row to hyperlink and use Format Painter to give the URL format to the others.

Commented Dec 14, 2021 at 11:23

Here's a way I found. I'm on a Mac using Excel 2011. If column B had the text values you want to be hyperlinks, put this formula in the cell C1 (or D1 or whatever as long as it's a free column): =HYPERLINK(B1,B1) This will insert a hyperlink with the location as the link text and the "friendly name" as the link text. If you have another column that has a friendly name for each link, you could use that too. Then, you could hide the text column if you didn't want to see it.

If you have a list of IDs of something, and the urls were all http://website.com/folder/ID , such as:

A1 | B1 101 | http://website.com/folder/101 102 | http://website.com/folder/102 103 | http://website.com/folder/103 104 | http://website.com/folder/104

you could use something like =HYPERLINK("http://website.com/folder/"&A1,A1) and you wouldn't need the list of urls. That was my situation and worked nicely.

According to this post: http://excelhints.com/2007/06/12/hyperlink-formula-in-excel/ this method will work in Excel 2007 as well.

160 1 1 silver badge 13 13 bronze badges answered Aug 1, 2012 at 18:24 658 1 1 gold badge 8 8 silver badges 16 16 bronze badges Works in Windows, Excel 2013 and is great alternative to an extra column. Commented Apr 10, 2017 at 20:44

FYI, for Open Office the above gave an error but it seems that =HYPERLINK(C1) works if given only one parameter

Commented May 22, 2017 at 12:08

I wanted something that would display only if there was a link to display: =IF(NOT(ISBLANK(A2)),HYPERLINK("https://jirasite.atlassian.net/browse/PROJECT-"&A2,"PROJECT-"&A2),"")

Commented Oct 6, 2023 at 19:12

OK, here's a hokey solution, but I just can't figure out how to get Excel to evaluate a column of URLs as hyperlinks in bulk.

  1. Create a formula, ="=hyperlink(""" & A1 & """)"
  2. Drag down
  3. Copy new formula column
  4. Paste Special Values-only over the original column
  5. Highlight column, click Ctrl - H (to replace), finding and replacing = with = (somehow forces re-evaluation of cells).
  6. Cells should now be clickable as hyperlinks. If you want the blue/underline style, then just highlight all cells and choose the Hyperlink style.

The hyperlink style alone won't convert to clickable links, and the "Insert Hyperlink" dialog can't seem to use the text as the address for a bunch of cells in bulk. Aside from that, F2 and Enter through all cells would do it, but that's tedious for a lot of cells.

14.8k 22 22 gold badges 58 58 silver badges 52 52 bronze badges answered Apr 7, 2010 at 20:47 Indolent Coder Indolent Coder 728 3 3 silver badges 5 5 bronze badges Just do =(HYPERLINK(INDIRECT(ADDRESS(ROW(),COLUMN()+1)),"Dress" & ROW())) it's easier Commented Mar 18, 2013 at 13:56 Agreed, this is the best answer which doesn't require maintaining 2 columns. Commented Mar 18, 2018 at 1:44 +1 For the Replace '=' -> '='. that what i search for. thanks. Commented Aug 27, 2018 at 8:45

If adding an extra column with the hyperlinks is not an option, the alternative is to use an external editor to enclose your hyperlink into =hyperlink(" and ") , in order to obtain =hyperlink("originalCellContent")

If you have Notepad++, this is a recipe you can use to perform this operation semi-automatically:

answered May 8, 2017 at 13:53 20.1k 14 14 gold badges 105 105 silver badges 212 212 bronze badges

Great answer. This is the only way to go if you do not want any extra columns. The solution can be used for any kind of editor or programming language (not just Notepad++). In Excel 2010 this does create clickable links, but I had to manually apply the "hyperlink formatting style" to really make them look like links (i.e. blue underlined).

Commented Mar 29, 2018 at 9:58

Note: This does not require notepad++ . just for the sake of clarity, you can do this with any text editor. The trick is to just wrap each line with =hyperlink("__linecontentgoeshere__") . for those who may be misled by the mention of notepad++

Commented Apr 26, 2019 at 23:10

@dreftymac This gives a recipe of how to do this almost automatically, not editing line by line. It leverages Notepad++ capability to extend the cursor on multiple lines and support of regular expressions; other editors might have similar functions. I am clarifying at the beginning of my answer what is the intended result, that should get rid of the misunderstanding.

Commented Apr 29, 2019 at 21:12

//I am clarifying at the beginning of my answer what is the intended result// Thank you for emphasizing your point. I was not criticizing your answer, just making it a bit more clear for those who might not know anything about Notepad++, and may not realize it is simply one of many text editors out there that can do this kind of thing. I know you realize that, but not everyone does.

Commented Apr 30, 2019 at 0:29 Commented Jun 26, 2019 at 12:52

I shocked Excel didn't do this automatically so here is my solution I hope would be useful for others,

  1. Open this on your Chrome or Firefox
  1. Copy the whole column to clipboard
  2. Paste the column on the page you just opened on the browser and press "Linkify"
  3. Copy the result from the tab to the the column on Excel

Instead the first step, you can use the below page, first, click on "Run code snippet" then paste the column on it
answered Sep 17, 2018 at 21:06 Ebrahim Byagowi Ebrahim Byagowi 11k 5 5 gold badges 73 73 silver badges 88 88 bronze badges wow ! This is the easiest and best answer ! Commented Oct 1, 2021 at 7:10 Very different from other answers, but very clever. Commented Apr 28, 2022 at 20:58 This will work for emails: data:text/html,
Commented May 25, 2022 at 4:40 Commented Aug 28, 2023 at 7:15
  1. Create a temporary new column of hyperlinks using formula =HYPERLINK()
  2. Copy that column into Microsoft Word (copy to clipboard only after Word is running).
  3. Copy everything in the new word document (ctrl+a, then ctrl+c).
  4. Paste into Excel, replacing the original column of text. Delete the temporary column with the formula.
answered Nov 4, 2019 at 18:34 2,075 5 5 gold badges 28 28 silver badges 34 34 bronze badges

This method works for me using the hyperlink function:

=HYPERLINK("http://"&B10,B10)

Where B10 is the cell containing the text version of the URL (in this example).

14.8k 22 22 gold badges 58 58 silver badges 52 52 bronze badges answered Mar 26, 2013 at 5:07 Wasabihound Wasabihound 71 1 1 silver badge 1 1 bronze badge

=HYPERLINK("http://"&B10,B10) didn't work in my Excel 2013, however =HYPERLINK("http://"&B10) DID work.

Commented May 8, 2013 at 9:18

With Excel 2007 on Windows, I found these steps simplest;

  1. Select cells with the non-active URLs
  2. Copy
  3. Paste as hyperlink
75.1k 26 26 gold badges 154 154 silver badges 180 180 bronze badges answered May 26, 2013 at 15:08 59 1 1 silver badge 1 1 bronze badge

In later version (I'm using Office 2011), this isn't working - I just get a hyperlink to a desktop file (this one), not to the url shown in the cell. e.g. file://localhost/Users/xxxx/Desktop/Blahblahblah File Name.xlsx - Sheet1!A2

Commented Jan 30, 2014 at 20:27

I found that none of the methods here worked if the hyperlink did not include http:// as they linked to local locations.

I also wanted to fool-proof the script as the users would not be able to maintain it themselves and I would not be available.

It will only run on cells in a selected range if they contain a dot and no spaces. It will only run for up to 10,000 cells.

Sub HyperAdd() Dim CellsWithSpaces As String 'Converts each text hyperlink selected into a working hyperlink Application.ScreenUpdating = False Dim NotPresent As Integer NotPresent = 0 For Each xCell In Selection xCell.Formula = Trim(xCell.Formula) If xCell.Formula = "" Or InStr(xCell.Formula, ".") = NotPresent Then 'Do nothing if the cell is blank or contains no dots Else If InStr(xCell.Formula, " ") <> 0 Then CellsWithSpaces = CellsWithSpaces & ", " & Replace(xCell.Address, "$", "") GoTo Nextxcell End If If InStr(xCell.Formula, "http") <> 0 Then Hyperstring = Trim(xCell.Formula) Else Hyperstring = "http://" & Trim(xCell.Formula) End If ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=Hyperstring End If i = i + 1 If i = 10000 Then Exit Sub Nextxcell: Next xCell If Not CellsWithSpaces = "" Then MsgBox ("Please remove spaces from the following cells:" & CellsWithSpaces) End If Application.ScreenUpdating = True End Sub