63

I have a list of links in an Excel spreadsheet that I want to turn into hyperlinks so I can view them in a web browser. Is there a way I can change all of them to hyperlinks at once? I'm using Excel 2013. Thanks for your help!

2
  • Excel automatically creates hyperlinks when a cell entry begins with http://, www., ftp://, mailto:, file://, news: or \\. So, unless the feature was turned off, I'd bet the hyperlinks are invalid. This means they may not work if converted as is. Can you give us an example of the hyperlinks you are trying to convert?
    – CharlieRB
    Apr 9, 2013 at 17:27
  • 4

8 Answers 8

69

No need to do any programming or go to another app to fix this.

TEXT link to Clickable Hyperlink in EXCEL

Add a New Column next to your column of text links

If your text link is www.example.com (no http://):

  • Paste this formula into the cell of your new column next to your original column of TEXT that you want to change to a clickable hyperlink.

    =HYPERLINK("http://"&A2)

    The last two characters before the close parens is the target cell / whatever column and row has the text link

    You can copy this formula down the NEW column and you are all set

If you text link is http://www.example.com:

  • Add the new column

    If the http:// is already in the text itself Just past this into the cell next to your text link and it should work

    =HYPERLINK(A2)

    The last two characters before the close parens is the target cell / whatever column and row has the text link

    You can copy this formula down the NEW column and you are all set

2
  • 9
    what if you dont want to add a new column, you want to keep the column with the text present without repeating it? May 12, 2020 at 17:41
  • 2
    Create a new column with the hyperlink formula, then copy it and paste-special values back, then delete it.
    – JimB2
    Jul 29, 2022 at 5:30
18

I have used these steps myself.

Select the offending column of links On the Home tab ribbon in Excel, select Clear > Clear Hyperlinks

Observe that the links are now removed in the column. If you click any cell twice in column H, the link will reset to the correct location and become active.

It would take forever to click each cell twice to make all the links active, so try this instead. Press Alt + F11 on your keyboard. This will bring up a Microsoft Visual Basic window. On the top Nav bar in Visual Basic, select Insert > 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

Dim xCell As Range
    
    For Each xCell In Selection
        ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:=xCell.Formula
    Next xCell 
End Sub 

Press Alt + F11 on your keyboard to close the Visual Basic editor. In the spreadsheet, select ONLY the cells you wish to be changed to clickable links in the column. If you select the whole column, this can overwhelm Excel and cause the file to crash.

Press Alt + F8. This will bring up a Macro Window. HyperAdd will already be selected. Hit Run

You’re done. Test your links. Each link should now be active.

2
  • 2
    Instead of double clicking ("click any cell twice in column H") you can use the keyboard equivalent: F2, Enter (which also conveniently moves you to the next cell down. Jul 9, 2020 at 10:33
  • Brilliant! It also works better than a formula "=hyperlink()" cos it is recognised by VBA as a solid/real link (unlike "=hyperlink()" or even copy-paste values of the "=hyperlink()" formula) Jun 19, 2021 at 21:10
7

I had same issue and in poking around, I found a quick workaround that actually gets the job done without any macros, etc.

In the first cell of the column that has the URL's in it (i.e. the first URL, not the title) and double click on the URL in Cell to 'activate' it. Wait till it turns into a link (you may have to actually open the item so the sheet recognizes it.

Once it is underlined, you simply click the next cell down and select to the end of the column data. NOW the Cell Styles will work. Click Cell Styles from the Home Tab and select Hyperlink and viola - all the cells change to clickable hyperlinks.

May not be pretty, but it works!

1
  • 3
    A slight improvement to this approach is, instead of clicking with the mouse to activate and move down cell-to-cell, you can alternately hit F2 then Enter on the keyboard. Using two hands, this is likely to be much quicker unless you're incredibly accurate with a mouse or an incredibly slow typist! It will also be quicker than a lot of the fancier solutions for lists of maybe a couple of hundred or fewer rows. Jun 12, 2020 at 1:12
1

You want the HYPERLINK() function.

http://office.microsoft.com/en-us/excel-help/hyperlink-function-HP010342583.aspx

It will take a a cell with a url and convert it to a clickable link. This will allow you to CLICK THE LINKS WHILE STILL IN EXCEL. If you need to create a HTML file that you browse in IE or FF as a big list of links, use Brad's answer.

0

You don't specify in what form the links are in but assuming they are fully qualified (eg. http://www.google.com). Then you should be able to save the Worksheet as a Web Page. If you limit the Save to the selected Sheet this will produce a single HTML file with the links as clickable elements.

Links

NOTE: I have only tested this on Excel 2010 right now.

0

Make alle cells in a defined row to hyperlinks using VBA

Sub MakeHyperlinks()
    Dim lngRow As Long
    Dim lngCol As Long

    lngRow = 1 ' start first row
    lngCol = 2 ' look for cells in second col

    With Sheets("Sheet1")
        Do Until IsEmpty(.Cells(lngRow, lngCol)) = True ' stop when found the first empty cell
            .Hyperlinks.Add Anchor:=.Cells(lngRow, lngCol), Address:=.Cells(lngRow, lngCol)
            lngRow = lngRow + 1
        Loop

    End With
End Sub
0

Style section of the ribbon bar

If you look in the style section of on the home tab, you can select the column and click Hyperlink. That will hyperlink all of them

1
  • 12
    This just makes it blue and underlined - it doesn't make it a hyperlink that you can click.
    – teedyay
    Sep 27, 2017 at 8:46
-1

I've been fighting with this issue all evening, and after quite a bit of poking around I finally found a solution that does not require writing a macro, or excessive amounts of clicking. (My particular problem required me to convert 3000+ cells to hyperlinks)

  • Start by Right clicking on the ribbon and select "Customize the Ribbon".
  • Under the "Choose commands from" menu, select "Commands Not in the Ribbon".
  • In the list of commands you are given you should find a command for "Paste as Hyperlink".
  • Add that command to your ribbon somewhere.
  • Select all the cells you wish to convert to hyperlinks and copy them to the clipboard.
  • Press the "Paste as Hyperlink" button that is now on your ribbon.

Congratulations, you're done!

3
  • 1
    My apologies. I was clearly tired when I posted this last night. After going back to the spreadsheet I was creating links in I discovered that all of the cells I used this method to create a hyperlink with were not linked to the external URL, but instead linked to a cell within the spreadsheet. Sep 6, 2019 at 20:04
  • 1
    Thank you for the detailed instructions but having just followed them before reading your comment about it not actually creating an external hyperlink I wonder if this answer could be removed to prevent others like myself going down a rabbit-hole? Apr 16, 2021 at 4:26
  • Second @Michael12345 's comment; this answer should be removed Apr 25, 2022 at 23:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .