Build Navigation Buttons with Excel Macros

By Michael Alexander

The most common use of macros is in navigation. Workbooks that have many worksheets or tabs can be frustrating to navigate. To help your audience, you can create a switchboard, like the one shown here. When users click the Example 1 button, for example, they’re taken to the Example 1 sheet.

Use macros to build buttons that help users navigate your reports.

Use macros to build buttons that help users navigate your reports.

Creating a macro to navigate to a sheet is quite simple:

  1. Start at the sheet that will become your starting point.

  2. Start recording a macro.

  3. While recording, click the destination sheet (the sheet this macro will navigate to).

  4. Stop recording.

  5. Add a button form control on your starting point and Assign the macro to a button by selecting your newly recorded macro in the Assign Macro dialog box.

Excel has a built-in hyperlink feature, which enables you to convert the contents of a cell to a hyperlink that links to another location. That location can be a separate Excel workbook, a website, or another tab in the current workbook. Although creating a hyperlink may be easier than setting up a macro, you can’t apply a hyperlink to a form control (such as a button). Instead of a button, you use text to let users know where they’ll go when they click the link.