Excel tip: The missing link

0

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.

Fig. 1

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

fig2excelllinks

Fig. 2

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

fig3excell links

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 on Microsoft Excel. He is also editor-in-chief of CFO Excel Pro newsletter.

Share.

About Author

Comments are closed.