Who this is for
- Business Analysts who present metrics to stakeholders.
- Anyone turning time-stamped data into clear, actionable visuals.
- Beginners who know basics of spreadsheets or BI tools.
Prerequisites
- Basic comfort with a spreadsheet or BI tool (e.g., creating a simple chart).
- Understanding of dates, sorting, and basic aggregation (sum/average).
Why this matters
- Monitor KPIs over time (revenue, traffic, active users, backlog).
- Spot seasonality, spikes, dips, and trend direction early.
- Explain changes with context (campaigns, releases, outages).
- Support decisions: staffing, budgets, inventory, product roadmap.
Concept explained simply
A line chart connects values across time to show how something changes. If you have a date column and a number (sales per day, tickets per week), a line chart is often the clearest way to see the trend.
Mental model
Think of the x-axis as a timeline (a road) and the y-axis as altitude. The line shows how your metric “climbs or descends” as time passes. Peaks and valleys are terrain changes worth explaining.
Key choices when building a line chart
- Time granularity: Day, week, month, quarter. Match to the decision cadence and data volume. Too fine = noisy; too coarse = hides patterns.
- Aggregation: Sum for totals (e.g., sales), average for rates (e.g., satisfaction), median for skewed data.
- Missing data: Show gaps or annotate. Avoid plotting zero unless zero is true zero.
- Scale: Linear for most cases. Log scale only for orders-of-magnitude differences and when your audience understands it.
- Axis baseline: Line charts don’t require zero. Keep consistent scales when comparing multiple panels.
- Smoothing: Use a moving average (e.g., 7-day, 3-month) to reduce noise. Don’t hide important spikes.
- Multiple lines: Limit to 2–4 lines. Label lines directly if possible; keep colors distinct and accessible.
- Annotations: Mark key events (launches, promos, outages) to explain shifts.
- Dual axis: Avoid when possible. Consider indexing (start both at 100) or small multiples instead.
When NOT to use a line chart
- No time sequence (e.g., product categories) → use bar charts.
- Few points (e.g., 2–3 time periods) → a bar chart may be clearer.
- Irregular intervals without clear ordering → consider scatter with trendline.
Worked examples
Example 1: Monthly revenue with seasonality
Data (Jan–Dec): 120k, 115k, 130k, 128k, 135k, 140k, 138k, 142k, 150k, 155k, 190k, 210k. Promo in November.
- Granularity: Month.
- Aggregation: Sum of monthly revenue.
- Lines: Revenue and 3-month moving average.
- Annotations: "Promo" at November.
- Insight: Upward trend with strong holiday seasonality; sustained lift after promo.
Example 2: Daily active users (DAU), last 30 days
- Granularity: Day; add a 7-day moving average to smooth weekday/weekend swings.
- Missing data: One missing day → leave a gap and note an outage.
- Insight: Growth is gradual; MA line confirms upward drift; weekend dips are normal.
Example 3: Support tickets open vs resolved
- Two metrics with different units of interpretation (inflow vs outflow).
- Avoid dual axes. Option A: Two small multiples with same scale. Option B: Index both to 100 at the start to compare direction.
- Insight: Resolved rate trending up faster than opens after a staffing change.
Tip: Indexing for fair comparison
Create an index column: value / first_value * 100. Plot the index for each series to compare relative change even if raw units differ.
How to build (any spreadsheet or BI tool)
- Clean data: one column for date/time, one for value (and optional category for multiple lines).
- Sort by date ascending; ensure true date format.
- Aggregate to the needed granularity (daily/weekly/monthly).
- Insert a line chart. Put time on the x-axis and value on the y-axis.
- Format: readable axis labels, consistent date ticks, clear title that states metric and period.
- Optional: add a moving average series; choose a window that matches natural cycles (7-day, 4-week, 3-month).
- Add annotations for key events.
- Label lines directly (at line end) and use accessible colors.
Exercises
Do these hands-on tasks. Then check the solutions. For quick feedback, use the checklist under each exercise.
Exercise 1 — Build a monthly revenue trend with a 3-month MA
Data (USD, by month): Jan 120k, Feb 115k, Mar 130k, Apr 128k, May 135k, Jun 140k, Jul 138k, Aug 142k, Sep 150k, Oct 155k, Nov 190k (Promo), Dec 210k.
- Create a line chart of monthly revenue.
- Add a 3-month moving average as a second line.
- Annotate the November promotion.
- Decide whether the y-axis should start at zero; justify your choice.
- [ ] The chart shows two lines: Revenue and 3-mo MA.
- [ ] November is labeled "Promo".
- [ ] Axis choice preserves readability without misleading exaggeration.
- [ ] Title states metric and date range.
Need a hint?
- Create the moving average with a rolling window (e.g., average of current and prior two months).
- Use a callout or data label for event annotation.
- Line charts do not require zero baseline; keep scales consistent and honest.
Show solution
Create a monthly line chart with two series: Revenue and 3-mo MA. Keep a linear y-axis that does not start at zero to reveal meaningful month-to-month changes; ensure the scale is not overly tight (avoid exaggerating tiny fluctuations). Add a text annotation at November labeled "Promo". Title: "Monthly Revenue (Jan–Dec) with 3-Month Moving Average". Insight: strong holiday lift with smoother trend showing sustained growth.
Exercise 2 — Compare YoY website sessions on one chart
Monthly sessions (2024 vs 2025):
- 2024: 80k, 82k, 85k, 90k, 95k, 97k, 100k, 98k, 102k, 105k, 110k, 120k
- 2025: 84k, 86k, 92k, 97k, 103k, 108k, 111k, 115k, 118k, 124k, 130k, 140k
- Plot two lines aligned by month (Jan–Dec).
- Index both series to 100 at January for fair comparison.
- Label the lines directly ("2024", "2025") and minimize legend reliance.
- Summarize the main YoY insight in a subtitle.
- [ ] Both lines start at 100 in January.
- [ ] Months are aligned, evenly spaced.
- [ ] Direct labels at line ends are readable.
- [ ] Subtitle states the core insight (e.g., "2025 leads 2024 from March onward").
Need a hint?
- Create a helper column: index = value / January_value * 100.
- Use distinct, high-contrast colors; avoid red/green pairing.
- If lines overlap heavily, add subtle transparency or small multiples.
Show solution
Compute index values for each year (value / Jan_value * 100). Plot both indexed series on the same line chart, months on the x-axis. Directly label line ends as "2024" and "2025". Subtitle: "2025 sessions outpace 2024 from March onward, finishing ~17% higher in December."
Common mistakes and how to self-check
- Plotting zero for missing data: Leaves false dips. Self-check: Are there gaps where data truly doesn’t exist?
- Overcrowding lines: 5+ series becomes unreadable. Self-check: Can you follow each line without squinting?
- Inconsistent scales across comparisons: Misleads. Self-check: Are panels using the same axis ranges?
- Ambiguous labels: Unclear units/timeframe. Self-check: Does the title say metric + period; are axes labeled?
- Over-smoothing: Hides important spikes. Self-check: Compare raw vs MA; is any critical event flattened?
- Dual axes misuse: Suggests correlation where none exists. Self-check: Could indexing or small multiples replace dual axes?
Practical projects
- Create a product adoption trend: new users per week with a 4-week MA and release annotations.
- Build a revenue dashboard card: last 24 months, YoY comparison via indexed lines, plus a callout for last month’s change.
- Operations: visualize ticket backlog and resolution rate in small multiples with shared scales and notes on staffing changes.
Mini challenge
You get 18 months of lead volume by week with two promo periods and two outages. Create a line chart that shows the underlying growth while making the promos and outages obvious. Keep lines to two (raw and 4-week MA), and avoid dual axes. Write a one-sentence insight.
Learning path
- Revisit chart selection basics: when to use line vs bar or area.
- Learn time-series hygiene: handling missing dates and irregular intervals.
- Advance to comparisons: YoY indexing, small multiples, confidence bands (where applicable).
- Move on to forecasting basics: moving averages vs simple models for short-term planning.
Quick Test
Take the short test below to confirm understanding. Available to everyone; log in to save your progress.
Next steps
- Turn one recurring report you own into a clean trend line with an MA and one annotation.
- Replace any dual-axis trend in your deck with indexed lines or small multiples.
- Ask a peer to read your line chart for 10 seconds and tell you the main message. If they struggle, simplify.