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.
After that, here we go...
Insert and name your new sheets
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.
Next, name your sheets.
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
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.
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.
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.
From the shortcut menu, select Size and Properties...
Under the Format Shape pane, scroll down to the Properties section.
In the Properties section, select Don't move or size with cells.
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. Note: You may need to resize your textboxes to flush with the navigation bar.
Create links for each navigation button
Right-click on one of the boxes.
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.
Under the Link menu, select Insert Link...
In the Link to: section, select Place in This Document.
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.
Select the sheet you want to go to.
Click ScreenTip... if you want to provide title text upon hovering.
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.
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...
Related topics to creating a navigation bar in Excel
Here are some related topics to creating a navigation bar in Excel.