Cell Reference In Excel | Relative , Absolute , Mix Reference

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.