A reader asked the following question: “I have pasted hundreds of website addresses into Excel. They did not turn into hyperlinks (Figure 1). I discovered that I could select a cell, hit F2, then hit enter to create the hyperlink. But I don’t want to have to do this hundreds of times.
The solution: Use the =HYPERLINK() function. Insert an empty column near your data. Use =HYPERLINK(A1,A1) (Figure 2).
Enter the formula and copy it to all rows (Figure 3).
Copy the formula column and use Paste Values to paste again above the formulas. This will eliminate the hyperlink formula and leave you with just the hyperlinks. You can now copy these hyperlinks to the original data
Caution: This strategy works very well for web addresses that start with http://. This won’t work for cell A5 in Figure 3. A hyperlink will appear, but when someone follows the hyperlink, they say the address is invalid. In this case, you can use this formula: =HYPERLINK (“http://”&A5,A5).
The following video walks a user through the solution:
CFO contributor Bill Jelen is an Excel MVP and the author of 35 books on Microsoft Excel. He is also editor-in-chief of CFO Excel Pro newsletter.