Section 14: Data Analysis Mark Nicholls – ICT Lounge
IGCSE ICT – SECTION 14
DATA ANALYSIS
MICROSOFT EXCEL
STEP BY STEP GUIDE
Mark Nicholls
ICT Lounge
,Section 14: Data Analysis Mark Nicholls – ICT Lounge
Data Analysis – Self Study Guide
Contents
Learning Outcomes ……………………………………………………………………………………… Page 3
What is a Data Model? ..................................................................................... Page 4
Spreadsheet Basics…………………………………………………………………………………………. Page 4 – 6
Resizing Column Widths…………………………………………………………………………………. Page 6
Autofil…………………………………………………………………………………………………………….. Page 7
Absolute Cell Reference…………………………………………………………………………………… Page 8
Printing Spreadsheet Values………………………………………………………………………….. Page 9
Printing Spreadsheet Formulae……………………………………………………………………….. Page 10
Basis Formulae (Operators)……………………………………………………………………………. Page 11 – 13
Naming Cells and Cell Ranges………………………………………………………………………… Page 14 – 15
Introduction to Functions………………………………………………………………………………… Page 16
SUM Function……………………………………………………………………………………… Page 16 – 17
AVERAGE Function……………………………………………………………………………… Page 18
MAX Function…………………………………………………………………………………….. Page 18
MIN Function……………………………………………………………………………………… Page 19
Alternative to Typing in Functions………………………………………………………………….. Page 19 – 20
Activity 1 ………………………………………………………………………………………………………… Page 20
INT Function ………………………………………………………………………………………. Page 21 – 22
ROUND Function ……………………………………………………………………………….. Page 22 – 23
Activity 2 ………………………………………………………………………………………………………. Page 23
COUNT Function ………………………………………………………………………………… Page 24
COUNTA Function ……………………………………………………………………………… Page 25
Activity 3 ………………………………………………………………………………………………………. Page 26
COUNTIF Function ……………………………………………………………………………… Page 26 – 28
Activity 4 ………………………………………………………………………………………………………. Page 29
SUMIF Function …………………………………………………………………………………. Page 30 – 31
Activity 5 ………………………………………………………………………………………………………. Page 32
SUMIF Function with NOT criteria………………………………………………………. Page 33 – 34
COUNTIF Function with NOT criteria……………………………………………………. Page 35 – 36
Activity 6 ………………………………………………………………………………………………………. Page 36
IF Function ……………………………………………………………………………………….. Page 37 – 39
Page |1
,Section 14: Data Analysis Mark Nicholls – ICT Lounge
Activity 7 ………………………………………………………………………………………………………. Page 39
NESTED IF Function …………………………………………………………………………….. Page 40 – 41
Activity 8 ……………………………………………………………………………………………………….. Page 42
IF AND Function ……………………………………………………………………………….. Page 43 – 44
Using Lookups ………………………………………………………………………………………………… Page 45
HLOOKUP Function ……………………………………………………………………………. Page 45 – 47
VLOOKUP Function ……………………………………………………………………………. Page 48 – 49
LOOKUP Function between 2 Spreadsheets ………………………….…………… Page 50 – 51
Activity 9 ………………………………………………………………………………………………………. Page 52
Interrogating Data (Using Filters) …………………………………………………………………. Page 52 - 53
Numbered Filters ……………………………………………………………………………….. Page 54 - 55
Activity 10 ………………………………………………………………………………………………………. Page 56
Finding help for Graphs and Charts……………………………………………………………….. Page 56
Finding help for Hiding/Showing Columns and Rows……………………………………….. Page 56
Finding help for Printing Gridlines and Areas of Spreadsheets……………………….. Page 56
Page |2
, Section 14: Data Analysis Mark Nicholls – ICT Lounge
In this section you will learn how to:
Enter text and number data into a spreadsheet
Use editing functions such as cut, copy and paste
Enter formulae and simple functions into a spreadsheet
Replicate formulae and functions in a spreadsheet
Test the spreadsheet for functionality
Select subsets of data within the spreadsheet
Change display and formatting of cells within a spreadsheet
Change size of rows and columns within a spreadsheet
Adjust the page orientation
Save a spreadsheet
Print a spreadsheet showing formulae or values
Create a graph or a chart
Label a graph or a chart
Change chart colours to print in black and white
For this section you will need these source files from your teacher:
CLASSLIST.CSV ROOMS.CSV CLIENT.CSV
SALARY.CSV CLUBS.CSV SALES.CSV
COSTS.CSV STAFF.CSV EMPLOYEES.CSV
TASKS.CSV ITEMS.CSV TEACHERS.CSV
JOBS.CSV TUCKSHOP.CSV OPERATORS.CSV
TUTORS.CSV PROJECT.CSV WEBHITS.CSV
RAINFALL.CSV
Page |3
IGCSE ICT – SECTION 14
DATA ANALYSIS
MICROSOFT EXCEL
STEP BY STEP GUIDE
Mark Nicholls
ICT Lounge
,Section 14: Data Analysis Mark Nicholls – ICT Lounge
Data Analysis – Self Study Guide
Contents
Learning Outcomes ……………………………………………………………………………………… Page 3
What is a Data Model? ..................................................................................... Page 4
Spreadsheet Basics…………………………………………………………………………………………. Page 4 – 6
Resizing Column Widths…………………………………………………………………………………. Page 6
Autofil…………………………………………………………………………………………………………….. Page 7
Absolute Cell Reference…………………………………………………………………………………… Page 8
Printing Spreadsheet Values………………………………………………………………………….. Page 9
Printing Spreadsheet Formulae……………………………………………………………………….. Page 10
Basis Formulae (Operators)……………………………………………………………………………. Page 11 – 13
Naming Cells and Cell Ranges………………………………………………………………………… Page 14 – 15
Introduction to Functions………………………………………………………………………………… Page 16
SUM Function……………………………………………………………………………………… Page 16 – 17
AVERAGE Function……………………………………………………………………………… Page 18
MAX Function…………………………………………………………………………………….. Page 18
MIN Function……………………………………………………………………………………… Page 19
Alternative to Typing in Functions………………………………………………………………….. Page 19 – 20
Activity 1 ………………………………………………………………………………………………………… Page 20
INT Function ………………………………………………………………………………………. Page 21 – 22
ROUND Function ……………………………………………………………………………….. Page 22 – 23
Activity 2 ………………………………………………………………………………………………………. Page 23
COUNT Function ………………………………………………………………………………… Page 24
COUNTA Function ……………………………………………………………………………… Page 25
Activity 3 ………………………………………………………………………………………………………. Page 26
COUNTIF Function ……………………………………………………………………………… Page 26 – 28
Activity 4 ………………………………………………………………………………………………………. Page 29
SUMIF Function …………………………………………………………………………………. Page 30 – 31
Activity 5 ………………………………………………………………………………………………………. Page 32
SUMIF Function with NOT criteria………………………………………………………. Page 33 – 34
COUNTIF Function with NOT criteria……………………………………………………. Page 35 – 36
Activity 6 ………………………………………………………………………………………………………. Page 36
IF Function ……………………………………………………………………………………….. Page 37 – 39
Page |1
,Section 14: Data Analysis Mark Nicholls – ICT Lounge
Activity 7 ………………………………………………………………………………………………………. Page 39
NESTED IF Function …………………………………………………………………………….. Page 40 – 41
Activity 8 ……………………………………………………………………………………………………….. Page 42
IF AND Function ……………………………………………………………………………….. Page 43 – 44
Using Lookups ………………………………………………………………………………………………… Page 45
HLOOKUP Function ……………………………………………………………………………. Page 45 – 47
VLOOKUP Function ……………………………………………………………………………. Page 48 – 49
LOOKUP Function between 2 Spreadsheets ………………………….…………… Page 50 – 51
Activity 9 ………………………………………………………………………………………………………. Page 52
Interrogating Data (Using Filters) …………………………………………………………………. Page 52 - 53
Numbered Filters ……………………………………………………………………………….. Page 54 - 55
Activity 10 ………………………………………………………………………………………………………. Page 56
Finding help for Graphs and Charts……………………………………………………………….. Page 56
Finding help for Hiding/Showing Columns and Rows……………………………………….. Page 56
Finding help for Printing Gridlines and Areas of Spreadsheets……………………….. Page 56
Page |2
, Section 14: Data Analysis Mark Nicholls – ICT Lounge
In this section you will learn how to:
Enter text and number data into a spreadsheet
Use editing functions such as cut, copy and paste
Enter formulae and simple functions into a spreadsheet
Replicate formulae and functions in a spreadsheet
Test the spreadsheet for functionality
Select subsets of data within the spreadsheet
Change display and formatting of cells within a spreadsheet
Change size of rows and columns within a spreadsheet
Adjust the page orientation
Save a spreadsheet
Print a spreadsheet showing formulae or values
Create a graph or a chart
Label a graph or a chart
Change chart colours to print in black and white
For this section you will need these source files from your teacher:
CLASSLIST.CSV ROOMS.CSV CLIENT.CSV
SALARY.CSV CLUBS.CSV SALES.CSV
COSTS.CSV STAFF.CSV EMPLOYEES.CSV
TASKS.CSV ITEMS.CSV TEACHERS.CSV
JOBS.CSV TUCKSHOP.CSV OPERATORS.CSV
TUTORS.CSV PROJECT.CSV WEBHITS.CSV
RAINFALL.CSV
Page |3
