Charts & Visualization — Excel & Spreadsheets

💡What You'll Build

By the end of this module, you will be able to choose the right chart type for any data story, apply the data-ink ratio to strip charts down to their essentials, build sparklines for compact trend views, and design a 4-6 chart dashboard with KPIs and slicers that a VP can read in 30 seconds.

In 1986, engineers at Morton Thiokol made a chart that could have saved seven lives

The night before the Space Shuttle Challenger launched, engineers knew the O-ring seals on the solid rocket boosters were vulnerable to cold temperatures. They had data from 23 previous launches showing the relationship between temperature and O-ring damage. They tried to convince NASA managers to delay the launch.

Their charts failed. The data was presented in a confusing, cluttered format that buried the critical pattern. The temperature at launch the next morning was 36 degrees Fahrenheit — well below any previous launch. The O-rings failed. Seven astronauts died.

Edward Tufte, the father of data visualization, later showed that a simple scatter plot of temperature vs. O-ring incidents would have made the danger unmistakable. The data was there. The chart was not.

Charts are not decoration. They are communication tools that can change decisions — and sometimes save lives.

65%of people are visual learners

60000xfaster the brain processes images vs text

7astronauts lost due to poor data presentation

Choosing the right chart type

The most common charting mistake is not ugly formatting — it is choosing the wrong chart type for the story you are telling. Here is the decision framework:

What you want to show	Best chart type	Example
Comparison across categories	Bar chart (horizontal) or column chart (vertical)	Revenue by department
Trend over time	Line chart	Monthly website traffic over 2 years
Part of a whole	Pie chart (max 5-6 slices) or stacked bar	Budget allocation by category
Relationship between two variables	Scatter plot (XY chart)	Temperature vs. ice cream sales
Distribution of values	Histogram	Employee salary distribution
Composition over time	Stacked area chart	Revenue mix by product line over quarters

⚠️Never use a pie chart with more than 6 slices

Humans are terrible at comparing angles. A pie chart with 12 slices is almost impossible to read. If you have more than 5-6 categories, use a bar chart instead. And never use 3D pie charts — they distort the data by making slices closer to the viewer appear larger. 3D pie charts are the Comic Sans of data visualization.

✗ Wrong Chart Choice

✗Pie chart with 15 tiny slices
✗Line chart for categorical data
✗Bar chart for time series
✗3D chart for any purpose

✓ Right Chart Choice

✓Bar chart for many categories
✓Bar/column chart for categories
✓Line chart for trends over time
✓2D chart always

Building a chart step by step

Step 1: Select your data (including headers) — Excel uses headers as labels

Step 2: Insert → Chart → Choose chart type (or use "Recommended Charts" for suggestions)

Step 3: Excel generates a default chart — it usually needs work

Step 4: Click the chart → Chart Design tab → add title, legend, data labels as needed

Step 5: Remove clutter: delete gridlines you do not need, simplify the legend, clean up axis labels

Step 6: Apply the "squint test" — squint at your chart. Can you still see the main message? If not, simplify.

There Are No Dumb Questions

"Should I always include a chart title?"

Yes — always. A chart without a title forces the reader to figure out what they are looking at. Make your title describe the insight, not just the data. Bad title: "Revenue by Quarter." Better title: "Revenue grew 23% in Q3 after the product launch." The title should tell the reader what to take away.

"When should I use data labels instead of axes?"

Use data labels when you have few data points (under 10) and the exact numbers matter. Use axes when you have many data points and the pattern matters more than individual values. Using both creates clutter. Choose one.

Formatting for clarity, not beauty

The goal of chart formatting is clarity — making the data easier to understand. Every formatting choice should serve communication, not aesthetics.

The data-ink ratio: Edward Tufte coined this concept — maximize the proportion of "ink" that represents actual data, and minimize everything else. Gridlines, borders, background colors, and 3D effects are all non-data ink. Remove them unless they serve a purpose.

Format element	Keep it if...	Remove it if...
Title	Always keep	Never remove
Axis labels	The values are not self-evident	Data labels make them redundant
Gridlines	Reader needs to estimate values precisely	Data labels provide exact values
Legend	Multiple data series	Only one series (label it directly)
Data labels	Few data points, exact values matter	Many data points, pattern matters more
Borders/outline	Never needed	Always remove
Background color	Never needed	Always remove (use white)

🔑The 5-second rule

A well-designed chart communicates its main message within 5 seconds of looking at it. If your audience is still trying to figure out what the chart shows after 5 seconds, it needs simplification. Remove elements until the message is unmistakable.

🔒

Fix This Chart

25 XP

Your colleague created a chart for a board presentation. It has: - A 3D pie chart with 14 slices showing "Revenue by Product Line" - Rainbow colors with no legend - No chart title - The smallest slice represents 1.2% of revenue What specific changes would you make to fix this chart? 1. Chart type change: ___ 2. Title to add: ___ 3. Category handling: ___ 4. Color approach: ___ _Hint: Replace the pie chart with a horizontal bar chart, sorted from largest to smallest. Add a descriptive title. Group the smallest categories (under 5%) into "Other." Use a single color with the top item highlighted in a contrasting color._

Sparklines — tiny charts inside cells

Sparklines are miniature charts that fit inside a single cell. They show trends at a glance without taking up space. Place them next to the data they represent for a compact, scannable view.

Sparkline type	Best for	Example
Line	Trends over time	Monthly revenue trend per product
Column	Comparing values	Weekly sales by rep
Win/Loss	Binary outcomes	Daily stock up/down

How: Select the cell where you want the sparkline → Insert → Sparklines → Choose type → Select data range → OK.

Sparklines are particularly powerful in dashboards. Imagine a table of 20 sales reps, each with 12 months of data. Instead of 20 separate charts, each rep gets a tiny line sparkline showing their trend right next to their total. In one glance, you see who is trending up, who is trending down, and who is flat.

Dashboard layouts — combining charts for impact

A dashboard is a single view that combines multiple charts, tables, and KPIs to give a complete picture of performance. Dashboards turn spreadsheets into decision-making tools.

Dashboard design principles:

KPIs at the top — the 3-5 most important numbers in large font (total revenue, growth rate, customer count)

Trends in the middle — line or area charts showing how KPIs change over time

Breakdowns at the bottom — bar charts or tables showing details by category, region, or product

Slicers on the side — let viewers filter the entire dashboard by time period, region, or segment

Consistent colors — use the same color for the same category across all charts (blue = East region everywhere)

There Are No Dumb Questions

"Should I build dashboards in Excel or use a dedicated tool like Tableau?"

For internal team dashboards with moderate data volumes, Excel is perfectly fine — especially if your team already uses it. Dedicated BI tools (Tableau, Power BI, Looker) become necessary when you need real-time data connections, interactivity beyond slicers, or dashboards shared with hundreds of users. Start in Excel. Graduate to BI tools when Excel becomes the bottleneck.

"How many charts should be on one dashboard?"

The sweet spot is 4-6 charts plus 3-5 KPI tiles. More than that and the dashboard becomes overwhelming — the viewer does not know where to look. If you need more, create multiple dashboard tabs (Overview, Sales Detail, Regional Breakdown) rather than cramming everything onto one screen.

🔒

Design a Sales Dashboard

50 XP

You are building a monthly sales dashboard for your VP of Sales. Available data: Date, Sales Rep, Region (North/South/East/West), Product Category, Deal Size, Deal Status (Won/Lost/Pending). Design the dashboard layout. For each element, specify: 1. **Top KPIs (3-5 numbers):** What metrics would you show? → ___ 2. **Main chart:** What chart type and what does it show? → ___ 3. **Supporting charts (2-3):** What additional views would you include? → ___ 4. **Slicers:** What filters would you add? → ___ _Hint: KPIs might include Total Revenue (Won deals), Win Rate %, Average Deal Size, Number of Deals Closed, Pipeline Value (Pending). Main chart could be a line showing monthly revenue trend. Supporting charts could include revenue by region (bar), top reps (bar), win rate by category (bar). Slicers for Region and Time Period._

Common chart mistakes and how to fix them

Mistake	Why it is bad	Fix
Truncated Y-axis (starting at 500 instead of 0)	Exaggerates differences, misleads the reader	Start the Y-axis at 0 for bar charts
Dual Y-axes	Extremely easy to mislead by scaling axes differently	Use two separate charts or index to a common baseline
Too many colors	Overwhelming, impossible to distinguish	Use one base color with one accent color for emphasis
Missing context	"Revenue is $5M" — is that good or bad?	Add benchmarks, targets, or prior year comparisons
Pie charts for comparison	Cannot compare slice sizes accurately	Use bar charts instead
Chart junk (borders, shadows, gradients)	Distracts from the data	Remove all non-data elements

🔑The best chart is the simplest one that tells the story

If a table communicates the data more clearly than a chart, use a table. Not everything needs to be visualized. A chart should add understanding that the raw numbers alone cannot provide — pattern, trend, outlier, or comparison. If it does not add understanding, it adds clutter.

Back to the Challenger

Remember the Morton Thiokol engineers? They had the data — 23 launches worth of O-ring performance at different temperatures. A simple scatter plot would have shown the unmistakable pattern: colder temperatures meant more O-ring damage. At 36 degrees, the danger would have been obvious to anyone in the room.

The lesson is not just "make better charts." It is that chart design is a communication skill with real consequences. Every chart you build — whether it is a sales dashboard for your VP or a budget breakdown for your team — either clarifies a decision or obscures one. The pivot tables you built in the previous module generate the numbers. The charts in this module give those numbers a voice.

Next up: Building charts and dashboards by hand is fine for a one-time report. But what about the report you build every Monday? In the next module, you will learn to record macros that automate repetitive formatting, sorting, and processing tasks — turning 45 minutes of clicking into 4 seconds of automation.

Key takeaways

Choose chart type by purpose: bar for comparison, line for trends, scatter for relationships — never pie for more than 6 categories
The Challenger disaster shows that chart design is not cosmetic — clear visualization can change life-or-death decisions
Data-ink ratio: maximize data, minimize decoration — remove gridlines, borders, backgrounds, and 3D effects
The 5-second rule: if the main message is not obvious in 5 seconds, simplify the chart
Sparklines provide trend context in a single cell — ideal for compact dashboards
Dashboard design: KPIs at top, trends in middle, details at bottom, slicers on the side, 4-6 charts maximum
Chart titles should describe insights ("Revenue grew 23% after launch"), not just labels ("Revenue by Quarter")

Knowledge Check

1.You need to show how monthly website traffic has changed over the past 24 months. Which chart type is most appropriate?

2.Edward Tufte's 'data-ink ratio' principle states that you should:

3.Why are 3D pie charts considered poor data visualization practice?

4.What is the ideal number of charts on a single dashboard page, and what should go at the top?