Navigation and Buttons in Excel

If you regularly use a large Excel spreadsheet and repeatedly need to move between particular sections, it is worth adding some simple links to automate moving from one area to another. The link can be placed anywhere convenient on a worksheet and when clicked, it takes you directly to another location. If you follow good spreadsheet design practice (see spreadsheet testing) and keep your data separate from your calculations and reports, these hyperlinks can save a lot of time.

Hyperlinks

Select a cell where you wish to place the link. Using the ribbon option [Insert] Links | Hyperlink a dialog box opens and lets you link to an external file/web page/document or to a place within the current spreadsheet (document). In the current document you can either choose a cell reference (all of the worksheet names are listed) or a previously defined range name.

A new hyperlink will be added to your worksheet (normally identified by a blue font and underlined). A single click on this link will instantly move you to the specified location. If the cell containing the link already contained text, that text becomes the visible link, otherwise the destination cell reference appears as the link text.

You may choose to add your hyperlink to a button or a drawing object instead of a text item. Graphics such as arrows, boxes and banners can be selected from the [Insert] Shapes ribbon option. If you format the button or shape object, you can choose whether or not you wish it to appear when the document is printed (Format Shape | Properties | Print object).

There is also a HYPERLINK function which performs a similar role but it can have a destination which is determined by the text in another cell. The text which defines the destination address can be dependent on a formula and can therefore create a number of alternatives. It could create a link to some alternative external documents or to different worksheet and cell locations within a spreadsheet.

In this example a series of hyperlinks have been created to take the user from an invoice record on a worksheet, to the corresponding receipt record on another worksheet.

Use MATCH() to find the location of the Invoice Number on the Receipts worksheet. If the number isn't found, an error will occur.

• [F4] =MATCH(A4,Receipts!$A$4:$A$100,0)

Create a text string for the location. If [F4] is an error, the location string will be column A on the current row.

• [G4] =IF(ISNA(F4),"[temphyper.xls]invoices!A" & TEXT(ROW(A4),"0"),"[temphyper.xls]receipts!A" & TEXT(F4+3,"0"))

The HYPERLINK function uses the text string in column G as the location address.

• [B4] =HYPERLINK(G4,"go to recpt")

The hyperlink causes the cursor to jump to the cell containing a matching invoice reference on the receipts worksheet.

The location text strings will be more complicated if the filename or the worksheet name contains multiple words separated by spaces. Apostrophes will be required around the names.

If you are linking to locations in the current file, you can simplify the formula and avoid the need for typing the filename. The hash (#) character can be used as a substitute for the current file. In the following example, the HYPERLINK function in column D uses the addresses in column E. These addresses have been generated using three alternative formulas.

The ADDRESS function can be used to create the text reference required for the hyperlink. ADDRESS(row number, column number) will return a text range of a cell. E.g. ADDRESS(5,3) will return the text $C$5. (This text in turn could be used by the INDIRECT function.)

In the following example the user types a name in cell E2 and MATCH() is used to find the name in a table. ADDRESS (in G3) creates a text reference to the cell containing the name (C10). HYPERLINK (in C4) prefixes the reference with '#' and creates a link to the named record.

Note that all hyperlinks within an Excel spreadsheet can be affected by a field called Hyperlink Base within [File] | Info | Properties - Show All Properties | Hyperlink Base. This can force all hyperlinks to be searched for within a particular folder or web location without having to specify the path.

Buttons

Buttons can be placed on your worksheets and configured to run a macro when clicked.

To add a button, select [Developer] Controls | Insert | Form controls. The button can be selected from this sub menu and placed anywhere on a worksheet. The properties of the button allow you to assign a pre-existing macro to it and to edit the text displayed on the button.

If you consider that writing macros is beyond your ability, think again. Excel provides the option of recording macros whereby your keystrokes are saved and can then be replayed. If you have a worksheet containing various different reports, record the keystrokes necessary to set the print area of a required report and any alterations to the page setup details. Each configuration can be linked to a series of user friendly buttons.

Get this information as a document accompanied by Excel worksheets

Click here for details about obtaining this file. It has been rewritten for Excel 2010.