Be able to format numerical values to display currency symbols

Topic 16 – Graphs and Charts

Objective

Be able to create, label and edit a graph or chart, and format the numerical values it displays – including currency symbols, decimal places, thousand separators and percentages.

Why proper formatting matters

• Prevents mis‑interpretation (e.g. 5000 vs. $5,000).
• Ensures consistency across tables, charts and written reports.
• Meets Cambridge IGCSE ICT assessment objectives (AO2 – apply skills; AO3 – evaluate presentation).

Quick‑check checklist for creating a chart

1. Select the data range – contiguous or non‑contiguous cells.
2. Choose an appropriate chart type – see the Chart‑type decision matrix.
3. Add a second data series or secondary axis if you need to compare two different units (see Secondary‑axis mini‑procedure).
4. Edit chart elements:

   • Chart title – clear, concise, and always include the unit (e.g. “Monthly Sales (£)”).
   • Axis titles – describe what each axis represents and include units.
   • Legend – only if more than one data series.
   • Data labels – turn on if the exam asks for exact values on the chart.

5. Format the source data (currency, percentages, decimal places) – see the Formatting numerical values section.
6. Adjust the axis scale – set minimum, maximum and major/minor units via Format Axis → Number (see Adjusting axis scales).
7. Enhance appearance – colour scheme, fill, explode a pie‑slice, position data labels.

Chart‑type decision matrix

Secondary‑axis mini‑procedure

1. Select the chart, then click the data series you want to move.
2. Right‑click ► Format Data Series.
3. In the Series Options pane, choose Plot Series on Secondary Axis.
4. Adjust the secondary axis scale if required (see Adjusting axis scales).
5. Give the secondary axis a clear title with the appropriate unit (e.g. “Units sold (pcs)”).

Formatting numerical values

All formatting can be done in the Format Cells dialog (right‑click ► Format Cells or use the toolbar). The three most common categories are:

Step‑by‑step: Apply a currency format

1. Select the cell(s) containing the numeric values.
2. Open Format Cells ► Number tab.
3. Choose the Currency category.
4. Select the required symbol from the drop‑down list.
5. Set the number of decimal places (normally 2).
6. Choose how negative numbers appear (red, minus sign, or parentheses).
7. Click OK.

Custom number‑format codes (useful for exam questions)

Using TEXT to return a formatted text string

When you need the formatted value for concatenation (e.g., chart label or report text), use:

=TEXT(A2,"$#,##0.00")

Note: The result is a text string and cannot be used directly in further calculations.

Applying formatting to chart data

• Format the source cells first (as described above). Most spreadsheet programs inherit this format for axis labels and data labels.
• If the currency symbol does not appear on an axis or data label:

  1. Select the axis (or data labels) in the chart.
  2. Open Format Axis → Number (or Format Data Labels → Number).
  3. Choose the same custom format code (e.g., $#,##0.00).

Adjusting axis scales

Examination tasks often require a specific scale. Use the Format Axis dialog to:

• Set a fixed minimum and maximum value.
• Define the major unit (interval between gridlines).
• Optionally set a minor unit for finer gridlines.

When to fix the scale: If the question states a required range (e.g., “axis from 0 to 20,000 in steps of 5,000”) you must override the automatic setting. If no range is given, the default “auto” is acceptable.

Enhancing chart appearance

• Colour schemes – apply a consistent palette that matches the document’s style.
• Fill & border – solid fill for columns, thin border for clarity.
• Explode a pie slice – click the slice and drag outward to highlight a key data point.
• Data‑label positioning – inside, outside, or centred depending on readability.

Example: Bar chart of monthly sales (currency) with a secondary axis for units sold

1. Apply the custom currency format £#,##0.00 to the “Sales” column.
2. Apply a number format with thousand separator (no decimals) to the “Units sold” column.
3. Select A1:C4 and insert a Column (Clustered) chart.
4. Right‑click the “Units sold” series → Change Series Chart Type → choose Line and tick “Secondary Axis”.
5. Give the chart the title “Monthly Sales (£) and Units Sold (pcs)”.
6. Format the primary (vertical) axis:

   • Number → Custom → £#,##0.00.
   • Set Minimum = 0, Maximum = 15,000, Major unit = 5,000.

7. Format the secondary axis:

   • Number → Custom → 0 (no decimals).
   • Set Minimum = 0, Maximum = 500, Major unit = 100.

8. Turn on data labels for both series – they will display the formatted values automatically.
9. Apply a simple two‑colour scheme (e.g., blue columns, orange line) and check that all titles and axis labels include units.

Troubleshooting box

Key points to remember for the exam

• Choose the chart type that best matches the data relationship (see the decision matrix).
• Apply the required currency (or percentage) format before creating the chart.
• Always include the unit in chart titles, axis titles and legends.
• If the exam specifies a scale, set Minimum, Maximum and Major unit manually; otherwise, the auto‑scale is acceptable.
• When a secondary axis is needed, follow the mini‑procedure and label it with its own unit.
• Use colour, fill and label positioning to aid readability, but avoid unnecessary decoration.

Link to Cambridge IGCSE ICT assessment objectives

• AO2 – Apply skills: Selecting data, choosing the correct chart type, creating a secondary axis, formatting cells, adjusting axis scales and applying colour schemes are all practical skills tested in the exam.
• AO3 – Evaluate presentation: Selecting an appropriate chart, adding clear titles/labels with units, using consistent currency formatting and choosing a suitable scale demonstrate an understanding of effective data presentation.