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 hyperlinkedvery 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)
I have a workbook with multiple sheets in it. I am forcefully hiding those sheets (not visible within sheet unhide menu option) and unhiding them with a VBA code.
Thru a cell/hyperlink in a particular sheet, i want to
(a) First – Unhide the target Sheet
(b) Then – Go to a particular cell reference.
Using excel 2007 I’m running into an issue where a hyperlink used on the false side of a if is creating a hyperlink when the if evaluates to false.
Example:
=if($A$1=0,”No Link”,hyperlink(“http://test.com”,”test”))
Will populate the cell with a link called “No Link” when cell A1 = 0
I think it’s an excel bug, any ideas to get around this?
Thanks in advance
Tim
Hell, can anyone help with the following, please?
My folder (folder A) is part of a system used at work by many people, so it’s full name is Disk C/folder 1/folder 2/folder 3/folder 4/folder A.
What I have in folder A is an Excel spreadsheet plus three other folders B,C,D each of which in turn contain several Excel spreadsheets plus in turn folders (say X,Y,Z) containing more spreadsheets.
In all the spreadsheets I have hyperlinks to other spreadsheets which are in A,B,C etc.
These work fine – for example if I want a hyperlink in spreadsheet x which is in Folder X to a spreadsheet b in folder B, then the actual hyperlink is Disk C/folder 1/folder 2/folder 3/folder 4/folder A/folder B/spreadsheet b.
The problem is that I don’t have control over folders 1,2,3 and 4.
So if someone comes in and changes the name of folder 2, for example, all my hyperlinks will fail, and will have to be recreated. Is there any way to protect from this please?
Hi
The code below works ok. However I have a column (say C)with email addresses where I want the hyperlink on each one, and copy formula down. Need to replace the email address with something like mailto:C20?
Not quite working. Any help ?
Jon Peltier says:
January 20, 2010 at 7:05 pm
Warrick:
Try this:
=HYPERLINK(“mailto:whatever@whatever.com?subject=”&DB2,DB2)
Hi all.
The goal is to filter worksheet data, using arguments passed from an hyperlink.
Can I use an hyperlink to achieve this?
Many thanks
question – why does compiler choke on this?
Const dSheet = “graphtab” ‘destination sheet
Dim copyToRow As Long ‘row counter
Dim insheet As Worksheet ‘current worksheet
dSheet.Range(“A” & copyToRow).Hyperlinks.Add _
Address:=“#'” & insheet.Name & “‘!A61”, _
Anchor:=Worksheets(dSheet).Range(“A” & copyToRow), _
TextToDisplay:=insheet
roy:
creates a String constant. I don’t think it’s possible to have an object constant. You can have a Variant, but I don’t think it will let you put an object in there. You need to make it a variable, like
Const sSHEETNAME As String = “graphtab”
Set sh = Sheets(sSHEETNAME)
sh.Range(“A1”…)
I am not a programmer and dont have the concept ‘object’ in contrast with variant or deviant for that matter. I’ve made some changes however compiler still does not accept destSheet;
Worksheets destSheet! & “A” & destRow,
Setup:
Dim destSheet As Worksheet ‘name of destination sheet
Dim srcSheet As Worksheet ‘current sheet – with data
Dim whatToCopy As Range ‘list of cells to copy
Dim destRow As Long ‘counter synchronizing srcSheet and row in destSheet
Dim cnt As Long ‘loop limit to number of worksheets in workbook
‘MsgBox “Start”
Start:
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Application.CutCopyMode = True
‘ initialize
destSheet = ActiveWorkbook.Sheet.graphtab
cnt = ActiveWorkbook.Sheets.Count
destRow = 2
Skip:
For Each srcSheet In Worksheets
If destRow = (cnt + 3) Then GoTo Cleanup:
If srcSheet.Name = “v.q.” & “*'” Then ‘ if vdbench file skip
destRow = destRow + 1
Application.StatusBar = “Skipping”
GoTo Skip:
End If
Main: ‘exclude destination sheet
If srcSheet.Name <> destSheet Then
‘hyperlink from destSheet column A current row to srcSheet column A Row 61
Worksheets destSheet! & “A” & destRow, _
.Hyperlinks.Add, _
Anchor:=destheet! & “A” & destRow, _
Address:=“”, _
SubAddress:=srcSheet.Name & !A61, _
TextToDisplay:=srcSheet.Name
‘specify cells to copy
Set whatToCopy = srcSheet.Range(“F62,T62,Z62,AF62,AL62,AR62,AX62”)
whatToCopy.Copy
‘copy cell value not formula
Worksheets destSheet.Range(“B” & destRow), .PasteSpecial
Worksheets destSheet.Range(“B” & destRow), .xlPasteValues
‘increment sheet pointer
destRow = destRow + 1
‘adjust column to widest entry
Worksheet destSheet.Range(“B” & destRow), .EntireColumn.AutoFit
End If
Application.StatusBar = srcSheetName & “complete”
Next ‘get next sheet
Cleanup:
End If
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.CutCopyMode = False
Application.StatusBar = False
Worksheets(destSheet).Active
End Sub
hmmm your compiler complains differently than mine.
If I understand your code correctly, this is how I would write it.
Dim shDestination As Worksheet
Dim shSource As Worksheet
Dim lDestRow As Long
Dim lSheetCnt As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.DisplayStatusBar = True
Set shDestination = ActiveWorkbook.Sheets(“graphtab”)
lSheetCnt = ActiveWorkbook.Sheets.Count
lDestRow = 2
For Each shSource In ActiveWorkbook.Worksheets
If shSource.Name <> shDestination.Name And Not shSource.Name Like “v.q.*” Then
‘hyperlink from destSheet column A current row to srcSheet column A Row 61
shDestination.Hyperlinks.Add _
Anchor:=shDestination.Range(“A” & lDestRow), _
Address:=“”, _
SubAddress:=shSource.Name & “!A61”, _
TextToDisplay:=shSource.Name
shSource.Range(“F62,T62,Z62,AF62,AL62,AR62,AX62”).Copy
shDestination.Range(“B” & lDestRow).PasteSpecial xlPasteValues
End If
lDestRow = lDestRow + 1
Application.StatusBar = shSource.Name & ” complete”
Next shSource
shDestination.Range(“B” & lDestRow).EntireColumn.AutoFit
Application.DisplayAlerts = True
Application.ScreenUpdating = True
Application.CutCopyMode = False
Application.StatusBar = False
shDestination.Activate
End Sub
I want to create a link in an email that, when clicked, would open an excel file and take the user to a specific sheet and cell address. I have been led to believe that
L:MyExcelDirectoryMyBook.xls#Sheet1!A1
is the correct syntax (where L: is a network drive to which users are mapped) – using the pound sign with the standard sheet and cell reference.
Doesn’t work – any thoughts on what a working context should be?
Gregg: That # option only works within Excel, not from external sources. At least as far as I know. I don’t think you can get to a specific spot in a workbook from an external link.
I’m inserting hyperlinks in a spreadsheet so that customers can click the price/description of an item and it will take them to the appropriate website. The links work fine when I save it on the computer, but the file is too big to email so I had to save it to a CD. When I reopen the file on the CD, all of the hyperlinks are gone. Anyone know how to fix that?
How to create the friendly name of the cell to hyperlink automatically to the file name in a folder.
I simply needed to thank you so much yet again. I am not sure the things that I would’ve sorted out without the actual points documented by you on my subject. It has been a very terrifying situation in my opinion, but understanding a new specialized avenue you dealt with it took me to jump with joy. I’m happy for this assistance as well as sincerely hope you know what an amazing job you happen to be undertaking educating other individuals via your blog. Probably you haven’t got to know any of us. Read more on my site accutane action class suit or buy cheap isotretinoin online
Hi
i have this problem, i am a new beginner in hyperlink. i am trying to organise the data in my excel so that it is easy for my boss to read whenever she comes to check my computer. my problem is that i have client statements that are sorted on a monthly basis at the end of the month i have to report to my boss about the total earnings for the month. the statements are in one workbook across worksheets. i want to do a hyperlink in my masterfile to help me link to the other documents within the same workbook so that i can be able to add the total for the end of the month. can someone help me please? how can i do multiple hyperlinks at the same time within one workbook instead of doing it one by one? thank you in advance