Data Manipulation and Analysis

Q: What is the difference between relative, absolute, and mixed cell references in a spreadsheet?

A relative reference (e.g., A1 ) changes automatically based on relative position when copied, an absolute reference (e.g., \$A\$1 ) fixes both row and column, and a mixed reference (e.g., \$A1 or A\$1 ) locks either only the column or only the row.

Q: What do common spreadsheet errors such as #DIV/0! , #VALUE! , #REF! , #NAME? , and #N/A indicate?

They indicate specific formula issues: #DIV/0! is division by zero, #VALUE! is wrong data type usage, #REF! is an invalid/deleted cell reference, #NAME? is an unrecognised function or named range, and #N/A means a lookup value was not found.

Q: What is the difference between the WHERE clause and the HAVING clause in an SQL query?

The WHERE clause filters individual records before grouping and aggregation, whereas the HAVING clause filters grouped records after aggregation using aggregate functions.

Practice

Information and Communication Technology · Senior Secondary (HKDSE) · 6 min read · Updated: 29 Aug 2026

Data Manipulation and Analysis: Your Guide to Becoming a Data Whiz!

Hello! Welcome to one of the most practical and powerful topics in HKDSE ICT: Data Manipulation and Analysis. Ever wonder how businesses analyse trends or how organisations manage thousands of student records reliably? It all comes down to processing and structuring data effectively.

In this chapter, we explore two core tools tested in the Compulsory Part: Spreadsheets and Relational Databases. Think of spreadsheets as your flexible calculation and modelling grid, and databases as structured, multi-table systems designed for data integrity. Let's get started!

Part 1: Mastering Spreadsheets

Spreadsheets let you store, organise, format, and calculate numerical and textual data in rows and columns.

1.1 The Building Blocks of a Spreadsheet

Cell: A single box in the grid identified by its cell address (e.g., A1, B2).
Row & Column: Rows are numbered horizontally (1, 2, 3...); columns are labelled vertically with letters (A, B, C...).
Worksheet & Workbook: A worksheet is an individual grid; a workbook is the spreadsheet file containing one or more worksheets.
Formula: A calculation or expression entered into a cell. All formulas must begin with an equals sign ( $=$).

Key Concept: Cell References

1. Relative Reference (e.g., A1)
Changes automatically based on relative position when copied across rows or columns.
Example: If cell C1 contains `=A1+B1` and is copied down to C2, it automatically adjusts to `=A2+B2`.

2. Absolute Reference (e.g., \$A\$1)
Fixes both column and row so that the address does not change when copied. The \$ symbol locks the reference.
Example: If an MPF contribution rate of 5% is stored in cell H1, the formula to compute the deduction for gross salary in A2 is `=A2 * \$H\$1`. When copied down, A2 updates to A3, but `\$H\$1` remains fixed.

3. Mixed Reference (e.g., \$A1 or A\$1)
Locks either only the column (`\$A1`) or only the row (`A\$1`) when copied.

1.2 Formulas, Functions, and Error Values

Operators

Arithmetic Operators: `+`, `-`, `*`, `/`, `^` (exponentiation)
Relational (Comparison) Operators: `=`, `>`, `<`, `>=`, `<=`, `<>` (not equal to). These evaluate to TRUE or FALSE.
Logical Operators: `AND()`, `OR()`, `NOT()`

Core Spreadsheet Functions

Basic Aggregates: `SUM(range)`, `AVERAGE(range)`, `COUNT(range)` (counts numbers only), `COUNTA(range)` (counts non-empty cells), `MAX(range)`, `MIN(range)`.
Conditional Calculation:
- `IF(logical_test, value_if_true, value_if_false)`: Evaluates a condition. e.g., `=IF(A2>=50, "Pass", "Fail")`
- `COUNTIF(range, criteria)`: Counts cells satisfying a condition. e.g., `=COUNTIF(B2:B50, ">=50")`
- `SUMIF(range, criteria, [sum_range])`: Adds cells that meet a condition. e.g., `=SUMIF(A2:A30, "Class 5A", C2:C30)`
Lookup Functions:
- `VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`: Searches vertically down the first column of a table and retrieves a value from the specified column. Set `range_lookup` to `FALSE` (or `0`) for an exact match.
- `HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`: Searches horizontally across the first row of a table.
Math & Ranking Functions:
- `INT(number)`: Rounds a number down to the nearest integer.
- `ROUND(number, num_digits)`: Rounds a number to a specified number of decimal places.
- `MOD(number, divisor)`: Returns the remainder after division.
- `RANK(number, ref, [order])`: Returns the rank of a number in a list (order `0` for descending, `1` for ascending).

Common Spreadsheet Error Codes

#DIV/0!: Attempted to divide a number by zero or an empty cell.
#VALUE!: Wrong data type used in a function or formula (e.g., text where a number is expected).
#REF!: A cell reference is invalid (often because referenced cells were deleted).
#NAME?: Spreadsheet does not recognise text in a formula (often a typo in a function name).
#N/A: Value is not available (commonly seen when `VLOOKUP` cannot find a match).

1.3 Manipulating and Analysing Data

Sorting: Arranging records ascending or descending using single or multiple keys (e.g., primary sort by Class, secondary sort by Student Name).
Filtering: Displaying only rows that satisfy specific criteria while hiding the rest.
What-If Analysis & Goal Seek: Testing how changes in input parameters impact calculated results, or finding the input needed to achieve a target outcome.
Pivot Tables & Pivot Charts: Interactive summarisation tools that aggregate large datasets across rows, columns, values (e.g., SUM, COUNT), and page filters.

Part 2: Relational Databases

A Relational Database organises data into structured, linked two-dimensional tables to minimise redundancy and maintain data integrity.

2.1 Database Structure & Concepts

Table (Entity): A set of related data organised in rows and columns.
Record (Tuple / Row): A single data entry containing all attributes for one entity instance.
Field (Attribute / Column): A specific category of data with a defined data type (e.g., Text, Number, Date/Time, Boolean).
Primary Key (PK): A field (or combination of fields) that uniquely identifies each record in a table. It cannot contain null values.
Composite Key: A primary key made up of two or more fields combined.
Foreign Key (FK): A field in one table that refers to the Primary Key of another table, creating a link between them.

Table Relationships

One-to-One (1:1): Each record in Table A relates to at most one record in Table B.
One-to-Many (1:N): A single record in Table A can relate to multiple records in Table B (e.g., one Class has many Students).
Many-to-Many (N:M): Multiple records in Table A relate to multiple records in Table B (e.g., Students and Clubs; resolved in implementation using an intermediate junction table).

Data Validation Checks

Validation rules prevent incorrect data from being stored:

Presence Check: Ensures a mandatory field is not left blank.
Range Check: Ensures a number or date falls within allowed boundaries (e.g., mark between $0$ and $100$).
Type Check: Ensures the entered value matches the field data type (e.g., numeric input in an Age field).
Format Check: Ensures data matches a predefined pattern (e.g., HKID format or phone number pattern).

2.2 SQL Queries (Structured Query Language)

SQL is used to retrieve, filter, aggregate, and sort data from database tables.

Core SQL Syntax & Clauses

`SELECT field1, field2, AGGREGATE_FUNCTION(field3)`
`FROM TableName`
`WHERE condition`
`GROUP BY field1`
`HAVING aggregate_condition`
`ORDER BY field1 ASC|DESC;`

Key SQL Clauses & Operators

SELECT & FROM: Specifies fields to display and the source table.
WHERE: Filters records before grouping. Supports operators like `=`, `<>`, `>`, `<`, `BETWEEN value1 AND value2`, `IN (val1, val2)`, and `LIKE` pattern matching (`%` matches any sequence of characters; `_` matches a single character).
Aggregate Functions: `COUNT()`, `SUM()`, `AVG()`, `MAX()`, `MIN()`.
GROUP BY: Groups rows sharing common values for aggregate calculations.
HAVING: Filters grouped records after aggregation (used with aggregate functions).
ORDER BY: Sorts the output in `ASC` (ascending) or `DESC` (descending) order.

Example SQL Query

`SELECT Class, COUNT(StudentID) AS TotalStudents, AVG(ExamScore) AS AverageScore`
`FROM Students`
`WHERE Status = 'Active'`
`GROUP BY Class`
`HAVING AVG(ExamScore) >= 60`
`ORDER BY Class ASC;`

2.3 Forms and Reports

Forms: Provide an intuitive graphical interface for users to enter, view, and modify records one at a time while enforcing validation rules.
Reports: Organise and format query/table data for printed output or onscreen presentation, including group headers, summary totals, and page numbering.

Chapter Summary

In Spreadsheets, master relative/absolute referencing, error troubleshooting, and key functions (`SUMIF`, `COUNTIF`, `VLOOKUP`, `ROUND`, `RANK`).

In Databases, understand relational structure (tables, records, fields, primary/foreign keys, validation rules) and write precise SQL queries using `SELECT`, `WHERE`, `GROUP BY`, `HAVING`, and `ORDER BY`.

References

Information and Communication Technology Curriculum and Assessment Guide (Secondary 4–6) (2021) — Curriculum Development Council & HKEAA
Technology Education – Curriculum Documents — Education Bureau (EDB)
Technology Education: Key Learning Area Curriculum Guide (Primary 1 – Secondary 6) — Curriculum Development Council (EDB)

Quick check

Can you answer these now?

Open each question to check the key ideas from this chapter.

What is the difference between relative, absolute, and mixed cell references in a spreadsheet?

A relative reference (e.g., A1) changes automatically based on relative position when copied, an absolute reference (e.g., \$A\$1) fixes both row and column, and a mixed reference (e.g., \$A1 or A\$1) locks either only the column or only the row.

How does the VLOOKUP function work in a spreadsheet?

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches vertically down the leftmost column of a table for a value and returns the data from the corresponding row in a specified column.

What do common spreadsheet errors such as #DIV/0!, #VALUE!, #REF!, #NAME?, and #N/A indicate?

They indicate specific formula issues: #DIV/0! is division by zero, #VALUE! is wrong data type usage, #REF! is an invalid/deleted cell reference, #NAME? is an unrecognised function or named range, and #N/A means a lookup value was not found.

What is the purpose of Goal Seek and What-If Analysis in spreadsheets?

They allow users to explore different scenarios by observing how changing input values affects calculated results, or by determining the exact input value required to achieve a target outcome.

What are Primary Keys, Composite Keys, and Foreign Keys in a relational database?

A Primary Key uniquely identifies each record and cannot be null; a Composite Key is a primary key composed of multiple fields; and a Foreign Key is a field in one table referencing the Primary Key of another table to establish relationships.

What are the common types of data validation checks used in database fields?

Common validation checks include presence checks (non-blank), range checks (within numeric/date bounds), type checks (correct data type), and format checks (matching a specified pattern).

What is the difference between the WHERE clause and the HAVING clause in an SQL query?

The WHERE clause filters individual records before grouping and aggregation, whereas the HAVING clause filters grouped records after aggregation using aggregate functions.

What are the distinct roles of Forms and Reports in database management systems?

Forms provide an interactive graphical interface for data entry and modification of individual records, whereas Reports organise, format, and aggregate data for presentation and printing.

Ready to test yourself?

8 practice questions on this topic, with worked solutions. Multiple choice is marked as you go. No account needed.

Practise This Topic

More Information and Communication Technology chapters

* The content provided by thinka is generated by AI and may not always be accurate or up-to-date. Please use it as a supplementary resource and verify with official materials.

Put These Notes into Practice

Reading the notes is a great start. Now practise with unlimited AI-generated questions and get instant feedback. 100,000+ students are already improving their grades.

Start Practising Now View Pricing

Done reading? Test yourself with AI practice questions

Practise This Topic Now