will introduce you to the exciting new pivot table features in Excel 2007. Create quick summaries and pivot charts, add impact with traffic light icons, design calculated fields, group dates and numbers.
Creating Excel Hyperlinks Is a Drag
Almost a year ago, we talked about creating a table of contents sheet in Excel. In that post, one of the suggestions was to type a list of sheets on a worksheet, then change each sheet name into a hyperlink.
Create Hyperlinks By Dragging
Well, forget all that typing — here’s a method that’s even easier. Instead of typing, you can drag cells to create hyperlinks.
In this example, there’s a table of contents sheet (TOC), a Sales sheet, and an Expenses sheet. We’ll create a hyperlink to the Sales sheet, on the TOC sheet.
-
First, save the workbook, if you haven’t done so already. If you’re in a new workbook, that hasn’t been saved, this technique won’t work.
-
Next, select a cell that you want to link to. In the screen shot below, the Sales sheet is activated and cell A1 is selected.
-
Point to the selected cell’s border, and press the right mouse button.
-
We want to drag the cell to the TOC worksheet. Press the Alt key on the keyboard, and drag the cell over the TOC sheet tab.
-
The TOC sheet will be activated, and you can release the Alt key.
-
Drag to cell B4, and release the right mouse button
-
In the popup menu that appears, click Create Hyperlink Here
A hyperlink is automatically created, using the text from the Sales Report cell that you dragged.
To go to the Sales sheet, click the Sales Report hyperlink.
Watch the Create Hyperlinks Video
To see the steps performed, you can watch this short Excel tutorial video.
______________
November 4th, 2009 at 3:08 am
My boss crashed my spreadsheet and used the recovery facility in Excel 2003, which is how I discovered to my horror that recovery breaks all the hyperlinks you make using Ctrl-K. I had to rebuild them all as HYPERLINK() functions, which aren’t so volatile.
November 4th, 2009 at 7:16 am
Very nice.
Thanks Debra. Little tips like this really come in handy sometimes.
November 4th, 2009 at 2:05 pm
Can you create the links in a template so they move to the new spreadsheet even if it hasn’t been saved yet? That would be really helpful.
November 4th, 2009 at 5:02 pm
Derek, that’s a pain. Thanks for the warning about the recovery feature.
Thanks Lincoln, glad you like the tips!
Athena, yes links in a new workbook based on the template should work. You just can’t use the ‘Create Hyperlink Here’ feature if the workbook hasn’t been saved.
November 4th, 2009 at 11:03 pm
Love the title of this post. Almost as good as “Freezing in Excel Hell”!
November 4th, 2009 at 11:51 pm
Thanks Jeff! Glad these works of art are appreciated.
November 9th, 2009 at 5:37 am
[...] Make table of contents with just dragging [...]
November 10th, 2009 at 4:31 am
i tried the above feature, but i somehow not getting it. popup menu shows copy cell, fill formatting, fill without formating, i don get the create hyperlink. wat do i do ? am using MS excel 2003. pls help
November 10th, 2009 at 9:19 am
Reena, when you point to the border of the cell that you want to drag, the pointer should have 4 arrows. If you drag the cell when that 4-headed arrow is showing, you’ll see the Create Hyperlink Here command.
However, if you point to the bottom right corner of the cell, where the little black square is (the autofill handle), the pointer changes to a black plus sign. If you drag the cell when that plus sign is showing, you’ll see the Copy Cell, Fill Formatting Only, commands.
November 11th, 2009 at 12:10 am
I wanted to create an Index page with hyperlinks to Sheets and Charts but couldn’t get the Charts working.
This was my solution, which I am happy with. It dynamically creates the index when the Index page is activated. and
Private Sub Worksheet_Activate()
Dim rowcounter As Integer
rowcounter = 1
Sheets(”Index”).Cells.ClearContents
For Each Sh In ThisWorkbook.Sheets
Select Case Sh.Name
Case “Index”
Case Else
Sheets(”Index”).Cells(rowcounter, 1).Value = Sh.Name
rowcounter = rowcounter + 1
End Select
Next
With ActiveWorkbook.Worksheets(”Index”).Sort
.SetRange Range(”A1:A100″)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Select Case Target.Column
Case 1
If Len(Target.Value) > 0 Then Sheets(Target.Value).Select
End Select
End Sub
November 11th, 2009 at 4:42 pm
Paul, thanks for sharing your code, to help index workbooks that have chart sheets.
November 12th, 2009 at 2:37 am
Thanks Debra, i got it now. that’s really wonderful.