Navigation in Excel with Shapes

Excel shapes can serve as hyperlinks for spreadsheet navigation. By assigning links to shapes, spreadsheet developers have full flexibility in modifying a link’s size and look.

Difficulty Beginner
Time Est. 30 Minutes
VBA/Macros No
Template Files 2016-10-13-image-link-navigation-template
Solution Files 2016-10-13-image-link-navigation-solution

Spreadsheet Design Exercise 2

This exercise within the Spreadsheet Design series, continues the focus on Navigation.

The final product in today’s overview will be almost identical to the final product of yesterday’s overview – a horizontal navigation bar for your Excel application. Yesterday’s post discussed a technique for implementing navigation utilizing text hyperlinks. Today’s post will discuss implementing navigation through image hyperlinks. The steps are almost identical, although there are key differences in steps 2 and 5. Regardless, I will repeat all steps on this post as a point of reference.

The difference between image and text-based navigation lies mostly in flexibility – image-based navigation offers much more flexibility in terms of formatting and button size. To provide a quick example: if your application has narrow columns to present data, image links can span multiple columns without the need to merge cells (a good practice to avoid).

Step 1: Develop a list of core Excel application (workbook) components.

Today’s final product will replicate yesterday’s product in terms of content. The navigation menu will contain the following 3 tabs:

  1. Dashboard
  2. AAPL Data
  3. MSFT Data

Step 2: Develop a menu template.

We’ll continue to use a horizontal menu for this example. You can set up your own horizontal menu, or start with this exercise’s template: 2016-10-13-image-link-navigation-template.

In the menu bar, click on Insert > Shapes, then select the square, as shown below.

Insert a new navigation shape into the spreadsheet.
Menu path to the shape tool.

Insert a square into your first navigation cell by clicking in the cell and dragging from one end to the other.  In this example, the shape can be placed in cell B4.

In terms of adjusting the size of the shape, a good method is a formatting tool that snaps the shape to the edges of the cell. To active this feature, click on the shape to activate it – eight “handlebars” will surround the shape. Drawing Tools will then appear with a Format tab. Click on (1) Format > (2) Align, then (3) activate Snap to Grid.

2016-10-13-2
Menu path to the Snap to Grid tool.

As you resize the shape within the cell, you will notice that movement is restricted to cell edges.

Once you get to a desirable shape size, you can format the colors and content of the shape. Around the middle of the Format ribbon, Shape Fill, and Shape Outline options are available.

While the shape is still active, type “Dashboard” on your keyboard. The text will show up on the shape, similar to if it were a text box. Go to the Home menu to center the text and change any other formatting.

The final step for the template visuals is to replicate the remaining two buttons with a copy, paste, and drag. Name the remaining two APPL Data and MSFT Data.

Step 3: Create hyperlinks.

Similar to yesterday’s post, 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).

Also very similar to yesterday’s post, add hyperlinks to each menu item through he hyperlink menu. While the shape (1) is active, go to (2) Insert > (3) Hyperlink. Within the dialogue box, click on (4) Place in This Document on the left panel, then (5) click on Sheet1. Repeat these steps for sheets 2 and 3, respectively.

2016-10-13-3
Hyperlink menu path and dialog box.

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.

Step 4: Replicate navigation menu on each tab.

The first part of this step will group the three buttons to facilitate easier management of the navigation menu. Activate the three buttons at the same time by clicking on each with the keyboard’s “Ctrl” button pressed. The group the shapes by navigating to Format > Group, then pick “Group” within the dropdown menu. If done successfully, you should see one active border surround the 3 shapes.

Simply copy the navigation menu range and paste it in the exact same spot on the remaining two tabs. Note that if you copy the range which contains the group shape, the shape will be pasted as well. Remember to also paste column widths. You should then test each of the links, and the navigation 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 provide a small visual identifier to the navigation menu. This example provides a small white triangle resting between the menu border and the text. It gives a sense of the content page flowing up to the navigation menu. Many other design elements can be invoked such as shape background color changes (similar effect to yesterday’s example), or other visual tricks, such as raised tabs or button glow effects.

2016-10-13-4
Visual cue on link button to let users know where they are.

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.

Screen Captures

2016-10-13-5

Download Solution: 2016-10-13-image-link-navigation-solution

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, classify content, and consistent visuals. While it is possible to do this with tabs, it somewhat cumbersome – additional tabs may not be relevant to navigation, 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.

Drawbacks/Bugs

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.

Leave a Reply