EXCEL CELL
REFERENCING
Notes on Relative, Absolute,
and Mixed References
WHAT IS CELL
REFERENCING?
Cell referencing in Excel
refers using the address of a
cell (e.g., A1, B2) in formulas
or functions to access its
values.
TYPES OF CELL
REFERENCES
1. Relative Reference (A1)
2. Absolute Reference ($A$1)
3. Mixed Reference ($A1 or
A$1)
RELATIVE REFERENCE (A1)
Changes based on position
when copied.
Example: =A1 in B1 becomes
=A2 when copied to B2.
ABSOLUTE REFERENCE
($A$1)
Fixed reference – does not
change when copied.
Example: =$A$1 remains the
same in any cell.
MIXED REFERENCE
Locks either the column or row
$A1 – Locks column A, row can
change.
A$1 – Locks row 1, column can
change.
USE IN FORMULAS
Combine references for flexible
calculations.
Example: =A1*$B$1 – A1
changes, B1 is fixed.
TIPS & SHORTCUTS
Use F4 to toggle reference
types while editing.
Understand reference behavior
before copying formulas.
PRACTICAL EXAMPLE
To always multiply by B1: use
=A1*$B$1
Relative: =A1*B1 | Absolute:
=A1*$B$1
ENTER THE DATA BELOW IN SPREADSHEET
TASK
1. Use Absolute cell
reference to compute the
vales for the Absolute Total
2. Use Relative cell reference
to compute the vales for the
Relative Total
3. Lock row 3 and multiply
vales of column F with
values in Column E
LOGICAL OPERATORS
SIGN NAME
= Equal To
> Greater Than
< Less Than
………………… Greater Than or Equal
To
………………… Less Than or Equal To
………………… Not Equal To
IF FUNCTION
The IF function performs
a logic test and returns
one value if the condition
is True and another if the
condition is False
IF SYNTAX
=IF(Logical test, Value_if_true,
Value_if_False)
Logical test – The condition you want to
check (eg A1>50)
Value_if_true – The result if the condition
is True
Value_if_false – The result if the condition
is False
Use the if function to assign
value above 20 with text “above
20” and those below assign
“below 20”
SUMIF FUNCTION
SUMIF FUNCTION
The SUMIF function adds the
values in a range that meets a
single specified condition
(criteria)
SUMIF SYNTAX
=SUMIF(Range,Criteria)
Range – The range of cells you want
to evaluate using the criteria
Criteria – The condition that
determines which cells to add
EXAMPLE ONE
=SUMIF(A1:A5, “>10”)
Sums value in A1:A5 that are
greater than 10
EXAMPLE TWO
=SUMIF(A1:A5, “Apple”,B1:B5)
Sums values in B1:B5 where A1:A5
has the word Apple
WILD CARD
E.g *
COUNT FUNCTION
The COUNT function is used to
count the number of cells that
contain numbers within a range
The COUNT function only counts
numeric values
COUNT EXAMPLE
=COUNT(A1:A5)
Counts numeric values in cells A1 to
A5
=COUNT(A1:A5,B1:B5)
Counts numeric values in cells A1 to
A5 and cells B1 to B5
COUNTIF
COUNTIF
COUNTIF is a function in excel and
google sheets used to count the
number of the cells that meet a
specific condition (criteria) in a
given range
COUNTIF SYNTAX
=COUNTIF(Range,Criteria)
Range – The group of cells to
evaluate
Criteria – The condition to count
cells by (e.g a number, text,
expression)
EXAMPLE
=COUNIF(A1:A10, “Apple”)
Counts how many times “Apple”
has appears in A1:A10
YOU MANAGE A SMALL STORE AND ARE
TRACKING DAILY SALES. BELOW IS A
PORTION OF THE SALES DATA ENTERED
INTO EXCEL (ASSUME THE DATA STARTS
FROM CELL A1):
Date Sales (USD)
01-Jun 250
02-Jun 300
03-Jun
04-Jun 180
05-Jun N/A
06-Jun 400
07-Jun 270
08-Jun -
TASK
[Link] the COUNT function to
determine how many valid sales
entries (i.e., numeric values) are in
the Sales (USD) column.
YOU ARE GIVEN THE FOLLOWING DATA
IN AN EXCEL WORKSHEET (ASSUME
THE DATA STARTS IN CELL A1):
Employee Name Department Status
John Smith Sales Active
Sarah Johnson HR Inactive
Ahmed Khan Sales Active
Maria Lopez IT Active
James Brown HR Active
Linda White Sales Inactive
Raj Patel IT Active
Emma Wilson Sales Active
TASK
Use the COUNTIF Function to find:
•a. The number of employees in the
Sales department.
•b. The number of Active employees.
•c. The number of Active employees in
the Sales department.
RANK
RANK
The RANK function is used to find
the position (rank) of a number
within a list of numbers.
RANK SYNTAX
=RANK(number,ref,[order])
Number – The number you want to rank
Ref – the range of numbers to rank
against
Order:
0 – descending order (highest gets rank
1)
1- ascending order (lowest gets rank 1)
USE THE RANK FUNCTION
Student Name Score Rank
Alice 85
Ben 92
Chloe 78
David 85
Ella 96
Frank 88
Grace 92
Henry 73
Irene 88
Jack 78
VLOOKUP
VLOOKUP is a function in excel used
to search for a value in the first
column of a range or table and
returns a value from the same row
in a specified column
SYNTAX
=VLOOKUP(lookup_value,table_array,col_inde
x_number,[range_lookup])
Lookup_value – The value to search for in the
first column
Table_array – the table or range of cells to
search in
Col_index_num – the column number in the
table from which to return a value
Range_lookup – optional. True or approximate
match, False for exact match
HLOOKUP FUNCTION
Hlookup stands for horizontal
Lookup. It searches for a value in
the first row of a table and returns a
value in the same column
NB
Can’t search in rows below the
first
SYNTAX
=HLOOKUP(Lookup_valu
e,table_array,row_index_
num,range_lookup)
Item Apple Banana Mango
Price 1000 1500 2000
Stock 20 35 50
THANKS FOR ATTENDING