Create Buttons To Navigate Between Worksheets

Mon 21st February 2011

If you have a workbook with so many sheets not all of the tabs are visible on the screen, you can create a main page with buttons that will take you to the desired page with a single click.

Microsoft Excel is a mighty big application. If you were to scroll down to the bottom row in Excel 2010 you would be busy for quite some time; there are a maximum of 1,048,576 rows. Multiply this by the 16,384 columns and we are talking a serious number of cells. Of course none of us are ever likely to use Excel to that extent but

For example, if you have a separate worksheet for each month of the year then the screen will not accommodate all of the tabs that run along the bottom of the program window. This is quite easily dealt with because when the number of tabs exceeds the amount of space available then the navigation buttons to the left become active. These allow the user to move through the months with ease, or skip straight to December by clicking the last sheet button. This is fine when dealing with a comparatively small number of worksheets, but how do you navigate through a workbook consisting of many sheets? The answer is surprisingly simple.

Let us assume you have a workbook on the states of the USA, and that each state has its own worksheet. That's a total of fifty sheets, one for each state from Alabama to Wyoming. This greater number of sheets means that more time and effort is needed to navigate to those states in the middle area. In my standard view I have eight tabs visible on the screen. To get to New York, for instance, would require twenty-four mouse clicks. A far easier way to navigate through workbooks such as this is to create buttons on a single sheet that will take you to each state with a single click. Here's what to do in pre-2007 versions of Excel.

First you must create your tabs. You need 51 in total, one for the Main sheet and fifty for the states. Right click on the tabs at the bottom of your worksheet and select Insert and then Worksheet. After you have created your 51 tabs go through each one again right clicking and then selecting Rename. Start with your Main sheet and then rename the other tabs in the names of the states. When you have done this you are ready to create your buttons.

A good button shape is the rectangle with rounded corners that sits in the Basic Shapes menu in AutoShapes (if you cannot see AutoShapes on your screen, go to View/Toolbars/Drawing). Pull one of these out to a size that will be suitable for the number of buttons you will be creating, which is fifty in our example States of the USA workbook. Fill the shape with a colour and either remove the outline by selecting No Line, or make the line the same colour as the fill.

That is your basic button. Now pull out a text box and move it into position above your shape. Select No Fill and No Line so the text box becomes totally transparent. Now type the name of your first state into the text box on the button. In the above scenario, this would be Alabama. Apply any formatting to the text at this stage, as it will be copied and pasted many times. You might want to experiment with different fonts and colours until you find a combination that looks good against your button colour.

This next stage is very important. Select the text box and make any final adjustments to its position on the button. Now take one of the handles on the text box and move it inwards, just enough so that it is not entirely covering the button below it. You should be able to select the button and the text box independently. If you can do this then select the button and hold down the Shift key and select the text box. Right click and go to Grouping and select Group. This will convert your two objects into a single one.

Copy this (Ctrl + C) and paste it (Ctrl + V). Repeat this until you have sufficient buttons for your needs and arrange them neatly on your worksheet. The next part is fiddly, but worthwhile if it is a workbook that will be updated regularly.

Select the second of your buttons and right click. Go to Grouping and select Ungroup. Delete what is in the box and type in the appropriate text for that button, Alaska in the example workbook, and then hold down the Shift key and select both objects. Right click again and go to Grouping then select Regroup. Do this to all the buttons on your sheet.

When all of the buttons are updated you are on the home straight. Simply select your buttons one at a time and right click. Select Hyperlink and in the dialog box click on Place in This Document. Then click on the page that matches the button and OK. When you get to the end it's job done. You have effectively created a 'home' page that will take you to any of the pages within the book at the click of a mouse. You can save even more time by creating a Return button on each sheet and, using the same process as above, create Hyperlinks back to your main page. This does not take very long as you only need create a single button to copy and paste, with no alteration to the text.

In Excel 2007 things are slightly different. Go to the Insert menu and select Shapes. From the collection of various shapes choose the rectangle with curved corners as before. Pull out the rectangle to the desired size and then, in the Format menu, go to Shapes Styles. This will open up a selection of different colours and styles that offer more variation than the rather flat colours of earlier versions. When you are happy with the colour of your button, double click it and you should be able to type directly onto it, without the laborious process of grouping and ungrouping as described above. Copy and paste as before and change the text on your buttons until you have them all. Then create the links as before.

Creating your own buttons is an effective way to navigate quickly between worksheets, especially when not all of the tabs are visible. Using shapes to create buttons that will link into other worksheets serves as yet another demonstration of the versatility of Excel.

Author is a freelance copywriter. For more information on excel course london, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1491-create-buttons-navigate-between-worksheets.html