Data Visualization and Dashboard Design

The Chart That Lied

The sales chart showed a dramatic upward trend. Revenue was skyrocketing. The CEO was thrilled. Then someone noticed: the Y-axis started at $950,000 instead of $0. The actual increase was 3%—barely visible on a properly scaled chart. The chart was technically accurate but visually misleading.

By the end of this lesson, you’ll create charts that tell the truth, apply conditional formatting that reveals patterns instantly, and design dashboards that communicate insights at a glance.

🔄 Quick Recall: In the previous lesson, we built pivot tables that summarize data dynamically. Remember slicers for interactive filtering? Today we add the visual layer—charts linked to pivot tables and conditional formatting that highlights what matters.

Choosing the Right Chart Type

Charts to avoid:

• 3D charts: Distort proportions and are harder to read
• Pie charts with too many slices: More than 6 slices becomes unreadable
• Dual-axis charts: Confusing; use two separate charts instead

I have this data to visualize:
[describe your data and what insight you want
to communicate]

Recommend:
1. The best chart type and why
2. What should be on each axis
3. How to title the chart (state the insight,
   not just the data label)
4. Any formatting recommendations
5. What NOT to do with this visualization

✅ Quick Check: You want to show how your company’s market share compares to three competitors. Which chart type would you use? Why not a line chart?

Chart Design Principles

Title = The Insight

Bad title: “Quarterly Revenue” Good title: “Revenue Grew 23% in Q3, Led by Enterprise Segment”

The title should tell the audience what they’re supposed to learn from the chart.

Minimize Clutter

Remove anything that doesn’t add information:

• Remove gridlines (or make them very light)
• Remove chart borders
• Reduce legend text if axis labels are clear
• Use direct labels on data points instead of a separate legend

Color with Purpose

• Use one color for most data, a contrasting color to highlight the key insight
• Don’t use more than 5-6 colors in one chart
• Use colorblind-friendly palettes (avoid red/green combinations)
• Gray out less important data to draw attention to what matters

Start Y-Axis at Zero

Unless you have a specific analytical reason not to, the Y-axis should start at zero. Truncating the axis exaggerates small differences and misleads the viewer.

Conditional Formatting

Conditional formatting turns numbers into visual patterns.

Color Scales

Apply a gradient (green to red, or blue to white) across a range of cells. Highest values get one color, lowest get another.

Excel: Home → Conditional Formatting → Color Scales Google Sheets: Format → Conditional formatting → Color scale

Use for: Comparing values across a range, identifying outliers, heatmap-style views.

Data Bars

Add mini bar charts inside cells, proportional to the value.

Excel: Home → Conditional Formatting → Data Bars Google Sheets: Not natively available; use a workaround with REPT and bar characters.

Icon Sets

Add symbols (arrows, traffic lights, stars) based on value thresholds.

Example: Green up arrow for sales above target, yellow right arrow for on track, red down arrow for below target.

Custom Rules

Create specific rules for your business logic:

• Highlight cells red where value < 0
• Bold cells where status = “Overdue”
• Green background where progress > 80%

✅ Quick Check: You have a table of 50 salespeople with their monthly targets and actual results. How would you use conditional formatting to instantly see who’s above target, on track, and below target?

Dashboard Design Basics

A dashboard combines multiple charts and key metrics on one sheet or screen.

The Dashboard Layout

TOP ROW: Key metrics (big numbers)
[Total Revenue]  [Orders]  [Avg Order]  [Growth %]

MIDDLE ROW: Main charts (largest, most important)
[Revenue Trend Line Chart]  [Sales by Region Bar Chart]

BOTTOM ROW: Supporting details
[Top Products Table]  [Recent Orders Table]

Dashboard Design Rules

1. Most important information at the top left (where eyes go first)
2. Maximum 6-8 visual elements per dashboard (more causes cognitive overload)
3. Consistent formatting across all charts (same colors, fonts, styles)
4. Interactive filters (slicers) to let viewers explore the data
5. One clear purpose per dashboard (sales dashboard, not “everything dashboard”)

I want to build a dashboard showing:
[describe what metrics and comparisons you need]

Help me design:
1. Which key metrics to show as big numbers (top row)
2. Which charts to include and what type
3. Layout: what goes where on the page
4. What filters/slicers would make it interactive
5. Color scheme and formatting guidelines

Linking Charts to Pivot Tables

The most powerful technique: create charts from pivot tables. When the pivot table updates (new data, different filter), the chart updates automatically.

Steps:

1. Build your pivot table (Lesson 5)
2. Select the pivot table
3. Insert → Chart
4. The chart is now linked to the pivot table
5. When you change pivot table filters or layout, the chart reflects changes instantly

Try It Yourself

Using the pivot table data from Lesson 5:

1. Create a bar chart showing sales by region
2. Create a line chart showing sales over time
3. Apply conditional formatting to your raw data (color scale on sales amounts)
4. Build a simple dashboard combining 2 charts and 3 key metrics on one sheet
5. Add a slicer to filter the dashboard by region

Key Takeaways

• Choose chart types based on the data story: bars for comparison, lines for trends, pies for composition
• Chart titles should state the insight, not just label the data
• Minimize chart clutter: remove gridlines, reduce colors, use direct labels
• Conditional formatting turns numbers into instant visual patterns (color scales, data bars, icons)
• Dashboards combine multiple visualizations with a clear purpose; limit to 6-8 visual elements
• Charts linked to pivot tables update automatically when data changes

Up Next

In Lesson 7: Automation, Templates, and Workflow Design, we’ll build reusable templates and automated workflows that turn hours of manual work into one-click operations.