Excel Tip: The Missing Link

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.”

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.