Be able to format numerical values to a specified number of decimal places

Topic 16 – Graphs and Charts

Learning Objectives

• Create, label and edit a chart or graph.
• Format numerical values to a specified number of decimal places (including currency, percentages and custom symbols).
• Add a second data series, use a secondary axis and adjust scales.
• Enhance the appearance of charts (colours, patterns, exploded sectors) while keeping them readable and accessible.

1. Why Decimal Formatting Matters

• Ensures consistency across data labels, axis values and tables.
• Improves readability, especially when numbers are very large or very small.
• Prevents mis‑interpretation caused by excessive or insufficient precision.

2. Standard Rounding Rules (to n decimal places)

1. Identify the digit in the n‑th decimal place.
2. Look at the digit immediately to its right (the n+1‑th decimal place).
3. If the n+1‑th digit is 5 or greater, increase the n-th digit by 1.
4. If the n+1‑th digit is less than 5, leave the n-th digit unchanged.
5. Discard all digits to the right of the n-th place.

3. Display Formatting vs. Calculated Rounding

• Display formatting changes only how a number appears (e.g., 2 dp). The underlying value remains unchanged – this is the default in exam questions unless a rounded value is explicitly required.
• Calculated rounding (using ROUND, ROUNDUP, ROUNDDOWN) creates a new value that is actually rounded. Use it when the rounded figure must be used in further arithmetic (e.g., total sales).

4. Label Checklist (exam‑ready)

Every chart must contain the following elements. Tick each box when you finish the chart.

5. Quick‑Reference: Required Label Elements per Chart Type

6. Format Cheat‑Sheet (Excel / Google Sheets)

7. Practical – Applying Decimal Formatting in Spreadsheet Software

7.1 Formatting Cells (Display Only)

1. Select the cells containing the numbers you wish to format.
2. Right‑click → Format Cells… (Excel) or Format → Number → More Formats → Custom number format (Google Sheets).
3. In the Number category, set the required number of decimal places (e.g., 2). Choose Currency, Percentage or Custom as needed.
4. Click OK. The values shown on the sheet and in charts now have the chosen precision, while the underlying numbers stay unchanged.

7.2 Rounding Values for Calculations

Use the ROUND family of functions when the rounded figure must be used in later arithmetic.

• =ROUND(number, n) – round to n dp.
• =ROUNDUP(number, n) – always round up.
• =ROUNDDOWN(number, n) – always round down.

Example: =ROUND(A2,2) stores the value of A2 rounded to two decimal places.

7.3 Formatting as Currency, Percentage or Custom Symbols

• Currency: Choose the Currency category and select the appropriate symbol (e.g., £, $). Set decimal places (usually 2).
• Percentage: Choose Percentage and set decimal places (e.g., 1 dp gives 12.3%). Essential for pie‑chart data labels.
• Custom: Use a custom format such as 0.0" kg" to append units, or 0.00"E" for scientific notation.

8. Creating & Editing Charts

8.1 Step‑by‑Step Chart Creation

1. Enter your data in a clear tabular layout (one column for categories, one or more columns for series).
2. Select the entire data range.
3. Insert ► Chart (Excel) or Insert ► Chart (Google Sheets).
4. Choose the appropriate chart type (see Section 9).
5. Click OK / Insert**. The chart appears on the sheet.

8.2 Labelling the Chart (using the Checklist)

• Chart title – concise description of what the chart shows.
• Axis titles – include variable name, unit and decimal‑place notation (e.g., “Average temperature (°C, 1 dp)”).
• Data labels – enable via chart tools; set number format to match the source cells.
• Legend – only when more than one data series is displayed; keep wording short.

8.3 Chart‑type Selection (Cambridge‑recommended)

9. Adding a Second Data Series & a Secondary Axis

1. Enter the second series in an adjacent column.
2. Right‑click the chart → Select Data → Add a new series and specify its range.
3. To plot the new series on a secondary axis: right‑click the series on the chart → Format Data Series → check Plot Series on Secondary Axis.
4. Adjust the secondary axis scale (see Section 10) so both series are readable.

9.1 Axis‑scale Settings

• Right‑click the axis → Format Axis.
• Under Axis Options set:

  • Minimum and Maximum values (fixed or automatic).
  • Major unit – interval between tick marks.
  • Number format – choose the required decimal places, currency, or percentage.

• Use consistent intervals for primary and secondary axes to avoid distortion.

10. Chart Aesthetics & Accessibility (e‑Safety)

• Colour contrast: Dark text on a light background (or vice‑versa); avoid colour‑only cues for critical information.
• Pattern fills: Useful for black‑and‑white printing or for readers with colour‑vision deficiency.
• Exploding sectors in a pie chart can highlight a particular slice.
• Data‑label positioning: Place labels inside bars/segments when space permits; otherwise use leader lines.
• Limit the number of data series to a maximum of 4 for clarity.

11. Practice Questions

1. Round the following values to the indicated decimal places and write the result as it would appear on a chart axis:

   • 23.6789 – 0 dp → 24
   • 5.4321 – 1 dp → 5.4
   • 0.98765 – 3 dp → 0.988

2. The raw data for a line graph is:

   Month	Average Temperature (°C)
   Jan	4.236
   Feb	5.112
   Mar	7.894

   Apply a format of 1 dp to the temperature column. What values will be displayed on the graph?

   Answer: 4.2 °C, 5.1 °C, 7.9 °C

3. Explain why using too many decimal places on a pie‑chart data label can be misleading.
4. In a spreadsheet you have the following sales figures (in £): 123.456, 78.9, 45.678. You need the total for a report, rounded to the nearest pound. Show the correct method and the final total.

   Method: Use =ROUND(123.456,0)+ROUND(78.9,0)+ROUND(45.678,0) → 123 + 79 + 46 = 248 £.

12. Summary Checklist (Exam Quick‑Recall)

• Identify the most suitable chart type for the data.
• Create the chart: select data → Insert → Choose type.
• Label the chart using the Label Checklist (title, axis titles with units + dp, data labels, legend).
• Format numbers:

  • Decimal places – display formatting unless a rounded value is required for calculations.
  • Currency, percentage or custom symbols as the question specifies.
  • Use ROUND (or ROUNDUP/ROUNDDOWN) only when the rounded result will be used later.

• Add a second data series and, if needed, a secondary axis; set appropriate scales and matching number formats.
• Apply aesthetic enhancements (colour contrast, patterns, exploded sectors) while keeping the chart readable.
• Check for common pitfalls:

  • Rounding before summing vs. rounding the final total.
  • Mismatch between display formatting and calculated values.
  • Too many decimal places making labels illegible.

• Review the final chart for clarity, accuracy and accessibility before exporting or printing.