Excel Effects

Reference guide.

Creating a navigation bar in Excel – Quick tip

Creating a navigation bar in Excel - Quick tip

When creating a navigation bar in any program, it greatly enhances the user experience. And, it is even better if you do it in Excel.

You see, if your app is easy to navigate, then that makes it more efficient. In the end, that is what you want.

Therefore, this quick tip focuses on creating a navbar in Excel that links to different sheets within your document. In this instance, we put the navbar on the top of each page. Overall, it will be similar to what you would see on many websites.


For the purpose of this lesson, we suggest you use Microsoft Excel 2007 or higher or compatible. In addition, we expect you to have at least a beginner level knowledge of Excel.


Steps to creating a navigation bar in Excel

Below are the steps to create a top navbar in Excel.

Start with a blank canvas

It always feels good to start off with a blank canvas. So, go ahead and start a new Excel document. Pressing Ctrl-N should do that for you. Otherwise, do it the long way, with File...New from the menu.

Creating a navigation bar - Start with a blank canvas

After that, here we go...


Insert and name your new sheets

Creating a navigation bar - Insert and name your new sheets
  1. Create four sheets by clicking on the plus sign next to the sheet at the bottom of the screen. Also, you can press Shift-F11.

Now, you will have four sheets (Sheet1, Sheet2, Sheet3, and Sheet4), as shown below.

Creating a navigation bar - Four sheets

Next, name your sheets.

Name your sheets
  1. Double-click on each sheet, and give each sheet a unique name. In our case, we chose Home, Summary, Data, and References.

Creating a navigation bar background row for each sheet

Create a background row for each sheet
  1. Right-click on one of the sheet tabs.
  2. From the shortcut menu, chose Select All Sheets.

After doing this, any change you make in one sheet will also change in all the other sheets in your selection. And, that is what we want to do.


  1. Click on row 1 and change the background color to your choosing. After that, resize the row to about 38 pixels.

Now, you have set the background color at the same location for all the sheets in your selection.


Make navigation bar buttons for each sheet

If you have not done so, then click on one of the tabs to deselect the rest of the sheets. We only want to work in one single sheet now.

Make navigation bar buttons for each sheet
  1. Create four textboxes and update the text according to the tabs names. Note: Your text does not need to be the same as the tab names.
Select all the textboxes for your navigation bar and right-click on the selection.
  1. Select all the textboxes for your navigation bar and right-click on the selection.
  2. From the shortcut menu, select Size and Properties...
Under the Format Shape pane, scroll down to the Properties section.
  1. Under the Format Shape pane, scroll down to the Properties section.
  2. In the Properties section, select Don't move or size with cells.
  3. Close the Format Shape pane.

After doing this, it stops the textboxes from shifting in size or position when the user changes the size of the cells.

Basically, you want to keep the textboxes intact on any structural changes made to the sheet.


Move the textboxes to the top of the sheet, right in front of the background row.
  1. Move the textboxes to the top of the sheet, right in front of the background row. Note: You may need to resize your textboxes to flush with the navigation bar.

Create links for each navigation button

Create links for each navigation button
  1. Right-click on one of the boxes.
  2. From the shortcut menu, click Link. Note: The Insert Hyperlink dialog box opens. If you click on the arrow next to Link, then go to the next step.
  3. Under the Link menu, select Insert Link...
Insert Hyperlink dialog box to create links for our navigation bar.
  1. In the Link to: section, select Place in This Document.
  2. Click in the Type the cell reference: box and enter a cell reference you want to go to when the navbar button is clicked. Note: Instead of A1, you may want to go below the navigation bar.
  3. Select the sheet you want to go to.
  4. Click ScreenTip... if you want to provide title text upon hovering.
  5. Click OK.

Next, you have to copy the textboxes to the other tabs.


Copy the navigation boxes across the tabs

Select the navigation bar textboxes and copy (Ctrl-C) them. Go to the next tab and click in cell A1. Paste (Ctrl-V) the boxes at that spot, A1.
  1. Select the navigation bar textboxes and copy (Ctrl-C) them.
  2. Go to the next tab and click in cell A1.
  3. Paste (Ctrl-V) the boxes at that spot, A1.
  4. After that, repeat steps 13 to 15 for the remaining tabs.

Success!

So, were you successful in creating a navigation bar in Excel?

If you had some problems creating your navbar, then feel free to go back and try again.

Optional improvement to the navigation bar

Lastly, to improve the user experience, you can make the button for the active sheet a different color.


Final version

Below, is our final version...

Create a navigation bar in Excel - Quick tip - Final version

Related topics to creating a navigation bar in Excel

Here are some related topics to creating a navigation bar in Excel.


Online courses  Home

Techronology

Artplotty Grid

Development Category (English)300x250

Microsoft Surface Book 3