www.myonlinetraininghub.com
Open in
urlscan Pro
107.154.149.68
Public Scan
URL:
https://www.myonlinetraininghub.com/excel-hyperlink-buttons
Submission: On November 09 via manual from IN — Scanned from DE
Submission: On November 09 via manual from IN — Scanned from DE
Form analysis 4 forms found in the DOM
POST https://www.myonlinetraininghub.com/wp-comments-post.php
<form action="https://www.myonlinetraininghub.com/wp-comments-post.php" method="post" id="commentform" class="comment-form" novalidate="">
<p class="comment-notes"><span id="email-notes">Your email address will not be published.</span> <span class="required-field-message" aria-hidden="true">Required fields are marked <span class="required" aria-hidden="true">*</span></span></p>
<p class="comment-form-comment"><label for="comment">Comment <span class="required" aria-hidden="true">*</span></label> <textarea id="comment" name="comment" cols="45" rows="8" maxlength="65525" required=""></textarea></p>
<p class="comment-form-author"><label for="author">Name <span class="required" aria-hidden="true">*</span></label> <input id="author" name="author" type="text" value="" size="30" maxlength="245" required=""></p>
<p class="comment-form-email"><label for="email">Email <span class="required" aria-hidden="true">*</span></label> <input id="email" name="email" type="email" value="" size="30" maxlength="100" aria-describedby="email-notes" required=""></p>
<p class="comment-form-url"><label for="url">Website</label> <input id="url" name="url" type="url" value="" size="30" maxlength="200"></p>
<p class="comment-form-cookies-consent"><input id="wp-comment-cookies-consent" name="wp-comment-cookies-consent" type="checkbox" value="yes"> <label for="wp-comment-cookies-consent">Save my name, email, and website in this browser for the next time
I comment.</label></p>
<p class="form-submit"><input name="submit" type="submit" id="submit" class="submit" value="Post Comment"> <input type="hidden" name="comment_post_ID" value="15468" id="comment_post_ID">
<input type="hidden" name="comment_parent" id="comment_parent" value="0">
</p>
<p class="comment-form-ant-spm" style="clear: both; display: none;">
<strong>Current ye@r</strong> <span class="required">*</span>
<input type="hidden" name="ant-spm-a" id="ant-spm-a" value="2022">
<input type="text" name="ant-spm-q" id="ant-spm-q" size="30" value="2.2">
</p>
<p class="comment-form-ant-spm-2" style="display:none;">
<strong>Leave this field empty</strong>
<input type="text" name="ant-spm-e-email-url" id="ant-spm-e-email-url" size="30" value="">
</p>
</form>#
<form action="#" autocomplete="off" aria-label="Search form 1">
<input type="search" class="orig" placeholder="Search 500+ posts here..." name="phrase" value="" aria-label="Search input 1" autocomplete="off">
<input type="text" class="autocomplete" name="phrase" value="" aria-label="Search autocomplete, ignore please" aria-hidden="true" autocomplete="off" disabled="">
<input type="submit" aria-hidden="true" aria-label="Hidden button" style="width:0; height: 0; visibility: hidden;">
</form>Name: mc-embedded-subscribe-form — POST https://myonlinetraininghub.us1.list-manage.com/subscribe/post?u=47d3fb3986ef1634c2797a0f4&id=b5252539e1
<form action="https://myonlinetraininghub.us1.list-manage.com/subscribe/post?u=47d3fb3986ef1634c2797a0f4&id=b5252539e1" method="post" id="mc-embedded-subscribe-form" name="mc-embedded-subscribe-form" class="validate" target="_blank"
novalidate="novalidate">
<div id="mc_embed_signup_scroll">
<h2><i class="fa fa-envelope" aria-hidden="true"></i> Subscribe to Our Newsletter</h2>
<p> Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More. </p>
<div class="mc-field-group">
<input type="email" value="" placeholder="Enter email address here" name="EMAIL" class="required email" id="mce-EMAIL" aria-required="true">
</div>
<div id="mce-responses" class="clear">
<div class="response" id="mce-error-response" style="display:none"></div>
<div class="response" id="mce-success-response" style="display:none"></div>
</div> <!-- real people should not fill this in and expect good things - do not remove this or risk form bot signups-->
<div style="position: absolute; left: -5000px;" aria-hidden="true"><input type="text" name="b_47d3fb3986ef1634c2797a0f4_b5252539e1" tabindex="-1" value=""></div>
<div class="clear"><input type="submit" value="Subscribe" name="subscribe" id="mc-embedded-subscribe" class="button"></div>
</div>
</form>Name: options —
<form name="options" class="asp-fss-flex" autocomplete="off">
<input type="hidden" style="display:none;" name="current_page_id" value="15468">
<input type="hidden" style="display:none;" name="woo_currency" value="USD">
<input type="hidden" name="qtranslate_lang" style="display:none;" value="0">
<input type="hidden" name="filters_changed" style="display:none;" value="0">
<input type="hidden" name="filters_initial" style="display:none;" value="1">
<fieldset class="asp_filter_generic asp_filter_id_1 asp_filter_n_0 hiddend">
<legend>Generic filters</legend>
<div class="asp_option hiddend" aria-hidden="true">
<div class="asp_option_inner">
<input type="checkbox" value="exact" id="set_exact1_1" name="asp_gen[]">
<label for="set_exact1_1"> Hidden label </label>
</div>
</div>
<div class="asp_option hiddend" aria-hidden="true">
<div class="asp_option_inner">
<input type="checkbox" value="title" id="set_title1_1" data-origvalue="1" name="asp_gen[]" checked="checked">
<label for="set_title1_1"> Hidden label </label>
</div>
</div>
<div class="asp_option hiddend" aria-hidden="true">
<div class="asp_option_inner">
<input type="checkbox" value="content" id="set_content1_1" data-origvalue="1" name="asp_gen[]" checked="checked">
<label for="set_content1_1"> Hidden label </label>
</div>
</div>
<div class="asp_option hiddend" aria-hidden="true">
<div class="asp_option_inner">
<input type="checkbox" value="excerpt" id="set_excerpt1_1" data-origvalue="1" name="asp_gen[]" checked="checked">
<label for="set_excerpt1_1"> Hidden label </label>
</div>
</div>
</fieldset> <input type="checkbox" style="display: none !important;" value="post" aria-label="Hidden label" aria-hidden="true" id="1_1customset_1_1100" name="customset[]" checked="checked">
<input type="checkbox" style="display: none !important;" value="page" aria-label="Hidden label" aria-hidden="true" id="1_1customset_1_1101" name="customset[]" checked="checked">
<div style="clear:both;"></div>
</form>Text Content
* Skip to primary navigation
* Skip to main content
* Skip to primary sidebar
* Skip to footer
* My Courses
☰ Menu
My Online Training Hub
Learn Dashboards, Excel, Power BI, Power Query, Power Pivot
* Courses
* Pricing
* Free Courses
* Power BI Course
* Excel Power Query Course
* Power Pivot and DAX Course
* Excel Dashboard Course
* Excel PivotTable Course – Quick Start
* Advanced Excel Formulas Course
* Excel Expert Advanced Excel Training
* Excel Tables Course
* Excel, Word, Outlook
* Financial Modelling Course
* Excel PivotTable Course
* Excel for Customer Service Professionals
* Multi-User Pricing
* Resources
* Free Downloads
* Excel Functions Explained
* Excel Formulas
* IF Function
* Excel IF Statement Explained
* Excel IF AND OR Functions
* IF Formula Builder
* Time & Dates in Excel
* Excel Date & Time
* Calculating Time in Excel
* Excel Time Calculation Tricks
* Excel Date and Time Formatting
* Excel Keyboard Shortcuts
* Excel Custom Number Format Guide
* Pivot Tables Guide
* VLOOKUP Guide
* ALT Codes
* Excel VBA & Macros
* Excel User Forms
* VBA String Functions
* Members
* Login
* Blog
* Excel Webinars
* Excel Add-ins
* Excel Forum
* Register as Forum Member
* Cart
* Login
EXCEL HYPERLINK BUTTONS
November 20, 2013 by Mynda Treacy
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:
1. Insert a shape
2. 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.
Get Workbook
By submitting your email address you agree that we can email you our Excel
newsletter.
Please enter a valid email address.
Download the Excel Workbook. Note: This is a .xlsx file please ensure your
browser doesn't change the file extension on download.
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
1. Right-click on the shape and select Hyperlink. This will open the Insert
Hyperlink dialog box.
2. 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:
MORE HYPERLINK POSTS
CREATE HYPERLINKED LIST OF FILES IN SUBFOLDERS
Using a little VBA we can create a hyperlinked list of files from subfolders
right in our Excel worksheet.
CREATE HYPERLINKED LIST OF FILES IN A FOLDER USING VBA
Using Excel VBA, we can create a hyperlinked list of files from a folder in our
worksheet
EXCEL FACTOR 21 HYPERLINK TRIPTYCH
Tips and tricks with hyperlinks to make them dynamically update as the selection
in the sheet changes. Sample workbook available.
SAVE TIME WITH HYPERLINKS FOR WORD, EXCEL AND OUTLOOK
Use hyperlinks to make navigating in your documents easier. You can also link to
external files on your hard drive to make them easier to find and open.
MORE EXCEL POSTS
EASILY REMOVE EXCEL PASSWORD PROTECTION
How to remove Excel password protection when you’ve forgotten the password.
Works for sheets, workbooks and read only files.
IMPORT DATA FROM A PICTURE TO EXCEL
Import data from a picture to Excel. Works with pictures from a file or the
clipboard and loads it to the spreadsheet.
5 EXCEL ONLINE FEATURES BETTER THAN DESKTOP
5 Excel Online Features Better than Desktop including searchable data
validation, track changes, single line ribbon and more.
10 COMMON EXCEL MISTAKES TO AVOID
10 common Excel mistakes to avoid, including merge cells, external links,
formatting entire rows/columns and more.
COOL NEW FEATURES IN EXCEL FOR MICROSOFT 365
Cool New Features in Excel for Microsoft 365 including the navigation pane,
smooth scroling, unhide multiple sheets and more.
DYNAMIC DEPENDENT DATA VALIDATION
Dynamic Dependent Data Validation with dynamic array formulas like FILTER make
it quick and easy to set up.
EXCEL QUICK ACCESS TOOLBAR
The Excel Quick Access Toolbar is not only a handy for your mouse, but it also
enables some super easy keyboard shortcuts.
SHARE AND COLLABORATE IN EXCEL
Share and Collaborate in Excel just like Google Sheets! Show changes, custom
views, threaded comments with @ mentions and more.
EXCEL WORKBOOK PROTECTION
Excel Workbook protection can prevent your users from breaking your reports
while still allowing interaction with Slicers and refreshing.
EXCEL CUSTOM NUMBER FORMAT CONDITIONS
Custom number format conditions allow you to specify a different format based on
the size of a number. Sample workbook to download
Filed Under: Excel Tagged With: Hyperlink
LEAVE A REPLY CANCEL REPLY
Your email address will not be published. Required fields are marked *
Comment *
Name *
Email *
Website
Save my name, email, and website in this browser for the next time I comment.
Current ye@r *
Leave this field empty
COMMENTS
1. Taylor says
April 13, 2022 at 7:38 pm
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,
Reply
* Mynda Treacy says
April 13, 2022 at 8:57 pm
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
Reply
2. Margut says
February 27, 2021 at 10:48 pm
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?
Reply
* Mynda Treacy says
February 28, 2021 at 2:11 pm
Not is a sheet is hidden.
Reply
3. Letsatsi says
September 5, 2020 at 3:06 pm
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..
Reply
* Mynda Treacy says
September 5, 2020 at 3:14 pm
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.
Reply
4. Amer says
June 27, 2018 at 8:43 pm
Hello
Any user can move the hyperlink button .haw can I protect
Thank you
Reply
* Catalin Bombea says
June 27, 2018 at 11:45 pm
You have to protect the worksheet, they will not be able to move the
button.
Catalin
Reply
* Amer says
June 30, 2018 at 2:07 am
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
Reply
* Catalin Bombea says
June 30, 2018 at 2:20 pm
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
Reply
* Amer says
July 1, 2018 at 2:13 am
Hi catalin
Thankyou very much. It’s working
I’m so happy thank you again
* Catalin Bombea says
July 1, 2018 at 6:14 pm
Hi Amer,
great, glad to hear you managed to make it work.
Cheers,
Catalin
5. Muhammad Sufyan says
April 18, 2018 at 8:32 pm
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.
Reply
* Mynda Treacy says
April 18, 2018 at 9:32 pm
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
Reply
6. Slobodan Horvatic says
July 9, 2014 at 6:43 pm
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
:))
Reply
* Mynda Treacy says
July 9, 2014 at 7:18 pm
Great tip, thanks Slobodan 🙂
Reply
7. duncan says
January 21, 2014 at 9:24 am
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
Reply
* Mynda Treacy says
January 21, 2014 at 2:17 pm
Onwards and upwards 🙂
Reply
8. nitin says
December 4, 2013 at 5:20 pm
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…..
Reply
* Catalin Bombea says
December 6, 2013 at 1:32 pm
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
Reply
9. Philip Treacy says
November 22, 2013 at 11:39 am
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 :
Sub OpenTemplate()
Workbooks.Add Template:="C:\Users\PGT\AppData\Roaming\Microsoft\Templates\test.xltx"
End Sub
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
Reply
10. Rachael Hanna says
November 21, 2013 at 6:32 am
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
Reply
* Mynda Treacy says
November 21, 2013 at 9:40 am
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.
Reply
Search
No products in the cart.
Hi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog,
webinars, YouTube channel and courses we hope we can help you learn Excel, Power
Pivot and DAX, Power Query and Power BI.
Since 2014, I've been honored that Microsoft has recognised me as an MVP.
SUBSCRIBE TO OUR NEWSLETTER
Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.
GUIDES AND RESOURCES
* Excel Keyboard Shortcuts
* Excel Functions
* Excel Formulas
* Excel Custom Number Formatting
* ALT Codes
* Pivot Tables
* VLOOKUP
* VBA
* Excel Userforms
* Free Downloads
Download Free PDF
FREE WEBINARS
Watch our free webinars and learn to create Interactive Dashboard Reports in
Excel or Power BI
Click Here to Watch Now
OTHER INFO
* About My Online Training Hub
* Contact
* Disclosure Statement
* Frequently Asked Questions
* Guarantee
* Privacy Policy
* Terms & Conditions
* Testimonials
* Excel (113)
* Excel Charts (87)
* Excel Dashboard (17)
* Excel Formulas (180)
* Excel PivotTables (44)
* Excel Shortcuts (2)
* Excel VBA (87)
* General Tips (6)
* Online Training (4)
* Outlook (2)
* Power Apps (2)
* Power Automate (1)
* Power BI (10)
* Power Pivot (14)
* Power Query (56)
Help Desk
Excel Resources
Become an Affiliate
Copyright © 2022 · My Online Training Hub ·
Microsoft and the Microsoft Office logo are trademarks or registered trademarks
of Microsoft Corporation in the United States and/or other countries. Product
names, logos, brands, and other trademarks featured or referred to within this
website are the property of their respective trademark holders.
Generic filters
Hidden label
Hidden label
Hidden label
Hidden label
More results...
683 Shares
Share
Tweet
Pin
Email
Share
Share
Print
Share