Charts & Visualisations

Why Charts?

Numbers in a table require active reading. A well-designed chart communicates the pattern instantly — the trend, the comparison, the outlier. Excel has 17 chart types and hundreds of customisation options. The key is choosing the right chart type for the data story you're telling.

Choosing the Right Chart Type

What you want to show	Best chart type
Compare values across categories	Column or Bar chart
Show trends over time	Line chart
Show part-to-whole proportions	Pie or Doughnut chart
Compare two numeric variables	Scatter (XY) chart
Show distribution	Histogram
Show range (min-max)	Box and Whisker
Multiple metric types	Combo chart
Compact in-cell trend	Sparkline
Progress toward a target	Gauge / Bullet chart
Geographic data	Map chart (Excel 365)

Creating a Chart

Quick Method

1. Select the data range (include headers)
2. Press Alt + F1 — inserts a chart on the current sheet
3. Or: F11 — inserts chart on a new chart sheet

Recommended Charts

1. Select your data range
2. Insert → Recommended Charts — Excel suggests chart types based on your data
3. Choose one from the suggestions or click All Charts for full selection

Manual Selection

1. Select data
2. Insert → Charts group → click the chart type icon

Column and Bar Charts

Column: vertical bars — best for comparing across categories or over time (short time series) Bar: horizontal bars — same as column but better when you have many categories or long category labels

Sales by Quarter:
Q1: ₹45 lakh
Q2: ₹62 lakh
Q3: ₹58 lakh
Q4: ₹79 lakh
→ Column chart: Q4 peak is immediately obvious

Clustered vs Stacked

• Clustered: Bars side by side — best for direct comparison across sub-groups
• Stacked: Bars stacked — best for showing total and sub-group contribution
• 100% Stacked: Shows proportions within each column — like multiple pie charts in a row

Line Charts

Show trends over time. Best when you have many time points (weekly, monthly over a year+).

Monthly revenue for 12 months + forecast for next 3 → Line chart with dashed forecast line

Line Chart Formatting Tips

• Smooth line: right-click series → Format Data Series → Smooth line
• Data labels on key points only (not every point — too cluttered)
• Add a horizontal reference line (e.g., target) as a separate series with just the target value repeated

Pie and Doughnut Charts

Show part-to-whole. Use only when:

• You have 2–6 slices (more than 6 = hard to read)
• The slices add up to a meaningful 100%

Market share:
Product A: 42%
Product B: 28%
Product C: 18%
Other: 12%
→ Pie chart works here

Doughnut: Like a pie but with a hole — the hole can contain the total value or a KPI.

Avoid: Exploded pie, 3D pie, pie-of-pie (usually the wrong choice).

Scatter (XY) Charts

Shows the relationship between two numeric variables:

X-axis: Years of experience
Y-axis: Salary
Each dot = one employee

→ Scatter chart shows whether more experience correlates with higher salary
→ Add a trendline to visualise the relationship

Adding a Trendline:

1. Right-click any data point → Add Trendline
2. Choose: Linear, Exponential, Polynomial, Moving Average
3. Check Display R-squared value to see how well the line fits

Formatting Your Chart

Chart Elements (+ button)

Click the chart → the + button appears (top-right) → toggle:

• Chart Title — click to type directly
• Axis Titles — label both axes
• Data Labels — show values on bars/points
• Legend — show/hide; move position
• Gridlines — major/minor horizontal/vertical
• Trendline — add for scatter/line charts
• Error Bars — show standard deviation or standard error

Formatting Pane

Double-click any chart element → Format pane appears on the right:

• Fill: solid colour, gradient, pattern, no fill
• Border: colour, width, dash style
• Effects: shadow, glow, soft edge, 3D

Colour Themes

Chart Design → Change Colors — apply a consistent palette matching your brand.

Data Series Colours

Right-click a bar/series → Format Data Series → Fill → change colour per series or per point.

Moving and Resizing Charts

• Move: click chart → drag (or Cut → Paste to another sheet)
• Resize: drag corner handles
• Move to its own sheet: Right-click chart border → Move Chart → New Sheet

Dynamic Chart Titles

Link chart title to a cell so it updates automatically:

1. Click the chart title
2. Type = in the formula bar
3. Click the cell containing the title text (e.g., =Sheet1!B1)

B1 = "Q2 2026 Revenue by Department"
Chart title now mirrors B1 — update B1 and the chart title updates instantly

Combo Charts — Two Chart Types in One

Show two different data types (e.g., revenue bars + growth rate line):

1. Select data with both series
2. Insert → Combo Chart → Custom Combination
3. Set each series independently: Bar for Revenue, Line for Growth %
4. Add a Secondary Axis for the line (if scales differ greatly)

Primary axis (left): Revenue in ₹ lakhs (bar)
Secondary axis (right): Growth % (line)

Or from an existing chart:

1. Right-click one series → Change Series Chart Type
2. Set to Line → check Secondary Axis

Sparklines — In-Cell Mini Charts

Sparklines are tiny charts inside a single cell, perfect for dashboard tables:

1. Select a blank cell at the end of a row
2. Insert → Sparklines → Line (or Column, Win/Loss)
3. Select the data range for that row
4. Click OK

Month:    Jan   Feb   Mar   Apr   May   Sparkline
Revenue:  45    62    58    79    91    [tiny line chart]

Sparkline Customisation

Sparkline tab (appears when sparkline is selected):

• High Point / Low Point / First Point / Last Point — highlight with different colours
• Markers — show dots at every data point
• Axis — set same scale across multiple sparklines (for fair comparison)

Practical Examples

Example 1: Monthly Revenue Dashboard

Data: Month (Jan-Dec), Revenue, Target, Previous Year

Chart setup:
- Revenue: Column chart (primary axis)
- Target: Line chart (primary axis) — horizontal dashed target line
- Previous Year: Area chart (secondary axis, transparent fill)

Elements:
- Title: ="Revenue Dashboard — "&TEXT(MAX(A2:A13),"YYYY")
- Data labels on: only the last bar (current month)
- Legend: below chart

Example 2: Employee Salary Distribution Histogram

Data: all employee salaries

Insert → Statistical → Histogram
Bins: automatic or custom (₹60k, ₹70k, ₹80k, ₹90k, ₹100k+)

Immediately see: is the distribution skewed? Where is most of the workforce?

Example 3: Departmental KPI Sparkline Table

Table:
Department  | Jan | Feb | Mar | Apr | May | Jun | Trend
Finance     | 78  | 82  | 79  | 85  | 88  | 91  | [sparkline]
Technology  | 95  | 92  | 98  | 102 | 99  | 108 | [sparkline]
Marketing   | 68  | 71  | 70  | 75  | 73  | 78  | [sparkline]

Sparklines: Insert → Line sparklines → colour High Point green, Low Point red

Example 4: Scatter Plot — Experience vs. Salary

X: Years of experience (column E)
Y: Salary (column C)

Insert → Scatter → Scatter with only Markers
Add Trendline: Linear, show equation, show R²
→ R² = 0.72 → 72% of salary variance explained by experience

Chart Design Best Practices

1. One message per chart — don't cram 5 metrics into one chart
2. Start axes at zero for bar charts (truncating makes small differences look huge)
3. Avoid 3D charts — they distort proportions
4. Label directly where possible — data labels > legend when fewer than 4 series
5. Consistent colours — same category = same colour across all charts
6. Remove gridline clutter — keep only major horizontal gridlines on bar/line charts
7. Title describes the insight — "Q4 Revenue Surpasses Target" > "Revenue Chart"

Common Mistakes

1. Wrong chart for the data

Pie chart with 10 slices, 3D bar chart, line chart for unordered categories — choose based on the data relationship, not aesthetics.

2. Non-zero axis baseline

Bar charts starting at 50,000 instead of 0 make small differences look dramatic.
Fix: Right-click axis → Format Axis → Minimum → 0 (for bar/column)
Exception: Line charts can start at a meaningful non-zero baseline for trend focus.

3. Selecting non-contiguous data that includes total rows

Select rows 2–11 AND a "Grand Total" row → the total bar dwarfs all others
Fix: Exclude total rows from chart data range

4. Chart not updating when data changes

Check: is the chart's data range still pointing to the right cells? If you inserted/deleted rows, the range may have shifted.

Practice Exercises

1. Create a clustered column chart comparing Q1 and Q2 revenue for each department. Add data labels showing the actual values.
2. Create a line chart showing monthly sales for the full year, with a second series showing the previous year for comparison.
3. Build a scatter chart of salary vs. years of experience for 10 employees. Add a linear trendline and display the R-squared value.
4. Insert sparklines in the rightmost column of a monthly data table (one sparkline per department row). Highlight the high and low points.
5. Create a combo chart: column chart for monthly revenue + line chart for cumulative revenue (use secondary axis for the line).

Summary

In this chapter you learned:

• Choose chart types by data story: Column/Bar = compare; Line = trend; Pie = proportion; Scatter = relationship; Histogram = distribution
• Create charts: select data → Insert → chart type, or Alt+F1 for quick insert
• Chart elements: + button toggles title, axis titles, data labels, legend, trendline, gridlines
• Format: double-click any element → Format pane for fill, border, effects
• Combo chart: two chart types in one series panel; add secondary axis for scale differences
• Dynamic title: click chart title → type = → reference a cell
• Sparklines: Insert → Sparklines → Line/Column; fits inside a single cell; great for dashboard tables
• Trendline: right-click series → Add Trendline → show equation and R²
• Best practice: zero baseline for bars, direct labels over legend, one message per chart, no 3D

Next up: Data Validation & Protection — control what data can be entered and protect your spreadsheets.