0:00
hello and welcome in this video we'll
0:02
explore how to create and use relative
0:04
named ranges in Excel if you're
0:06
unfamiliar with Excel cell references I
0:08
highly recommend watching our video on
0:10
Cell references first you'll find the
0:12
link in the description below now let's
0:14
get started unlike traditional named
0:16
ranges which always refer to fixed cells
0:19
relative named ranges adjust dynamically
0:21
depending on where they're used this
0:23
makes them incredibly useful for
0:25
creating flexible and reusable formulas
0:28
as demonstrated here the formula equals
0:30
price asteris quantity uses relative
0:33
named references if you use this formula
0:35
in cell D2 it will refer to B2 and C2
0:39
calculating it as equals B2 asteros C2
0:42
when you use the same formula in j14 it
0:44
will automatically adjust to refer to
0:46
h14 and i14 working as equals h14
0:50
asterisk i14 in simple terms relative
0:53
named references adjust based on their
0:55
current position in the worksheet making
0:57
them Dynamic and context sensitive let's
0:59
let's first perform the calculations
1:01
using the traditional a relative cell
1:03
referencia method start by writing the
1:05
formula in the first cell of the column
1:07
then copy it down the entire list by
1:09
either double clicking the fill handle
1:11
or dragging it as you can see Excel
1:14
gives us the expected results the
1:16
references in each formula automatically
1:18
adjust based on their relative cell
1:20
positions now let's create named ranges
1:23
first select CB2 go to the name box type
1:26
price and press enter similarly select
1:29
cellc two type quantity in the name box
1:32
and press enter it's important to note
1:34
that the named ranges created using the
1:36
name box are absolute reference cesa by
1:39
default this means they always point to
1:41
the specific cells they were created for
1:43
to test this write the formula equals
1:46
price Aster's quantity in cell D2 you'll
1:48
see it calculates correctly because it's
1:50
pointing to B2 and C2 now copy this
1:54
formula down the column by double
1:56
clicking the fill handle notice that all
1:58
the formulas still refer to be 2 and C2
2:01
this happens because the default
2:02
behavior of named ranges is to use
2:04
absolute references now let's convert
2:07
these named ranges into a relative named
2:10
ranga here's how select cell D2 first
2:14
this step is crucial because the active
2:15
cell determines how the relative
2:17
reference will behave go to the formulas
2:19
Tab and in the defined names group click
2:22
name manager in the name manager select
2:24
the name price and click edit in the
2:26
edit name dialogue box remove the dollar
2:28
signs from the formula in the refers to
2:30
field to make it relative and click okay
2:33
repeat the same steps for the quantity
2:35
named range remove the dollar signs from
2:37
the formula and click okay close the
2:40
name manager window now let's observe
2:42
what happens all the copied or drag
2:44
formulas in column D automatically
2:47
update this time they calculate the
2:49
results dynamically just like
2:51
traditional relative cell references the
2:53
difference is that we're now referring
2:54
to named ranges instead of directly
2:57
referencing the cells so what makes a
3:00
relative named ran unique it's defined
3:02
using a relative reference rather than
3:04
the default absolute reference this
3:07
allows the range to adjust dynamically
3:09
depending on where the formula is used
3:11
now try using these Dynamic relative
3:14
named ranges in different cells across
3:16
the worksheet you'll notice that the
3:18
formulas always return results based on
3:20
their position relative to where the
3:22
formula is entered this Dynamic Behavior
3:24
makes them incredibly flexible and
3:26
adaptable for various calculations and
3:28
that's how you can create and use
3:30
relative named ranges in Excel they're a
3:32
powerful tool for building Dynamic and
3:34
efficient formulas if you found this
3:36
tutorial helpful please like the video
3:38
subscribe to the channel and hit the
3:40
notification Bell for more Excel tips
3:42
and tricks thank you for watching and
3:44
I'll see you in the next
