Up next in 10
Create Named Formulas in Excel That Don't Need Cell or Range References
5K views
Feb 7, 2025
Say goodbye to manually entering cell or range references! In this tutorial, you'll learn how to create named formulas in Excel that dynamically calculate results without relying on fixed cell or range references. Discover how these formulas adapt based on the active cell to calculate values either above or to the left—making your calculations more flexible and efficient. Perfect for automating repetitive tasks and simplifying your Excel workflow. Watch now to take your Excel skills to the next level!
View Video Transcript
0:00
hi in today's video we're exploring
0:02
named formulas in Excel a powerful
0:05
feature that makes your calculations
0:06
clearer more efficient and easier to
0:08
manage name formulas allow you to create
0:11
reusable calculations that you can
0:12
reference anywhere in your workbook just
0:14
like Dynamic named ranges or name
0:16
constants but here's what sets them
0:18
apart instead of pointing to a specific
0:21
cell or range named formulas perform
0:24
calculations and return results that can
0:26
be used directly in other formulas in
0:28
this video we'll show you how to create
0:30
two Dynamic named formulas that
0:32
automatically sum values without the
0:34
need to manually intercell references
0:36
like you do with the sum function and
0:38
the best part these named formulas adapt
0:40
to the active cell intelligently
0:42
identifying the range to sum without
0:44
requiring you to specify anything let's
0:46
get started so how do you create these
0:49
magical name formulas it's simpler than
0:51
you might think instead of entering a
0:53
formula directly into a cell you'll
0:55
Define it in the name manager first open
0:58
the formulas Tab and click on name
1:00
manager then click new to create a new
1:03
name give your formula a descriptive
1:05
name and in the refers to field that's
1:07
where the magic happens you'll enter
1:09
your formula here but instead of seeing
1:11
the result in a Cell Excel will store it
1:13
as a named formula let's walk through a
1:16
practical example imagine you want to
1:17
dynamically sum the range above the
1:19
current cell without hardcoding the
1:21
references you can achieve this using
1:24
the formula displayed on the screen this
1:26
formula is fully Dynamic it
1:28
automatically adjusts the range based on
1:30
the position of the active cell for
1:32
instance if the name formula is applied
1:34
in cell B12 it will sum the range B1 b11
1:38
similarly if it's in cell C13 it will
1:40
sum the range C1 C12 all without any
1:44
manual adjustments this name formula is
1:46
versatile and can be used anywhere in
1:48
the workbook in any column or row to
1:51
dynamically sum the values above the
1:53
active cell let's break down this
1:55
formula step by step to understand how
1:56
it works first the indirect function is
1:59
used to dynamically generate a reference
2:02
here R1 C and column creates a reference
2:05
to the first row of the current column
2:07
the R1 C part indicates Row one and
2:10
column retrieves the column number of
2:11
the active cell by using false as the
2:14
second argument the reference is
2:15
interpreted in our 1 C1 style next the
2:18
offset function builds a range starting
2:21
from the reference created by indirect
2:23
it starts at the first row of the
2:24
current column and extends downwards the
2:27
height of this range is determined by
2:29
row minus one which calculates the
2:31
number of rows above the current cell
2:33
the one at the end specifies that the
2:35
range is one column wide finally the sum
2:38
function adds up all the values in this
2:40
dynamically created
2:42
range now let's create another named
2:45
formula to dynamically sum the values to
2:47
the left of the active cell without
2:50
manually entering the cell range to do
2:52
this go to the formulas Tab and click to
2:55
find name give the formula a name such
2:57
as suore left and enter the formula
2:59
shown on the screen we'll dive deeper
3:01
into these formulas later in the video
3:03
but for now try using the sumore left
3:05
formula it dynamically sums the range to
3:08
the left of the active cell without
3:10
needing any cell references for example
3:13
if you use this name formula in cell G2
3:16
it will sum the range A2 F2 similarly if
3:19
it's applied in cell G3 it automatically
3:22
adjusts to sum the range A3 F3 there's
3:25
no need to manually specify the range
3:28
this named formula figures it out for
3:29
you
3:30
this formula is similar to the one we
3:31
discussed earlier but it's been modified
3:33
to sum values to the left of the current
3:35
cell instead of the ones above it
3:38
previously we used the indirect function
3:40
to reference the first row of the
3:42
current column in this version it points
3:44
to the First Column of the current row
3:46
this change shifts the focus from
3:48
vertical ranges to horizontal ones the
3:50
offset function then creates a range
3:52
starting at the First Column of the
3:54
current Row the height of the range is
3:56
fixed at one because we're working with
3:58
a single Row the width of the range
4:00
however is defined by column minus one
4:03
which calculates how many columns are to
4:05
the left of the active cell finally the
4:08
sum function adds up the values in this
4:10
horizontal range to the left of the
4:11
current cell and that's how you can
4:13
create named formulas in Excel if you
4:15
found this tutorial helpful don't forget
4:17
to like the video subscribe to the
4:19
channel and hit the notification Bell
4:21
for more Excel tips and tricks thank you
4:23
for watching and I'll see you in the
4:25
next video
#Calculators & Reference Tools