Spreadsheets
Learn the core concepts and decision-making in Cambridge 9626 Topic 8, 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 8: spreadsheets
What you will learn
- Create task-appropriate screen/print layouts and control rows, columns, panes, validation and protection
- Write formulas with arithmetic, indices, relative/absolute/mixed references and named ranges
- Use calculation, counting, lookup, logical, date/time, text and conditional functions
- Test formulas and validation with suitable data; sort, filter, summarise, import/export and chart data
Structure and references control reliability
A spreadsheet should suit its audience and output: page size, orientation, margins, fit, headers and footers matter for print. Validation controls input and protection prevents accidental edits, but neither is a substitute for checking. Relative references change when copied; absolute references such as $B$2 stay fixed; mixed references fix only a row or column. Named cells and ranges make formulas easier to interpret.
Functions answer different questions
Use SUM/AVERAGE/MIN/MAX and rounding functions for calculations; COUNTIF(S), SUMIF(S) and AVERAGEIF for criteria; lookup functions to retrieve matching values; IF/IFS with AND/OR for decisions; date/time and text functions to extract or combine content. Test formulas using ordinary, boundary and invalid data. Sort and filter carefully, preserve source data, and choose charts that make the intended comparison clear.
Sources for this lesson
Pause and explain
Which approach best demonstrates secure, reliable IT practice in spreadsheets?
Worked example
In a marks sheet, B2:B31 contains marks, D1 contains a fixed pass mark, and each row should show Pass/Retry. Give a formula that can be copied down.
Show the worked solution
- Compare the current row's mark with the fixed pass threshold.
- Use an absolute reference for D1 so it does not shift.
- Copy down and test marks just below, equal to and above the threshold.
Answer In row 2, use =IF(B2>=$D$1,"Pass","Retry") and copy down. B2 changes to B3 and so on, while $D$1 remains fixed. Test 1 below the pass mark, exactly at it, and 1 above; confirm the task's rule treats equality as a pass.
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 table has Product in A, Region in B, Units in C and UnitPrice in D. Write the logic for total revenue for East and explain the criterion.
Give me a hint
Revenue is Units × UnitPrice, summed only for matching rows.
Compare my reasoning
- Calculate row revenue as C×D.
- Use a conditional sum for rows whose region equals East.
- Check that the formula range sizes and labels align.
Add a Revenue field with =C2*D2, then use a conditional sum such as =SUMIF(B2:B100,"East",E2:E100), where E contains row revenue. If using SUMIFS directly, use the product of units and price carefully or add a helper column. Confirm the criteria range and sum range cover the same records.
Look for these in your work
- Uses a condition on Region
- Calculates revenue rather than units alone
- Checks aligned ranges
A lookup returns the wrong tax rate after rows are sorted. Give two likely causes and one test.
Give me a hint
Check lookup mode, key uniqueness and whether the result column moves with the table.
Compare my reasoning
- Check exact versus approximate match settings.
- Check that the lookup key is unique and ranges remain paired.
- Test a known key before and after sorting.
The formula may be using approximate matching on an unsorted key, or its lookup and return ranges may no longer refer to corresponding columns after edits. Duplicate keys can also make the result ambiguous. Test a known tax code using exact match and verify the returned rate against the source table after sorting.
Look for these in your work
- Identifies plausible causes
- Names a controlled test
- Verifies against source data
A manager wants to compare monthly sales by region and see the trend over time. Recommend a summary and visualisation.
Give me a hint
Use grouping and a time axis; do not mix incomparable series without labels.
Compare my reasoning
- Summarise sales by month and region.
- Use a pivot table/report for grouped values.
- Choose a line chart or clearly labelled series and check units.
Create a pivot table with month as rows, region as columns and sum of sales as values, then use a line chart to show trends across ordered months. Label currency and series, check missing months, and avoid implying a continuous trend across unordered categories. Keep a copy of the raw data for verification.
Look for these in your work
- Groups by both dimensions
- Selects a suitable chart
- Communicates units and caveats
Common mistakes
- Copying a formula with the wrong reference type
- Testing only a typical value
- Choosing a chart before identifying the comparison
What can you explain now?
A table has Product in A, Region in B, Units in C and UnitPrice in D. Write the logic for total revenue for East and explain the criterion.
Compare with the explanation
Add a Revenue field with =C2*D2, then use a conditional sum such as =SUMIF(B2:B100,"East",E2:E100), where E contains row revenue. If using SUMIFS directly, use the product of units and price carefully or add a helper column. Confirm the criteria range and sum range cover the same records.
Revenue is Units × UnitPrice, summed only for matching rows.
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
When should a formula use absolute rather than relative references?
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.