You can use a menu command to create a hyperlink to a named range or you can use a simple drag-and-drop trick.
Adding a hyperlink to an Excel sheet is a simple enough task, but like so many Office tasks, there's more than one to get the job done. First, I'll show you the traditional way. Then, I'll show you an easy shortcut.Using the Hyperlinks option
The traditional method requires a named range. First, you assign a name to the area of the sheet that you want to link to. Then, you create a hyperlink to that name.
In this first example, we'll insert a hyperlink in a sheet named TOC for Table of Contents. The hyperlink will link to a product list on a sheet named Products (shown below in the first numbered list). You can work with any workbook you want, you just need two sheets.
To get started, let's add a named range (cell really) to the Products sheet as follows:
- Select cell B1 on the Products sheet. This cell contains a title for the sheet.
- Select the Name box (left of the Formula bar).
- Enter the string you want to assign as the cell's name and click Enter. In this case, assign the name Products to cell B1 on the Products sheet.
Creating the name was certainly easy enough. Now, to create a hyperlink to Products (the named cell) do the following):
- Select the cell where you want to insert the hyperlink. In this case, that's cell A2 on the TOC sheet (shown above).
- In Excel 2003, choose Hyperlink from the Insert menu. Or, right-click the cell and choose Hyperlink. In Excel 2007 and 2010, click Hyperlink in the Links group on the Insert menu.
- Click the Place In This Document link to the left.
- Select the named range in the Or Select A Place In This Document list.
- Click OK. The result is a hyperlink that selects cell B1 on the Products sheet. Notice that the hyperlink uses the text in the named cell.
There's another way that you might find a bit easier. It's a drag-and-drop technique, but the sheet tabs must be visible for this technique to work. In addition, it won't work with a new, unsaved file so be sure to save the file first. (You can delete the Products named range, or not. This technique doesn't require a named range.)
To create the same hyperlink using drag-and-drop, do the following:
- Select the cell you want to link to (B1 on the Products sheet).
- Hover the mouse over any of the selected cell's borders until the four-sided cross arrow pointer appears.
- Right-click the border (the standard left click will not work) and the regular mousepointer will replace the four-sided arrow.
- Hold down the [Alt] key while you drag the mouse to the appropriate sheet tab—in this case, that's the TOC (table of contents) tab. Excel will make the TOC sheet the current sheet.
- Release the [Alt] key and select the cell where you want to position the link and release the mouse (A2).
- Select Create Hyperlink Here from the resulting shortcut menu.
It might take a time or two to get the feel of this drag-and-drop technique. Basically, you're just dragging a cell from one sheet to another.