DAY 19: Excel - Advanced Charts & Dashboards

Exercise 1: Create a Waterfall Chart for Profit Breakdown

Goal: Learn to build a waterfall chart that shows how revenue flows through various cost categories to arrive at net profit.

Setup:

• Prepare data with these rows: Revenue, COGS, Gross Profit, Operating Expenses, Marketing, R&D, Net Profit
• Example values: Revenue $500K, COGS -$200K, Operating Expenses -$100K, Marketing -$50K, R&D -$30K, Net Profit $120K

Step-by-Step Instructions:

1. Organize your data in three columns:
   • Column A: Category (Revenue, COGS, Gross Profit, etc.)
   • Column B: Amount (actual values with negative for costs)
   • Column C: Display Value (for chart - we'll calculate this)
2. Calculate cumulative values for waterfall: In cell C2 (next to Revenue):

   =B2

   In C3 (next to COGS):

   =C2+B3

   Copy this formula down through all rows. This creates the "waterfall" effect
3. Insert the Waterfall Chart:
   • Select your data range (A1:C8 including headers)
   • Go to Insert tab → Charts group → Insert Waterfall, Funnel, Stock, Surface, or Radar Chart
   • Click Waterfall chart (first option)
   • Shortcut: Alt+N+S (Insert → Statistical Chart)
4. Format the waterfall chart:
   • Right-click on any data point → Format Data Series
   • Set "Revenue" as Start: Right-click first column → "Set as Total"
   • Set "Gross Profit" and "Net Profit" as totals (floating bars)
   • Change colors: Select series → Format Data Series → Fill → choose colors (green for positive, red for negative)
5. Add data labels:
   • Click chart → Chart Elements button (+) → Data Labels
   • Right-click labels → Format Data Labels → check "Value"
   • Format as currency: Click labels → Home tab → Currency format
6. Add chart title and customize:

   Chart Title: "Profit Breakdown: Q4 2024"
   Axis Title: "Amount ($000s)"

   Click Chart Elements (+) → Chart Title and Axis Titles

💡 Tips:

• Waterfall vs Column: Waterfall shows cumulative effect; column shows individual values - use waterfall for flow visualization
• Set as Total: Right-click specific bars to set them as totals (they'll float instead of stack)
• Color scheme: Use green for increases, red for decreases, blue for totals - maintains visual consistency
• Keyboard shortcut: F11 creates a chart on new sheet; Alt+F1 creates embedded chart
• Common mistake: Forgetting to set totals - Gross Profit and Net Profit should be set as totals, not cumulative
• Business use: Perfect for showing P&L statements, budget variance analysis, and bridge charts

✅ Expected Result: A waterfall chart with Revenue starting high (green), costs reducing it step-by-step (red), and Net Profit as the final total (blue), clearly showing the profit journey

🔄 Connection to Previous Learning: Builds on Day 7's charting basics, adding advanced visualization for financial analysis.

Exercise 2: Build a Combo Chart (Column + Line) for Sales and Growth

Goal: Learn to create combination charts that display two different metrics with different scales (sales volume and growth percentage).

Setup:

• Data needed: Months (Jan-Dec), Sales ($), Growth % (year-over-year)
• Example: Jan Sales $50K (5% growth), Feb $55K (8% growth), etc.

Step-by-Step Instructions:

1. Organize your data:
   • Column A: Month (Jan, Feb, Mar...)
   • Column B: Sales ($50,000, $55,000, $52,000...)
   • Column C: Growth % (5%, 8%, 4%...)
2. Insert initial chart:
   • Select all data (A1:C13)
   • Insert tab → Charts → Column Chart → Clustered Column
   • Shortcut: Alt+F1 to insert default chart
3. Convert Growth % to Line Chart:
   • Click on one of the Growth % bars (click twice to select just that series)
   • Right-click → Change Series Chart Type
   • Or: Chart Design tab → Change Chart Type
   • Set Growth % series to "Line with Markers"
   • Check "Secondary Axis" for Growth % series
   • Click OK
4. Format the chart elements:
   • Column bars (Sales): Right-click → Format Data Series → Fill: solid blue
   • Line (Growth %): Right-click → Format Data Series → Line: orange, Marker: filled circle
   • Primary axis (left): Sales in dollars - format as currency
   • Secondary axis (right): Growth in percentage - format as %
5. Add titles and labels:

   Chart Title: "Monthly Sales Performance and Growth Rate"
   Primary Vertical Axis: "Sales Revenue ($)"
   Secondary Vertical Axis: "Growth Rate (%)"
   Horizontal Axis: "Month"

6. Add data labels to line only:
   • Click line → Chart Elements (+) → Data Labels → Above
   • Format labels to show percentage: Right-click → Format Data Labels → Number → Percentage
7. Polish the legend:
   • Chart Elements (+) → Legend → Right or Bottom
   • Click legend → Format Legend → adjust font size to 10pt

💡 Tips:

• When to use combo charts: When comparing metrics with different scales ($ vs %, units vs rate, volume vs percentage)
• Secondary axis is key: Always use secondary axis when scales differ significantly to avoid visual distortion
• Color strategy: Use contrasting colors for different series (blue/orange, green/red) for clarity
• Markers on lines: Help readers identify specific data points - use filled circles or diamonds
• Keyboard shortcut: Ctrl+1 opens Format pane for selected element
• Common business uses: Revenue vs margin %, units sold vs price, actual vs target, volume vs satisfaction

✅ Expected Result: A chart with blue columns showing sales amounts (left axis) and an orange line with markers showing growth percentage (right axis), clearly visualizing both volume and trend

🔄 Connection to Previous Learning: Extends Day 7's basic charts by combining multiple chart types for more sophisticated analysis.

Exercise 3: Create an Interactive Dashboard with Slicers

Goal: Learn to build an interactive dashboard that filters multiple charts and tables using slicers.

Setup:

• Create sample sales data: Date, Region, Product Category, Sales Rep, Revenue
• Minimum 50-100 rows for meaningful filtering

Step-by-Step Instructions:

1. Convert your data to a Table:
   • Select your data range
   • Insert tab → Table (or Ctrl+T)
   • Ensure "My table has headers" is checked → OK
   • Rename table: Table Design tab → Table Name → "SalesData"
2. Create a Pivot Table summary:
   • Insert → PivotTable
   • Rows: Region
   • Values: Sum of Revenue
   • Place on a new sheet called "Dashboard"
3. Create a Pivot Chart from the Pivot Table:
   • Click anywhere in Pivot Table
   • PivotTable Analyze tab → PivotChart
   • Choose Column Chart → OK
   • Resize and position on dashboard
4. Create a second analysis (by Product Category):
   • Copy the first Pivot Table (Ctrl+C, Ctrl+V)
   • Change Rows field from Region to Product Category
   • Insert a Pie Chart for this Pivot Table
   • Position below or beside the column chart
5. Add Slicers for interactivity:
   • Click on first Pivot Table
   • PivotTable Analyze tab → Insert Slicer
   • Check: Region, Product Category, Sales Rep
   • Click OK
6. Connect slicers to all Pivot Tables/Charts:
   • Right-click on a slicer → Report Connections
   • Check all Pivot Tables/Charts you want to filter
   • Repeat for each slicer
7. Format the slicers:
   • Click slicer → Slicer tab → Slicer Styles
   • Choose a professional style (blue or gray tones)
   • Resize slicers: Drag corners or use Slicer tab → Size
   • Arrange slicers vertically on the left or top of dashboard
8. Add KPI cards (summary metrics):
   • In separate cells, create formulas:

     =GETPIVOTDATA("Revenue",A3)  // Total Revenue from Pivot Table
     =COUNTA(UNIQUE(SalesData[Sales Rep]))  // Unique sales reps
     =AVERAGE(SalesData[Revenue])  // Average deal size

   • Format these cells with large fonts (20-24pt), bold
   • Add labels above: "Total Revenue", "Sales Reps", "Avg Deal"
   • Apply borders and background colors (light blue or green)
9. Add a timeline slicer (for dates):
   • Click Pivot Table → PivotTable Analyze → Insert Timeline
   • Select Date field → OK
   • This creates a visual date range selector
   • Position at top of dashboard
10. Final touches:
    • Add dashboard title in large text (18-24pt): "Sales Performance Dashboard"
    • Hide gridlines: View tab → uncheck Gridlines
    • Add background color to dashboard area (light gray #F5F5F5)
    • Align all elements neatly using Alt key while dragging (snaps to grid)

💡 Tips:

• Slicer connections: Always connect slicers to ALL related Pivot Tables for synchronized filtering
• Layout strategy: Place slicers on left or top, KPIs at top, charts in grid below
• Color consistency: Use same color scheme across all charts and slicers (pick 2-3 colors max)
• Keyboard shortcuts: Ctrl+T (create table), Alt+N+V (PivotTable), Ctrl+1 (format)
• Performance tip: Limit to 3-5 slicers; too many slows down the dashboard
• Mobile-friendly: Keep dashboard to one screen if possible (no scrolling needed)

✅ Expected Result: An interactive dashboard where clicking slicer buttons instantly filters all charts and tables, showing filtered data across multiple visualizations simultaneously

🔄 Connection to Previous Learning: Combines Day 7's Pivot Tables with Day 13's data interactivity concepts, creating professional business intelligence.

Exercise 4: Build a Dynamic Gauge Chart for KPI Tracking

Goal: Learn to create speedometer-style gauge charts to visualize KPIs against targets.

Setup:

• Data needed: Actual performance value (e.g., 75%), Target (100%), and threshold ranges (0-50% Poor, 50-75% Average, 75-100% Good)

Step-by-Step Instructions:

1. Set up the gauge data structure:

   A1: Category    B1: Value
   A2: Poor        B2: 50
   A3: Average     B3: 25      (75-50=25)
   A4: Good        B4: 25      (100-75=25)
   A5: Total       B5: 100
   A6: Actual      B6: 75      (your actual performance)

2. Create base doughnut chart:
   • Select A1:B5 (Category and Value, including Poor/Average/Good)
   • Insert tab → Charts → Doughnut Chart
   • This creates the colored gauge background
3. Add the pointer (actual value):
   • Right-click chart → Select Data
   • Add new series: Series name = "Actual", Values = B6 (75)
   • Add one more series: Series name = "Remaining", Values = =100-B6 (25)
   • Click OK
4. Format the gauge sections:
   • Click chart → right-click on the Actual and Remaining series → Format Data Series
   • Change to Pie chart type (not doughnut)
   • Set "Angle of first slice" to 270 degrees (makes it semicircle)
5. Color code the ranges:
   • Click Poor section → Format Data Point → Fill: Red
   • Click Average section → Format Data Point → Fill: Yellow
   • Click Good section → Format Data Point → Fill: Green
   • Click Remaining section (bottom half) → Fill: No fill, Border: No line (makes it invisible)
   • Click Actual pointer → Fill: Dark gray or black, adjust size to be thin
6. Adjust doughnut hole size:
   • Right-click outer series → Format Data Series
   • Doughnut Hole Size: 60-70% (creates gauge look)
7. Add the percentage in center:
   • Insert → Text Box
   • Type "75%" (link to cell B6: =B6&"%")
   • Format: Font size 36pt, Bold, Center aligned
   • Position in center of doughnut hole
   • Add label below: "Achievement Rate" in smaller font (14pt)
8. Remove unnecessary elements:
   • Delete legend (not needed with color coding)
   • Remove chart title or add meaningful title: "Sales Target Achievement"
   • Remove gridlines if any
9. Make it dynamic: Change B6 (Actual) to =75 or link to a cell that calculates actual performance The gauge will automatically update!

💡 Tips:

• Half-circle gauge: Use "Angle of first slice" = 270° to create semicircle; 180° for full circle
• Alternative method: Use a combination of pie and doughnut charts for more control
• Color psychology: Red = danger/poor, Yellow = caution/average, Green = good/safe (universal understanding)
• Dynamic ranges: Use formulas for Poor/Average/Good values so thresholds can be adjusted
• Multiple gauges: Create 3-4 gauges for different KPIs on same dashboard (Sales, Margin, Customer Sat, etc.)
• Advanced tip: Use data validation dropdown to select different KPIs, making one gauge show multiple metrics

✅ Expected Result: A semicircle gauge with red/yellow/green zones, a dark pointer showing actual performance at 75%, and large percentage displayed in the center

🔄 Connection to Previous Learning: Uses Day 7's chart concepts with creative formatting to create executive-level KPI visualizations.

Exercise 5: Create a Heat Map with Conditional Formatting

Goal: Learn to build visual heat maps using conditional formatting to show performance across two dimensions (e.g., sales by region and product).

Setup:

• Create a data table: Rows = Regions (North, South, East, West), Columns = Product Categories, Values = Sales amounts

Step-by-Step Instructions:

1. Set up your matrix data:

          Electronics  Clothing  Books    Furniture
   North     $45,000    $32,000  $28,000   $51,000
   South     $38,000    $42,000  $35,000   $29,000
   East      $52,000    $28,000  $41,000   $33,000
   West      $29,000    $51,000  $38,000   $45,000

   Arrange in cells A1:E5
2. Apply basic heat map formatting:
   • Select data range (B2:E5 - numbers only, not headers)
   • Home tab → Conditional Formatting → Color Scales
   • Choose Green-Yellow-Red Color Scale
   • Shortcut: Alt+H+L+S
3. Customize the color scale:
   • Home → Conditional Formatting → Manage Rules
   • Edit the color scale rule
   • Set custom thresholds:

     Minimum: Red (for lowest values)
     Midpoint: Yellow (50th percentile)
     Maximum: Green (for highest values)

   • Or use 3-Color Scale with specific values: Low=$25K, Mid=$40K, High=$55K
   • Click OK
4. Add data bars for extra visualization:
   • Select same range (B2:E5)
   • Conditional Formatting → Data Bars → Gradient Fill (blue)
   • Now you have both color scale AND data bars (double visualization)
   • Alternative: Use only one - color scales OR data bars, not both (cleaner look)
5. Format the table professionally:
   • Select entire table including headers (A1:E5)
   • Home tab → Format as Table → choose a table style
   • This adds filter buttons and alternating row colors
   • Table Design tab → Table Style Options → check "Banded Rows"
6. Add a totals row:
   • Table Design tab → Total Row (checkbox)
   • Excel adds SUM at bottom automatically
   • Apply same conditional formatting to totals row for consistency
7. Highlight top performers with icons:
   • Select data range (B2:E5)
   • Conditional Formatting → Icon Sets → 3 Arrows (Colored)
   • Manage Rules → Edit Rule → Icon Style: Show icon only
   • This adds up/down/side arrows based on performance
8. Add sparklines for trend (if you have time-series data):
   • Insert → Sparklines → Line (or Column)
   • Data Range: Select the region's sales across months
   • Location: New column F2
   • Repeat for each region
9. Create dynamic threshold with cell references:
   • In cell G1, type: Target: $40,000
   • Select B2:E5 → Conditional Formatting → New Rule
   • Format cells based on their values
   • Set minimum to reference $G$1 (your target threshold)
   • Now changing G1 updates entire heat map coloring!
10. Final polish:
    • Increase font size for readability (11-12pt)
    • Center-align all numbers
    • Format as currency: Ctrl+Shift+4 or Home → Currency
    • Add title above table: "Regional Sales Performance Heat Map"
    • Adjust column widths for clean appearance (Alt+H+O+I for AutoFit)

💡 Tips:

• Color scale vs Data bars: Color scales show relative values with colors; data bars show magnitude with bar length - choose based on audience preference
• Red-Green caution: About 8% of males have red-green color blindness - consider using blue-yellow or blue-orange scales for accessibility
• Threshold strategy: Use percentile-based thresholds (10th, 50th, 90th) for consistent ranges, or fixed values for business targets
• Keyboard shortcut: Alt+H+L (opens Conditional Formatting menu)
• Clear formatting: Select cells → Conditional Formatting → Clear Rules → Clear Rules from Selected Cells
• Business applications: Sales performance grids, project status dashboards, resource utilization maps, risk matrices

✅ Expected Result: A color-coded table where highest values are green, lowest are red, with optional data bars or icons, making patterns and outliers immediately visible at a glance

🔄 Connection to Previous Learning: Builds on Day 23's conditional formatting basics and Day 7's table features to create professional data visualizations without charts.