Excel Tip: The Missing Link

When pasted URLs don't become hyperlinks, try this solution.
Bill JelenJanuary 29, 2014

A reader posed the following question: “I pasted hundreds of web site addresses into Excel. They did not turn into hyperlinks (Figure 1). 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.”


Drive Business Strategy and Growth

Drive Business Strategy and Growth

Learn how NetSuite Financial Management allows you to quickly and easily model what-if scenarios and generate reports.

Fig. 1

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

Enter the formula and copy it down to all rows (Figure 3).

Copy the column of formulas and use Paste Values to paste back on top of the formulas. This will get rid of the hyperlink formula and leave you with only the hyperlinks. You can now copy those hyperlinks back over the original data


Fig. 2

Caution: This strategy works great for web addresses that have the leading http://. It will not work for cell A5 in Figure 3. 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).


 Fig. 3

The following video walks a user through the solution:

CFO contributor Bill Jelen is an Excel MVP and the author of 35 books about Microsoft Excel. He is also editor-at-large of the CFO Excel Pro newsletter.

4 Powerful Communication Strategies for Your Next Board Meeting