Home	Excel	Access
| Feedback | Links | Spreadsheet Testing | Macros on the Quick Access toolbar |

Navigation and Buttons in Excel

Would you like to learn more?

Business Analysis with Microsoft 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 is only intended to work with external links. It could be configured to read from a list of filenames (stored in a range of cells), thereby offering a link to alternative external documents, depending on a calculated result. In other words if a result = x then link to document_one, if a result = y then link to document_two and so on.

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.