I like to use hyperlinks in my Excel workbooks to help with navigation.
I link them to a Shape that looks like a button to make it intuitive for the user, and also because I think it looks nicer than a text hyperlink.
If I have a lot of worksheets in the file I'll make an index in the first sheet and include a button back to the index in every other worksheet too. Just like in the example file.
The process is quick and easy:
- Insert a shape
- Insert the hyperlink
Let’s take a look at the steps in a bit more detail, plus I’ll give you a tip to prevent your hyperlinks from breaking if you change the worksheet name or move the linked cell.
You’ll find the shapes on the Insert tab of the ribbon in the Illustrations group.
I like to use the Rounded Rectangle for my buttons. To format them select the Shape then on the Drawing Tools: Format tab you’ll find various formatting options.
I’ve chosen the Shape Style that makes it look like a button:
Once you’ve entered the text on your button you can insert the hyperlink.
- Right-click on the shape and select Hyperlink. This will open the Insert Hyperlink dialog box.
- In the ‘Link to’ options choose ‘Place in This Document’ and then on the right 'Type the cell reference' field enter the cell you want to land in, and select the worksheet from the list.
Once you set up one button you can easily replicate it by copying and pasting. Simply hold down CTRL and left click the Shape > CTRL+C to copy > CTRL+V to paste.
I particularly like to use this technique for my buttons that return to the index. Since these are the same on every sheet I just set up one and copy and paste it as many times as I need.
Tip: make sure you get your formatting just how you want and insert the hyperlink before copying and pasting!
If you find yourself applying the same formatting options every time you insert a Shape why not set it up as your default.
Once you've completed your formatting right click the Shape > Set as default.
Now when you insert any shape it will have that formatting applied. Too easy!
Prevent Hyperlinks from Breaking
Hyperlinks are great but if you change the worksheet name you’re linking to, or insert cells above your ‘cell reference’ you’ll find that the hyperlink either doesn’t work anymore, or it’s no longer going to the right location in the workbook.
To avoid these problems I like to give the cell I’m linking to a Defined Name. That way if I change anything in the workbook my name will automatically pick up the change and my hyperlink won’t break.
To define a name, simply go to the cell in the worksheet that you want your hyperlink to go to and in the name box type in the name and press ENTER:
Now when you insert your hyperlink you choose from the Defined Names list instead of the cell reference:
Download the Workbook
Enter your email address below to download the sample workbook.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.