Be able to format numeric data appropriately including to display the number of decimal places, different currency symbols, percentages

Topic 20 – Spreadsheets (Cambridge IGCSE ICT)

Learning Objectives (aligned to AO1‑AO3)

• AO1 – Knowledge & Understanding: Identify spreadsheet components, explain cell‑referencing conventions, and describe the purpose of each formatting option.
• AO2 – Application: Build a multi‑sheet model, import external data, apply formulas, functions and sorting/filtering techniques, and format numeric output correctly.
• AO3 – Analysis & Design: Design a solution that meets given specifications, justify the choice of functions, and document the workflow clearly.

Syllabus Mapping (quick reference)

1. Building a Data Model (20.1)

1.1 Worksheet basics

• Insert / delete rows or columns: Ctrl + + / Ctrl + - or use Insert ► Rows/Columns.
• Add a new sheet: Click the “+” tab or press Shift + F11.
• Rename & colour‑code tabs for easy navigation (e.g., “RawData”, “Calc”, “Report”).

1.2 Naming ranges

1. Select the required cells.
2. Type a name (no spaces) in the Name Box (e.g., Sales2024) and press Enter.

Named ranges improve readability and are frequently required in exam questions.

1.3 Cell‑referencing conventions

• A1 style (default): column letter + row number (e.g., A1).
• R1C1 style: row number first, then column number (e.g., R1C1). Activate via File ► Options ► Formulas ► R1C1 reference style – useful for understanding absolute references.
• Absolute reference: \$A\$1 locks column & row when copied.
• Mixed reference: \$A1 (column locked) or A\$1 (row locked).

1.4 Linking between worksheets

To refer to a cell on another sheet, use the syntax SheetName!Cell. Example:

=SUM(Jan!B2:B13)   // totals column B on the sheet named “Jan”

When the source sheet is renamed, the reference updates automatically.

1.5 Quick practice – converting a reference

Original formula in Sheet1!C2: =A2*B2. Copy it one row down. What changes? Convert the formula to =A\$2*B\$2 so that the row numbers stay fixed when copied.

2. Formulas & Functions (20.1)

2.1 Operator precedence (order of operations)

1. Parentheses ()
2. Exponents ^
3. Multiplication * and division /
4. Addition + and subtraction ‑

Example: = (B2‑C2) * \$D\$1 / 100 → subtraction → multiplication → division.

2.2 Core functions checklist

2.3 Nested functions (AO3 design)

Functions can be placed inside one another to achieve complex logic. Example:

=IF(ROUND(AVERAGE(Sales),1) > 1000, "Good", "Review")

This combines AVERAGE, ROUND and IF in a single formula.

3. Importing External Data (20.2)

3.1 CSV / TXT files

1. Data ► Get Data ► From Text/CSV.
2. Select the file (e.g., sales.csv) and click Import.
3. In the Text Import Wizard choose the correct delimiter (comma, tab, semicolon) and column data format.
4. Click Load to place the data on a new sheet.

3.2 Importing from the web

1. Data ► Get Data ► From Web.
2. Enter the URL of the table (e.g., a public CSV link) and follow the prompts.
3. Choose the table you need and click Load.

3.3 Linking to another workbook

• Open both workbooks.
• In the destination cell type ='[Source.xlsx]Sheet1'!A1 or use Copy → Paste Link.
• The linked cell updates automatically when the source file is saved.

3.4 Why it matters for AO2

Exam questions often state “import the file data.csv and calculate total revenue”. Demonstrating the correct import steps earns marks for data handling.

4. Sorting, Filtering & Searching (20.2)

4.1 Simple sort

1. Select any cell in the data range.
2. Data ► Sort A‑Z or Sort Z‑A.
3. Confirm that “My data has headers” is ticked if appropriate.

4.2 Custom sort (multiple keys)

1. Data ► Sort ► Custom Sort….
2. Add Level 1 (e.g., “Region”), then Level 2 (e.g., “Sales” descending).
3. Click OK.

4.3 Basic filter

1. Select the header row.
2. Data ► Filter (or click the filter icon).
3. Use the drop‑down arrows to choose criteria (e.g., “Number Filters → Greater Than → 1000”).

4.4 Advanced filter (criteria range)

1. Create a small criteria range on the sheet (e.g., cells G1:G2 with header “Sales” and condition “>1000”).
2. Data ► Advanced.
3. Set List range to your data, Criteria range to the cells you just created, and choose “Copy to another location” if you want the filtered list elsewhere.
4. Click OK.

4.5 Searching (Find & Replace, wild‑cards)

• Find: Ctrl + F, enter text/number, use * (any characters) or ? (single character).
• Replace: Ctrl + H – useful for correcting repeated errors before finalising the model.

5. Presenting Data (20.3)

5.1 Numeric formatting

5.1.1 Decimal places

1. Select cells → right‑click → Format Cells.
2. Category: Number.
3. Set the required Decimal places (e.g., 2) and click OK.

Only the display changes; the underlying value retains full precision.

5.1.2 Currency & Accounting

1. Format Cells → Currency or Accounting.
2. Choose the symbol ($, £, € etc.), number of decimal places and the negative‑number style (red or parentheses).

Currency places the symbol directly next to the number; Accounting aligns the symbol to the left of the column and adds a uniform space for negatives.

5.1.3 Percentage

1. Select the cells containing decimal fractions.
2. Format Cells → Percentage.
3. Set the desired decimal places (e.g., 1) and click OK.

The stored value is unchanged; Excel only multiplies by 100 for display.

5.1.4 Custom number formats

Use the syntax positive;negative;zero;text. Examples:

5.1.5 Conditional formatting (AO2)

1. Select the target range.
2. Home ► Conditional Formatting.
3. Choose a rule type (e.g., “Greater Than”, “Top 10%”, or “Use a formula”).
4. Define the formatting (fill colour, font colour, data bar, etc.).
5. Click OK.

Typical exam use: highlight sales below a target, or flag dates that are past due.

5.2 Chart creation (required for 20.3)

1. Select the data range (including headings).
2. Insert ► Recommended Charts or choose a specific type (Column, Line, Pie, Bar, Scatter).
3. Adjust the Chart Title, axis labels, and legend as required.
4. Use Design ► Add Chart Element for data labels or trendlines.

Charts must be placed on the “Report” sheet and sized to fit the printed page.

5.3 Page‑layout & printing settings (20.3)

• Orientation: Page Layout ► Orientation ► Portrait/Landscape.
• Margins: Normal, Wide, Narrow, or Custom Margins.
• Print Area: Select cells ► Page Layout ► Print Area ► Set Print Area.
• Print Titles: Under Page Layout ► Print Titles, repeat rows/columns on each printed page.
• Gridlines & Headings: Page Layout ► Sheet Options ► check “Print” for gridlines and/or headings if required.
• Center on page: Page Layout ► Margins ► Center on page (both horizontally and vertically).
• Header/Footer: Insert ► Text ► Header/Footer – add page numbers, file name, or date.

6. Documenting the Solution (20.3)

• Use cell comments (right‑click ► Insert Comment) to explain complex formulas.
• Provide a brief evidence document (Word or PDF) that lists:

  1. What the model does (purpose).
  2. Key worksheets and their contents.
  3. Formulas/functions used and why they were chosen.
  4. Formatting choices (e.g., “Currency – £, 2 d.p. for all monetary values”).
  5. Steps taken to import data and any links created.

• Rename worksheets clearly (e.g., “RawData”, “Calc”, “Report”).
• Keep a consistent colour‑scheme for headings and totals – this aids readability and marks for presentation.

7. Integrated Practical Exercise (covers AO1‑AO3)

1. Data entry (enter exactly as shown):

   Cell	Value
   A1	1234.567
   A2	0.256
   A3	9876.543
   A4	-45.678

2. Define a named range Sales that refers to A1:A4.
3. Apply the following formats:

   • A1 & A3 – Currency (US $), 2 d.p.
   • A2 – Percentage, 1 d.p.
   • A4 – Custom format 0.00;[Red]-0.00 (negatives in red).

4. In B1 calculate the total of Sales using an absolute reference: =SUM(Sales).
5. In B2 calculate the average, rounded to one decimal place: =ROUND(AVERAGE(Sales),1).
6. Apply Conditional Formatting to A1:A4 so any value > 5000 is highlighted in light green.
7. Create a simple Column Chart of the values in A1:A4 and place it on a new sheet named “Report”.
8. Set the print area to A1:B4, choose Landscape orientation, Narrow margins, and enable Print Titles to repeat row 1 on each page.
9. Write a short evidence paragraph (≈ 100 words) describing each step you performed and the reason for the chosen formatting.

8. Assessment Questions (exam‑style)

1. What is the displayed value of 0.075 when formatted as a percentage with three decimal places?
2. How would you format a cell to show the value 1234 as £1,234.00?
3. Explain the difference between the Number and Accounting categories for currency formatting.
4. Write a formula that returns “Pass” if the value in C5 is ≥ 50, otherwise returns “Fail”.
5. Describe the steps to import a CSV file named data.csv and link the column “Revenue” to cell D2 in your workbook.
6. Using the data in cells A1:A5 (10, 20, 30, 40, 50), write a formula that returns the median value.
7. Give an example of a custom number format that displays positive numbers normally, negative numbers in red with a minus sign, and zero as the word “‑‑‑”.
8. Outline how you would set up an advanced filter to extract rows where the “Score” column is ≥ 80 and the “Region” column equals “North”.
9. Explain why changing the number of decimal places in a cell does not affect the result of a calculation that uses that cell.
10. Provide a brief (≈ 50 words) description of how you would document your spreadsheet solution for an examiner.