ICT Core A4: Spreadsheet
Master spreadsheet skills from basics to advanced functions
Learning Modules
Introduction
Purpose, workspace, and features of spreadsheets
Data Entry
Cell formats, number formats, and editing techniques
Formulas
Constants, operators, and cell references
Functions
Mathematical, logical, text, and statistical functions
Cell Reference
Relative, absolute, and mixed references
Interactive Quiz
Test your knowledge with 15 questions
Flashcards
Review key concepts with 3D flip cards
Past Questions
Practice with exam-style questions
Introduction to Spreadsheet
Purpose of Using Spreadsheets
Spreadsheets are powerful tools used for:
- Organizing and storing data in rows and columns
- Performing calculations using formulas and functions
- Analyzing data with charts and graphs
- Creating financial reports and budgets
- Managing databases and performing data analysis
Spreadsheet Workspace
Key components of a spreadsheet workspace:
- Cell: The intersection of a row and column (e.g., A1, B2)
- Row: Horizontal series of cells (numbered 1, 2, 3...)
- Column: Vertical series of cells (labeled A, B, C...)
- Formula Bar: Displays the content of the active cell
- Worksheet: A single page in a workbook
- Workbook: A file containing one or more worksheets
Key Features
Calculations
Perform complex calculations automatically
Data Visualization
Create charts and graphs from data
Data Organization
Sort and filter data efficiently
Data Entry Techniques
Number Formats
Different number formats serve different purposes:
| Format | Example | Purpose |
|---|---|---|
| General | 12345 | Default format for numbers |
| Number | 12,345.00 | Display numbers with decimal places |
| Currency | $12,345.00 | Display monetary values |
| Percentage | 50% | Display values as percentages |
| Date | 01/01/2024 | Display date values |
| Time | 12:00 PM | Display time values |
| Text | '12345 | Treat numbers as text |
Cell Formatting Features
Wrap Text
Displays long text on multiple lines within a cell. Used when cell content exceeds column width.
Merge Cells
Combines multiple cells into one larger cell. Commonly used for titles and headers.
| Sales Report 2024 | ||
| Q1 | Q2 | Q3 |
Freeze Panes
Keeps specific rows or columns visible while scrolling. Useful for keeping headers visible in large datasets.
AutoFill
Automatically fills cells with data patterns (numbers, dates, formulas). Drag the fill handle to extend patterns.
- 1, 2, 3 → AutoFill → 4, 5, 6...
- Monday, Tuesday → AutoFill → Wednesday, Thursday...
- Jan, Feb → AutoFill → Mar, Apr...
Formulas in Spreadsheet
Elements of a Formula
A formula always begins with an equal sign (=) and can contain:
= A1 + B1 * 2
- Constants: Fixed values (e.g., numbers, text)
- Operators: Symbols that specify calculations (+, -, *, /)
- Cell References: Addresses of cells (e.g., A1, B2)
- Functions: Pre-built formulas (e.g., SUM, AVERAGE)
Operators
Arithmetic Operators
| Operator | Operation | Example | Result |
|---|---|---|---|
| + | Addition | =5+3 | 8 |
| - | Subtraction | =10-4 | 6 |
| * | Multiplication | =6*7 | 42 |
| / | Division | =20/4 | 5 |
| ^ | Exponentiation | =2^3 | 8 |
| % | Percentage | =50% | 0.5 |
Comparison Operators
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | =A1=B1 |
| > | Greater than | =A1>B1 |
| < | Less than | =A1 |
| >= | Greater than or equal to | =A1>=B1 |
| <= | Less than or equal to | =A1<=B1 |
| <> | Not equal to | =A1<>B1 |
Text Concatenation Operator
The ampersand (&) joins text strings together:
=A1 & " " & B1
Example: If A1="Hello" and B1="World", result is "Hello World"
Order of Operations
Spreadsheets follow the standard mathematical order of operations (PEMDAS):
- Parentheses ()
- Exponents ^
- Multiplication * and Division /
- Addition + and Subtraction -
= (5 + 3) * 2^2 / 4
Result: (8) * 4 / 4 = 8
Excel Functions
Mathematical Functions
| Function | Purpose | Example |
|---|---|---|
| SUM | Adds all numbers in a range | =SUM(A1:A10) |
| AVERAGE | Calculates the average of numbers | =AVERAGE(B1:B5) |
| MAX | Returns the largest value | =MAX(C1:C20) |
| MIN | Returns the smallest value | =MIN(D1:D15) |
| ROUND | Rounds a number to specified digits | =ROUND(E1,2) |
| ABS | Returns absolute value | =ABS(-5) |
| POWER | Raises a number to a power | =POWER(2,3) |
Logical Functions
| Function | Purpose | Example |
|---|---|---|
| IF | Returns one value if condition is true, another if false | =IF(A1>50,"Pass","Fail") |
| AND | Returns TRUE if all conditions are true | =AND(A1>0,B1<100) |
| OR | Returns TRUE if any condition is true | =OR(C1="Yes",D1="Yes") |
| NOT | Reverses the logic of its argument | =NOT(E1=0) |
=IF(D2>=5,"Pass","Fail")
If the score in D2 is 5 or greater, display "Pass", otherwise display "Fail"
Text Functions
| Function | Purpose | Example |
|---|---|---|
| LEFT | Returns leftmost characters | =LEFT(A1,3) |
| RIGHT | Returns rightmost characters | =RIGHT(B1,4) |
| MID | Returns characters from middle | =MID(C1,2,5) |
| LEN | Returns length of text | =LEN(D1) |
| UPPER | Converts text to uppercase | =UPPER(E1) |
| LOWER | Converts text to lowercase | =LOWER(F1) |
| CONCATENATE | Joins text strings | =CONCATENATE(A1," ",B1) |
Statistical Functions
| Function | Purpose | Example |
|---|---|---|
| COUNT | Counts cells containing numbers | =COUNT(A1:A10) |
| COUNTA | Counts non-empty cells | =COUNTA(B1:B20) |
| COUNTIF | Counts cells meeting criteria | =COUNTIF(C1:C15,">50") |
| MEDIAN | Returns the median value | =MEDIAN(D1:D10) |
| MODE | Returns the most common value | =MODE(E1:E20) |
Lookup Functions
| Function | Purpose | Example |
|---|---|---|
| VLOOKUP | Searches vertically in first column | =VLOOKUP(A1,A:D,2,FALSE) |
| HLOOKUP | Searches horizontally in first row | =HLOOKUP(B1,A1:E5,3,FALSE) |
| INDEX | Returns value at intersection of row and column | =INDEX(A1:D10,3,2) |
| MATCH | Returns position of value in range | =MATCH(C1,A:A,0) |
Cell Reference Types
Relative Reference
Changes when formula is copied to another cell. Default reference type.
=A1+B1
When copied from C1 to C2, becomes: =A2+B2
When copied from C1 to D1, becomes: =B1+C1
Absolute Reference
Remains constant when formula is copied. Uses dollar signs ($) before column letter and row number.
=A1*$B$1
When copied anywhere, $B$1 always refers to cell B1
Common use: Multiplying values by a fixed tax rate or conversion factor
Mixed Reference
Combination of relative and absolute references. Either column or row is fixed.
=$A1
Column A is fixed, row changes when copied down
=A$1
Row 1 is fixed, column changes when copied across
Comparison Table
| Reference Type | Format | Behavior When Copied | Use Case |
|---|---|---|---|
| Relative | A1 | Both column and row change | General calculations |
| Absolute | $A$1 | Neither column nor row change | Fixed constants, rates |
| Mixed (Column) | $A1 | Column fixed, row changes | Vertical lookup tables |
| Mixed (Row) | A$1 | Row fixed, column changes | Horizontal lookup tables |
Press F4 key to cycle through reference types when editing a formula
Interactive Quiz
Test your knowledge with 15 questions
Flashcards
Click cards to flip and review key concepts
Past Exam Questions
Practice with exam-style questions