Charts in Sheets

Who this is for

• Aspiring and junior Data Analysts who need to present insights clearly.
• Anyone using Google Sheets or similar spreadsheets to visualize data quickly.
• Non-technical stakeholders who want to read and sanity-check charts.

Prerequisites

• Comfort with entering and cleaning tabular data (headers, consistent types).
• Basic formulas (SUM, AVERAGE) and sorting/filtering.
• Knowing your question: compare categories, trends over time, distributions, or relationships?

Why this matters

Analysts are asked to turn raw tables into decisions. Clear charts help you:

• Show weekly revenue trends to forecast targets.
• Compare campaign performance across channels.
• Spot outliers in customer spend.
• Explain relationships, like ad spend vs. signups.

Concept explained simply

A chart is a visual summary of numbers. Your job is to match the question to the right chart and format it for instant understanding. In Sheets, you usually select data, Insert → Chart, then adjust Chart type, Data range, Series, and Customize options (titles, axis, colors, labels).

Mental model

• Question → Encoding: determine what should be on the x-axis, y-axis, color, or series.
• Data shape → Chart: tidy columns with clear headers; avoid merged cells.
• Clarity → Decisions: remove anything that slows reading (extra gridlines, 3D, clutter).

Common chart types (and when to use)

• Line: trends over time (daily, weekly, monthly). Works best with 2–4 series.
• Column/Bar: compare categories at a point in time. Use bar if labels are long.
• Stacked Column/Bar: show parts of a whole across categories. Use carefully; totals should matter.
• Combo: mix column (quantity) with line (rate) and optional secondary axis.
• Scatter: relationship between two numeric variables; reveals correlation and outliers.
• Pie/Donut: single part-to-whole snapshot with ≤5 slices; otherwise avoid.
• Histogram: distribution of a single numeric variable (bins).

From data to chart in Sheets

1. Prepare data: one header row, no merged cells, each column one variable.
2. Select your range (include headers). If time series, ensure the date column is recognized as dates.
3. Insert → Chart. Sheets guesses a type; you can change it.
4. Setup tab: choose Chart type, Data range, Series. Use "Switch rows/columns" if the grouping is wrong.
5. Customize tab: add Chart & axis titles, format numbers, set colors, show data labels if helpful.
6. Self-check: Can someone get the main point in 5 seconds?

Worked examples

Example 1: Monthly revenue trend (line)

Data:

Month,Revenue
Jan,42000
Feb,47000
Mar,45000
Apr,52000
May,56000
Jun,59000

1. Select both columns including headers.
2. Insert → Chart → Line chart.
3. Customize: Title "Monthly Revenue", y-axis as currency, show data markers.

Insight: Upward trend with minor dip in March.

Example 2: Channel performance (column)

Data:

Channel,Signups
Email,320
Paid Search,450
Social,210
Referral,280

1. Select range and insert a Column chart.
2. Sort data by Signups descending (optional) for readability.
3. Add data labels. Title: "Signups by Channel".

Insight: Paid Search leads; Social lags.

Example 3: Composition across months (stacked column)

Data:

Month,Desktop,Mobile,Tablet
Jan,1200,900,100
Feb,1300,950,110
Mar,1250,1000,120

1. Select range → Chart → Stacked column.
2. Show values as percentages if the total matters more than raw counts.
3. Legend at bottom, distinct colors for each device.

Insight: Mobile share is rising month over month.

Example 4: Ad spend vs. signups (scatter)

Data:

AdSpend,Signups
200,40
350,70
500,100
650,120
800,155

1. Select both columns → Scatter chart.
2. Title: "Ad Spend vs Signups". Add trendline and show R².
3. Consider log scale if one axis spans 10x range.

Insight: Positive relationship with diminishing returns at higher spend.

Choosing the right chart quickly

• Time trend → Line/Area.
• Compare categories → Column/Bar (clustered).
• Part-to-whole → Stacked or Pie (≤5 slices).
• Relationship (x vs y) → Scatter.
• Distribution of one variable → Histogram.
• Two units (count + rate) → Combo with secondary axis.

Formatting for clarity

• Use direct labels where possible; reduce gridlines.
• One highlight color for the key series; muted grays for context.
• Right units: % for rates, currency for money, consistent decimals.
• Concise titles: "Q2 Signups up 18% vs Q1" beats "Chart 1".
• No 3D, avoid heavy shadows; they distort reading.

Common mistakes and self-check

• Wrong grouping (rows vs columns). Fix via "Switch rows/columns".
• Mixed units on one axis. Use secondary axis or separate charts.
• Unsorted categorical bars. Sort to emphasize ranking.
• Overloaded legends. Reduce series to the essentials.
• Truncated axes obscuring differences. Start at zero for bars; lines may vary with care.

Exercises

These mirror the interactive exercise below and prepare you for the quick test.

1. Exercise 1: Trend + composition + rate

   Copy this data to a new sheet:

   Month,Sessions,Signups,ConversionRate
   Jan,12000,360,3.0%
   Feb,13000,390,3.0%
   Mar,15000,420,2.8%
   Apr,17000,510,3.0%
   May,19000,570,3.0%
   Jun,21000,588,2.8%

   • Create a Line chart for Sessions over time.
   • Create a Column chart for Signups by month.
   • Create a Combo chart: columns for Sessions and a line for ConversionRate on a secondary axis.
   Hints

   • If the months don’t plot correctly, ensure the Month column is text or proper dates.
   • To assign a series to a secondary axis: Customize → Series → Apply to specific series → Axis → Right.
   Expected outcome

   • Three readable charts with clear titles and units (% on the rate, plain numbers for counts).
   • Insight: Sessions rise steadily; conversion rate dips in Mar/Jun.

Checklist before sharing

• Title states the message (not just the topic).
• Axes have correct units and readable scales.
• Legend is minimal; highlight key series.
• Data labels used only where they add value.
• No 3D effects; gridlines toned down.
• Colors pass basic contrast (light background, darker data).

Practical projects

1. Weekly KPI board: Build a single sheet with four charts (Revenue line, Signups column, Conversion combo, Refund rate line). Update weekly for a month.
2. Campaign comparison: Given data below, produce a ranking column chart and a scatter of Cost vs CPA.

   Campaign,Cost,Clicks,Conversions
   A,1200,300,36
   B,800,250,22
   C,1500,400,44
   D,600,180,14

3. Distribution deep dive: Paste 100 order amounts and chart a histogram with sensible bins. Add a note explaining skew/outliers.

Learning path

• Master basic chart types and formatting (this page).
• Pivot tables → Pivot charts for grouped summaries.
• Interactive dashboards with slicers and named ranges.
• Automate refresh with simple formulas and data validation controls.

Mini challenge

Use this data and choose the best single chart to communicate the key message. Add a one-line title that states the insight.

Month,New Users,Active Users,ChurnRate
Jan,800,2400,4.0%
Feb,820,2500,3.8%
Mar,900,2600,4.5%
Apr,950,2750,3.9%
May,980,2900,3.6%
Jun,1000,3000,3.7%

• Pick chart type and justify in 1–2 bullets.
• Ensure rates display as % and use a secondary axis if mixing units.

Next steps

• Refine one real report by applying the checklist above.
• Create a template sheet with your preferred colors and number formats.
• Practice explaining each chart in one sentence aloud.