Excel: Pasted URLs Don't Become Hyperlinks

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Problem: I pasted hundreds of web site addresses into Excel. They did not turn into hyperlinks. I found that I could select a cell, press F2, then Enter to make the hyperlink. But I don't want to have to do that hundreds of times.

  1. Pasted hyperlinks are not hot.

    Strategy: Use the =HYPERLINK() function. Insert a blank column near your data. Use =HYPERLINK(A1,A1).

  2. The second A1 is supposed to be a friendly name.

    Enter the formula and copy it down to all rows.

  3. Column B contains live hyperlinks.

    Hide column A, leaving column B intact.

    Gotcha: This strategy works great for web addresses that have the leading http://. It will not work for cell A5. A hyperlink will appear, but when someone follows the hyperlink, it will say the address is invalid. In that case, you could use this formula: =HYPERLINK("œhttp://"&A5,A5).

For more resources for Microsoft Excel