Up next in 10
Named Ranges in Excel: Understand Scope of Named Ranges - Create Multiple Named Ranges at once
14K views
Feb 7, 2025
Learn how to define (or assign) names to individual cells or ranges of cells, simplifying their reference in formulas. We'll guide you through creating and using named ranges in formulas, as well as editing and managing existing names. Additionally, we'll explore the scope of named ranges in Excel, helping you understand how to effectively apply them in different contexts. 00:00 Introduction 00:14 Define Named Ranges (Single Cell) 01:24 Define Named Ranges (Multiple Cells) 02:17 Navigate to Named Ranges 02:49 Scope of Defined Names (Workbook Scope) 03:25 Scope of Defined Names (Worksheet Scope) 05:17 Update Existing Formulas with Named Ranges 06:47 Insert Names with "Use in Formula" Feature 07:27 Insert Names with Paste Name Option 07:46 Create Multiple Names Ranges At Once 09:16 Named Manager (Create, Modify, and Delete Names)
View Video Transcript
0:00
welcome in this video we'll cover how to
0:02
create modify and manage named ranges
0:05
update formulas with names and
0:08
understand the scope of named ranges
0:09
you'll also learn how to create multiple
0:11
named ranges at once the name box is a
0:14
quick and easy way to define named
0:16
ranges in Excel making your formulas
0:18
more readable here's a simple example
0:21
using a tax rate value in a formula
0:23
suppose we have a formula that
0:25
calculates the total price after
0:27
applying tax in this formula B2 is the
0:30
original price and E2 is the tax rate to
0:33
apply the formula to the entire list
0:35
either drag the fill handle down or
0:37
double click it this will automatically
0:39
calculate the total price incorporating
0:41
the tax rate defined in sell E2 to make
0:44
the formula easier to understand and
0:46
manage we'll assign the name tax to cell
0:48
E2 to do this click on Cell E2 go to the
0:52
name box type tax and press enter cell
0:55
E2 is now named tax making future
0:58
references to it more intuitive and
1:00
easier to manage especially if you're
1:02
working across multiple worksheets next
1:05
update your formula by replacing the
1:06
cell reference E2 with a named range tax
1:10
as you start typing a list of named
1:12
ranges will appear you can easily
1:14
identify named ranges by the icon to the
1:16
left of the name finally double click
1:19
the fill handle again to apply the
1:21
updated formula across the entire list
1:23
name ranges are not limited to single
1:25
cells you can define a named range that
1:28
includes multiple contiguous or
1:30
non-contiguous cells or even an entire
1:32
row or column continuing with the
1:35
previous example let's create named
1:37
ranges for the price and total price
1:39
columns select the range B2 to B21 type
1:42
price in the name box and press enter
1:45
select the range C2 to c21 type total
1:48
underscore price in the name box and
1:50
press enter now you can use these named
1:53
ranges in formulas for better
1:55
readability and flexibility for example
1:58
instead of using the range B2 B21 to sum
2:01
the price column you can use the name
2:03
price similarly use the total underscore
2:07
price to sum the total price
2:13
column we can easily navigate to named
2:15
ranges by selecting them from the name
2:17
box drop down click the down arrow in
2:19
the name box and Excel will display a
2:21
list of the named ranges you've created
2:24
when we select a name from the dropdown
2:26
Excel will automatically jump to that
2:28
range for example selecting tax will
2:30
take you to sell E2 selecting price will
2:33
highlight the range B2 to B21 selecting
2:36
total underscore price will select the
2:38
range C2 to c21 using the name box you
2:42
can quickly see a list of all named
2:43
ranges and easily identify and jump to
2:46
any range by selecting it a defined name
2:48
in Excel can have either workbook scope
2:51
or worksheet scope by default names
2:53
created through the name box of workbook
2:55
scope meaning they can be used across
2:57
the entire workbook for example a name
3:00
created on sheet one can also be used in
3:02
formulas on sheet two to test this let's
3:05
add another sheet input some products
3:07
and prices and then use the tax name to
3:09
calculate the total price after applying
3:11
the tax rate set in sheet one cell2 name
3:14
tax as shown in the video the formula
3:17
correctly references the tax name which
3:19
points to sell E2 on sheet one and
3:21
calculates the price after applying the
3:23
tax rate now let's talk about worksheet
3:25
Scope when you define a named range with
3:28
worksheet scope that name Will only be
3:30
available on a specific worksheet this
3:32
is useful when you want to restrict the
3:34
use of a name to just one sheet which
3:36
can help prevent confusion or errors in
3:38
large workbooks for example let's say we
3:41
want to define a named range called
3:43
discount for the value in cell G2 on
3:45
sheet 2 and we want this name to be
3:47
available only on sheet two to do this
3:50
first select cell G2 in sheet 2 then go
3:53
to the formulas Tab and click Define
3:55
name in the new name dialogue box enter
3:58
discount as the name for this range now
4:01
in the scope dropdown select sheet two
4:03
to ensure this name is only available on
4:06
that sheet once you're ready click okay
4:09
now that we've defined the name let's
4:10
use it in the formula to calculate the
4:12
final prod cut price after applying the
4:14
discount percentage which is stored in
4:16
cell G2 named discount here the formula
4:19
shown on the screen will subtract the
4:21
discount percentage from the original
4:23
price giving us the price after discount
4:25
as you can see the discount name is
4:27
working perfectly in the formula on
4:29
sheet sheet 2 exactly as
4:34
expected now let's see what happens if
4:37
we try to use the discount name on sheet
4:39
one when I try to reference discount
4:41
here Excel doesn't recognize it that's
4:44
because the discount name is scoped to
4:45
sheet two so it's not available on sheet
4:48
one or any other sheet if you still need
4:50
to use a named range that's not scoped
4:52
to the workbook on a different sheet
4:53
you'll need to reference it explicitly
4:55
by typing the sheet name followed by an
4:57
exclamation mark and then the name of
4:59
the range is shown on the screen as you
5:01
can see once I reference it explicitly
5:03
the formula works correctly because
5:05
we've specifically referenced sheet two
5:07
where the named range is
5:15
defined did you notice the Define name
5:18
button is actually a drop- down when you
5:20
click the down arrow it reveals an
5:22
option called apply names selecting
5:24
apply names opens a dialogue box that
5:26
lists all the named ranges available for
5:28
the current sheet the apply names
5:30
feature is especially useful when you've
5:32
been using cell references in your
5:34
formulas and later assign names to those
5:36
ranges Excel doesn't automatically
5:39
update the formulas to use the new names
5:41
instead of manually replacing cell
5:43
references with named ranges you can use
5:46
apply names to do this automatically
5:48
let's demonstrate with an example here
5:51
in the total price column we have the
5:53
formula that references a cell named tax
5:55
in E2 if we replace the named range tax
5:58
with the absolute reference is shown on
5:59
screen and drag the formula down it now
6:02
directly refers to E2 not the named
6:04
range now the formula no longer uses the
6:07
named range tax it just directly
6:09
references the cell E2 which holds a tax
6:12
rate here we see how the applying names
6:14
feature Works go to the formulas tab
6:16
click the drop- down arrow next to
6:18
Define name and select apply names in
6:20
the dialogue box that appears select tax
6:23
from the list and click okay Excel will
6:25
automatically replace the cell reference
6:27
with the named range tax this way
6:30
instead of manually updating each
6:32
formula Excel does the work for you
6:35
ensuring consistency across your
6:36
formulas with the correct named
6:45
ranges you might have also noticed the
6:47
use in Formula dropdown in the Define
6:50
names group on the formulas tab clicking
6:52
this drop down shows a list of all named
6:54
ranges in your workbook this is
6:56
especially helpful when you're unsure of
6:58
the exact name of assigned to a range
7:00
and you want to insert it directly into
7:02
a formula to demonstrate let's calculate
7:05
the price after applying tax start by
7:07
typing equals 300 asterisk 1+ now click
7:11
the use and formula drop down and choose
7:13
tax from the list this will insert the
7:15
tax named range directly into the
7:18
formula this is particularly useful if
7:20
you're not sure which name has been
7:22
assigned and you want to quickly insert
7:24
it right into your formula another
7:25
alternative is to use the paste names
7:27
option or simply press the keyo keboard
7:29
shortcut F3 this opens the paste name
7:32
window where you can select the name you
7:34
want to insert into your formula select
7:36
the name you need click okay and Excel
7:38
will paste the name into the formula for
7:42
you another way to create named ranges
7:45
is by using the create from selection
7:47
option this is a great method for
7:49
creating multiple named ranges at once
7:51
especially when you have data organized
7:53
in a clear way in this example we have a
7:55
table with data that includes categories
7:57
like product price and quantity
8:00
let's say we want to create named ranges
8:01
for each column of data to do this
8:04
select the range of data you want to
8:05
create names for then go to the formulas
8:08
Tab and click on create from selection
8:11
the create from selection dialogue box
8:12
will appear here you can choose where
8:14
the name should be based whether it's
8:16
from the row column or both since our
8:19
data has headers in the top row we'll
8:21
select the top row option Excel will
8:23
automatically create named ranges based
8:26
on the headers of each column once you
8:27
click okay Excel will create named
8:30
ranges for product price and quantity
8:33
making it easier to refer to these
8:34
ranges in formulas this method is a
8:36
quick and efficient way to create
8:38
multiple named ranges at once especially
8:41
when working with structured data now
8:43
that we've defined the named ranges
8:45
let's use them in a formula for example
8:47
we can calculate the average price and
8:49
quantity to calculate the average price
8:52
use the name price instead of range this
8:55
will return the average of all the
8:57
values in the B2 to be 21 name price
9:00
range similarly calculate the average
9:02
quantity this will return the average of
9:04
all the values in the quantity range
9:07
Excel will now automatically calculate
9:09
the averages for both columns using the
9:11
named ranges we've just created lastly
9:14
let's learn how to modify and delete
9:16
named ranges the name manager in Excel
9:18
makes it easy to find review edit and
9:21
delete names to open the name manager go
9:24
to the formulas Tab and click name
9:26
manager or press contrl F3 the name
9:29
manager will display all the names in
9:31
your workbook it shows the current value
9:33
of each name the range it refers to and
9:36
its scope whether it's available to the
9:37
entire workbook or just a specific sheet
9:40
to edit a name open the name manager
9:43
select the name you want to edit and
9:44
click the edit button at the top of the
9:46
window in the edit name dialogue box you
9:48
can modify the name add a comment and
9:50
change the refers to range however note
9:53
that the scope of a name cannot be
9:55
changed once it's set click okay to save
9:58
your changes to delete a named range
10:01
open the name manager if it's not
10:03
already open select the name you want to
10:04
delete and click the delete button a
10:06
confirmation message will appear click
10:08
okay to confirm the deletion you can
10:10
also create a new name from within the
10:12
name manager window click the new button
10:15
which will open the new name dialogue
10:16
box here you can enter the name select
10:19
the scope and Define the range in the
10:21
refers to box that's it in this tutorial
10:24
we've explored several methods to create
10:26
and manage named ranges we've also
10:28
covered how to modify and delete Define
10:30
names in the upcoming video we'll dive
10:33
into Dynamic named ranges thank you for
10:36
watching if you found this tutorial
10:37
helpful please like subscribe and share
10:40
your support means a lot to us
#Calculators & Reference Tools
#Data Sheets & Electronics Reference