Spreadsheet models, formulas and outputs
Build a spreadsheet that calculates correctly, supports analysis and presents values clearly for screen and print.
Exam scope and assessment notes
Cambridge IGCSE Information and Communication Technology 0417, for exams in 2026, 2027 and 2028. Examples, datasets and practice are original StudyForA material; they are not Cambridge questions, source files, mark schemes or grades. Practical lessons teach transferable operations, but learners must perform them in locally available office and web-authoring software and inspect their own files. This site does not reproduce a Cambridge practical examination or award an official grade. The separate 2029 syllabus is not mapped here.
Before you begin
- No previous ICT course is assumed. Read each scenario carefully and justify a tool or method by connecting it to the task, user and constraints.
Work on paper. Try each question before revealing help, and explain why your method works.
Quick readiness check
Which reference keeps both its row and column fixed when copied?
What you will learn
- Create and edit a spreadsheet model using formulas, functions, relative and absolute references, named ranges and external data.
- Use arithmetic, nested functions, SUM, AVERAGE, MAX, MIN, INT, rounding, counting, LOOKUP, VLOOKUP, HLOOKUP, XLOOKUP and IF.
- Sort, search and filter data; format values and apply conditional formatting.
- Set row/column display, print area, orientation, pages, gridlines and headings.
Formulas express relationships
A formula combines values, cell references and operators; a function is a named calculation such as SUM or AVERAGE. Brackets make the intended order of operations clear. Relative references adjust when copied; absolute references such as $B$2 stay fixed. Named cells and ranges make important constants easier to identify.
A model might calculate total cost as quantity × unit price, then use IF to apply a rule. Lookup functions find related values in a table. Nested functions combine calculations. External data must be checked for units, missing values and update time before it is used.
Analyse and present the results
Sort by one or several criteria and filter a subset using logical or comparison conditions. Use wildcards only when partial text matching is intended. Conditional formatting highlights cells according to rules but does not fix the underlying value.
Format numbers as currency, decimal places or percentages; wrap text and resize rows and columns so data is fully visible. Show formulas or values as the task requires, set print area and orientation, and check page count, headings and gridlines in print preview.
Sources for this lesson
Pause and explain
A price in B2 is multiplied by a single rate stored in F1 for every row. What should the copied formula use?
Worked example
Rows contain quantity in B2:B10 and unit price in C2:C10. A tax rate is stored in F1 and should stay fixed when formulas are copied. Give the total in D2.
Show the worked solution
- Multiply quantity by price and by one plus the tax rate.
- Use an absolute reference for F1 so the rate does not shift.
- Copy the formula down and test one row by hand.
Answer In D2 enter =B2*C2*(1+$F$1). The dollar signs keep F1 fixed when the formula is copied down. Check a row manually; if F1 contains 8%, the formula multiplies quantity × price × 1.08.
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.
Scores are in C2:C20. Give formulas for the mean, maximum and number of numeric scores.
Give me a hint
Use the matching aggregate functions and keep the full range consistent.
Compare my reasoning
- Use AVERAGE for the mean.
- Use MAX for the highest score.
- Use COUNT for numeric entries, or COUNTA only if every non-empty cell should count.
=AVERAGE(C2:C20), =MAX(C2:C20), and =COUNT(C2:C20). COUNT ignores text; COUNTA would count any non-empty cell, so use it only if that is the intended definition of a recorded score.
Look for these in your work
- Chooses suitable functions
- Distinguishes COUNT from COUNTA
A learner's grade is 'Pass' when score in D2 is at least 50 and 'Review' otherwise. Write the formula and test boundary values.
Give me a hint
Use IF with a comparison that includes 50.
Compare my reasoning
- Set the logical test D2>=50.
- Return 'Pass' when true and 'Review' when false.
- Test D2=49, 50 and 51.
=IF(D2>=50,"Pass","Review"). It returns Review for 49 and Pass for 50 and 51; test the boundary because using >50 would incorrectly fail a score of exactly 50.
Look for these in your work
- Uses the inclusive boundary
- Checks below, at and above the boundary
A printed budget hides the last two columns. Give a correction sequence that preserves readable numbers.
Give me a hint
Check width, orientation, scaling and print area in preview.
Compare my reasoning
- Confirm the intended print area includes the two columns.
- Adjust column widths and consider landscape orientation.
- Use fit-to-page only if the text remains readable, then preview again.
Check the print area and ensure all required columns are included. Resize columns and consider landscape orientation or a deliberate page break. If scaling to fit, verify the text and numbers remain legible; preview again before printing rather than shrinking blindly.
Look for these in your work
- Checks print area and page setup
- Preserves readability
Common mistakes
- Copying a relative tax-rate reference so it shifts to an empty cell
- Formatting a number as a percentage without checking the stored value
- Printing hidden or truncated columns without noticing
What can you explain now?
When should a reference such as $F$1 be absolute?
Compare with the explanation
When a copied formula must keep using the same fixed cell.
An absolute reference does not shift when a formula is filled to another cell.
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
Explain the difference between a formula and function, then show why an absolute reference may be needed when copying a model.
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.