N-Level Computer Applications (SEAB 7018) Spreadsheets and Charts: entering and formatting data, formulas with relative and absolute references, common functions including IF, sorting and filtering, and creating well-labelled charts

Why this module matters

Spreadsheets and Charts teaches you to organise, calculate and present data, a skill assessed directly in the lab-based practical paper that includes spreadsheets. This module covers entering and formatting data, writing formulas with the all-important distinction between relative and absolute references, the common functions including IF, sorting and filtering, and creating well-labelled charts. Because the practical paper is hands-on, practising until these are automatic is the best preparation.

This guide ties together the matching dot-point pages, each with its own worked detail and practice. The strands below build from the basics to data analysis and presentation.

Entering and formatting data

Start with getting data in and making it clear. See entering and formatting data for cell formats and layout.

A spreadsheet is a grid of cells in numbered rows and lettered columns, so each cell has an address such as B3. Enter data into cells, then apply cell formatting so it reads clearly: number format with decimal places, currency for money, percentage for proportions, and date formats. Add borders and adjust column width so values are not cut off and the data is easy to read.

Formulas and cell references

Next, calculating. See formulas and cell references for operators and reference types.

A formula begins with an equals sign and calculates a result from cell references and arithmetic operators (+, -, *, /), for example =A1+B1. A relative reference (such as A1) changes when copied; an absolute reference (such as $A$1) is fixed with dollar signs so it does not change when copied; a mixed reference locks only the column ($A1`) or only the row (`A$1). Use absolute references for a value that lives in one cell and is needed by many formulas, such as a single rate.

Common functions

Functions summarise and test data quickly. See common functions for the core functions and IF.

Use these over a range (such as B2:B10):

• SUM: adds the values in a range.
• AVERAGE: gives the mean of a range.
• MAX: the largest value in a range.
• MIN: the smallest value in a range.
• COUNT: how many cells in a range contain numbers.
• IF: returns one value when a condition is true and another when false, for example =IF(B2>=50,"Pass","Fail").

Sorting and filtering

Then organising records. See sorting and filtering for ordering and showing the rows you need.

Sorting arranges records in order (for example by name A to Z, or by score highest first), and you can sort by more than one column, such as by class then by score. Filtering shows only the rows that meet chosen criteria, for example only students who passed. Crucially, select the whole table so that each row of data stays together when you sort, otherwise the columns become mismatched.

Creating charts

Finally, presenting data visually. See creating charts for choosing and labelling a chart.

Select the data, choose a suitable chart type, a column or bar chart to compare categories, a line chart for change over time, or a pie chart for proportions of a whole, and label it with a title, axis labels and a legend so it can be understood on its own.

How this module is examined

• Lab-based practical paper (spreadsheets task). Build a working spreadsheet: enter and format data, write formulas using relative and absolute references correctly, use functions including IF, sort and filter records, and create a labelled chart.
• Master references before the exam. Knowing when to use the dollar sign so a copied formula behaves correctly is the highest-value spreadsheet skill.

Check your knowledge

Try these, then take the matching quiz for this module.

1. State what a spreadsheet formula always begins with. (1 mark)
2. Explain what the dollar signs in $A$1 do when a formula is copied. (2 marks)
3. Write an IF formula that displays Pass if cell B2 is at least 50, otherwise Fail. (2 marks)
4. State which chart type is best for showing change over time. (1 mark)