LO5: Spreadsheet Software (Microsoft Excel)

Book Activities

FunctionPageActivity FileLOSupportVideo
Cell Referencing88-89 Cell Referencing.xlsx 5.1.1.b Create or change a cell reference
Overview of formulas in Excel
Arithmetic Functions 89 Arithmetic functions.xlsx 5.1.1.a.i SUM function
basic math in Excel 2013
SUMPRODUCT 89-90 SUMPRODUCT.xlsx 5.1.1.a.i SUMPRODUCT
ROUND-ROUNDUP-ROUNDDOWN Functions 90-91 ROUND-ROUNDUP-ROUNDDOWN.xlsx 5.1.1.a.i ROUND
ROUNDUP
ROUNDDOWN
Logical (Boolean) Operators 91 Logical and Boolean Operators.xlsx KS3 Expected Prior Learning Using calculation operators in Excel formulas
Using IF with AND, OR and NOT functions
Making Decisions (IF) 91 MAKING DECISIONS.xlsx 5.1.1.a.ii IF Function
Errors (IFERROR) 92 ERRORS.xlsx 5.1.1.a.ii IFERROR Function
VLOOKUP 93-95 VLOOKUP.xlsx 5.1.1.a.iii VLOOKUP
MATCH and Index Functions 95-96 Match and Index.xlsx 5.1.1.a.iii Match Function
Index Function
INDIRECT Function 96 Indirect.xlsx 5.1.1.a.iii INDIRECT Function
Joining, splitting and presenting text 96 Joining-SplittingandPresentingText.xlsx 5.1.1.a.iv Combine text from two or more cells into one cell
CONCATENATE/CONCAT Function 96-97 Joining-SplittingandPresentingText.xlsx 5.1.1.a.iv CONCAT Function
TEXTJOIN Function (*Office 2019+ ONLY) 97 Joining-SplittingandPresentingText.xlsx 5.1.1.a.iv TEXTJOIN Function
LEFT,RIGHT,MID Functions 97 Joining-SplittingandPresentingText.xlsx 5.1.1.a.iv LEFT Function
RIGHT Function
MID Function
PROPER Function 97-98 Joining-SplittingandPresentingText.xlsx 5.1.1.a.iv PROPER Function
UPPER and LOWER Functions 98 Joining-SplittingandPresentingText.xlsx 5.1.1.a.iv UPPER Function
LOWER Function
DATE and TIME, TODAY and NOW Functions 98 DATE-TIME-TODAYandNOW.xlsx 5.1.1.a.v DATE Function
ADD or SUBTRACT Dates
TIME Function
TODAY Function
NOW Function
Counting and adding data Functions 98-100 COUNTsandSUMIF.xlsx 5.1.1.a.vi COUNT Function
COUNTIF Function 98 COUNTsandSUMIF.xlsx 5.1.1.a.vi COUNTIF
SUMIF Function 98-99 COUNTsandSUMIF.xlsx 5.1.1.a.vi SUMIF Function
SUBTOTAL Function 99-100 SUBTOTAL.xlsx 5.1.1.a.vi SUBTOTAL Function
BODMAS 99 BODMAS.xlsx KS3 Expected Prior Learning Order in which Excel performs opeerations or calculations
Linking worksheets 100 LinkingWorksheets.xlsx 5.1.1.c Create an external reference (link) to a cell range in another workbook
Linking to external data 100-101 LinkingtoExternalData.xlsx 5.1.1.g Import data from external data sources Video:Import data from a Web Page
Importing data 101-103 ImportingData.xlsx
IImportExample1.csv
ImportExample2.txt
importExample3.txt
 5.1.1.f Importing data
PivotCharts and Tables 103-104 PivotChartsandtables.xlsx 5.1.1.h.i Overview of Pivot Tables and Pivot Charts
Pivot Tables
Pivot Charts
Dynamic Charts 105 DynamicCharts.xlsx 5.1.1.h.ii Dynamic Charts
Combination Charts 105-106 CombinationCharts.xlsx 5.1.1.h.iii Combination Charts
What-If Analysis 106-107 What-IfAnalysis.xlsx 5.1.1.d What-If Analysis
Macros 107-108 Macros.xlsm 5.1.1.e Quick start: Create a macro
Video: Work with Macros
Automate tasks with the Macro recorder
Assign a macro to a button
Applying Security 109-110 ApplyingSecurity.xlsx 5.1.1.j Protect an Excel file
Protect a Workbook
Protection and Security in Excel
Lock or Unlock Specific areas of a protected worksheet
Exporting Data 110 ExportingData.xlsx 5.1.1.k Exporting data
Cambridge National Level 1/2 Information Technologies - Hodder Education - Sonia Stuart & Brian Gillinder