Up next in 10
Create Dynamic Named Ranges That Automatically Adjust to Data in Excel
573 views
Feb 7, 2025
Building dynamic ranges using OFFSET, INDEX, and INDIRECT. Learn how to create Dynamic Named Ranges in Excel that automatically expand or contract as your data changes. In this tutorial, we'll start with the basics using functions like OFFSET, INDEX, and INDIRECT. Mastering Named Ranges in Excel: A Beginner's to Expert's Guide: https://www.youtube.com/watch?v=2SFgE7gyAjY
View Video Transcript
0:00
hi and welcome in this tutorial we'll
0:03
explore how to create Dynamic named
0:05
ranges in Excel ranges that
0:07
automatically expand or contract as your
0:09
data changes as we discussed in our
0:11
previous video a named range is a custom
0:14
name you can assign to a cell or group
0:16
of cells to make your formulas clearer
0:18
and easier to understand for example
0:20
instead of referencing E2 in a formula
0:23
you can give it a meaningful name like
0:25
tax rate to do this simply select cell
0:28
E2 type tax into the name box located at
0:31
the top left of the grid and press enter
0:34
once the name is assigned you can use it
0:36
in any formula as shown on the screen if
0:38
you're new to name ranges I recommend
0:40
watching our previous video on this
0:42
topic you'll find a link in the
0:43
description below basic named ranges are
0:46
static they don't update when you add or
0:48
remove data imagine you're working with
0:50
a growing list of sales figures static
0:53
ranges like B2 D12 and C2 C12 won't
0:57
include new data automatically that's
0:59
where Dynamic named ranges come in they
1:02
expand or Shrink automatically based on
1:04
your data let's create a dynamic range
1:07
using the offset function here's how to
1:09
do it go to the formulas Tab and click
1:11
Define name in the Define names group
1:14
this will open the new name dialogue box
1:16
in the name field enter a name for the
1:18
dynamic range we'll use price as an
1:21
example from the scope drop down select
1:23
workbook if you want the name to be
1:25
available throughout the workbook in the
1:27
refers to field type the offset formula
1:29
to displayed on the screen then click
1:31
okay to save your changes and close the
1:34
dialogue box to test it delete the
1:36
previous total price formula and use the
1:38
formula equals sum price as shown on
1:41
screen now add more data to column B
1:44
notice how it automatically includes the
1:46
new
1:48
values let's break down the formula we
1:51
use to create the dynamic named range
1:53
price the offset function generates a
1:56
reference to a range that is a specified
1:58
number of rows and Colum columns away
2:00
from a starting cell additionally it
2:02
allows you to define the height and
2:04
width of the resulting range dynamically
2:06
in our formula the function starts at
2:08
cell B2 as the reference point since we
2:11
don't need to move to another row or
2:13
column the row and column offset
2:15
arguments are both set to zero for the
2:17
height we use the count of function this
2:20
counts the number of non-empty cells in
2:21
the column ensuring the range expands or
2:24
shrinks automatically based on the data
2:26
in column B the width is set to one as
2:29
we're working with a single column this
2:31
combination ensures that as you add or
2:33
remove data in column B the range price
2:35
dynamically adjusts to include only the
2:37
populated cells let's create another
2:40
dynamic range for the second range price
2:42
plus tax start by going to the formulas
2:44
Tab and clicking Define name in the new
2:47
name dialogue box enter price uncore tax
2:50
in the name field this time we'll use
2:52
the same offset function formula but
2:54
modify the reference in the first
2:55
argument and the height argument to
2:57
represent column c as shown on the
2:59
screen click okay to save your changes
3:02
and close the dialogue box to test the
3:04
dynamic range delete the existing total
3:06
price plus tax formula and replace it
3:09
with equals sum price uncore tax as
3:11
shown on screen now when you add new
3:14
entries to column C the sum formula will
3:16
automatically include the new values and
3:19
there you have it we've demonstrated how
3:21
to create Dynamic named ranges using the
3:24
offset function combined with counter
3:26
but Dynamic ranges aren't limited to
3:28
just offset the formula shown on the
3:30
screen also utilize index and indirect
3:33
offering alternative ways to achieve the
3:35
same results whether you choose offset
3:38
index or indirect each method allows you
3:40
to create Dynamic ranges that adjust
3:42
automatically as your data grows or
3:44
changes now it's your turn to put these
3:47
techniques into practice and make your
3:49
Excel work smarter and more efficiently
3:51
thank you for watching please share this
3:53
video and don't forget to like subscribe
3:56
and hit the notification Bell for more
3:57
Excel tips and tutorials you're support
4:00
helps us create more valuable content
4:02
See you in the next video