Excel chart chooser — what do you want to show?

By Ravindra Bagale · Last updated: 2 November 2026

Why this page

Why: Wrong charts confuse managers. A pie of monthly sales hides the trend. A line of five unordered cities invents a fake journey.

What: Six goals. Each block has a recommended chart, a when-not-to-use note, a sample image, and How steps.

How: Jump to your goal below. Build the chart in Excel. Then try the practice tasks and quiz.

What do you want to show?

Compare values side by side

Recommended: Clustered column (or bar)

Why: Managers ask which city or product is higher. Columns make height easy to compare.

What: Categories on the axis; one or more series as columns.

When not to use: Do not use a pie for more than one series across years — pies do not compare years well.

How — numbered steps:

1. Select category + value columns.
2. Insert → Charts → Clustered Column.
3. Add a clear chart title (example: Orders by city).
4. If two years, add both value columns before Insert.
5. Keep gap width readable; avoid 3D columns.

Example: Priya Sharma (Pune) compares 2025 vs 2026 order volume for Pune, Nashik and Leeds.

What do you want to show?

Show change over time

Recommended: Line chart (or column for few periods)

Why: Trends answer “are we going up or down?” Lines guide the eye across months.

What: Time on the horizontal axis; measure on the vertical axis.

When not to use: Do not use a pie for time trends. Do not use a line when you have only two unordered cities.

How — numbered steps:

1. Put months in one column, values next to them.
2. Select the range → Insert → Line with markers.
3. Title: Sales trend (₹ lakhs).
4. Add a thin target line manually if needed (optional series).
5. Check that dates/months are sorted.

Example: Omar Hassan (Dubai desk using ₹ training file) plots Jan–Jun sales for leadership.

What do you want to show?

Show parts of a whole

Recommended: Pie or doughnut (few slices)

Why: Share-of-total questions (“how much is retail?”) fit part-of-whole charts.

What: One whole = 100%. Each slice is a category’s share.

When not to use: Avoid pies with more than 5–6 slices. Avoid pie when totals differ by month — use stacked column instead.

How — numbered steps:

1. Build a small summary: Category | Amount.
2. Insert → Pie or Doughnut.
3. Show percentages in data labels.
4. Order slices largest to smallest.
5. If many categories, switch to bar.

Example: James Cole (Birmingham) shows channel mix: Retail, Online, Wholesale, Other.

What do you want to show?

Show how values are spread

Recommended: Histogram-style column (or box plot add-in)

Why: Distribution answers “are most tickets small or large?” — not just the average.

What: Buckets (bins) on the axis; count of rows in each bucket.

When not to use: Do not use a line chart for unordered bins. Do not hide empty bins if the gap matters.

How — numbered steps:

1. Create bin labels (0–1k, 1–2k…).
2. COUNTIF / FREQUENCY into a count column.
3. Insert Clustered Column.
4. Title: Ticket size distribution.
5. Or use Excel’s Histogram chart if available in your build.

Example: Ananya Joshi (support, Pune) bins ticket values to see if refunds cluster under ₹2,000.

What do you want to show?

Show relationship between two numbers

Recommended: Scatter (X‑Y)

Why: Relationship questions: “when ads rise, do leads rise?” Scatter plots pairs of numbers.

What: One measure on X, another on Y; each point is a row.

When not to use: Do not use a pie. Do not put categories on a scatter X axis — use columns instead.

How — numbered steps:

1. Two numeric columns (Ads, Leads).
2. Select both → Insert → Scatter.
3. Add axis titles with units (₹ 000, leads).
4. Optional: add a trendline (Chart Design).
5. Check outliers before presenting.

Example: Sofia Keller (Berlin marketing) plots ads spend vs leads for weekly reviews.

What do you want to show?

Highlight one key number

Recommended: KPI card / single big number (or gauge carefully)

Why: Leaders often need one headline metric before details — on-time %, NPS, cash.

What: A large number, short label, optional target comparison.

When not to use: Do not bury a KPI inside a busy pie. Avoid 3D gauges that are hard to read.

How — numbered steps:

1. Compute the KPI in a cell (example: on-time deliveries / total).
2. Format as percentage.
3. Place it large on a dashboard sheet (font 28–36).
4. Optional: sparkline beside it for trend.
5. Link to detail charts below.

Example: Ravindra’s sample logistics card: 96.4% on-time delivery for the week.

Practice tasks (12)

1. Task 1. Build a clustered column for three cities × two years.
   Hint: Comparison block
2. Task 2. Make a 6-month line chart titled with units.
   Hint: Trend block
3. Task 3. Create a doughnut with four channels; show % labels.
   Hint: Part-of-whole
4. Task 4. Explain why a pie is a bad monthly sales trend chart.
   Hint: time ≠ slices
5. Task 5. Bin 20 ticket amounts into 5 buckets and chart counts.
   Hint: Distribution
6. Task 6. Plot ads vs leads on a scatter; add axis titles.
   Hint: Relationship
7. Task 7. Design a KPI cell for on-time % with large font.
   Hint: KPI block
8. Task 8. Take a wrong pie you find online and rewrite which chart fits.
   Hint: use the six goals
9. Task 9. Open Ch 8 lesson 8-12 and list two tips that match this page.
   Hint: sidebar link
10. Task 10. Rebuild the comparison chart as horizontal bars for long city names.
    Hint: bar vs column
11. Task 11. Add data labels only where they help (not on every scatter point).
    Hint: reduce clutter
12. Task 12. Bonus: one dashboard sheet with KPI + trend + comparison.
    Hint: Ch 13 dashboards

Revision cards

Three standalone cards. QR opens this chart chooser. Card images are on S3 under excel/chart-chooser/.