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.

Click Hyperlinks in Excel

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.

  1. 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.
  2. 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.
  3. Point to the selected cell's border, and press the right mouse button.

Excel Hyperlinks 01

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

Excel Hyperlinks 02

  1. The TOC sheet will be activated, and you can release the Alt key.
  2. Drag to cell B4, and release the right mouse button
  3. In the popup menu that appears, click Create Hyperlink Here

Excel Hyperlinks 03

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.

Excel Hyperlinks 04

Watch the Create Hyperlinks Video

To see the steps performed, you can watch this short Excel tutorial video.

______________

0 thoughts on “Creating Excel Hyperlinks Is a Drag”

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

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

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

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

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

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

  7. Hi Debra,

    Thanks for the great tip! I was wondering if there was a way to quickly create a specific cell hyperlink with a friendly name, instead of the cell contents?

    I've used CTRL + K before, but I've found it to be pretty cumbersome to use. When specifying an exact cell, it doesn't appear to let you select different tabs to locate the cell.

    I've also used the =HYPERLINK function, but it seems to be far from quick and easy because it appears to require the full filename and location.

    Recently, I've been using =IF to create "fake" links. My formula would look something like this:
    =IF(cell_reference_of_destination,"friendly name")
    Of course, these cells require double clicking and do not automatically format themselves to look like hyperlinks.

    Any thoughts? Thanks you!

    Yicheng

  8. @Yicheng

    You can use =HYPERLINK() without specifying the file name!

    Try something like:
    =HYPERLINK("#"&CELL("address",'Any Sheet!B2),"friendly name")

    The hyperlink will still work even if you rename the target sheet, insert or delete columns/rows before/after cell B2 because 'Any Sheet'!B2 is a cell reference and Excel will keep track of it and automatically update the formula if required.

    Object-based hyperlinks (inserted via [Ctrl-K]) would break and become useless in such cases.

    The major disadvantage of =HYPERLINK() function though is 255 character limit for both arguments.

    Miro

  9. Sorry, the formula above should read:

    =HYPERLINK("#"&CELL("address",'Any Sheet'!B2),"friendly name")

    (I've missed the apostrophe after 'Any Sheet)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.