Creating Hyperlinks
Here are two ways to create hyperlinks in your spreadsheet.
Menu: Insert>Hyperlink will bring up the Insert Hyperlink dialog box. There you can type in a URL, a file name, or point to location in your spreadsheet.
Function: The HYPERLINK worksheet function can also be used to create hyperlinks. To use this method, just type the function in a cell:
=HYPERLINK(”www.dicks-clicks.com”,”My Web Site”)
The URL is the first argument. The optional second argument is a “friendly name” that will appear in the cell. If you omit the second argument, the cell will show the URL.
Andrew:
I like using hyperlinks for quick navigation around a worksheet or workbook if I’m working with a large file. Just insert them at strategic places and say goodbye to scrollbars (well, maybe not entirely)
You can use a “friendly name” for this too.
29 April 2004, 2:28 amCoca IQ Bogdan:
Yeee… very nice… I hate excel , but I must to use it… does anyone know a more simple problem? Lotus 1-2-3 still exists?
1 December 2004, 5:32 amHeather:
Ok, I have just learned how to do hyperlinks so I am not sure what I am doing wrong.
I am trying to add a hyperlink into one of my excel spreadsheets from another excel document and it says it can’t open specified file, why?
It works great with my word document but not my excel document.
Can you please help me with this.
Thank you in advance.
Heather
11 November 2005, 10:35 amfrank:
When you create hyperlinks, you can locate them in an endless variety of places on your worksheet. But what happens if you want to remove them and you don’t remember where they are? Excel doesn’t tell you. This routine helps you to find them:
Sub find_hyperlinks()
Dim r As Range
Set r = Range(”a1:cv1500″)
For Each c In r
If c.Hyperlinks.Count > 0 Then
msgbox c.address
End If
Next c
End Sub
To get rid of the hyperlink, right click, remove, or in Excel ’97 via the edit menu – hyperlinks edit hyperlink - remove.
If you created hyperlinks to other Excel documents, your file will always open with a message asking “This file contains links to other documents - do you want to update them? This is a nuisance, because you’ll have to answer this question perpetually each time your file opens. In Excel ’97 there is no way to get rid of it. Luckily, as from Excel 2002, the Edit - Links dialog has a “Break Links” option.
14 November 2005, 3:00 pmercan:
Ok, what I want to do is assign a macro to a hyperlink, so that if a user selects that cell with that hyperlink in it, it will run a macro and pop up a chart, I don;t want to use buttons because there could be many such cells. Any ideas as to how I can accomplish this ??
Thank you.
26 December 2005, 11:55 pmercan
Jon Peltier:
Ercan -
Independent of the hyperlink, put a SelectionChange event in the worksheet code module. To do this, right click the sheet tab, and select View Code. In the code module that pops up, select Worksheet from the top left dropdown, which puts the following procedure into the code module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
End Sub
You then adjust what’s between the Sub and End Sub to get what you want:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
28 December 2005, 3:02 pmSelect Case Target.Address
Case “$A$1″
‘ carry out action for Cell A1
Case “$A$2″
‘ carry out action for Cell A2
‘ etc.
End Select
End Sub
ercan:
Appreciate the help, thanks…
28 December 2005, 8:00 pmercan:
I have another question, this time regarding Excel Chart Events.
I want to be able to click anywhere on a chart and drag over a region on the x-axis , to resize the chart to plot only that region, a bit like kike zooming in.
Say for example , a chart shows prices of a stock over one day , if I click on the chart at a point that corresponds to 10:30 and drag the mouse while holding keeping the left mouse button pressed and releasing it when I am at 13:00 pm , the chart automatically re-draws it self with the x-azis ranging from 10:30 to 13:00 pm ….
Appreciate the help.
2 January 2006, 1:21 amel parce:
A question for the Excel experts. I have a workbook that that displays links for documents that are scattered throughout a network. Is there any way display information in Excel about those hyperlinked documents such a creation date or author. I’m really interested in being able to monitor when these files get changed or updated.
Thanks,
el parce
11 January 2006, 8:39 amLA:
When I try to create a hyperlink in an Excel workbook cell, I can’t make a partial selection of text for the link; all the text in the cell is automatically hyperlinked–very ugly!
Thanks!
1 February 2006, 12:02 pmJewan Sukhdeo:
Question: I have an Excel workbook but when I select Insert and attempt to click on the Hyperlink selection in the dropdown menu it is not available for me to select. If I create a new document I can do so/ for some reason I cann access this in the workibook I have ben working with.
Help
Jewan
19 February 2006, 1:40 pmmark:
i just have read your blog, but do you know how to creat hyperlink?
20 April 2006, 11:28 pmi have used Active Link ActiveX Control before, it’s easy, and it’s the fastest way to create Hyperlinks in your apps.
see it : http://www.yaodownload.com/software-development/components-libraries/activelinkxcontrol/
Stacy:
Is the workbook shared? If so, you cannot add a hyperlink.
12 April 2007, 2:25 pmSunil:
I would like to create a hyperlink based on what’s in a cell. Thus if a cell has value “x” in it, i want it to link to a hyperlink which is “http://www.value_x_somethingelse.net” I
don’t know how to do this when there’s something after the “x” thus i can make it work if the link is “http://www.value_x_.net” but not when there’s something after the “x” ie “http://www.value_x_somethingelse.net”
Any advice?
30 June 2007, 12:11 pmElizabeth:
I have the OPPOSITE problem. I have a spreadsheet of hyperlinks where the friendly name is displayed. I want to remove the friendly name, so that the hyperlink is what is in the cell.
Is there a way to do this without editing each cell?
23 July 2007, 12:40 pmJake Marx:
Elizabeth,
You could do something like this:
Dim hyp As Hyperlink
If rwsTarget Is Nothing Then Set rwsTarget = ActiveSheet
For Each hyp In rwsTarget.Hyperlinks
hyp.TextToDisplay = hyp.Address
Next hyp
End Sub
You can either call it without arguments or pass in a reference to the worksheet from which you wish to remove friendly names.
24 July 2007, 10:28 amDaniel:
I have a spreadsheet which is a listing of hundreds of urls which are friendly names (non-linked). Does anyone know the easiest way to turn them into hyperlinks (going to the same url as their friendly name)? Do I have to do them one by one?
28 September 2007, 9:28 amMike Alexander:
Daniel,
You can use the HYPERLINK function in Excel. It takes two arguments:
=HYPERLINK(URL, Friendly Name)
An example would be:
29 September 2007, 8:54 am=HYPERLINK("http://www.datapigtechnologies.com","DataPig")
The cell would show DataPig, but would go to the correct
Mike Alexander:
I forgot to mention; you can use a cell reference in any of the arguments.
For instance, if you already have friendly names in Column B, you can do something like this:
=HYPERLINK("http://www.datapigtechnologies.com",B1)
Then you can copy down.
29 September 2007, 6:06 pmRoger:
How can I insert two hyperlinks in a single cell?
3 March 2008, 6:04 pmJon Peltier:
Roger - Don't be greedy. One hyperlink per cell.
4 March 2008, 9:48 amAlina:
What about creating a hyperlink in a cell to link it to a word document. I tried that and it worked, but I just could not "save" the hyperlink. I have to edit them all over agai when I open my excel file. Can anybody help me please? Thanks.
27 June 2008, 3:16 pmGem:
Hi guys, I'm loathe to ask for help here, but this blog is really helpful. How do i use the contents of a cell as the website address? I have a macro to create a variable filename/hence variable address online to go view it. this is in A5. I want to link to this in B5. I have tried with a cell reference but this only links to that cell. cheers
19 August 2008, 8:33 amGem:
oops, not to matter. cheers
19 August 2008, 9:10 amSimon:
Hi
How would you add instructions to a hyperlink to make it open in a new window when in a webpage - ie "_blank" =HYPERLINK("http://www.dicks-clicks.com",target=????)
Thanls
17 November 2008, 7:33 am