Be able to enhance the appearance of a graph or chart including extracting a pie chart sector, changing the colour scheme or fill patterns

Topic 16 – Graphs and Charts

Learning Objective

Students will be able to create a graph or chart from a data set, choose the most appropriate chart type, add labels, secondary axes and extra data series, and then enhance its appearance – including colour‑scheme selection, fill‑pattern use, exploding a pie‑chart sector, adding trendlines or error bars, and preparing the chart for printing or digital presentation.

1. Creating a Chart from Data

1.1 Selecting the Data

• Open the worksheet that contains the data.
• Contiguous range: click the first cell and drag to the last (e.g. A1:C7).
• Non‑contiguous range: hold Ctrl (Windows) or ⌘ (Mac) and click each block you need.
• Named ranges (optional): define a name via Formulas ► Define Name and use the name in the chart wizard – useful for dynamic charts.
• Ensure the first row (or column) contains labels; these become axis titles or legend entries automatically.

1.2 Inserting the Chart

1. Select the required data range.
2. Go to Insert ► Chart (or Chart Wizard in older spreadsheet packages).
3. In the preview window choose the chart type that matches the data (see Section 1.3).
4. Click OK/Insert. The chart appears on the worksheet and is automatically selected.

1.3 Choosing the Right Chart Type

1.4 Using Chart Templates & Quick‑Style Options

• After creating a chart, go to Chart Design ► Save as Template to reuse the same formatting on other data sets.
• Apply a built‑in Quick Style (e.g., “Style 1”, “Style 2”) to change colours, fonts and effects with one click.
• Templates are especially useful for exam questions where a specific layout is required.

2. Adding Additional Data Series, Secondary Axes and Supporting Features

1. Select the chart, then choose Chart Design ► Select Data (or “Edit Data”).
2. Click Add and specify the range for the new series (e.g., D2:D7).
3. To plot the new series on a secondary axis:

   1. Right‑click the series → Format Data Series.
   2. In the pane, choose Series Options ► Plot Series on Secondary Axis.
   3. Adjust the secondary axis scale if required (right‑click the axis ► Format Axis → set Minimum, Maximum, Major unit).

4. Optional supporting features:

   • Data Table: Chart Elements ► Data Table adds a table of values beneath the chart.
   • Trendline: right‑click a data series → Add Trendline. Choose Linear, Exponential, etc., and optionally display the equation and R².
   • Error Bars: right‑click the series → Add Error Bars** → specify Fixed value, Percentage, or Custom range.
   • Data Markers (for line or scatter charts): Format Data Series ► Marker Options** – choose shape, size and colour.

3. Labelling a Chart

• Chart Title – Chart Elements ► Chart Title. Keep it concise (e.g., “Quarterly Sales 2024”).
• Axis Titles – Chart Elements ► Axis Titles. Use units (e.g., “Revenue (£ 000)”).
• Axis Scaling & Tick Marks – right‑click an axis ► Format Axis. Set:

  • Minimum and Maximum values.
  • Major and Minor unit intervals.
  • Tick‑mark type (inside, outside, cross).

• Gridlines – Chart Elements ► Gridlines. Show major gridlines for readability; hide minor gridlines unless needed for precision.
• Legend – Chart Elements ► Legend. Position where it does not obscure data (top‑right, bottom‑center, etc.).
• Data Labels – Chart Elements ► Data Labels. Choose format (value, percentage, custom). For pie charts, percentages are usually most helpful.
• Data Table – optional, adds a numeric table beneath the chart (useful for printed reports).

4. Enhancing the Appearance of a Chart

4.1 Colour Schemes – Including Colour‑Blind‑Safe Palettes

1. Select the chart.
2. Open Chart Design ► Change Colours and pick a built‑in theme or choose Customize Colours** to define your own palette.
3. For examinations, colour‑blind‑safe palettes are recommended (see Table 2).
4. If a single series needs a different colour, click that series → Format Data Series ► Fill ► Solid colour**.

4.2 Applying Fill Patterns (Print‑Friendly)

Use patterns when colour alone may be lost in black‑and‑white printing.

1. Select the data series (or a single data point).
2. Open Format Data Series ► Fill & Line ► Fill ► Pattern fill.
3. Choose a pattern (horizontal stripes, vertical stripes, diagonal cross‑hatch, dots, etc.) and set foreground/background colours.

4.3 Exploding (Extracting) a Pie‑Chart Sector

1. Click the pie chart to select the whole chart.
2. Click a second time on the slice you wish to emphasise.
3. Drag the slice outward, or right‑click → Format Data Point** and set an Explosion distance (e.g., 10 pt).
4. Optionally change that slice’s colour or apply a pattern to make it stand out further.

4.4 Adding Trendlines, Error Bars and Data Markers

• Trendline: right‑click a series → Add Trendline**. Choose type, display equation and R² if required for analysis.
• Error Bars: right‑click a series → Add Error Bars**. Select Fixed value, Percentage, or Custom range to show measurement uncertainty.
• Data Markers (line/scatter charts): Format Data Series ► Marker Options**. Choose shape (circle, square, diamond), size and colour to highlight individual points.

4.5 Print‑Friendly and Colour‑Blind Checks

1. Switch to Print Preview and view the chart in greyscale – patterns should remain distinct.
2. Use a colour‑blind simulator (or the built‑in “Colour‑Blind” view in some spreadsheet programs) to confirm that no two adjacent series rely on indistinguishable colours.
3. Keep font size ≥ 10 pt for readability when printed on A4.

5. Exporting and Embedding Charts

1. Copy as Picture:

   • Select the chart, then choose Copy ► Copy as Picture….
   • In the dialog, select Picture and Screen (or Printer for higher resolution).
   • Paste into Word, PowerPoint or a PDF document.

2. Export as Image File:

   • Right‑click the chart → Save as Picture….
   • Choose PNG (recommended for lossless quality) or JPEG (smaller file size).
   • Specify a resolution (e.g., 300 dpi for printed reports).

3. Embedding in a Document:

   • In Word/PowerPoint use Insert ► Object ► From File and select the saved image.
   • Resize using the corner handles while holding Shift to maintain aspect ratio.
   • For live data updates, embed the chart as an Excel Object (Insert ► Object ► Create from File ► Link).

6. Practical Example – Enhancing a Column Chart (Colour‑Blind Friendly)

Data set: Quarterly sales (in £ 000) for 2024

1. Create the chart – select A1:B5 (Quarter, Sales) → Insert ► Column chart.
2. Apply a colour‑blind‑safe palette – Chart Design ► Change Colours ► “Blue‑Orange”.
3. Highlight Q2:

   • Click the Q2 column, open Format Data Point ► Fill ► Pattern fill.
   • Choose “Diagonal cross‑hatch”, foreground colour #D55E00 (orange), background #FFFFFF.

4. Add a secondary axis for profit % (data in column C):

   • Chart Design ► Select Data ► Add series → range C2:C5.
   • Right‑click the new series → Format Data Series ► Plot Series on Secondary Axis.
   • Set secondary axis Minimum = 0, Maximum = 30, Major unit = 5.

5. Insert a trendline for sales – right‑click the sales series ► Add Trendline ► Linear ► check “Display Equation on chart”.
6. Label the chart:

   • Chart Title: “Quarterly Sales & Profit 2024”.
   • Axis titles: “Sales (£ 000)” (primary), “Profit (%)” (secondary).
   • Data Labels: Inside End for sales, show value; for profit series, Outside End, show percentage.

7. Check print‑friendliness – preview in greyscale; Q2’s cross‑hatch remains visible.
8. Export – right‑click ► Save as Picture… → PNG, 300 dpi.

7. Checklist – Before Submitting Your Chart

• Data range correctly selected (contiguous, non‑contiguous, or named).
• Appropriate chart type chosen (including histogram, bubble, 100 % stacked where relevant).
• All required data series plotted; secondary axis added if units differ.
• Axis titles, chart title, legend and data labels are present and use correct units.
• Axis scaling, tick‑marks and gridlines set for easy reading.
• Colour scheme is consistent, colour‑blind safe, and matches any provided template.
• Fill patterns used where colour alone may be ambiguous (especially for B&W printing).
• Key slice/series highlighted (exploded pie slice, patterned bar, distinct marker, etc.).
• Trendlines, error bars or data markers added if the task requires analysis.
• Chart fits neatly on the page – no overlapping text, sufficient white space, legible font size (≥ 10 pt).
• Print‑friendly test completed (greyscale view, colour‑blind check).
• Chart exported or embedded in the required document format (PNG, JPEG, or linked Excel object).