Up next in 10
How to Use Structured References in Excel: Table, Column, and Item Specifiers
12K views
Nov 8, 2024
In this tutorial, we dive into structured references in Excel and explore how to work with item specifiers to make your formulas more powerful and flexible. Learn how to reference entire tables, specific data rows, headers, and totals with ease. We'll walk you through the differences between unqualified and fully qualified structured references, show you how to use specifiers like [#All], [#Data], [#Headers], and [#Totals], and explain how to reference table data both inside and outside of the table. By the end of this video, you'll be able to write cleaner, more efficient formulas that adjust automatically as your data changes.
View Video Transcript
0:00
hi everyone and welcome in today's video
0:02
we'll be exploring structured references
0:04
in Excel normally when we create
0:07
formulas we use cell references like a
0:09
one or B2 but Excel offers a more
0:12
powerful and flexible way to work with
0:14
data structured references by using
0:17
structured references and tables you can
0:19
make your formulas easier to read and
0:21
maintain plus they automatically adjust
0:24
when you add or remove data so you don't
0:26
have to worry about updating your
0:27
formulas every time your table changes
0:29
let's dive in and see how it works next
0:32
let's clear the formulas in the total
0:34
cost column that we created using cell
0:36
references so we can demonstrate how to
0:38
use structured references first select
0:41
the data range then go to the Home tab
0:44
click on format as table and choose a
0:47
table style you prefer this action will
0:49
convert your data into an Excel table by
0:52
default Excel assigns a name to the
0:54
table like table one table two and so on
0:58
however you can easily rename the table
1:01
to something more meaningful to do this
1:04
click anywhere inside the table to
1:06
activate the table design tab this tab
1:08
appears when you select a cell within
1:10
the table in the Properties Group you'll
1:12
see an option to rename the table for
1:15
this example we've renamed the table to
1:18
my table now that the data is formatted
1:20
as a table let's enter the total cost
1:23
formula using structured references
1:25
rather than traditional cell references
1:27
as shown on the screen let's break down
1:29
this formula quantity inside the
1:32
brackets refers to the quantity column
1:34
in the table when we add the at symbol
1:36
it means the value from the same Row in
1:39
the quantity column similarly unit price
1:42
inside the brackets refers to the unit
1:44
price column by adding its symbol we
1:47
specify the value from the same Row in
1:50
the unit price column in this way Excel
1:53
uses the column names directly in the
1:55
formula making it much easier to read
1:58
and understand at a glance when we press
2:00
enter Excel automatically fills the
2:03
total cost formula down the entire
2:05
column applying the correct calculation
2:07
for each row this is known as a
2:09
calculated column in a calculated column
2:12
the formula is applied to every Row in
2:14
the table and it updates automatically
2:17
if you add new rows or data to the table
2:20
notice that the formulas we've used so
2:22
far don't include the table name when
2:24
you're working within the table you
2:26
don't need to include the table name in
2:28
your formula this is called called an
2:30
unqualified structured reference however
2:33
if you're referencing data outside the
2:35
table or if you're referring to a
2:37
different table you'll need to include
2:39
the table name as part of the reference
2:42
for example the same formula would look
2:44
like this if used from a different table
2:46
or outside the original table this is
2:49
called a fully qualified structured
2:51
reference and it ensures that Excel
2:53
knows exactly which table and column
2:55
you're referring to especially when
2:57
working with multiple tables in the same
2:59
work book however there's a slight issue
3:02
with the formula we wrote we're using
3:04
its symbols which refers to the current
3:06
row when we're outside the table we can
3:09
delete the at symbol if we're writing
3:11
the formula for a different row now this
3:14
formula refers to the entire quantity
3:16
and unit price columns in my table not
3:19
just a specific row if you intend to
3:21
reference a specific row you would need
3:23
to use a function like index to pull a
3:26
value from that specific row
3:39
next let's explore item specifiers in
3:42
structured references the formula shown
3:44
on the screen tells Excel to reference
3:47
the entire table this includes the
3:49
headers the data and any totals the
3:52
hashtag all specifier treats the entire
3:55
table as a single unit allowing you to
3:57
include everything in the table not just
4:00
the data now let's enable the total row
4:03
option from the table design tab this
4:05
adds a row at the bottom of the table
4:06
that automatically calculates totals for
4:09
each column as you can see when we use
4:11
the hashtag all it includes the newly
4:14
added total row along with the headers
4:16
and the
4:19
data let's write the equals my table
4:22
without any specifier it automatically
4:24
refers to the entire table however Excel
4:28
treats this as a range reference to the
4:30
data area and depending on how you're
4:32
using it Excel might focus on just the
4:34
data without explicitly highlighting the
4:36
headers and total Row the data specifier
4:40
however specifically refers to the data
4:42
rows within the table excluding the
4:44
headers and the total row so if you want
4:47
to reference only the data itself no
4:49
headers no totals the data specifier is
4:52
the more precise Choice the reference
4:54
equals my table might still implicitly
4:57
include headers depending on the context
4:59
text but doesn't specifically highlight
5:01
them as part of the
5:04
reference if you want to reference just
5:06
the headers you can use the header
5:08
specifier for example the formula shown
5:11
on the screen returns only the column
5:13
headers from my table such as item
5:16
quantity unit price and total cost
5:19
similarly if you want to reference only
5:21
the total row you can use the total
5:24
specifier the formula shown on the
5:26
screen will return the values from the
5:27
total row but only if the total row is
5:30
enabled in the table design
5:33
tab that wraps up our look at Excel
5:36
tables and structure references by using
5:39
table names and structured references
5:41
you can create Dynamic and readable
5:43
formulas that automatically adjust as
5:46
your data changes thank you for watching
5:48
please share this video and don't forget
5:50
to like subscribe and hit the
5:52
notification Bell for more Excel tips
5:54
and tutorials your support helps us
5:56
create more valuable content See you in
5:59
the next video video
#Calculators & Reference Tools
#Data Sheets & Electronics Reference
#Education
#Electronics & Electrical
#General Reference
#Reference
#Technical Reference