Navigation in Excel with Text Links

Navigating through an Excel workbook should be like navigating through a well designed website. By approaching projects with the mindset of good design, you can ensure great usability standards.

Difficulty Beginner
Time Est. 20 Minutes
VBA/Macros No
Template Files 2016-10-12-aapl-prices
Solution Files 2016-10-12-text-link-navigation-solution

Spreadsheet Design Exercise 1

This exercise within the Spreadsheet Design series begins the focus on Navigation.

Text hyperlink navigation is the easiest solution to allow users to quickly jump between different components of your workbook. This solution will be familiar to users, mimicking navigation systems currently prevalent in desktop software and web-based applications. In addition to the basic mechanics, we will also speak about design features and visuals to make the approach more appealing to users.

Step 1: Develop a list of core workbook components.

Advanced planning is essential to any spreadsheet design. The first step is to determine which links will be provided on the navigation menu. Each menu item will be displayed as an individual tab. For our application, which compares stock price history between Apple and Microsoft, we will include 3 tabs:

  1. Dashboard
  2. AAPL Data
  3. MSFT Data

Step 2: Develop a menu template.

For this example, we’ll be using a horizontal menu. These menus are the most popular on desktop software and web applications, and are effective for displaying 4 to 5 top-level pages. A vertical menu is also acceptable, although it may post some limitations when working with tabular data.

When developing your first navigation menu, it’s important to incorporate all the design elements early on. This menu will be duplicated to all spreadsheets you will be using, so finalizing the design early can help you avoid making duplicate changes later on. Start with a straightforward design, such as follows:

A simple navigation menu.

Step 3: Create hyperlinks.

Each menu item should have its own worksheet. Add two new worksheets by clicking on the “+” tab on the tab toolbar. On “Sheet1”, highlight the first menu item (cell B4 in this example).

Next, add hyperlinks to each menu item through he hyperlink menu. With each menu item selected, go to (1) Insert > (2) Hyperlink. Within the dialog box, click (3) Place in This Document on the left panel, then (4) click “Sheet1”. Repeat these steps for sheets 2 and 3, respectively.

Link locations can be assigned through the Insert Hyperlink dialog.

When you click the hyperlinks, you’ll notice that they bring you to the two blank tabs, return to Sheet1 through the tab toolbar and test all menu items.

You may notice that the hyperlink has changed the formatting of your text. You can modify the font type and size to your liking, but unfortunately the text colors are based off of your system settings.

Step 4: Replicate navigation menu on each tab.

Simply copy the menu range and paste it in the exact same spot on the remaining two tabs. Remember to also paste column widths. You should then test each of the links, and the menu should look identical on each sheet.

Step 5: Let the user know where they are.

Visual cues should be provided to let the user know where they are in the application. In this example, we’ll add a page subtitle under the navigation menu, and highlight the menu item on the navigation bar. Do this for each menu item in the workbook.

The appearance of a highlighted tab.

Step 6: Add content/data to each sheet.

The initial setup of the navigation bar is complete and you can now begin adding content to each sheet.

Video/Screen Captures


Other Points

You may be asking ‘Why not just use the tabs?’ With that, I bring you back to our original post on navigation, where I speak about the 4 design themes: consistent navigation, visual cues, classified content, and consistent visuals. While it is possible to do this with tabs, it somewhat cumbersome – additional tabs may not be relevant to the user interface, tabs are often out of the users visual space, and tab names tend to be haphazardly named. In short, a horizontal navigation bar—such as the one used in this example—will be familiar to the user.


There are a few drawbacks to this approach. Most obvious is the fact that you’ll need to change the navigation menu on multiple tabs if you need to add a new section to your application. There is also the hyperlink text color limitation, but this can be circumvented by using shapes in place of text.

Leave a Reply