IGCSE ICT CHAPTER 20.
1 (SPREADSHEETS)
Excel Spreadsheet Skills – IGCSE ICT
Cell, Row & Column Management
Insert
• Insert Cell(s): Right-click → Insert → Shift cells right/down.
• Insert Row(s): Right-click row number → Insert.
• Insert Column(s): Right-click column letter → Insert.
Delete
• Delete Cell(s): Right-click → Delete → Shift cells left/up.
• Delete Row(s)/Column(s): Right-click row/column → Delete.
Merge Cells
• Select cells → Home tab → Merge & Center (or Merge Across).
• Used for titles or formatting across multiple columns.
Formulae & Cell References
Create Formulae Using Cell References
• Start with = sign.
• Example: =A1 + B1 adds values in cells A1 and B1.
Replicate Formulae
• Relative Reference: Adjusts when copied (e.g., =A1+B1 becomes =A2+B2).
• Absolute Reference: Fixed reference using $ (e.g., =$A$1+B1).
Arithmetic Operators in Formulae
Operator Function Example
+ Addition =A1 + B1
- Subtraction =A1 - B1
* Multiplication =A1 * B1
/ Division =A1 / B1
^ Indices (Power) =A1 ^ 2
Named Cells & Ranges
• Assign a name to a cell or range for easier reference.
• Example: Name cell A1 as Price, then use =Price * Quantity.
Common Functions in Excel
Function Purpose Example
SUM Adds values =SUM(A1:A10)
AVERAGE Calculates mean =AVERAGE(B1:B5)
MAX Finds highest value =MAX(C1:C10)
MIN Finds lowest value =MIN(D1:D10)
INT Rounds down to nearest integer =INT(E1)
ROUND Rounds to specified decimal places =ROUND(F1, 2)
COUNT Counts numeric cells =COUNT(G1:G10)
LOOKUP Searches for a value =LOOKUP(100, A1:A10)
Function Purpose Example
VLOOKUP Vertical lookup =VLOOKUP(100, A1:B10, 2)
HLOOKUP Horizontal lookup =HLOOKUP("Math", A1:D4, 2)
XLOOKUP Flexible lookup (newer Excel versions) =XLOOKUP("Apples", A1:A5, B1:B5)
IF Conditional logic =IF(A1>100, "High", "Low")
External Data Sources
• Use data from other sheets or workbooks:
o Example: =SUM('[Sales.xlsx]Jan'!A1:A10) pulls data from another file.
Nested Functions
• Combine functions inside others:
o Example: =IF(A1>100, MAX(A1:B1), MIN(A1:B1))
o This checks a condition and returns either the max or min value.
FORMULAE AND FUNCTIONS
Difference Between Formula and Function
Concept Description Example
A user-defined calculation using cell references, numbers, and
Formula =A1 + B1 * C1
operators
Function A built-in Excel command that performs a specific task =SUM(A1:A5)
• Every function is part of a formula, but not every formula uses a function.
• Formulas offer flexibility; functions offer simplicity and efficiency.
Order of Mathematical Operations in Excel
Excel follows a specific order when evaluating formulas, similar to the BODMAS/PEMDAS rule:
Operator Precedence
Order Operation Type Symbol Example
1 Parentheses () =(2+3)*4 → 20
2 Exponents (Indices) ^ =2^3 → 8
3 Multiplication/Division * / =6/2*3 → 9
4 Addition/Subtraction + - =5+2-1 → 6
Using Brackets
• Brackets change the default order.
• Example: =5+2*3 → 11
But =(5+2)*3 → 21
Absolute vs Relative Cell Referencing
Relative Cell Reference
• Adjusts automatically when copied.
• Format: A1
• Example: =A1+B1 → becomes =A2+B2 when copied down.
Absolute Cell Reference
• Remains fixed when copied.
• Format: $A$1
• Example: =$A$1+B1 → $A$1 stays constant.
Mixed Reference
• One part fixed, one part relative.
• Format: $A1 or A$1
• Useful in tables or when copying across rows/columns.
Use Cases
• Relative: Calculations across rows (e.g., totals per item).
• Absolute: Fixed values like tax rates, commission percentages.
• Mixed: When copying formulas across both rows and columns.
Excel Functions with Examples
Function Purpose Example Explanation
Adds a range of
SUM =SUM(A1:A5) Adds values from A1 to A5
numbers
Calculates the Finds the average of B1 to
AVERAGE =AVERAGE(B1:B10)
mean B10
Finds the highest Returns the largest number in
MAX =MAX(C1:C10)
value C1 to C10
Finds the lowest Returns the smallest number
MIN =MIN(D1:D10)
value in D1 to D10
Rounds down to
INT nearest whole =INT(4.9) Returns 4
number
Rounds to specified Rounds value in E1 to 2
ROUND =ROUND(E1, 2)
decimal places decimal places
Counts numeric Counts how many cells in F1
COUNT =COUNT(F1:F10)
cells to F10 contain numbers
Searches for a Finds 50 or closest match in
LOOKUP =LOOKUP(50, A1:A10)
value in a range A1 to A10
Function Purpose Example Explanation
Searches for 101 in column A
=VLOOKUP(101, A2:C10, 2,
VLOOKUP Vertical lookup and returns value from
FALSE)
column 2
=HLOOKUP("Math", A1:D2, 2, Searches for "Math" in row 1
HLOOKUP Horizontal lookup
FALSE) and returns value from row 2
Flexible lookup Finds "Apples" in A1:A5 and
=XLOOKUP("Apples", A1:A5,
XLOOKUP (new Excel returns corresponding value
B1:B5)
versions) from B1:B5
Returns "Pass" if A1 > 50, else
IF Conditional logic =IF(A1>50, "Pass", "Fail")
"Fail"
Nested condition
=IF(AND(A1>50, B1>50), "Pass",
IF + AND with multiple Checks if both A1 and B1 > 50
"Fail")
checks
Nested condition
=IF(OR(A1>50, B1>50), "Pass",
IF + OR with either Checks if either A1 or B1 > 50
"Fail")
condition
Adds A1 to A5 and rounds the
ROUND +
Nested function =ROUND(SUM(A1:A5), 0) result to nearest whole
SUM
number
IF + =IF(VLOOKUP(A1, A2:B10, 2, Uses VLOOKUP and applies
Conditional lookup
VLOOKUP FALSE)>100, "High", "Low") condition
Using External Data Sources
You can reference data from other sheets or workbooks:
=SUM('[Sales2024.xlsx]Jan'!B2:B10)
This pulls data from the "Jan" sheet in another workbook named Sales2024.xlsx.
