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.
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.
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?
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
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.
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.
ercan
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)
Select 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
Appreciate the help, thanks…
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.
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
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!
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
i just have read your blog, but do you know how to creat hyperlink?
i 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/
Is the workbook shared? If so, you cannot add a hyperlink.
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?
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?
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.
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?
Daniel,
You can use the HYPERLINK function in Excel. It takes two arguments:
=HYPERLINK(URL, Friendly Name)
An example would be:
=HYPERLINK(”http://www.datapigtechnologies.com”,”DataPig”)
The cell would show DataPig, but would go to the correct
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.
How can I insert two hyperlinks in a single cell?
Roger - Don’t be greedy. One hyperlink per cell.
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.
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
oops, not to matter. cheers
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
Is it possible to pass arguments to the hyperlinks within excel. The hyperlink in excel will invoke a batch file with some arguments. Is this possible?
http://www.dailydoseofexcel.com/archives/2004/05/03/beginning-vba-events/
Raghav: The Worksheet_FollowHyperlink event may be useful.
Hi,
My problem is the following: I have an Excel WB and in one of the columns I have countries listed, where the different projects take place (1 country per cell). I would like to create hyperlinks to the website of each country’s Wikipedia site, where the address is http://en.wikipedia.org/Wiki/“Country name”. How can I do this, withouth creating the links one-by-one?
Thanks a lot for the help!
Assuming the country is in column C. Then fill down.
Hi I have a an issue with a workbook I am working on. It is a list of orders that I am generating from filling in a master sheet. As the master is completed it is “Submitted” and a separate file in created and saved based the order number (as entered). In addition a new line is created on the directory page and some basic information is copied from the new file. I want to add a hyperlink to the newly created file from within the newly created line. The hyperlink will always reference a file with the same name as the value returned in say cell C7 (as calculated). Any help would be greatly appreciated.
Brain: How about a formula like:
hi ! i would like to know if i can create a link independant of the location in excel. this linking document id a word file. please let me know. thanks in advance.
Great blog folks. Here’s a curly one. I want to create an email hyperlink which generates an email with a populated subject line based on cell content.
i.e the link location would be mailto:whatever@whatever.com, the friendly name would be a Meeting date and location and the email subject line would be the same as the same text as the friendly name.
Is this possible? Many thanks.
Try this:
Many thanks Dick,
This partially works in that the subject heading is there but it won’t incorporate cell content into the email subject heading i.e. if you I insert the function =HYPERLINK(”mailto:whatever@whatever.com?subject=DB2″,DB2) where cell DB2 = “Meeting 1/14/10 at Chotchkies”.
What happens here is that the friendly name is correct in that it shows “Meeting 1/14/10 at Chotchkies” but the email subject heading just shows DB2.
P.S. you may be able to get some concept of the size of spreadsheet I’m working off by the cell reference!
Cheers
Warrick
Warrick:
Try this:
=HYPERLINK(”mailto:whatever@whatever.com?subject=”&DB2,DB2)