Part X: Professional Dashboards

KPI Dashboard — Design, Build & Interact

Learning Objectives

• Understand dashboard design principles: layout grid, color scheme, typography, visual hierarchy
• Define KPIs with proper targets, thresholds, and measurement periods
• Create KPI cards showing actual vs target with percentage achievement and trend arrows
• Implement traffic light indicators using conditional formatting and icon sets
• Build gauge/speedometer charts using the doughnut chart trick
• Add interactive elements: slicers, dropdowns, combo boxes, scroll bars, option buttons
• Link form controls to cells and use INDIRECT for dynamic ranges
• Create dashboard navigation with hyperlinks, buttons, and VBA macros

Theory: Dashboard Design Principles

A professional dashboard follows a grid-based layout — typically a 12-column grid where KPI cards span 2-3 columns and charts span 4-6 columns. The key principles are:

1. Layout Grid

Divide your Excel sheet into zones: Header Zone (rows 1-3) for title and filters, KPI Zone (rows 4-8) for metric cards, Chart Zone (rows 9-25) for visualizations, and Detail Zone (rows 26+) for data tables. Merge cells strategically — each KPI card might occupy a 4-column × 5-row block.

2. Color Scheme

Use a maximum of 3-4 colors. A professional palette: dark navy (#0f172a) for headers, white (#ffffff) for card backgrounds, green (#059669) for positive metrics, red (#ef4444) for negative, and grey (#94a3b8) for secondary text. Avoid rainbow dashboards — they confuse rather than clarify.

3. Visual Hierarchy

The most important metric should be the largest element on screen. Use font sizes: 24pt for primary KPI values, 14pt for labels, 10pt for supporting text. The reader's eye should follow a Z-pattern: top-left → top-right → bottom-left → bottom-right.

4. Typography Rules

Use Calibri or Segoe UI for body text, bold weights for values. Numbers should be right-aligned, labels left-aligned. Format large numbers with Indian number system: ₹12,45,000 (not ₹1,245,000).

KPI Formulas & Calculations

Example 1: A Flipkart warehouse targets 5,000 dispatches/day. Today's actual is 4,750.

Example 2: Zomato delivery target: average 30 minutes. Actual average: 27 minutes.

Example 3: TCS quarterly revenue comparison:

Building a Gauge Chart (Doughnut Trick)

Excel doesn't have a native gauge chart. We create one using a doughnut chart with 2 data series:

1. Background ring: Three segments — Green zone (0-80%), Yellow zone (80-100%), Red zone (100-180%). These are fixed values: 80, 20, 80 = total 180.
2. Needle ring: Two segments — the KPI value and the remainder (180 - value). Format the remainder as "No Fill."
3. Rotate the chart 270° so the gauge starts at the bottom-left.
4. Set the bottom half to "No Fill" — this creates the semi-circle gauge effect.

Interactive Elements

Adding a Combo Box (Form Control)

1. Go to Developer → Insert → Combo Box (Form Control)
2. Draw the control on your dashboard
3. Right-click → Format Control
4. Set Input Range to your list (e.g., region names: North, South, East, West)
5. Set Cell Link to a hidden cell (e.g., Z1) — this stores the selected index number
6. Use =INDEX(RegionList, Z1) to get the selected region name

Solved Examples (1–15)

Example 1: Revenue KPI Card

KPI Card Layout: Achievement = =B4/A4*100 → 94%. Trend = =IF(B4>B3,"▲","▼") → ▲. Traffic Light = =IF(B4/A4>=1,"🟢",IF(B4/A4>=0.8,"🟡","🔴")) → 🟡

Example 2: Customer Count KPI

Target: 10,000 new customers. Actual: 11,250. Achievement: =11250/10000 = 112.5% 🟢. Trend vs last month (9,800): ▲ 14.8%.

Example 3: Average Order Value

A Myntra dashboard tracks AOV. Target: ₹1,800. Actual: ₹1,650. Achievement: 91.7% 🟡. Formula: =AVERAGE(Orders[Amount])

Example 4: Delivery SLA Compliance

Delhivery targets 95% on-time delivery. Formula: =COUNTIF(Deliveries[OnTime],"Yes")/COUNTA(Deliveries[OnTime]). Result: 4,275 out of 4,500 = 95% 🟢.

Example 5: Employee Productivity KPI

Infosys tracks revenue per employee. Target: ₹32 lakh/year. Actual: ₹29.5 lakh. Achievement: 92.2% 🟡.

Example 6: Scroll Bar for Month Selection

Create a scroll bar linked to cell Z2 (min=1, max=12, step=1). Use =INDEX({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},Z2) to display selected month. Link all KPI calculations to filter by this month using SUMIFS.

Example 7: Option Buttons for Region

Create 4 option buttons (North/South/East/West) in a Group Box. Link to cell Z3. Use =CHOOSE(Z3,"North","South","East","West") to get region name, then =SUMIFS(Sales[Amount],Sales[Region],ChosenRegion).

Example 8: Dynamic Chart Title

Link chart title to a cell: ="Sales Performance — "&SelectedRegion&" | "&SelectedMonth&" 2025". This updates automatically when filters change.

Example 9: Gauge Chart for NPS Score

Net Promoter Score target: 70. Actual: 63. Background: 60 (green), 20 (yellow), 20 (red). Needle value: 63. Remainder: 37. Rotate 270°. Hide bottom half.

Example 10: Sparkline KPI Cards

Add sparklines showing 12-month trend inside each KPI card. Select the card cell → Insert → Sparklines → Line. Data range: 12 monthly values. Set high point marker in green, low point in red.

Example 11: Conditional Icon Sets

Select achievement column → Home → Conditional Formatting → Icon Sets → 3 Traffic Lights. Custom: 🟢 ≥ 100, 🟡 ≥ 80, 🔴 < 80. Check "Show Icon Only" for clean KPI cards.

Example 12: Navigation Buttons

Insert a rounded rectangle shape → Right-click → Assign Macro → Create macro: Sub GoToSales() Sheets("Sales").Select: Range("A1").Select: End Sub. Format with green fill (#059669), white text, no border.

Example 13: Hyperlink Navigation

Use =HYPERLINK("#Sales!A1","📊 Go to Sales") for in-workbook navigation without macros. Style the cell as a button using borders and fill.

Example 14: Data Validation Dropdown

Data → Data Validation → List → Source: North,South,East,West. Named cell "RegionFilter". All SUMIFS reference this cell. Changing dropdown updates the entire dashboard.

Example 15: Complete KPI Card with All Elements

Practice Exercises

1. Create a KPI card for "Customer Satisfaction Score" with target 4.5/5, actual 4.2/5. Include achievement %, trend arrow (previous was 4.0), and traffic light.
2. Build a gauge chart showing production efficiency at 78% (target 90%). Color zones: Green 80-100%, Yellow 60-80%, Red 0-60%.
3. Create a combo box with 5 Indian cities (Mumbai, Delhi, Bangalore, Chennai, Kolkata). Link it to a SUMIFS formula that shows total sales for the selected city.
4. Design a scroll bar that lets users select a year (2020-2025). Connect it to a line chart showing monthly revenue for the selected year.
5. Build 4 option buttons for quarters (Q1-Q4) and create a dynamic chart title that updates based on selection.
6. Create a navigation dashboard with 5 buttons linking to different sheets: Sales, HR, Finance, Operations, Summary.
7. Design a KPI dashboard header with company logo placeholder, dashboard title, last-updated timestamp (=NOW()), and filter controls.
8. Build a traffic light matrix showing 6 KPIs × 4 regions with conditional formatting icon sets.

MCQ Quiz

Assignments

Sales Dashboard — Analytics & Visualization

Learning Objectives

• Structure sales data with proper columns: Date, Product, Region, Salesperson, Quantity, Amount
• Create an Excel Table data model with structured references
• Build PivotTables for multi-dimensional sales summarization
• Design 5 chart types: revenue trend, product mix, regional comparison, top salespeople, target vs actual
• Calculate Month-over-Month growth and visualize trends
• Add interactive slicers and timeline filters
• Apply conditional formatting to highlight top/bottom performers

Theory: Sales Data Model

Convert this data to an Excel Table (Ctrl+T) named tblSales. Structured references like tblSales[Amount] automatically expand as you add rows.

Example 1: Total Laptop sales in North region:

Example 2: Total sales in January 2025:

=SUMIFS(tblSales[Amount], tblSales[Date], ">="&DATE(2025,1,1), tblSales[Date], "<="&DATE(2025,1,31))

Example 3: Count of transactions by Raj Patel:

=COUNTIF(tblSales[Salesperson], "Raj Patel")  → Result: 2

MoM Growth Example:

Charts & Visualizations

Chart 1: Revenue Trend (Line Chart)

X-axis: Months. Y-axis: Revenue in ₹ Lakhs. Add a linear trendline to show overall direction. Format: smooth line, markers at data points, green color for positive months, red for declining months.

Chart 2: Product Mix (Pie/Donut Chart)

Use a PivotTable summarizing revenue by product. Create a doughnut chart showing each product's percentage contribution.

Chart 3: Regional Comparison (Clustered Bar)

Horizontal bar chart comparing North, South, East, West regions. Sort descending. Add data labels showing ₹ values in lakhs.

Chart 4: Top 5 Salespeople (Bar Chart)

Use LARGE and INDEX-MATCH to extract top 5 performers. Create horizontal bar chart sorted by revenue.

Chart 5: Target vs Actual (Combination Chart)

Target as a column, Actual as an overlapping column (narrower). Or use Target as a line overlay on Actual columns. Color: Target in light grey, Actual in green (if met) or red (if missed).

Adding Slicers & Timeline

1. Click inside your PivotTable
2. PivotTable Analyze → Insert Slicer → Select: Product, Region, Salesperson
3. PivotTable Analyze → Insert Timeline → Select: Date
4. Format slicers: Right-click → Slicer Settings → adjust columns. Use Slicer Styles to match your green theme.
5. Connect slicers to multiple PivotTables: Right-click slicer → Report Connections → check all PivotTables. Now one slicer filters all charts simultaneously.

Solved Examples (1–15)

Example 1: Regional Revenue Summary

Formula for % of Total: =B2/SUM(B$2:B$5) with absolute reference on the total.

Example 2: Product-wise PivotTable

Row Labels: Product. Values: Sum of Amount, Count of Transactions. Column Labels: Region. Filter: Date (by quarter). This creates a matrix showing revenue by product and region.

Example 3: Top Salesperson Extraction

=INDEX(tblSales[Salesperson], MATCH(LARGE(tblSales[Amount],1), tblSales[Amount], 0))

For top N, use LARGE(...,ROW()-StartRow+1) in a spill-down formula.

Example 4: Weighted Average Price

Formula: =SUMPRODUCT(tblSales[Amount])/SUM(tblSales[Qty]). This gives the average revenue per unit across all products.

Example 5: YoY Growth Comparison

Example 6: Sales by Day of Week

Add helper column: =TEXT([@Date],"dddd"). PivotTable by day. Finding: Saturday has highest sales (₹48L), Monday lowest (₹28L).

Example 7: Pareto Analysis (80/20 Rule)

Sort products by revenue descending. Add cumulative %. Top 20% products contributing 80% revenue. Use combo chart: bars for revenue, line for cumulative %.

Example 8: Conditional Formatting for Top/Bottom

Select salesperson revenue column → Conditional Formatting → Top/Bottom Rules → Top 10% (green fill). Bottom 10% (red fill). This instantly highlights star performers and those needing attention.

Examples 9-15: Quick Formulas

Practice Exercises

1. Create a sales dataset with 100 rows covering 4 products (Laptop, Mobile, Tablet, Accessories), 4 regions, 5 salespeople, for Jan-Jun 2025. Convert to Excel Table.
2. Build a PivotTable showing Revenue by Product (rows) and Region (columns) with Grand Totals.
3. Create a line chart showing monthly revenue trend with a 3-month moving average overlay.
4. Add 3 slicers (Product, Region, Salesperson) connected to all PivotTables on the dashboard.
5. Calculate MoM growth for each region and highlight months with negative growth in red.
6. Create a Target vs Actual column chart for 4 regions. Add data labels showing achievement %.
7. Build a Pareto chart identifying which products contribute 80% of revenue.
8. Design a complete sales dashboard fitting on one screen with 6 KPI cards and 4 charts.

MCQ Quiz