Show All

Hide All

A useful feature of Microsoft Office Excel is the ability to add hyperlinks to cells in a workbook and then use those hyperlinks to view information. A hyperlink is a text string, often colored blue and underlined, that you click to activate (or follow). When you display an Excel workbook in Excel Web Access, you can use a hyperlink to open a document, file, or Web Page in a new window, to start an e-mail program and create a message, to navigate to different locations within the current workbook, and to open another Excel workbook in Web browser view to a specific location. You can also control hyperlink behavior with Excel Web Access custom properties.

What a URL is and how it works

Creating a hyperlink in Office Excel for use in Excel Services

Excel Web Access custom property settings that affect hyperlinks

Using hyperlinks that go to a location outside of the current workbook

Using hyperlinks that go to a location within the current workbook

Passing another workbook's location as a query parameter in a hyperlink

What a URL is and how it works

When you create a hyperlink, its destination is encoded as a Uniform Resource Locator (URL) (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.), which can take several different forms:

Creating a hyperlink in Office Excel for use in Excel Services

Using Excel, you can create a hyperlink in an Excel workbook by:

• Typing a URL in a cell. A hyperlink is automatically created.
• Selecting a cell, and then by clicking Hyperlink in the Links group on the Insert tab (or by pressing CTRL K).
• Using the HYPERLINK function, for example:

  =HYPERLINK("file://FinData/report/budget report.xls", "Click for report")

• Pasting text into a cell to create a cell reference by using the Paste as Hyperlink command. (On the Home tab, in the Clipboard group, click the arrow next to Paste, and then click Paste as Hyperlink.)

For more information, see Microsoft Office Excel Help.

In Excel Services, when you hover over a hyperlink, the mouse pointer becomes a hand , indicating that you can click the text to activate the hyperlink.

Tip   To select the cell that contains the hyperlink without activating the hyperlink, position the mouse pointer on the far edge of the cell away from the hyperlink, and then click the cell.

 Notes 

• The color of the hyperlink is based on the default hyperlink color defined for the browser unless you explicitly set the default hyperlink color in the Excel workbook.
• Hyperlinks that reference an entire column or row are converted to a reference to the first cell in that row or column.
• In Excel, although you cannot create hyperlinks to PivotTable reports, PivotChart reports, or chart names, you can create a hyperlink to an underlying cell for each of these objects if you want to navigate to them by using a hyperlink.
• Only absolute hyperlinks are supported in Excel Web Access; relative links are not supported in Excel Web Access. If you need to change a portion of a hyperlink, for example the file name of a document that has changed, in Excel, you can use a cell reference in a formula that contains a HYPERLINK function, such as:

  =HYPERLINK("[http://example.microsoft.com/report/budget report.xls]"&D1)

  In Excel Services, you can then use a parameter to change the value of the cell. For more information, see Change workbook parameters in Excel Services.

• Hyperlinks in charts, graphic hyperlinks, and hyperlinks to noncontiguous ranges are converted to text strings, but they are not supported in Excel Web Access as active hyperlinks.

Excel Web Access custom property settings that affect hyperlinks

Two Excel Web Access custom properties control the behavior of hyperlinks as summarized in the following table.

Using hyperlinks that go to a location outside of the current workbook

You often use hyperlinks to files or documents outside of the current workbook to provide related or detailed information that supplements the current workbook. For example, a Web page might contain additional help information about the workbook or a description of relevant company policies and procedures. Or in Excel, you might have a list of rows that contain structured data and numbers with one column that links to a section in a corresponding Microsoft Office Word document that contains rich text and pictures.

If a hyperlink goes to a location outside of the current workbook, Excel Services opens a new window.

Using hyperlinks that go to a location within the current workbook

You often use hyperlinks to locations within the same workbook to navigate around the workbook. For example, if you have many separate worksheets in one workbook, you can create a dedicated worksheet that acts like a main menu and link to each separate worksheet, perhaps providing additional descriptive comments next to each hyperlink. Or you might have a large workbook that tracks the design of many parts in a product that your company is manufacturing. Each worksheet contains details on each part and any interdependencies between the parts, which can be cells that contain hyperlinks that go to the pertinent worksheet.

If a hyperlink goes to another location in the current workbook, then Excel Services displays the new location in the Excel Web Access Web Part (and does not open a separate window). You create this hyperlink in the following way:

Where the # (pound sign) is required, the [<workbook>] is required if the location is in another workbook, the <sheetname> is required if the location is in another worksheet, followed by an ! (exclamation point), and <range>, which can be any of the following:

• A cell location, such as Sheet1!A1.
• A local named range, such as, Sheet1!Q2Summary.
• A global named range, such as, FY04BalanceSheet.

When you click the hyperlink in Worksheet view, it goes to the workbook location. If it is a cell location, the cell is selected and positioned in the center of the Excel Web Access Web Part. If it is a local or global named range, the top left cell of the range is selected and positioned in the center of the Excel Web Access Web Part.

When you click the hyperlink in Named Item view, it goes to the item and selects the cell. If it is a cell location, the cell is positioned in the center of the Excel Web Access Web Part. If it is a local or global named range, the top left cell of the range is positioned in the center of the Excel Web Access Web Part. If there are two or more named items that contain the same cell reference or range, then the one that is used is the first one in ascending alphabetical order (as listed in the Show view drop-down list).

Passing another workbook's location as a query string parameter in a hyperlink

In Excel, you can create a hyperlink that references a cell or range location in another workbook by using the [#<workbook>]<sheetname>!<range> syntax. To link to another workbook in Excel Services, you use a different approach by passing the other workbook's location as a query string parameter in a hyperlink that uses the Web Browser view page (xlviewer.aspx) to open the workbook to the specific location.

For example, you can set up a Key Performance Indicator (KPI) list, where each item takes its value from a different workbook. For each item, you create a hyperlink that links each KPI to the workbook that its value comes from. When you click on that item to view in depth information, you automatically navigate to the workbook that the value depends on. The appropriate cell is immediately visible, making it more convenient than simply opening the workbook to its default location.

You create this hyperlink in the following way:

Where <server_name> is the name of the SharePoint server, <site_name> is one or more sites and possible subsites where the workbook is located, <workbook path> is the path and file name, and #<location> can be any of the following:

• A cell location, such as Sheet1!A1.
• A local named range, such as, Sheet1!Q2Summary.
• A global named range, such as, FY04BalanceSheet.

Important  Before publishing the Excel workbook to Excel Services, make sure that Office Excel creates an absolute address for the URL by doing the following:

1. Click the Microsoft Office Button, click Excel Options, and then click the Advanced category.
2. In the General section, click Web Options.
3. In the Web Options dialog box, click the Files tab, and then clear the Update links on save option.

If you pass another workbook's location as a query string parameter in a hyperlink, Excel Services opens a new window in browser view and goes to the workbook location. If it is a cell location, the cell is positioned in the center of the Web page. If it is a local or global named range, the top left cell of the range is positioned in the center of the Web page. In either case, the cell is not selected.