Back Forward Home Print Search
SharePoint Server 2007 Help and How-to >  Business intelligence >  Excel Services >  Viewing data
Using hyperlinks in Excel Services
Using hyperlinks in Excel Services

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.

In this article


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:

TypeURL Component ExampleDescription
Web pagehttp:// The HTTP (HTTP: Internet protocol that delivers information on the World Wide Web. Makes it possible for a user with a client program to enter a URL (or click a hyperlink) and retrieve text, graphics, sound, and other digital information from a Web server.) protocol (protocol: A method of accessing a document or service over the Internet, such as File Transfer Protocol (FTP) or Hypertext Transfer Protocol (HTTP).)
www.example.com/ The Web server (Web server: A computer that hosts Web pages and responds to requests from browsers. Also known as an HTTP server, a Web server stores files whose URLs begin with http://.) or network location
Jan07/ The path or folder
news.htm The file name
 
File or
document
file:// The file protocol
GRPC/The computer name
Mkt/ The path or shared folder
Comments.doc The file name
 
Mail
program
mailto:: The mail protocol
wuleeThe e-mail alias
 
Location
in the same
workbook
#The # (pound sign) character
Widgets!The worksheet name followed by an ! (exclamation point) character
F25The cell reference or range
 
Location
in another
workbook
http://CorpServer/DeptA89/_layouts/ xlviewer.aspx The server, location, the site location, and the Browser view, Web page location and file name
?id=The ?id= parameter argument
http://Mfct/Stats/Shared%20Documents/Parts.xlsx The path to the other workbook
&range=The &range= parameter argument
#Widgets!A1:F25The location in the other workbook
 
 Top of Page

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 Pointer in the shape of 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.

 Top of Page

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.

If this property's check box:Is selected: Is cleared:
Allow HyperlinksAll supported hyperlinks to files and documents outside of the current workbook are active.All supported hyperlinks to locations outside of the workbook are converted to inactive text strings.
Allow NavigationAll supported hyperlinks to locations within the current workbook are active.All supported hyperlinks to locations within the workbook are converted to inactive text strings.
 Top of Page

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.

 Top of Page

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:

General Syntax: [#[<workbook>]<sheetname>!<range>
Example: #[Parts.xlsx]Widgets!A1

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

 Top of Page

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:

General Syntax: http://<server_name>/<site_name>/_layouts/ xlviewer.aspx?id=<workbook path>&range=#<location>
Example: http://CorpServer/DeptA89/_layouts/ xlviewer.aspx?id=http://Mfct/Stats/Shared%20Documents/Parts.xlsx&range=#Widgets!A1:F25

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 ButtonButton image, 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.

 Top of Page