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.
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.
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:
- AAPL Data
- 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:
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.
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.
Add Content/Data to Each Sheet
The initial setup of the navigation bar is complete and data can now be added to each worksheet.
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.
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.