Cell Referencing Method - MS Excel

In Excel there are three types of cell references, these are Relative, Absolute and Mixed.
There are two types in Mixed reference, Absolute Row Relative Column Reference and Relative Row Absolute Column Reference.

Detailed usage of cell references are listed below.

Serial No	Method	What is referenced?	Type	Usage
1	A1	Single cell	Relative	A is column identifier, 1 is row identifier
2	A1:B3	Range	Relative	This references 6 cells
3	5:05	Range	Relative	All cells in row 5
4	B:B	Range	Relative	All cells in column B
5	5:07	Range	Relative	All cells in rows from 5 to 7
6	B:E	Range	Relative	All cells in columns from B to E
7	$A$1	Cell	Absolute	The cell A1. Cut-pasting formula to another location will not change the reference.
8	A$1	Cell	Mixed	The $ makes the column reference absolute, leaving the row reference relative
9	R1C1	Cell	Absolute	R1 is row 1, C1 is column 1
10	R1C1:R3C2	Range	Absolute	Same as A1:B3 as shown above
11	R	Range	Absolute	Reference to current row
12	C	Range	Absolute	Reference to current column
13	R[-2]C	Cell	Relative	Cell two rows up and in the same column
14	R[-2]C1	Cell	Mixed	Row reference is relative, column reference is absolute
15	R[2]C[3]	Cell	Relative	Cell two rows down of current row and three rows to right of current column
16	R[-1]	Range	Relative	Cells in the row above the current row
17	(defined name)	Range or Cell	Absolute	Named cells are always absolute
18	Sheet1!(reference)	Either	Either	Prefixing the sheet number and exclamation mark before any of the above references indicates the source worksheet.
19	=‘C:\[sample.xls]Sheet1’!$A$1	Cell	Absolute	Refers to an external file - a worksheet in the file and an absolute position within that worksheet.
20	=‘C:\[sample.xls]Sheet1’!octroi	Cell	Absolute	Same as above. Refers to a defined name “octroi”
21	Sheet1:Sheet3!A1:B3	Cell	Relative	Refers to all cells of A1 to B3 in all worksheets from Sheet 1 to sheet 3. “Sheet1” and “sheet3” are actual worksheet names.
22	Getpivotdata function	Range	Absolute	This is a special function which is used to refer to a range of rows/columns in a pivot table.