Charts and Visualization

Show, Don't Just Tell

Numbers in cells are precise. Charts are persuasive.

The right chart makes patterns obvious, comparisons easy, and insights memorable. The wrong chart obscures data and confuses audiences.

This chapter covers how to create effective charts.

Choosing the Right Chart Type

Column and Bar Charts

Use for: Comparing values across categories

Column (vertical bars): Categories on x-axis, values on y-axis

Bar (horizontal bars): Better for long category names or many categories

Examples:

  • Sales by region
  • Product revenue comparison
  • Survey responses by category

Line Charts

Use for: Showing trends over time

When to use: Time-based data where you want to show progression

Examples:

  • Monthly revenue over two years
  • Website traffic over time
  • Stock price movement

Pie and Donut Charts

Use for: Showing parts of a whole (percentages)

Warnings:

  • Only use with a few categories (5-7 max)
  • Must sum to 100%
  • Hard to compare similar-sized slices
  • Often better as bar charts

Examples:

  • Market share
  • Budget allocation
  • Revenue by product category

Scatter Plots

Use for: Showing relationship between two variables

Examples:

  • Price vs. quantity sold
  • Age vs. income
  • Advertising spend vs. sales

Area Charts

Use for: Showing cumulative totals over time

Stacked area: Shows composition changing over time

Combo Charts

Use for: Showing two different scales or types together

Example: Revenue (bars) and growth rate (line) on the same chart

Creating Charts

Basic Steps

  1. Select your data (including headers)
  2. Insert → Charts → Choose chart type
  3. Excel creates the chart
  4. Customize as needed

Quick Charts

Select data → Alt+F1 (inserts chart on same sheet)

Or: F11 (creates chart on new sheet)

Selecting Data

Include:

  • Column/row headers (become labels)
  • All data you want to chart

Tip: If data isn't contiguous, Ctrl+click to select multiple ranges.

Chart Elements

Chart Title

Click the title to edit. Make it descriptive: Bad: "Chart 1" Good: "Monthly Revenue 2024 vs 2023"

Axis Titles

Chart Elements (+) → Axis Titles

Label what each axis represents, including units.

Legend

Shows what each color/series represents. Position where it doesn't overlap data.

Data Labels

Show actual values on the chart. Chart Elements (+) → Data Labels

Use sparingly — too many labels clutters the chart.

Gridlines

Help readers estimate values. Keep major gridlines, often remove minor ones.

Trendlines

Chart Elements (+) → Trendline

Shows the overall pattern in the data. Useful for spotting trends.

Formatting Charts

Quick Layouts and Styles

Chart Design tab:

  • Quick Layout: Pre-built element arrangements
  • Chart Styles: Pre-built color and formatting schemes

Color Choices

  • Use consistent colors across related charts
  • Consider colorblind-friendly palettes
  • Don't use too many colors
  • Use color to highlight important data

Font and Size

  • Title should be readable
  • Axis labels should be legible
  • Don't let text overlap
  • Consider where the chart will be displayed

Removing Clutter

Less is often more. Consider removing:

  • Unnecessary gridlines
  • Redundant legends
  • 3D effects (usually just add confusion)
  • Excessive data labels
  • Chart borders

Chart Best Practices

Start Axis at Zero

For bar and column charts, the y-axis should start at zero. Otherwise, differences look exaggerated.

Exception: Stock charts and other specialized uses where relative change matters.

Use Consistent Scales

When comparing multiple charts, use the same scale so comparisons are valid.

Label Clearly

Every chart should be understandable without explanation:

  • Clear title
  • Labeled axes
  • Units included
  • Legend if needed

Match Chart to Message

Want to show trends? Line chart

Want to compare categories? Bar/column chart

Want to show parts of whole? Pie chart (sparingly)

Want to show correlation? Scatter plot

Avoid Chart Junk

Remove decorative elements that don't convey information:

  • 3D effects
  • Unnecessary backgrounds
  • Decorative images
  • Excessive colors

Sparklines

What They Are

Tiny charts that fit in a single cell. Show trends without taking space.

Creating Sparklines

Select destination cells → Insert → Sparklines → Line, Column, or Win/Loss

Select data range → OK

When to Use

  • Dashboards with limited space
  • Showing trends in tables
  • Quick visual indicators

Dynamic Charts

Charts from Tables

If your data is in an Excel Table (Ctrl+T), charts update automatically when you add data.

Charts from Pivot Tables

PivotTable Analyze → PivotChart

The chart updates when you change the pivot table.

Named Ranges with OFFSET

Create dynamic named ranges that grow with data, then chart those ranges.

AI Prompt: Chart Help

I need to visualize data in Excel.

My data:
[Describe your data or paste a few rows]

My goal:
[What insight or comparison do you want to show?]

Help me:
1. Choose the best chart type
2. Set it up properly
3. Suggest formatting to make it clear and professional

What's Next

Your charts look great. Now let's speed up your workflow.

Next chapter: Automation and efficiency — keyboard shortcuts, templates, and working faster.

Pivot Tables and SummarizationAutomation and Efficiency