Data analysis and visualisation
Learn the core concepts and decision-making in Cambridge 9626 Topic 17, then practise recall, application and transfer with fictional scenarios.
Exam scope and assessment notes
This is original StudyForA teaching and fictional practice based on Cambridge syllabus outcomes, not copied syllabus text, an official question, a required software brand or a Cambridge mark scheme. Practical software tasks here are planning and reasoning exercises; StudyForA does not inspect files created in spreadsheet, database, graphics, audio/video or animation software, simulate the practical exam environment, award official marks or predict a grade. Follow the software and file-format requirements issued by your centre for your exam year.
Before you begin
- Check whether your route is AS Level (Topics 1–11) or full A Level (Topics 1–21).
Work on paper. Try each question before revealing help, and explain why your method works.
Quick readiness check
Before Topic 17: data analysis and visualisation
What you will learn
- Clean and transform data from spreadsheet, model and database sources
- Compare, consolidate, split, merge and combine source data fields
- Create pivot-table reports and pivot charts
- Choose visual displays that communicate patterns accurately and efficiently
Prepare data before interpreting it
Data analysis and visualisation combine skills from spreadsheets, modelling and databases. Inspect source definitions and types, handle missing or duplicate values consistently, standardise dates/categories, and record transformations. When combining sources, align definitions and units before comparing them. Split a combined field only when its structure is understood; merge fields without losing distinctions that matter.
Visuals are a communication choice
Pivot tables group data across categories and aggregate values; pivot charts make those summaries easier to scan. Choose a display for the question: trends over time, comparisons across groups, distributions or composition. Use clear labels, units, sensible scales and accessible contrast. A chart can mislead through omitted categories, inconsistent periods or an axis that exaggerates small differences; preserve a trace back to the source.
Sources for this lesson
Pause and explain
Which approach best demonstrates secure, reliable IT practice in data analysis and visualisation?
Worked example
Two files record monthly energy use, one in kWh and one in MWh with different site names. How should they be consolidated before comparing sites?
Show the worked solution
- Standardise units and verify site-name mappings.
- Check month definitions, missing records and duplicates.
- Combine with documented transformations and compare summaries.
Answer Convert MWh to kWh using 1 MWh = 1000 kWh, map site names with a verified lookup, and align months and measurement periods. Check duplicates and missing data before appending or joining. Document the conversion and mapping, then create a pivot summary by month and site with units labelled consistently.
Try a topic test
Choose Easy, Normal or Difficult. Save your answers and open worked review after finishing.
Test this topic →From guided practice to a new situation
Write your answer and reasoning first. Use a hint only if you are stuck. The model solution is for self-checking; your written work is not automatically marked.
A date field contains values such as 2026-03, March 2026 and 03/26. What should be done before monthly analysis?
Give me a hint
Standardise one unambiguous representation and validate the interpretation.
Compare my reasoning
- Confirm the locale/meaning of ambiguous values.
- Convert to one consistent date or month key.
- Check missing, invalid and duplicate periods.
Confirm how ambiguous values such as 03/26 should be interpreted, then convert all records to a consistent date/month key. Validate that conversion against source records, identify invalid or missing periods and check duplicates. Do not silently assume a locale, since 03/26 could mean different dates.
Look for these in your work
- Resolves ambiguity with evidence
- Standardises dates
- Checks data quality
An analyst wants to compare average exam scores by subject and year. Describe a pivot table and chart configuration.
Give me a hint
Put the two categories in separate dimensions and choose an aggregation that matches the question.
Compare my reasoning
- Use Year and Subject as row/column categories.
- Use average score as the value aggregation.
- Choose a labelled chart and check group sizes.
Place Year on the horizontal categories and Subject as series (or vice versa), with Average of Score as the value. A line chart can show ordered year trends if repeated comparable years are available; a grouped bar chart may be clearer for a small number of years. Label the score scale and inspect sample counts so averages are not read without context.
Look for these in your work
- Configures both dimensions
- Matches aggregation to question
- Communicates sample context
A chart shows a sharp rise after the vertical axis starts at 95 rather than zero. What should a reviewer check?
Give me a hint
A truncated scale can change the apparent size of a difference.
Compare my reasoning
- Read the full axis range and unit.
- Compare the absolute difference with the displayed visual difference.
- Choose a scale or direct labels that show magnitude fairly.
The reviewer should check whether the truncated axis makes a small absolute change look dramatic, confirm units and values, and choose a scale that communicates the difference fairly. A non-zero baseline can sometimes be appropriate for a line chart, but it must be clear and should not distort the comparison.
Look for these in your work
- Identifies scale effect
- Checks actual values
- Offers a fair presentation
Common mistakes
- Combining data before matching units and definitions
- Using a visual that hides sample size or missing records
- Treating a pivot summary as proof of causation
What can you explain now?
A date field contains values such as 2026-03, March 2026 and 03/26. What should be done before monthly analysis?
Compare with the explanation
Confirm how ambiguous values such as 03/26 should be interpreted, then convert all records to a consistent date/month key. Validate that conversion against source records, identify invalid or missing periods and check duplicates. Do not silently assume a locale, since 03/26 could mean different dates.
Standardise one unambiguous representation and validate the interpretation.
After trying it yourself, choose your next review. This is your self-assessment.
Your review choice appears on Today. Sign in to sync it across devices.
Make it stick
Why must source definitions be reconciled before combining two datasets?
If you needed the solution, close it and solve the problem again from a blank page. A correct answer is stronger when you can explain the reason for each step.