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.

Overview

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.

Example File

Implementation

Text base navigation can be implemented by following general design principles around consistency and user feedback.

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 workbook—which compares stock price history between Apple and Microsoft—we include 3 tabs:

  1. Dashboard
  2. AAPL Data
  3. MSFT Data

Develop a Menu Template

For this example, we’ll be utilizing 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:

2016-10-12-1
A simple navigation menu.

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.

2016-10-12-2
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.

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

Provide Users with Feedback

Visual cues should be provided to let the user know where they are in the workbook. 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.

2016-10-12-3
The appearance of a highlighted tab.

Add Content/Data to Each Sheet

The initial setup of the navigation bar is complete and data can now be added to each worksheet.

Video/Screen Captures

Potential Drawbacks

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 workbook. There is also the hyperlink text color limitation, but this can be circumvented by using shapes in place of text.

Other Notes

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.

 

2 Comments

    1. This navigation solution simply transfers the user to different spreadsheets in the workbook. You can see the spreadsheet tabs broken out below the spreadsheet. Each tab has its own navigation menu, which is duplicated in its entirety, except for the color added to the “active” menu button. So, while it appears to the user that he/she is navigating in the same spreadsheet, they are actually jumping between three different spreadsheets.

      To summarize, you only need to change the background color of the “active” button in each of the three navigation menus.

Leave a Reply