Up next in 10
Excel Cell References: Relative, Absolute, Mixed, and 3D References Explained
2K views
Feb 7, 2025
Unlock the full potential of Excel with this beginner guide to cell references! In this tutorial, we'll explore: - Relative References: Adjust formulas automatically as you copy them. - Absolute References: Keep your fixed values consistent. - Mixed References: Understand flexible referencing for tables and calculations. - 3D References: Consolidate data across multiple worksheets. Learn when and where to use each type of reference, along with practical examples to help you work smarter and faster in Excel.
View Video Transcript
0:00
hi everyone welcome in this tutorial
0:02
we'll be diving into references in Excel
0:04
let's start with relative references
0:06
imagine you have a formula in cell D2
0:09
that multiplies B2 and C2 if you copy
0:12
this formula down to d8 excel will
0:14
automatically adjust the formula to
0:16
multiply B8 and C8 essentially Excel
0:20
shifts the references based on where the
0:21
formula is pasted relative references
0:24
are the default in Excel what makes them
0:26
unique is that when you copy or move a
0:28
formula cell adjust the cell references
0:31
automatically according to the direction
0:33
you move the formula relative references
0:35
are ideal when you need to apply the
0:37
same calculation across multiple rows or
0:39
columns they save you time and effort by
0:42
making copying and pasting formulas
0:44
quick and easy next let's talk about
0:46
absolute references unlike relative
0:49
references absolute references are fixed
0:52
meaning they don't change when you copy
0:54
or move the formula absolute references
0:56
are especially useful when you're
0:58
working with fixed values like tax rates
1:00
or interest rates that don't change
1:02
across your calculations let's revisit
1:04
our previous example where we calculated
1:06
the total price by multiplying the price
1:09
of the product by the quantity now let's
1:11
say we want to apply a 10% tax rate to
1:13
the total price in this case we entered
1:16
the formula to calculate the total price
1:18
after applying the 10% tax rate
1:20
referencing the tax rate in cell F2
1:23
however we didn't use an absolute
1:24
reference here when we drag the formula
1:27
down the reference changes to F3 and F 4
1:30
which are blank cells to fix this and
1:32
make sure the tax rate is always
1:34
referenced from cell F2 we need to use
1:36
an absolute reference this way Excel
1:39
will keep the reference fixed no matter
1:41
where we copy or drag the formula let's
1:44
correct the formula by adding the dollar
1:45
sign before both the column and row
1:47
number Dollar F doll2 this makes the
1:50
reference absolute and now Excel will
1:52
always point to the tax rate in F2 even
1:55
if you drag the formula down
2:00
next let's talk about mixed references
2:02
these combine both relative and absolute
2:04
references with mixed references you can
2:07
lock either the column or the row while
2:09
allowing the other part to change as you
2:11
copy the formula for example dollar A1
2:14
locks the column but lets the row change
2:17
on the other hand a dollar one locks the
2:19
row but lets the column change mixed
2:21
references are helpful when you want
2:23
part of the reference to stay fixed and
2:25
the other part to adjust is you copy the
2:27
formula to different cells we have a
2:29
table with four membership types and
2:31
durations the first row lists the one-
2:33
month prices and we'll calculate the
2:35
cost for each plan over the different
2:37
durations let's write the formula as
2:39
shown screen to calculate the fee for
2:41
the basic 3-month plan when you drag the
2:43
formula down to calculate the six-month
2:45
fee it correctly updates the reference
2:47
to cell A4 but it incorrectly references
2:49
cell C3 instead of C2 to fix this we
2:53
lock Row Two by adding a dollar before
2:55
the row number like shown on the screen
2:58
now when you drag the formula down it
2:59
will always reference row two keeping
3:02
the correct price for each plan now
3:04
let's drag the formula to the right to
3:06
calculate the fee for the standard plan
3:09
you'll notice it returns a value error
3:11
when we check the formula we see that
3:13
it's referencing column b instead of a
3:16
to fix this we add the dollar sign
3:17
before the column letter A to make it
3:19
absolute like shown on the screen now
3:22
when we copy or drag the formula across
3:24
the table it will correctly calculate
3:25
the fee for each plan and each duration
3:45
next let's discuss 3D references a 3D
3:49
reference refers to a range of cells
3:50
across multiple worksheets which is why
3:53
it's called 3D you're referencing
3:55
multiple sheets not just one a 3D
3:58
reference allows you to reference the
3:59
same cell or range across multiple
4:01
sheets in a workbook this is useful when
4:04
you need to perform a calculation that
4:06
includes data from different sheets in
4:08
our example we have three sheets sheet
4:11
one contains sales data for electronics
4:13
items sheet two contains sales data for
4:16
grocery items sheet 3 contains sales
4:18
data for fruits and vegetables we want
4:21
to sum the sales data from cell D3 to
4:23
d22 across all three sheets to do this
4:26
we use this formula this formula tells
4:29
Excel to sum the range D3 to d22 across
4:32
all three sheets sheet one sheet two and
4:36
Sheet three the column between sheet one
4:38
and Sheet three specifies the range of
4:40
sheets to include in the calculation a
4:43
3D reference only works when the sheets
4:45
you're referencing are listed in a
4:47
sequence you cannot reference
4:48
non-adjacent sheets like this also you
4:51
can't use 3D references across different
4:54
workbooks they only work within the same
4:56
workbook thanks for watching I hope this
4:59
tutorial help help you understand how to
5:00
use different types of cell references
5:02
in Excel including relative absolute
5:05
mixed and 3D references if you found
5:07
this video helpful don't forget to like
5:09
share and subscribe for more Excel tips
5:11
and tutorials if you have any questions
5:13
or need further clarification feel free
5:15
to leave a comment below happy learning
5:18
and see you next time
#Business & Productivity Software
#Calculators & Reference Tools
#Computer Education
#Data Sheets & Electronics Reference
#Technical Reference