22.1 Creating a Chart

Method 1 — Recommended Charts (Fastest)

1. Select the data you want to chart — include headers. For non-contiguous selections, hold Ctrl and click/drag each range.
2. Insert tab → Charts group → Recommended Charts
3. Excel analyses your data and suggests the most appropriate chart types with previews
4. Click any suggestion to preview it → click OK to insert it

Method 2 — Choosing a Specific Chart Type

1. Select your data (including headers)
2. Insert tab → Charts group → click the specific chart type button (Column, Bar, Line, Pie, etc.)
3. A dropdown shows subtypes → click the desired subtype
4. The chart is inserted as a floating object on the active sheet

Method 3 — Keyboard Shortcut

• Select data → press Alt+F1 → inserts the default chart type (usually a clustered column chart) on the same sheet
• Press F11 → inserts the default chart on a separate chart sheet

Data Selection Tips

• Select data and headers together — Excel uses column/row headers as axis labels and legend entries
• For multiple non-adjacent columns: select the first range → hold Ctrl → select additional ranges
• For a chart from an Excel Table: click inside the Table → Excel's chart will automatically grow as new rows are added to the Table
• Do not include grand totals in your selection — they visually dwarf the individual data series

22.2 Chart Types — Choosing the Right Chart

Choosing the wrong chart type misrepresents data. This table covers the most commonly used chart types and their correct use cases.

22.3 Understanding Chart Elements

Every chart is composed of several elements, each of which can be formatted or hidden independently. Clicking directly on any element selects it for formatting.

22.4 The Chart Design Tab

When a chart is selected, the Chart Design contextual tab appears in the Ribbon. It controls the chart's data, layout, and overall style.

Chart Design Tab Groups

Add Chart Element — Important Options

22.5 The Format Tab & Formatting Chart Elements

The Format contextual tab appears alongside Chart Design when a chart is selected. It controls the visual appearance of individual selected elements.

Selecting and Formatting Individual Elements

1. Click the chart to activate it
2. Click the specific element to format (a bar, a legend, an axis, the title)
3. Use the Format tab in the Ribbon — or — right-click the element → "Format [Element Name]…"
4. The Format pane opens on the right side of the screen with formatting options for that element

Format Pane — Fill & Line Tab

• Fill: No fill, Solid fill (pick any colour), Gradient fill (multi-stop colour blend), Texture fill, Picture fill (image inside a bar), Pattern fill
• Border/Line: Colour, width (in pt), dash style (solid, dashed, dotted), rounded ends, compound type

Format Pane — Effects Tab

• Shadow, Glow, Soft Edges, 3-D Format, 3-D Rotation — use these sparingly. Simple charts communicate better than overly decorated ones.

Formatting Data Series

1. Click any bar/line/slice to select the entire series
2. Right-click → Format Data Series…
3. Key options:
   • Gap Width (column/bar charts): controls the space between bar clusters. Narrow gap = wide bars, wider gap = thin bars. 150% is the default; 50–80% gives wider, more impactful bars.
   • Series Overlap: how much adjacent series bars overlap. Negative = gap between them; 0 = touching; positive = overlapping.
   • Plot Series On: Primary Axis or Secondary Axis (for combo charts)

Formatting Axes

1. Click the axis to select it → right-click → Format Axis…
2. Key options:
   • Minimum/Maximum bounds: set manually instead of letting Excel auto-scale. Fixing the minimum at a non-zero value can make small differences look large — use this carefully and honestly.
   • Major/Minor unit: the tick-mark interval (e.g., every 50,000 for a 0–500,000 axis)
   • Display units: show values in Thousands, Millions, or Billions — very useful for large financial figures
   • Number format: apply a custom format (e.g., R#,##0 for Rand values on the axis)
   • Category axis: tick "Categories in reverse order" to flip the axis direction
   • Label position: Next to Axis (default), High (far from axis), Low, None
   • Tick marks: Inside, Outside, Cross, None — for both major and minor

22.6 Combination Charts & Secondary Axis

A combo chart plots two data series using different chart types — typically columns for one series and a line for another. A secondary axis appears on the right side of the chart, allowing two series with very different scales to be plotted on the same chart.

Creating a Combo Chart

1. Select all data including both series and headers
2. Insert → Charts group → Combo Chart button — or — Insert → Recommended Charts → All Charts → Combo
3. The Insert Chart dialog opens showing each series with a dropdown to set its chart type and a checkbox for Secondary Axis
4. Set the primary series (e.g., Revenue) as: Clustered Column, Primary Axis
5. Set the secondary series (e.g., Growth Rate %) as: Line, Secondary Axis
6. Click OK

Adding a Secondary Axis to an Existing Chart

1. Click the data series that needs a secondary axis (e.g., click the growth rate line)
2. Right-click → Format Data Series…
3. In the Format pane → Series Options → select Secondary Axis
4. A second Y axis appears on the right with its own scale

SA Example — Revenue and Growth Rate

22.7 Moving, Sizing & Embedding Charts

Moving a Chart

• Within the same sheet: click the chart border (not inside it) → drag to the new position. Hold Alt while dragging to snap the chart to cell gridlines.
• To a dedicated chart sheet: click the chart → Chart Design → Move Chart → select New Sheet → name the sheet → OK. The chart becomes a full-page object on its own sheet.
• Between sheets: right-click the chart border → Move or Copy (similar to moving sheets) — or — Chart Design → Move Chart → Object In → choose the target sheet.

Resizing a Chart

• Drag any corner or edge handle of the selected chart to resize. Corner handles maintain aspect ratio when dragging.
• Precise size: Chart Format tab → Size group → type exact Height and Width values in pt or cm
• Lock to cell grid: right-click the chart → Format Chart Area → Properties → select "Move and size with cells" to lock the chart to the underlying cells

Copying a Chart

• Click the chart border → Ctrl+C → click a new location → Ctrl+V
• The copied chart is independent — changes to one do not affect the other
• To copy a chart as a static image (unlinked from data): copy → Paste Special → Picture — useful for PowerPoint/Word embedding

22.8 Chart Design Best Practices

A chart should communicate one clear message instantly. These principles help you create professional, honest, and readable charts.

Dos

• Use a descriptive title: state the key insight in the title ("Gauteng Revenue Leads All Regions in Q2 2025") rather than a generic label ("Regional Revenue")
• Always show units: label axes with the unit of measurement (R thousands, %, kg, number of units)
• Start the Y axis at zero for column and bar charts — truncating the axis exaggerates differences and misleads the audience
• Limit data series: 3–5 series maximum per chart. If you have more, consider small multiples or a different visualisation
• Use colour purposefully: highlight the key series in a bold colour; show comparison series in a muted grey
• Sort bar charts: for ranked comparisons (not time-series), sort bars from largest to smallest for easy visual ranking
• Use direct labels instead of a legend when possible — label the lines or bars directly to eliminate the cognitive work of looking at the legend

Don'ts

• Never use 3D charts for analytical work — the perspective distorts proportions and makes values harder to read
• Avoid decorative gridlines: use light grey or remove gridlines entirely — they should aid reading, not dominate the visual
• Do not use pie charts with many slices: more than 5–6 slices are unreadable — use a bar chart instead
• Avoid dual-axis charts unless necessary: they are hard to read; only use them when two series with different units genuinely need to be compared on the same chart
• Remove chart junk: borders around the chart area, heavy gridlines, over-use of shadow/glow effects, unnecessary 3D formatting all reduce clarity

22.9 Quick Self-Check

Q1: You have monthly revenue data for 12 months in two columns (Month, Revenue). Which chart type is most appropriate, and why? What chart type would be wrong to use, and why?

✓ A Line chart is most appropriate because it shows continuous data (revenue) changing over time (months), making the trend and direction of change immediately visible. The connecting line emphasises the continuous nature of time. A Column chart is also acceptable for monthly data when the exact value at each month is more important than the trend direction. Wrong choice: a Pie chart would be completely wrong — pie charts show proportions of a whole at a single point in time, not data changing over a time series. Twelve monthly revenue slices would be impossible to interpret meaningfully as a pie. Another wrong choice: an XY Scatter chart, which is for correlations between two numerical variables, not time-series data with categorical months on the X axis.

Q2: Your bar chart of department headcounts has bars that look very thin with wide gaps between them. How do you make the bars wider without changing the chart size?

✓ Click any bar to select the data series → right-click → Format Data Series → in the Series Options panel, reduce the Gap Width slider. The default Gap Width is 150% (the gap between bar clusters is 150% of the bar width). Reduce it to 50–80% to make the bars significantly wider. As you drag the slider, the bars update in real time so you can preview the effect. A narrower Gap Width creates more visually impactful, easier-to-read bars without changing the chart's overall dimensions.

Q3: You need a chart that shows monthly revenue in columns AND the month-on-month growth rate as a line on the same chart. The revenue ranges from R800,000 to R1,200,000 and the growth rate ranges from -5% to +15%. How do you set this up?

✓ This requires a combo chart with a secondary axis. Select all data (months, revenue, and growth rate including headers) → Insert → Combo Chart (or Recommended Charts and find the combo option). In the dialog: set Revenue series to Clustered Column on the Primary Axis; set Growth Rate series to Line (with markers optional) on the Secondary Axis (tick the checkbox). Click OK. The primary (left) Y axis auto-scales for revenue (e.g., 0 to R1,500,000); the secondary (right) Y axis auto-scales for growth rate (e.g., -10% to 20%). Both series now share the same month (X) axis but have independent Y scales, making the comparison meaningful.

Q4: Your column chart has a Y axis that starts at R800,000 instead of zero, making a small difference between two columns look like one is double the other. How do you fix this to create an honest chart?

✓ Click the Y axis to select it → right-click → Format Axis → in the Axis Options panel, find the Bounds section → change the Minimum from the automatic value to 0 (type 0 in the Minimum field and press Enter). The Y axis now starts at zero, showing the true proportional difference between the columns. A non-zero Y axis on a column or bar chart is one of the most common chart deceptions — it visually exaggerates differences that may be minor in reality. Always start column and bar chart Y axes at zero unless you have a specific analytical reason not to and you clearly disclose this.

Q5: You have created a chart on Sheet1 that you want to move to a dedicated sheet called "Revenue Chart" so it fills the full page and can be printed separately. How do you do this?

✓ Click the chart to select it → Chart Design contextual tab → Location group → Move Chart → select New Sheet → in the name field type "Revenue Chart" → click OK. The chart is removed from Sheet1 and placed on a new dedicated sheet named "Revenue Chart". On this sheet, the chart fills the entire page and prints as a full-page chart automatically. The chart remains linked to the original data on Sheet1 — any data changes update the chart. To print the chart sheet: click the Revenue Chart tab → Ctrl+P → the chart prints full-page.

Q6: What does the "Switch Row/Column" button on the Chart Design tab do, and when would you use it?

✓ Switch Row/Column transposes which dimension of your data is plotted as the category axis and which is grouped as the data series. For example: if you select a table with regions in rows (Gauteng, Western Cape, KZN) and quarters as columns (Q1, Q2, Q3, Q4), Excel may initially plot regions as the series (grouped bars) with quarters on the X axis. Clicking Switch Row/Column flips this: now quarters are the series (Q1 column, Q2 column, etc.) and regions appear on the X axis — a completely different chart from the same data. Use it when Excel's initial interpretation of your data is not the view you need, or when you want to switch between a "comparison across time" view and a "comparison across categories" view. It is often the fastest fix when your chart does not look as expected.

✓ Module 22 Complete — You Have Learned:

• Creating charts — 3 methods (Recommended Charts, specific type button, Alt+F1 / F11 shortcuts); data selection tips (include headers, Ctrl for non-adjacent, avoid totals, Tables auto-grow)
• Chart types — 15-type reference table: Column (clustered, stacked, 100%), Bar, Line, Area, Pie (with rules: max 5–6 slices, single series, parts of a whole), Doughnut, XY Scatter, Bubble, Waterfall, Histogram, Box & Whisker, Funnel, Combo; pie chart rules and why to avoid 3D pies
• Chart elements — all 12 elements identified (Chart Area, Plot Area, Title, Data Series, Data Labels, Legend, Axes, Axis Titles, Gridlines, Trendline, Error Bars); how to select each for formatting
• Chart Design tab — Quick Layout; Add Chart Element (chart title best practice, axis titles with units, data labels positions, data table, legend, gridlines, trendline with R²); Chart Styles gallery; Change Colors; Switch Row/Column; Select Data; Change Chart Type; Move Chart
• Format tab — selecting elements; Format pane (Fill: solid/gradient/picture/pattern; Border; Effects); formatting data series (Gap Width, Series Overlap, secondary axis); formatting axes (min/max bounds, units, display units in thousands/millions, number format, tick marks, label position)
• Combo charts — creating from Insert dialog with series type and axis assignment; adding secondary axis to existing chart (Format Data Series → Secondary Axis); SA revenue + growth rate example
• Moving, sizing, embedding — drag to move (Alt to snap to grid); Move Chart to new chart sheet or between worksheets; resize by dragging handles or precise size in Format tab; copy as image for PowerPoint/Word
• Best practices — descriptive insight-driven titles; always label units; Y axis starts at zero for column/bar; max 3–5 series; purposeful colour; sort bar charts for rankings; direct labels over legend; never 3D analytical charts; avoid decorative chart junk