Cell addresses

Intermediate Google Sheets

Richie Cotton

Data Evangelist at DataCamp

Rows and columns

	A	B
1	Command	Result
2	=ROW(C5)	5
3	=COLUMN(C5)	3

Addresses

	A	B
1	Command	Result
2	=ROW(C5)	5
3	=COLUMN(C5)	3
4	=ADDRESS(B2, B3)	$C$5
5	=ADDRESS(B2, B3, 4)	C5

Indirection

	A	B	C
1	Value	Command	Result
2	99	=ADDRESS(2, 1)	$A$2
3		=INDIRECT(C2)	99

Finding nearby cells

	A	B	C	D
1	Value1	Value2	Command	Result
2	3	5	=OFFSET(A3, 3, 1)	31
3	5	7	=OFFSET(A3, -1, 0)	3
4	11	13
5	17	19
6	29	31

Relative addresses

	A	B	C	D	E
1				Command	Result
2				=INDEX($B$3:$C$7, 3, 1)	11
3		3	5	=INDEX($B$3:$C$7, 4, 3)	#NUM!
4		5	7
5		11	13
6		17	19
7		29	31

Summary

• There are two ways of specifying a cell address.
• ROW() & COLUMN() return integer positions.
• ADDRESS() returns the address in A1 format text.
• INDIRECT() returns the value an an address.
• OFFSET() find returns values relative to another cell.
• INDEX() finds values within a cell range.

Happy cell hunting!

Intermediate Google Sheets