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.
Inserting Shapes
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.
Inserting Hyperlinks
- 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.
Quickly Replicate
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!
Default Shape
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
Please Share
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.
Slobodan Horvatic says
Hello everybody,
great techinuque indeed, looks really nice and adds value to the report!
There is one thing I just recently discovered though. There is a way to get rid of nasty (default) screen tip message when you hoover over the hyperlink. So, if you go to EditHyperlink, on the top right corner you can find “Screen tip” button. Just type more user friendly name and that is it :))
Mynda Treacy says
Great tip, thanks Slobodan 🙂
duncan says
Very good tutorial.
A few years ago I prepared a series of self directed learning spreadsheets for accountibg students. I wanted to hyperlink from my menu to the relevant sheet/exercise. Excel 2000 or thereabouts, I think … couldn’t be done!
Things can only get better!
Duncan
Mynda Treacy says
Onwards and upwards 🙂
nitin says
Is there a way by which we can insert formula which relate to data in other text boxes ?
Meaning: I have a sub index of my application which has hyper links to various sheets in the work book. against each sub-index item, I also have a sub total of the relative sheet so I have a summary of all the sheets in the sub index.
I would like to use your hyper-link shapes for the hyperlionks but would like to complement each shape with a counter shape with the sub total of that work sheet…..
Catalin Bombea says
Hi,
Please upload a sample of your workbook, it will be a lot easier for me to understand the situation! Every details you can give is important, so be generous with details 🙂 ,i will gladly help you. You can use the Help Desk to upload an example file: https://www.myonlinetraininghub.com/helpdesk/
Cheers,
Catalin
Philip Treacy says
Doug Drury emailed me asking how to open an Excel template using this hyperlink method.
Using a hyperlink to do this causes the template to be opened for editing. Whereas double clicking the template file opens a new workbook using that template.
To use a shape within a workbook to open a new workbook based on an existing template, you need to use a macro and assign the macro to the shape. Fortunately it’s a very simple macro :
Just add that into a module in your workbook, change the path to point to your template, assign it to your shape and when you click the shape, a new workbook will be created based on your template.
Phil
Rachael Hanna says
An(other) excellent tutorial – I will be sharing this one with a few friends. I often wondered if something like this were possible, because it would save a lot of time. Now I know that it is.
Regards
Mynda Treacy says
Thanks, Rachael 🙂
As another member pointed out, you can also insert hyperlinks to take you to locations outside of your workbook. A bit like icons on your desktop, you can have an Excel workbook with hyperlinks to your most common web pages, or programs/files etc.