Cell reference in excel with example
A cell reference refers to the name or address
of a specific cell or range of cells within the spreadsheet which is a combination of a column letter and a row number.
Each worksheet in Excel consists of several
cells. Each cell has a specific cell reference which helps the users to find
easily address the desired cell (s) within the functions. The address of a cell
is defined by the letter (A to XFD) of the column and each number (1 to 1048576)
represents a row in excel spreadsheet.
For example, A1
refers to the cell at the intersection of column A and row 1; B2 here B refers
to the second cell in column And 1 refer to the first row.
A cell reference refers to a cell or a
range of cells on a worksheet and can be used in MS Excel Function so that Microsoft Office Excel can
find the values or data that you want.
What are the 3 types of cell references in Excel?
Now there are three kinds of cell
references in excel that you can use in MS Excel:
1 Relative Cell References.
2 Absolute Cell References.
3 Mixed Cell References.
1 Relative Cell Reference
Relative cell references are useful when you have to create a formula for a range of cells and the formula needs to refer to a relative cell reference.
Now I want to calculate the total for each
item, we need to multiply the price of each item with the quantity of that
item.
For the first item, the formula in cell D2 you have to type B2* C2 (as shown below)
After it drag all item as shown below
Absolute Cell References in Excel?
Absolute cell references are useful when
you don’t want the cell reference to change as you copy or drag formulas. This
reference used when you have a fixed value that you need to use in the formula
(such as tax rate, commission rate, percentage number of months, etc.)
absolute cell references don’t change when
you copy or drag the formula to other cells.
For example, suppose you have the data set
as shown below where you have to calculate the commission for each item’s total
sales.
The
commission is 10% and is listed in cell E2.
To get the commission amount for each item
sale, use the following formula in cell E4 and copy for all cells:
=D4*$E$2
Note that there are two dollar signs ($) in the cell reference that has the commission – $E$2.
What does the Dollar ($) sign do?
A dollar symbol, when we add in front of the row and column number, makes it absolute it means absolute cell references don’t change when you copy or drag the formula to other cells.
Mixed Cell References in Excel?
Mixed cell references are more-tricky than
the absolute cell references if we compare.
There can be two types of mixed cell references:
1 The row is locked while the column
changes when the formula is copied or dragged.
2 The column is locked while the row
changes when the formula is copied or dragged.
Below is a excel sheet where you need to
calculate the three tiers of commission based on the percentage value in cell
E2, F2, and G2.
Now I want to calculate all these
commissions with just one formula by helping mix reference in excel.
Enter the below formula in cell E4 and copy
/ drag for all cells.
=$B4*$C4*E$2
The above formula uses both kinds of mixed
cell references (one where the row is locked and one where the column is
locked).
How to Change the Reference from Relative to Absolute (or Mixed)?
To change the cell reference from relative
to absolute, mix reference you need to
add the dollar sign before the column notation and the row number. faster way
to do this is by using the keyboard shortcut – F4.
For example, A1 is a relative cell reference,
and it would become absolute when you make it $A$1.
Here is what happens when you select the
reference and press the F4 key.
Press F4 key once: The cell reference
changes from A1 to $A$1 (becomes ‘absolute’).
Press F4 key two times: The cell reference
changes from A1 to A$1 (changes to mixed reference where the row is locked).
Press F4 key three times: The cell
reference changes from A1 to $A1 (changes to mixed reference where the column
is locked).
Press F4 key four times: The cell reference
becomes A1 again.
