MS Excel - Charts in Excel

In Microsoft Excel, charts are used to visually represent data, making it easier to analyze trends, patterns, and comparisons. Excel offers various chart types, each designed for specific purposes, and provides customization tools to make your charts more insightful and professional.

1. What Are Charts in Excel?

Charts convert raw data into a visual format — such as bars, lines, or pie slices — helping you quickly understand complex information.

Examples of Uses:

  • Compare sales across months.

  • Show growth trends over time.

  • Highlight performance gaps.

  • Visualize survey or feedback data.

2. How to Insert a Chart

Steps:

  1. Select your data range.

  2. Go to Insert → Charts.

  3. Choose your preferred chart type.

  4. Customize using Chart Tools in the Ribbon.

Shortcut:
Alt + F1 → Creates a default chart instantly.
F11 → Creates a chart in a new worksheet.

3. Types of Charts in Excel

A. Column & Bar Charts

Best for comparing values across categories.

  • Clustered Column → Compares multiple items side by side.

  • Stacked Column → Shows contribution of each category to the total.

  • Bar Chart → Similar to column charts but horizontal.

Use Case: Compare monthly sales across regions.

B. Line Charts

Shows trends over time or continuous data.

  • Simple Line → Displays a single data series.

  • Multi-Line → Compares multiple series.

  • Stacked Line → Highlights cumulative totals.

Use Case: Track yearly revenue growth.

C. Pie & Doughnut Charts

Used to display percentage contribution of each item to the total.

  • Pie Chart → Represents data as slices.

  • 3D Pie → Adds depth effect.

  • Doughnut Chart → Similar to pie but allows multiple series.

Use Case: Show market share percentages.

D. Area Charts

Highlight cumulative values over time.

  • Simple Area → Fills color below the line.

  • Stacked Area → Shows multiple contributions.

Use Case: Visualize website traffic growth month by month.

E. Scatter (XY) Charts

Used to show relationships between two variables.

  • Plots individual data points on an X-Y coordinate system.

  • Great for correlation analysis.

Use Case: Compare advertising spend vs. sales revenue.

F. Combo Charts

Combines two chart types into one.

  • Example: Column + Line chart.

  • Perfect when comparing different data sets with varying scales.

Use Case: Show monthly sales as bars and profit percentage as a line.

G. Waterfall Charts (Excel 2016 & later)

Shows incremental increases and decreases leading to a total.

  • Commonly used for financial statements.

Use Case: Track profit/loss breakdown.

H. Funnel Charts (Excel 2019 & later)

Displays progressive stages in a process.

  • Commonly used in sales pipelines or conversion tracking.

Use Case: Visualize lead conversion rates.

I. Radar & Spider Charts

Show data in a circular format, ideal for performance comparisons.

  • Each spoke represents a variable.

Use Case: Compare employee skill levels or customer satisfaction scores.

4. Customizing Charts

A. Change Chart Elements

Use Chart Elements (+) to add or modify:

  • Chart title

  • Data labels

  • Legends

  • Axis titles

  • Gridlines

B. Change Chart Styles

Go to Chart Tools → Design → Quick Layouts or Chart Styles to apply predefined designs.

C. Change Colors

  • Use Chart Tools → Format → Shape Fill / Shape Outline.

  • Apply theme colors for consistency.

D. Switch Row/Column

  • Design → Switch Row/Column → Useful when the chart doesn’t display data as expected.

5. Advanced Chart Features

A. Dynamic Charts with Named Ranges

  • Use OFFSET or TABLES to make charts auto-update when new data is added.

B. Add Trendlines

  • Right-click data series → Add Trendline.

  • Types: Linear, Exponential, Moving Average.

C. Add Secondary Axis

  • Useful when comparing two datasets with different scales.

  • Right-click data series → Format Data Series → Secondary Axis.

D. Use Sparklines

  • Mini-charts inside a single cell.

  • Go to Insert → Sparklines → Choose Line, Column, or Win/Loss.

6. Tips for Better Charts

  • Keep them simple and clean — avoid clutter.

  • Use appropriate chart types for your data.

  • Highlight important trends using colors and data labels.

  • Limit the number of colors to improve readability.

  • Use interactive charts with slicers and filters for dashboards.

If you want, I can create a one-page Excel Charts Cheat Sheet that shows:

  • All chart types with visuals

  • Best use cases for each

  • Customization tips

  • Shortcut keys

10 Advanced Excel Charts - Excel Campus