Free Excel Course Part 4 — Charts & Graphs | Line, Column, Bar, Gantt, Geo Heat Map & More
Having completed Part 1 (shortcuts and formatting), Part 2 (what-if functions), and Part 3 (formula reference), this fourth and final module focuses on Data Visualization, Charts, and Executive Dashboards.
When presenting financial analysis, operational updates, or project plans to executive leadership, visual charts communicate insights far more effectively than dense numeric tables. This guide walks through 8 essential chart types and custom visualizations—ranging from standard line and column charts to custom Gantt project schedules, sales funnels, and geographic heat maps. All exercise datasets and chart templates are included in the downloadable practice workbook.
| Course Module | Core Focus | Topics Included |
|---|---|---|
| Part 1 ✅ | Navigation & Formatting | 14 Essential Topics |
| Part 2 ✅ | Intermediate Functions | 7 Core Functions |
| Part 3 ✅ | Advanced Formulas | 60 Comprehensive Topics |
| Part 4 — Active Module | Charts & Visualizations | 8 Visualization Topics |
Curriculum Overview — All 8 Topics
| # | Visualization Type | Best Use Case & Analytical Purpose |
|---|---|---|
| 01 | Line Chart | Displaying continuous data trends and performance over time. |
| 02 | Column Chart | Comparing category totals vertically side-by-side across timeframes. |
| 03 | Bar Chart | Comparing category metrics horizontally when text labels are long. |
| 04 | Milestone Timeline | Plotting project milestones visually along a chronological axis. |
| 05 | Timeline Gantt Chart | Tracking project schedules, start dates, and task durations. |
| 06 | Win / Loss Sparklines | Displaying micro positive/negative indicators directly inside cells. |
| 07 | Sales Funnel Chart | Visualizing sales pipeline conversion drop-offs by stage. |
| 08 | Geo Heat Map | Mapping data concentration visually across geographic regions. |
Tracking performance trends and chronological changes over time
Line charts are ideal for displaying data trends over continuous time intervals (e.g., months, quarters, or years). They help identify growth trajectories, seasonal dips, and category performance shifts instantly.
Common Applications
- Tracking monthly sales revenue over a 12-month period.
- Plotting operational expense trends year-over-year.
- Monitoring website traffic volume over consecutive quarters.
In the workbook exercise, a Line Chart with Markers tracks vehicle sales volume across 6 years, comparing Sedans, SUVs, and Convertibles to highlight changing customer preferences over time.
Chart Creation Workflow:
- Select the data range including row and column headers.
- Go to the Insert tab → Charts group → click the Line Chart symbol.
- Select Line with Markers. Format axes and legend placement as needed.
Comparing discrete category values side-by-side using vertical bars
Column charts use vertical bars to compare values across distinct categories or time periods. They are effective when comparing data across two dimensions—such as category performance across specific quarters.
Common Applications
- Comparing quarterly sales figures across regional branch offices.
- Evaluating budget allocation vs. actual expenditure by department.
- Comparing product line revenue totals side-by-side.
Chart Creation Workflow:
- Select the full data table range.
- Go to Insert tab → Charts group → click Column Chart.
- Select Clustered Column for side-by-side category comparisons.
Horizontal bar comparison suited for long category text labels
Bar charts display comparison data horizontally. Using horizontal bars is recommended when category labels are long (such as department names or long product descriptions), as horizontal placement prevents text overlapping and angled rotation.
Chart Creation Workflow:
- Select the data range.
- Go to Insert tab → Charts group → click Bar Chart.
- Select Clustered Bar.
Plotting key project dates visually along a chronological axis
While Excel does not feature a default "Timeline" chart type, you can build custom milestone timelines by configuring a Scatter Plot chart.
In the practice exercise, an audit project schedule (from kickoff to final report sign-off) is converted into a milestone chart. Staggering vertical plotting offsets prevents milestone data labels from overlapping along the chronological timeline.
Creation Workflow:
- Set up columns for Milestone Description, Date (X-Axis), and Plotting Height Offset (Y-Axis).
- Select Date and Offset data ranges.
- Go to Insert tab → Charts group → select Scatter Plot.
- Add Data Labels referencing Milestone Description names and format connecting error bars.
Building project scheduling charts using stacked horizontal bar charts
A Gantt chart displays project task schedules, start dates, and task durations along a horizontal calendar grid. In Excel, Gantt charts are created by customizing a Stacked Bar Chart.
Gantt Chart Construction Workflow:
- Set up data columns: Task Description, Start Date, and Duration Days.
- Select Task Description and Start Date columns → insert a Stacked Bar Chart.
- Add the Duration Days column as a second data series.
- Format the initial Start Date bar series fill to "No Fill" to hide starting spacers, leaving task duration bars visible across dates.
- Set vertical axis to "Categories in reverse order" so initial tasks display at the top.
Displaying inline micro-indicators inside single table cells
Sparklines are micro-charts embedded directly inside individual cells. Win/Loss sparklines display upward markers for positive values and downward markers for negative values, making performance direction visible across large data tables without taking up dashboard space.
Sparkline Configuration Workflow:
- Select the target cell where the micro-chart should be embedded.
- Go to Insert tab → Sparklines group → click Win/Loss.
- In Data Range, select the row of periodic values (e.g., quarterly profit/loss metrics).
- In Location Range, select target display cells → click OK.
Visualizing pipeline progression and stage-by-stage conversion drop-offs
A Sales Funnel chart displays deal volumes through successive sales pipeline stages (e.g., Prospecting → Demo → Proposal → Closed Won), highlighting stage conversion efficiency and drop-off points.
Funnel Construction Workflow (Standard Method):
- Set up table: Stage Name and Deal Count (arranged from largest to smallest stage volume).
- Insert a calculation column for Centering Spacers:
(Top_Stage_Volume - Current_Stage_Volume) / 2. - Select table range → insert a Stacked Bar Chart.
- Set Centering Spacer series fill to "No Fill" to center funnel bars horizontally.
Mapping numeric data concentrations visually by geographical regions
A Geo Heat Map plots regional data (such as state sales totals or regional customer counts) on a visual map, shading regions based on numeric density.
Geo Heat Map Setup Workflow:
- Organize data with a clear Location column (e.g., State names or country codes) and a Metric column.
- Select data table range → go to Insert tab → Charts group → select Maps → Filled Map.
- Excel uses Bing Mapping services to render shaded geographic boundaries based on metrics.
Download Practice Workbook — Free
Download the Part 4 practice workbook containing all 8 chart exercise tabs, sample datasets, and formatting templates.
| Workbook Name | File Details | Download Link |
|---|---|---|
Excel Course Part-4 Charts & Graphs.xlsx |
8 visualization tabs · Line · Column · Bar · Timeline · Gantt · Sparklines · Funnel · Geo Map. | ⬇ Download (.xlsx) |
Direct workbook download. No registration or email sign-up required.
⬇ Download Practice Workbook (.xlsx)Connect With Us
This concludes our 4-Part Excel Course series. Have questions or need custom dashboard templates? Reach out below.
