I like to use Excel hyperlink buttons 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.
Another technique I like to use, particularly in dashboard report files where I might have multiple dashboards, is to create what appear to be tabs in the top of the report:
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.
Watch the Video
Download the Workbook
Enter your email address below to download the sample workbook.
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:
Hi, great tutorial! Is there a way to hyperlink to a hidden sheet? I dont want my user to be able to see all the individual tabs at the bottom of the excellsheet, instead i want them to be able to click through to them using the hyperlink buttons i have created
Hi Max,
No, hyperlinks to hidden sheets won’t work. However, you can hide the sheet tabs, which might make it more difficult for them to navigate without using the hyperlinks.
Mynda
Does this work on the online version of Excel and is there an alternative method if not?
Hi Kevin, yes, hyperlink buttons work in Excel Online.
Hi,
Is there anyway I can get the hyperlink link shape/button to be based on the hyperlink in another cell?
I have a HR dashboard that pulls up relevant employee info based on the name searched for or selected from the search bar/dropdown list at the top.
Currently data is populated from a table on another sheet by having shapes/textboxes on the dashboard = hidden cells running an iferror formula based on the employee selected. Ideally I would also like to have hyperlinks (same as above i.e. in shapes to look like buttons) on the dashboard linking to HR folder, pdf of contract and pdf or jpeg of passport on file. However I can’t figure out a way to insert hyperlinks based on a hyperlink in another cell.
It would obviously have to change based on which employee is selected. If joe blogs is selected the hyperlinks would go to his HR folder, contract or passports, if jane doe is selected it would go to hers.
Thanks,
Hi Taylor,
You’d have to write some VBA code to dynamically update the hyperlinks for each button as there’s no formulaic way to link shapes to hyperlinks in cells.
Mynda
Hi there, I hope you can help me, is it even possible to link a shape from main tab to other tabs on the same sheet however is hidden?
Not is a sheet is hidden.
I have a shop page and i inserted ‘Contact us’ navigation page at the bottom with the customer name and email and a send hyperlink button. The problem is that i copied all these from someone and pasted, now when i click the send button, it takes me to that person’s site that I copied from. I tried to edit the link but it does the same..
If editing the link doesn’t fix it then you’ll have to enter them again. Not sure why editing it wouldn’t work though. I’m assuming right-clicked to edit the hyperlink, not just typed something new in the cell.
Hello
Any user can move the hyperlink button .haw can I protect
Thank you
You have to protect the worksheet, they will not be able to move the button.
Catalin
Thank you
The cells was protect but the hyberlink button no
(You can’t edit but you can move or deleted )
Can you please help me
Hi Amer,
Right click the shape, Size and Properties, look into Properties section, the shape should have the Locked property checked by default.
Again, the worksheet must be protected to lock the shape, not the cells. When protecting he worksheet, there are other things you can use from protect sheet dialog options, Edit Objects is usually unchecked by default, make sure it’s unchecked.
Catalin
Hi catalin
Thankyou very much. It’s working
I’m so happy thank you again
Hi Amer,
great, glad to hear you managed to make it work.
Cheers,
Catalin
I have many shapes like Districts of Province, so I try to clink anyone to show result value in one excel cell.
Anyone Help me.
Hi Muhammad,
Thanks for your question, however I’m having trouble picturing what you’re trying to do. Please post your question on our Excel forum where you can also upload a sample Excel file so we can see your question in context.
Mynda
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 :))
Great tip, thanks Slobodan 🙂
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
Onwards and upwards 🙂
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…..
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
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
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
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.