Test spreadsheet elements (functions, validation, formatting)

Topic 8 – Spreadsheets (Cambridge International AS & A Level IT 9626)

Learning objective

Students will be able to test, apply and evaluate the essential spreadsheet elements – worksheet structure, referencing, functions, data validation, protection, formatting, charts, and basic data‑analysis tools – in line with the Cambridge syllabus.

1. Core spreadsheet components

• Worksheet – a single sheet consisting of a grid of rows (1, 2, …) and columns (A, B, …).
• Cell – the intersection of a row and a column, identified by a reference such as A1.
• Range – a rectangular block of cells, e.g. A1:C5. Ranges can be named for easier reference.
• Formula – an expression that begins with = and may contain operators, functions and cell references.
• Function – a predefined calculation (e.g. SUM, IF, VLOOKUP) that can be used inside a formula.

2. Creating and editing worksheet structure

Typical actions required in the practical exam:

• Insert / delete rows or columns (Home → Insert → Rows/Columns).
• Hide / unhide rows or columns (right‑click → Hide/Unhide).
• Resize rows or columns manually or with AutoFit (double‑click the border).
• Merge cells and centre the content (Home → Merge & Center).
• Freeze panes to keep headings visible while scrolling (View → Freeze Panes).
• Set page layout options – orientation, paper size, margins, headers & footers (Page Layout tab).
• Rename, colour‑code or delete worksheets (right‑click sheet tab).

3. Referencing in formulas

4. Core function families (exam‑relevant examples)

Only one representative is required for each family, but the table also lists additional functions that frequently appear in the assessment.

5. Formulas with mathematical notation

Complex calculations can be expressed with LaTeX for clarity. Example – compound interest:

$$A = P\left(1 + \frac{r}{n}\right)^{nt}$$

In a spreadsheet the same calculation is entered as:

=P*(1+r/n)^(n*t)

6. Data validation

Validation reduces entry errors by restricting the type, range or pattern of data that can be entered.

7. Cell and worksheet protection

1. Select the cells that users must be able to edit and choose Format Cells → Protection → Unchecked “Locked”.
2. Leave all other cells with the default “Locked”.
3. Protect the worksheet (Review → Protect Sheet). Set a password if required and decide which actions (e.g., select unlocked cells, format cells) are allowed.
4. To stop users from adding, moving or deleting sheets, protect the workbook (Review → Protect Workbook).

8. Cell formatting

1. Number formats – Currency, Percentage, Scientific, Date, Time, Custom (e.g., 0.00% or "Q"0).
2. Alignment & text wrap – Horizontal/vertical alignment, indent, wrap text, merge‑center for headings.
3. Cell styles – Use predefined styles (Heading 1, Total, Good, Bad) for consistency.
4. Conditional formatting
   • Home → Conditional Formatting → New Rule.
   • Rule types: “Format only cells that contain”, “Use a formula to determine which cells to format”, colour‑scale, data bars, icon sets.
   • Example: =E2>8000 → Font colour red, Bold.
   • Multiple rules can be layered; use “Stop If True” to control precedence.

9. Importing and exporting data

These operations are frequently examined in the practical paper.

10. Using a spreadsheet – essential operations

• Search – Ctrl + F (Find) or Ctrl + H (Replace).
• Sort – Data → Sort; single‑column or multi‑column with custom order.
• Filter – Home → Sort & Filter → Filter (AutoFilter) to display rows meeting criteria.
• PivotTable – Insert → PivotTable; drag fields to Rows, Columns, Values, Filters.
• PivotChart – With a PivotTable selected, Insert → PivotChart; choose chart type.
• Standard chart – Insert → Charts (Bar, Column, Line, Pie, Combo, Scatter). Use the Chart Design and Format tabs to refine.
• Data analysis tools – Data → Data Analysis (if the Analysis ToolPak is enabled) for Regression, Descriptive Statistics, etc.

11. Charts & graphs – checklist for the assessment

1. Choose a chart type that best represents the data:
   • Bar / Column – compare categories.
   • Line – show trends over time.
   • Pie – illustrate parts of a whole (use sparingly).
   • Combo – combine column and line series (e.g., revenue + growth %).
   • Scatter – display relationship between two numeric variables.
2. Add a clear chart title and axis titles.
3. Set appropriate axis scaling (e.g., start at 0, use a secondary axis when units differ).
4. Apply data labels, a legend, or a data table if required.
5. Format series colours and line styles for readability (use high‑contrast colours).
6. Resize the chart and position it logically on the worksheet (usually near the source data).
7. Check that the chart updates automatically when source data changes (dynamic range or Table).

12. Testing a spreadsheet – simple test plan

Before submitting, students should verify that the workbook behaves as expected.

• Normal data – typical values within the allowed range. Verify correct calculations, formatting and chart updates.
• Extreme data – maximum and minimum permissible values (e.g., 0, 100 000). Confirm that validation accepts them and that formulas do not overflow.
• Abnormal data – invalid entries such as text in numeric fields, dates outside the allowed range, or blank cells. Ensure validation messages appear and that error‑handling functions (e.g., IFERROR) prevent #REF! or #DIV/0! errors.
• Record the expected outcome for each test case and note any discrepancies.

13. Sample assessment tasks (practical paper style)

1. Enter the data shown in the table below and calculate the total revenue using SUM. State the exact formula used.
2. In column F, display “High” if revenue exceeds $5 000 and “Low” otherwise. Use the IF function.
3. Apply data validation to the “Quantity” column so that only whole numbers between 1 and 500 can be entered.
4. Format the “Revenue” column as Currency with two decimal places. Add conditional formatting to make any value > $10 000 appear in bold red text.
5. Write a lookup formula that retrieves the “Region” for a given “Product ID” from a separate lookup table (range $A$10:$C$20, region in column 3).
6. Protect the worksheet so that the calculated “Revenue” column cannot be edited, but the “Quantity” column remains editable. Use a password of your choice.
7. Create a PivotTable that shows total revenue per product name, and insert a Column Chart based on the PivotTable.
8. Export the completed worksheet as a PDF named RevenueReport.pdf.

14. Sample data table for the tasks

15. Answer key (brief)

1. Cell E6: =SUM(E2:E5)
2. Cell F2: =IF(E2>5000,"High","Low") – copy down to F5.
3. Data validation for C2:C5 – Settings: Whole number, between 1 and 500; Input message: “Enter a quantity (1‑500)”.
4. Format E2:E5 as Currency (2 d.p.). Conditional formatting rule: =E2>10000 → Font colour red, Bold.
5. Lookup formula (e.g., in G2): =VLOOKUP(A2,$A$10:$C$20,3,FALSE)
6. Unlock cells C2:C5, lock the rest, then Review → Protect Sheet, password “exam123”.
7. PivotTable: Rows – Product Name; Values – Sum of Revenue. Insert → Column Chart based on the PivotTable.
8. File → Export → Create PDF/XPS Document → save as RevenueReport.pdf.