Charts in Excel

Excel has more chart types than most people ever use — and most dashboards only need three or four. The real skill is not knowing where every chart type lives in the ribbon; it is knowing how to build clean, readable charts that communicate clearly, format them to professional standard, and connect them to a data model so they update automatically. This lesson covers the charts you will use most in dashboard work: line, bar, combo, and sparklines — along with the formatting decisions that separate a polished chart from a default Excel output.

Creating a Chart — The Right Way

The fastest and most reliable way to create a dashboard chart is from a PivotTable or Power Pivot data model using a PivotChart. PivotCharts stay connected to the data and update automatically on refresh. For charts that do not need to respond to slicers, a regular chart built from a named range or Excel Table works well and is simpler to format.

Method 1 — PivotChart from a PivotTable (recommended for dashboards):
  Click anywhere inside a PivotTable
  Insert → PivotChart → choose chart type → OK
  The chart is linked to the PivotTable — slicers connected to the PivotTable
  automatically filter the chart too

Method 2 — PivotChart from the Data Model directly:
  Insert → PivotChart → "Use an External Data Source" → "Use this workbook's Data Model"
  Creates a standalone PivotChart with its own field list
  Slicers can be connected to it independently

Method 3 — Regular chart from a Table or named range:
  Select the data range or Table
  Insert → Charts → choose type
  Best for static reference charts or when PivotTable overhead is not needed

After inserting any chart:
  Move it to the dashboard sheet: right-click chart → Move Chart → Object in → Dashboard
  Resize by dragging corners while holding Alt to snap to cell grid
  Remove the chart border: right-click → Format Chart Area → No line

The Alt+drag behaviour is one of the most useful tips for dashboard building. When you hold Alt while dragging or resizing a chart, it snaps precisely to cell borders. This makes it effortless to align multiple charts to the same grid without pixel-hunting. Use it for every chart and KPI card placement on the dashboard.

Line Charts — Trends Over Time

Line charts are the default for time series data. They show direction and rate of change clearly. The key formatting decisions for a professional line chart are: removing gridlines or making them very subtle, thickening the data line, removing markers unless the data has few points, and ensuring axis labels are readable at the chart's intended size.

Key line chart formatting steps:

1. Remove chart border:
   Right-click chart area → Format Chart Area → Border → No line

2. Set background to match dashboard:
   Format Chart Area → Fill → Solid fill → match dashboard background colour

3. Thicken the line:
   Click the data line → Format Data Series → Line → Width → 2.5pt or 3pt

4. Remove markers (or keep only the last point):
   Format Data Series → Marker → Marker Options → None
   For endpoint only: add a separate data series with just the last value

5. Lighten gridlines:
   Click a gridline → Format Gridlines → Line → Solid line → Light grey (#E2E8F0)
   Width: 0.5pt. Or delete gridlines entirely for very clean charts.

6. Format axis labels:
   Click axis → Format Axis → Number format → Custom → "Jan", "Feb" etc
   Font size: 8–9pt · colour: #64748B (medium grey)

7. Add a data label to the last point only:
   Right-click the endpoint marker → Add Data Label
   Format the label: font size 8pt, bold, brand colour, position Above

A small but high-impact detail: label the last data point directly on the chart rather than relying on a legend. When a reader glances at the chart, their eye follows the line to its endpoint — if the value is labelled there, they get the number immediately without having to cross-reference a legend. Remove the legend entirely when there is only one data series.

Bar and Column Charts — Comparing Categories

Use horizontal bar charts when comparing categories — especially when the category labels are long text that would be cramped on a vertical column chart axis. Use vertical column charts for time series with a small number of periods (monthly bars, quarterly comparisons) where left-to-right time flow is intuitive. The most important formatting rule for both: sort the data. An unsorted bar chart forces the reader to search for the largest and smallest values. A sorted chart communicates the ranking instantly.

Key bar/column chart formatting steps:

Remove gap between bars (column charts — narrower bars look dated):
  Right-click bar → Format Data Series → Series Options
  Gap Width: reduce from 150% to 50%–80%

Colour bars by value (highlight top performer):
  Click once to select all bars → click again to select one bar
  Format Data Series → Fill → Solid fill → different colour for top bar
  This draws the eye to the standout value instantly

Add data labels inside bars:
  Right-click data series → Add Data Labels
  Format Data Labels → Label Position → Inside End (for horizontal bars)
  Font: white, 9pt bold

Remove axes when data labels are present:
  If every bar has a label, the value axis is redundant
  Click the axis → Delete  (keeps the chart clean)

Sort source data:
  Sort the underlying data table by value descending before building
  Or use a helper column with RANK to maintain dynamic sort order

A tonal colour scale on bar charts — using a graduated shade of the same colour from darkest (highest) to lightest (lowest) — is more visually sophisticated than using the same flat colour for every bar or a rainbow of unrelated colours. It communicates the ranking through both position and colour simultaneously, reinforcing the insight twice.

Combo Charts — Two Metrics, One Visual

A combo chart overlays two different chart types on the same plot area — typically columns for the primary metric (revenue, volume) and a line for a secondary metric (margin %, growth rate, target). This allows comparison of two measures with different scales on a single chart without wasting space on a second chart.

Creating a combo chart:

Method 1 — From existing chart:
  Right-click any data series in the chart → Change Series Chart Type
  In the dialog: set Revenue to Clustered Column, Margin % to Line
  Tick "Secondary Axis" for the Margin % series → OK

Method 2 — From scratch:
  Insert → Charts → Combo → Clustered Column - Line on Secondary Axis
  Drag in the data or set the series ranges manually

Key combo chart formatting:
  - Make the two series clearly distinguishable: bold purple columns + amber line
  - Format the secondary axis (right side) with % format: right-click axis → Format Axis → Number → Percentage
  - The secondary axis scale should be set manually: right-click → Format Axis → Bounds: 0 to 0.5 (for 0%–50%) to prevent misleading scaling
  - Keep both axes labelled if the units are different
  - A short legend or direct data labels is required — readers cannot guess which is which

The secondary axis scaling warning is important: Excel will auto-scale the secondary axis to fill the chart area, which can make a small change in margin look dramatic or make a large change look flat. Always set the secondary axis bounds manually to a sensible range — typically 0 to the maximum expected value plus a 20% buffer. This ensures the visual accurately represents the magnitude of change.

Sparklines — Micro Charts in Cells

Sparklines are tiny charts that live inside a single cell. They show a trend at a glance next to the data they represent — ideal for summary tables where you want to add visual context without a full chart. Sparklines have no axes, no labels, and no interactivity — they are purely visual indicators of direction and relative change.

Inserting sparklines:
  Select the cell(s) where the sparkline will live (e.g. F2:F10)
  Insert → Sparklines → Line (or Column, Win/Loss)
  Data Range: select the source data rows (e.g. B2:E10)
  Location Range: auto-filled with your selection → OK

Sparkline formatting (Sparkline tab appears):
  Show: tick High Point and Low Point to mark min/max
  Sparkline Color: set to brand colour or conditional colour
  Marker Color: High Point green, Low Point red

Axis settings (important for fair comparison):
  Sparkline tab → Axis → Vertical Axis Minimum Value → Same for All Sparklines
  Sparkline tab → Axis → Vertical Axis Maximum Value → Same for All Sparklines
  This ensures all sparklines use the same scale — without this setting
  a small fluctuation can look the same size as a large one

The axis normalisation setting is the most commonly missed sparkline configuration. By default, each sparkline scales independently to fill its cell — a region that declined by 2% and one that declined by 20% will look identical. Setting all sparklines to the same axis range makes the visual actually meaningful and comparable across rows.

Next up — Slicers in Excel, where we cover building interactive filter controls, connecting them across multiple charts and PivotTables, and designing a slicer panel that makes a dashboard genuinely easy to use.