Spreadsheet
Information and
communication
technology 20.1. CREATE A DATA MODEL
20.2. MANIPULATE DATA
IGCSE 0417
20.3. PRESENT DATA
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 1
20.1 Create a data model
What is a data model?
Information and ❑ A spreadsheet model is used to explore different possible
communication
technology answers.
❑ These models are financial, mathematical or scientific.
IGCSE 0417
❑ It is sometimes called a ‘what if’ scenario or ‘what if’
modelling.
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 2
20.1 Create a data model
What is a data model?
Information and ❑ It lets us change data in the spreadsheet to see what will
communication
technology happen to the results.
❑ It is useful to know how to build a simple spreadsheet
IGCSE 0417
model
❑ and edit (change) the data within the model, or even
Spreadsheet
change the model itself, to produce different results.
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 3
Spreadsheet
FUNCTIONS & FORMULAE
Information and
communication • SUM & AUTOSUM • COUNT
technology
• AVERAGE • COUNTA
IGCSE 0417 • MIN • IF
• MAX • COUNTIF
• INT
• SUMIF
Spreadsheet • ROUND
Paper 3 • HLOOKUP & VLOOKUP
TR. WKN, LEGACY INTERNATIONAL SCHOOL 4
20.1.1 Create and edit a spreadsheet model
• INSERT CELLS, ROWS AND COLUMNS
Information and
communication
technology • DELETE CELLS, ROWS AND COLUMNS
• THE CONTENTS OF A SPREADSHEET CELL CAN BE:
IGCSE 0417 • » A NUMBER
• » TEXT, WHICH IS CALLED A LABEL
Spreadsheet
• » A FORMULA, WHICH ALWAYS STARTS WITH AN = SIGN.
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 5
20.1.1 Create and edit a spreadsheet model
• INSERT CELLS, ROWS AND COLUMNS
Information and
communication
technology • DELETE CELLS, ROWS AND COLUMNS
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 6
20.1.2 Create formulae using cell
references
USING ARITHMETIC OPERATORS IN FORMULAE
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 7
Spreadsheet or Data Analysis
• » FOR ADDITION USE THE + SYMBOL.
Information and
communication
technology • » FOR SUBTRACTION USE THE − SYMBOL.
• » FOR MULTIPLICATION USE THE * SYMBOL.
IGCSE 0417
• » FOR DIVISION USE THE / SYMBOL.
• » FOR INDICES USE THE ^ SYMBOL.
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 8
Spreadsheet or Data Analysis
• USING ARITHMETIC OPERATORS IN FORMULAE
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 9
20.1.4 CELL REFERENCING
Relative referencing
Information and
communication • the reference values change when they are replicated
technology ✓ values change when they are replicated.
✓ C1= A1+B1
IGCSE 0417 ✓ C2= A2+B2
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 10
20.1.4 CELL REFERENCING
• Absolute referencing
Information and
communication • these cell references to stay the same when they are
technology replicated
✓ values not change or fix when they are replicated.
IGCSE 0417 ✓ C1= $A$1+B1
✓ C2= $A$1+B2
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 11
SPREADSHEET
• Display and print spreadsheet values
Information and
communication • Display and print spreadsheet formulae
technology • More editing tools
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 12
20.1.7 Merge cells
• Select the Home tab, in the Alignment section select the icon for
Information and Merge & Center
communication
technology • Select, again from the Alignment section, the icon for Wrap text.
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 13
20.1.8 Use formulae
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 14
20.1.9 Named cells and ranges
• Create a named cell
Information and
communication • Create a named range
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 15
20.1.10 What are formulae and functions?
• » For addition use the + symbol e.g A1=B1+C1
Information and
communication • » For subtraction use the − symbol. e.g A1=B1-C1
technology
• » For multiplication use the * symbol. e.g A1=B1*C1
• » For division use the / symbol. e.g A1=B1/C1
IGCSE 0417 • » For indices use the ^ symbol. e.g A1=B1^C1
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 16
20.1.11 Use functions
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 17
20.1.11 Use functions
• SUM
Information and • AutoSum >>Home>>Editing Group
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 18
20.1.11 Use functions
• AVERAGE
Information and • AVERAGE>>Home>>Editing Group or Insert Function
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 19
20.1.11 Use functions
• MAX
Information and • MIN
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 20
20.1.11 Use functions
• INT
Information and • Formular= INT(B10)
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 21
20.1.11 Use functions
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 22
20.1.11 Use functions
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 23
20.1.11 Use functions
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 24
20.1.11 Use functions
Information and
communication • =COUNT(A2:A19)
technology
• =COUNTA(A2:A19)
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 25
20.1.11 Use functions
• =COUNT(A2:A19)
Information and
communication • =COUNTA(A2:A19)
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 26
20.1.11 Use functions
• =COUNT(A2:A19)
Information and
communication • =COUNTA(A2:A19)
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 27
20.1.11 Use functions
• = COUNTIF($C$3:$C$21,"<5")
Information and
communication Range Criteria
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 28
20.1.11 Use functions
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 29
20.1.11 Use functions
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 30
20.1.11 Use functions
• COUNTIF
Information and
communication • Page 454
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 31
20.1.11 Use functions
• =IF(I6>40,"Pass","Fail")
Information and • =IF(A1=5,A2*0.05,"No discount")
communication
technology • IF>>INSERT FUNCTION
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 32
Spreadsheet or Data Analysis
Nested IF
Information and
communication
technology • =IF(C3>=10,“ Very experienced", IF(C3>=5,"Experienced","Not
experienced"))
IGCSE 0417 • =IF(E2<6,"Add to this house", IF(E2>10,"Full", "Ideal Number"))
• =IF(E2<6, "Add to this house", IF(AND(E2>6,E2<10), “ Ideal
Spreadsheet Number","Full"))
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 33
Spreadsheet or Data Analysis
Information and SUMIF
communication Sum Range
technology • =SUMIF($B$3:$B$21,A35,$C$3:$C$21)
IGCSE 0417 Range Criteria
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 34
Spreadsheet or Data Analysis
Information and LOOKUP, HLOOKUP, VLOOKUP, XLOOKUP
Range
communication Lookup
technology
=HLOOKUP(B6,$B$2:$H$3,2,0)
IGCSE 0417 Lookup value
Table Array
Row Index
Num
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 35
Spreadsheet or Data Analysis
Information and LOOKUP, HLOOKUP, VLOOKUP, XLOOKUP
communication
technology
=VLOOKUP(B3,client.csv!$A$2:$B$8,2,0)
IGCSE 0417
Table Array in client file
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 36
Spreadsheet or Data Analysis
LOOKUP, HLOOKUP, VLOOKUP,XLOOKUP-461
Information and
communication =XLOOKUP(lookup value,lookup array, Return array, if not found, match mode)
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 37
Spreadsheet or Data Analysis
Search and select data
Information and
communication
technology ▪ Search using text filters
▪ Search using number filters
IGCSE 0417 ▪ Search using two or more criteria
▪ Search using wildcards
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 38
Spreadsheet or Data Analysis
Sort data
Information and
communication
technology
IGCSE 0417
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 39
Spreadsheet or Data Analysis
Information and Present data
communication
technology ▪ Display either formulae or values
• Select data for display
▪ Adjust rows and columns
IGCSE 0417
• Adjust row height/column width
• Hide a row/column
Spreadsheet ▪ Wrap text within a cell
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 40
Spreadsheet or Data Analysis
Enhance a spreadsheet
Information and
communication ▪ Bold, italic and underline
technology ▪ Background colour
▪ Font colour
IGCSE 0417 ▪ Pattern Fill
▪ Text orientation
▪ Borders and gridlines
Spreadsheet
Paper 3
TR. WKN, LEGACY INTERNATIONAL SCHOOL 41
