Skip to content

How to Build Custom Spreadsheet Navigation

Up until now I’ve talked a lot about spreadsheet navigation and the various ways of how you can improve it and make it better. However, I haven’t really talked a whole lot about how you would create this type of navigation if you wanted to build it yourself.

This being the case, I thought it would be a wonderful idea to share with you the exact steps you would take to build your very own custom spreadsheet navigation menu.

Following below are the instructions for building a side navigation panel that you will be able to use to jump between the different sheets in your Excel workbook without having to use the tabs.

If you want to download the finished version of the spreadsheet we are about to build to compare to what you end up building yourself or if you just want to have it for reference, you can use the download link below.

Custom Spreadsheet Navigation

Now, without further ado, let’s build our own custom spreadsheet navigation.

Step 1 – Create the Side Panel

The very first thing we’ve got to do before anything else is create the side panel area where we will place all of our spreadsheet navigation buttons.

We are going to use the first three columns for the panel. To create the panel, follow the mini-steps below.

A – Size the Columns

Highlight Column A, right click, and select “Column width…”

Enter a value of 4 and hit “Ok.”

Repeat this step for column C.

Repeat this step again for Column B, but enter a value of 24 instead of 4.

B – Add a Fill Color

Highlight all three of the columns that you just modified (A, B, and C).

Click the fill button and select the following color (or any color of your choice if you’d like).

We now have our side panel area defined and ready to go.

Step 2 – Create the Rest of the Worksheets

Now that we’ve got our side panel defined, we are ready to copy this sheet to make the rest of the worksheets for our workbook. This way, everything will look nice and uniform.

To do this, all we’ve got to do is right click on the worksheet tab at the bottom of the workbook and select “Move or Copy…”

Select “(move to end)” and check the box for “Create a copy” at the bottom of this Window. Then click “Ok”.

Repeat this step for as many sheets as you would like to have.

For this example, we will be creating six worksheets total. You can also right click each tab to rename the sheets to be anything you want. In this case, we keep the naming simple.

Step 3 – Create the First Button

Next, we are going to create our first navigation button using the shapes feature that Excel has to offer.

To create this button, follow the series of Mini-steps outlined below.

A – Insert a Rounded Rectangle

In the ribbon at the top of your workbook, go ahead and insert one rounded rectangle. For now, you can make it any size you wish.

B – Select “Snap to Grid”

Next, we are going to turn on the “Snap to Grid” feature for this shape. This will make it easier to align and size the shape properly to get it centered and the right size for our navigation panel.

Take a look at the photo below to see how to enable the “Snap to Grid” option.

C – Move and Size the Shape

Use your mouse to move and size the shape so that is spans the width of Column B and covers cells “B3”, “B4”, and “B5”.

Take a look at the photo below if you need more guidance.

D – Color the Shape

Now that we’ve got the shape positioned and sized correctly, we can configure both the fill and outline color to go along with the color theme of our side navigation panel.

Go ahead and click on the shape and then set the “Shape Fill” to the color you see below.

Next, click the “Shape Outline” and select the color you see below.

Don’t worry, even though the shape looks almost invisible at this point, it will all come together in the end.

Step 4 – Set the Property – “Don’t Move or Size with Cells”

Although this is a very quick and simple step, it is a very important one.

This will make it so that we can resize the rows and columns of our worksheet later without the worry of ruining the look of our navigation buttons.

To pull this off, simply right click on our shape and select “Size and Properties…”

Then, in the “Format Shape” panel on the right, under “Properties”, select the option “Don’t move or size with cells”.

Step 5 – Create 5 Copies of the Shape

To save us some time, we are going to copy and paste this shape rather than make 5 more of the same shape from scratch.

To do this, simply right click on the shape and select “Copy”.

Now, select cell “B8”, right click, and select “Paste” (keep source formatting), or hit “Ctrl-V”.

Repeat this paste action for cells “B13”, “B18”, “B23”, and “B28”.

You should now have six shapes in your side panel, and it should look something like this.

Step 6 – Create the Name Ranges to Be Used for the Shapes

For this step, we are going to create six name ranges that we will use for the names of our shapes.

This will make it easy for us to change the names of our navigation buttons at any time down the road. Follow the Mini-Steps below to accomplish this task.

A – Select “Sheet6”

Go ahead and select “Sheet6”.

B – Enter in Six Temporary Names

You can enter in these names anywhere you want on the sheet, but for the sake of this example, I will be entering the six names in cells “E4” through “E9”.

Enter in six temporary names in these six cells. (One name per cell.)

C – Select Each Cell and Enter a Named Range for Each

Select cell “E4” and enter in a name in the box shown in the photo below. Then select the “Enter” button on your keyboard.

Repeat this step for the remaining five cells. Be sure to use a different name for each cell.

I will be using the names “button1”, “button2”, etc.

Step 7 – Assign the Names to Each Shape

Now we are going to assign our named ranges to our shapes. This will be make our buttons display the values that we enter into any of our named range cells.

In other words, this will make it easy for us to re-label our navigation buttons at any time in the future.

Follow the series of Mini-Steps below to carry out this task.

A – Assign the Named Range to the Shape

Left click on the top button, and enter in “=” and the first named range in the formula bar at the top of the workbook and hit the “Enter” key.

In our case, we will enter in “=button1”. (See the image below)

B – Repeat for the Remaining Shapes

Repeat this same action for the other shapes. The only difference is that you use the second named range for the second shape, the third named range for the third shape and so on.

C – Format the Shape Text

We finally have all the shapes showing the correct label, but they don’t look very good at all.

To make them look better, format the text of these shapes in the following way:

  • Select all the shapes at once (you do this by holding down the “ctrl” key and clicking on all six shapes)
  • Set the color of the text as shown in the image below

  • Bring the text to the middle and the center

  • Set the font size to 18

We should now have six buttons that look a whole lot better.

Step 8 – Add Hyperlinks to the Six Shapes

Now that we have our six buttons, it’s finally time to add in their navigational ability.

To do this, we will add a hyperlink to each shapes. This will enable the shape to take you to the worksheet you would like to go whenever you click on it.

To add hyperlinks to each shape, follow the process below.

  • Right click on the shape
  • Select “Hyperlink” or “Link”

  • Select “Places in This Document”
  • Click on “Sheet1” (Sheet2 for Button2, etc.)

  • Click “Ok”

Don’t forget to do this for each shape, and be sure to click on each shape to test if it takes you to the correct sheet.

Step 9 – Copy and Paste to All of the Other Sheets

Now that we have six working buttons, we simply need to select all of them at once by clicking each one (remember to hold the “ctrl” key when clicking on all of the shapes).

Right click one more time and select “Copy,” or press Ctrl+C.

Navigate to Sheet2, select cell B3, and press Ctrl+V, or right click and select “Paste”.

Navigate to the other sheets and paste the buttons in the very same fashion.

Step 10 – Highlight the Active Button (Optional)

At this point we have our own fully functional custom navigation system.

However, there is still a little more we can do to add a little more flare to our masterpiece. All we are going to do is highlight the button that represents our “Active Sheet”.

What do I mean by this? I mean, that if we are on Sheet1, we will highlight the first button. If we are on Sheet2, we will highlight the second button, etc.

To create this highlight, all we have to do is modify the format of the active button on each sheet. So on Sheet1, we will right click on the first button and set the Fill Color, Outline Color, and Font Color to those shown below.

For Sheet2, we will do the same thing for the second button. On Sheet3, we will do the same thing for the third button.

We will do this until we’ve got all of the sheets covered.

You Did It!

There you have it! Our own custom built spreadsheet navigation.

If you messed up along the way or just want to check your work to see if you got everything right, you can download the finished product below and take a quick look. I’m sure you did awesome!

Custom Spreadsheet Navigation

Thank you for sticking with me throughout this whole process. I hope you had fun, and even more importantly, I hope you learned something new. And the next time someone complains about spreadsheet navigation, you can show them a much better way!

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top