Up next in 10
Create Dynamic Hyperlinks in Excel Using HYPERLINK Function to Link Cells, Ranges, Objects, Emails
625 views
Dec 4, 2024
The HYPERLINK function in Excel! Learn how to create dynamic links to cells, named ranges, other workbooks, websites, and email addresses. This step-by-step guide simplifies complex hyperlink syntax and equips you with tips and tricks to enhance your productivity. What you'll learn: 00:00 Introduction 00:16 Link to a cell in the current worksheet 01:27 Link to a cell in other worksheet 02:06 Link to a named object/range 03:46 Create a Hyperlink to another (external) workbook. 04:32 Create a Hyperlink to a cell in Sheet2 of external workbook 05:49 Create Hyperlink to a Named Range in an External Workbook 06:30 Linking Websites (URLs). 06:46 Create Email Links
View Video Transcript
0:00
hi everyone and welcome in this video
0:02
we'll explore how to use the hyperlink
0:04
function in Excel to create links to
0:06
cells sheets workbooks websites and even
0:09
email addresses we'll also cover
0:11
simplified syntax options that make
0:13
creating hyperlinks easier and more
0:16
intuitive let's start by learning how to
0:18
use the hyperlink function to jump to
0:20
different locations within the current
0:22
worksheet we'll begin with the
0:23
traditional syntax which includes the
0:25
workbook name enclosed in Brackets
0:27
followed by the worksheet name and the
0:29
cell address when you press enter Excel
0:32
creates a hyperlink in the cell clicking
0:34
on the link takes you to cell A2 in the
0:36
same worksheet and selects it if you
0:39
update the formula to reference another
0:40
cell such as changing A2 to B2 or B12
0:44
the hyperlink dynamically adjusts now
0:47
clicking the link will jump to the newly
0:49
specified cell the hyperlink function
0:51
takes two arguments the first argument
0:53
is the link location which is required
0:56
the second argument is the friendly name
0:58
which is optional and represents the
1:00
text displayed in the cell if you don't
1:02
provide the second argument Excel will
1:04
display the link location instead now
1:06
let's look at an alternative simplified
1:08
syntax that uses the hash symbol this
1:11
method omits the workbook name and
1:13
directly references the cell address
1:14
within the worksheet this approach is
1:16
more concise and easier to use our both
1:20
examples work the same way the version
1:22
with the hash symbol is simpler and
1:24
easier to
1:27
understand next we'll create hyperlinks
1:29
that jump to different worksheets in the
1:31
current workbook using the traditional
1:33
syntax we specify the workbook name the
1:35
target worksheet and the cell address
1:38
clicking this link takes you to cell D1
1:40
on sheet 2 updating the formula to cell
1:42
A1 will redirect the link to cell A1 on
1:45
sheet
1:50
two for the simplified syntax we use the
1:53
hash symbol hashtag followed by the
1:55
worksheet name an exclamation mark and
1:58
the cell address this approach roach
2:00
eliminates the need to include the
2:01
workbook name making it simpler to
2:06
implement now let's explore how to
2:08
create hyperlinks that jump to named
2:10
ranges or objects within the workbook to
2:13
view all named ranges go to the formulas
2:15
Tab and click name manager in our
2:18
workbook we have three named objects
2:20
categories products and sale names are
2:22
unique within a workbook when linking to
2:25
named ranges or objects there's no need
2:27
to specify the worksheet name for the
2:29
tradition syntax write the workbook name
2:32
enclosed in Brackets followed by the
2:34
name of the range or table you want to
2:36
jump to clicking the link will take you
2:38
directly to the range or table named
2:41
sale using the simplified syntax with a
2:44
hash symbol the formula becomes even
2:46
simpler you don't need to enter the
2:48
workbook or worksheet name just use the
2:50
name of the range or table followed by
2:52
the hash symbol in the hyperlink
2:54
function as you can see regardless of
2:56
whether the named ranges or objects are
2:58
located on different worksheets you only
3:00
need to reference their names when
3:01
creating hyperlinks if you modify the
3:04
formula by changing the name of the
3:05
range or table it will take you to that
3:07
range or table no matter which worksheet
3:10
it resides
3:14
in now let's add a friendly name in the
3:17
second argument of the hyperlink formula
3:19
to make your links more intuitive the
3:21
friendly name is the text displayed in
3:23
the cell helping users understand what
3:25
the link does for example if you write
3:28
total sale in the second argument of the
3:30
hyperlink formula linked to the sale
3:32
table this formula will take you
3:34
directly to the range or table named
3:36
sale and the link will display total
3:38
sale in the
3:45
cell in this section we'll explore how
3:48
to use the hyperlink function to link to
3:50
external workbooks to link to another
3:52
workbook simply enter the full path
3:54
along with the workbook name as the
3:56
first argument in the hyperlink function
3:58
this formula creates the hyperlink when
4:00
you click the link Excel opens the
4:03
specified workbook to make your
4:05
hyperlinks more userfriendly you can use
4:07
the second argument of the hyperlink
4:08
function to define a friendly name with
4:11
this approach instead of displaying the
4:13
file path in the cell the friendly name
4:15
appears making your links cleaner and
4:17
more intuitive for users if the current
4:19
workbook and the target workbook are
4:21
stored in the same folder you can skip
4:23
the full path and just use the workbook
4:25
name this makes the formula more concise
4:27
while still functioning correctly
4:33
to create a hyperlink that jumps to a
4:35
specific cell on a specific worksheet in
4:37
an external workbook you need to include
4:39
the full path and workbook name en Clos
4:41
in Brackets then add the worksheet name
4:44
followed by an exclamation mark and the
4:46
cell address where you want to jump as
4:48
mentioned earlier if the external
4:50
workbook is in the same folder as the
4:51
current workbook you can omit the full
4:54
path and just use the workbook name
4:55
alternatively you can use the hash
4:57
symbol instead of brackets write the
5:00
hash symbol after the workbook name then
5:02
the worksheet name followed by an
5:04
exclamation mark and the cell
5:10
address if the worksheet name contains
5:12
spaces you need to enclose it in single
5:15
quotes for example let's say the
5:17
worksheet name is employee list if you
5:19
don't use quotes Excel will throw an
5:21
error saying reference isn't valid to
5:24
fix it enclose the sheet name in single
5:26
quotes now when you click the hyperlink
5:28
it opens the external workbook and jumps
5:31
to the specified cell even if the sheet
5:33
name contains spaces as mentioned
5:35
earlier if the external workbook is in
5:37
the same folder as the current workbook
5:39
you can omit the full path and just use
5:41
the workbook name otherwise make sure to
5:44
include the full path finally you can
5:46
use the second argument of the hyperlink
5:48
function to give your link a friendly
5:50
name next let's create a hyperlink to a
5:52
named range or object in an external
5:55
workbook named ranges are unique and
5:57
Global within a workbook so you don't
5:59
need to specify the worksheet where the
6:01
named range is located simply enter the
6:03
workbook name in square brackets
6:05
followed by an exclamation mark and the
6:07
name of the range or object
6:09
alternatively you can use the hash
6:11
symbol to specify the path and workbook
6:13
name followed by the name of the range
6:16
or object both approaches work perfectly
6:19
and as always you can use the second
6:20
argument to add a friendly name for the
6:22
link making it more descriptive for
6:24
users
6:29
let's move on to creating web links and
6:31
email links using the hyperlink function
6:34
creating a web link is straightforward
6:36
enter the URL in the first argument of
6:38
the hyperlink function and provide a
6:40
userfriendly name in the second argument
6:42
this creates a clickable link when you
6:44
click it the link opens the specified
6:47
URL in your default web browser now
6:50
let's create an email link to do this
6:52
use the mailto prefix followed by the
6:55
email address of the recipient for
6:57
instance if you want the link to open an
6:59
email address to info@ brain.com you
7:02
would write as shown on the screen when
7:03
you click this link it opens your
7:05
default email client with the
7:06
recipient's email address pre-filled all
7:09
you need to do is write the subject and
7:11
body of the email before sending it with
7:13
these techniques you can quickly add web
7:15
and email links to your Excel
7:17
spreadsheets making it easier for users
7:19
to navigate or connect directly with
7:21
contacts thank you for watching please
7:23
share this video and don't forget to
7:25
like subscribe and hit the notification
7:27
Bell for more Excel tips and tutorials
7:29
your support helps us create more
7:31
valuable content See you in the next
7:33
video