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

HR Dashboard — People Analytics

Learning Objectives

• Define core HR metrics: headcount, attrition rate, average tenure, gender ratio
• Structure employee data with proper fields: EmpID, Name, Department, Designation, JoinDate, Salary, Gender, Status
• Calculate attrition rate, retention rate, and turnover cost
• Analyze CTC components: Basic, HRA, DA, PF (Indian context)
• Build headcount, salary, and diversity visualizations
• Create age distribution and tenure analysis charts

Theory: HR Data Structure & Metrics

Example 1: Company has 500 employees, 75 resigned in FY25. Attrition = 75/500 × 100 = 15%.

Example 2: Department-wise attrition:

Example 3: Monthly attrition trend: =COUNTIFS(tblEmp[Status],"Resigned",tblEmp[LastDate],">="&DATE(2025,1,1),tblEmp[LastDate],"<="&DATE(2025,1,31))

CTC Breakdown Example: Employee with ₹12,00,000 CTC:

Gender Diversity Analysis

Salary Band Analysis

Group employees into salary bands using COUNTIFS:

Solved Examples (1–15)

Practice Exercises

1. Create an employee dataset with 200 rows including all required columns (EmpID through Status). Use RANDBETWEEN for salaries.
2. Calculate headcount by department and visualize as a horizontal bar chart sorted descending.
3. Build a CTC breakdown calculator: input CTC in one cell, auto-calculate Basic, HRA, DA, PF, and take-home.
4. Create a gender diversity doughnut chart by department. Identify which department has the lowest female representation.
5. Build a salary band histogram using FREQUENCY or COUNTIFS. Compare bands across genders.
6. Calculate monthly attrition trend for 12 months and create a line chart with a benchmark line at 1.5%.
7. Create an age pyramid chart (horizontal bar chart with males on left, females on right) by 5-year age groups.
8. Build a tenure distribution chart showing employees grouped by years of service (0-1, 1-3, 3-5, 5-10, 10+).

MCQ Quiz

School/Academic Dashboard — Student Performance Analytics

Learning Objectives

• Structure student data: RollNo, Name, Class, Section, Subject scores, Attendance
• Implement CBSE 9-point grading system and percentage-to-grade conversion
• Calculate pass/fail analysis, class averages, and subject-wise performance
• Generate toppers list and ranks using RANK function
• Track attendance and visualize patterns
• Create parent-friendly report cards
• Build multi-dimensional PivotTables for class × subject × section analysis

Theory: Student Data & Grading Systems

CBSE 9-Point Grading Scale

Example 1: Grade for Aarav's Maths (92): =IF(92>=91,"A1",...) → A1

Example 2: Grade for Rohan's Maths (35): → D (Pass, but just barely)

Example 3: Grade for a student scoring 28: → FAIL

Pass/Fail Analysis

A student passes if they score ≥33 in every subject:

Example: Rohan scores 45,52,35,40,48. Maths = 35 ≥ 33 ✓. All subjects ≥ 33 → PASS. If Maths were 30 → FAIL (even though total may be reasonable).

Subject-wise Analysis

Grade Distribution Using COUNTIFS

=COUNTIFS(StudentData[Section], "A", StudentData[Maths], ">="&91, StudentData[Maths], "<="&100)

This counts how many Section A students scored A1 in Maths.

Solved Examples (1–15)

Practice Exercises

1. Create a student dataset (50 students, Class 10, 2 sections, 5 subjects) using RANDBETWEEN(20,100) for marks.
2. Add columns for: Total, Percentage, Grade (CBSE), Rank, Pass/Fail. Use appropriate formulas.
3. Create a section-wise comparison bar chart showing average marks per subject for Section A vs B.
4. Build a grade distribution chart (how many A1, A2, B1... across the class) using a stacked bar.
5. Identify all students who failed in exactly one subject. Use SUMPRODUCT to count subjects below 33.
6. Create an attendance tracker with conditional formatting: Green ≥90%, Yellow 75-90%, Red <75%.
7. Build a report card template that auto-fills when a Roll Number is entered (using VLOOKUP/INDEX-MATCH).
8. Create a PivotTable showing Section × Subject average marks matrix.

MCQ Quiz

Research Dashboard — Survey Analysis & Data Visualization

Learning Objectives

• Understand research data visualization principles and chart selection
• Analyze survey data: Likert scale responses, demographics, open-ended coding
• Create statistical charts: box plots, histograms, scatter plots with regression lines
• Build a literature review tracker and experiment results comparison
• Design publication-quality charts following APA/journal standards
• Use Data Validation to create survey input forms

Theory: Research Data Visualization Principles

Chart Selection Guide for Research

Publication-Quality Chart Rules

1. Remove chart junk: No 3D effects, no gradient fills, no unnecessary gridlines, no decorative elements
2. Axis labels: Always include axis titles with units (e.g., "Response Time (ms)", "Revenue (₹ Lakhs)")
3. Font: Use a consistent sans-serif font (Calibri, Arial). Size: title 12pt, labels 10pt, axis 9pt
4. Colors: Use colorblind-friendly palettes. Avoid red-green combinations. Use sequential blues or categorical Set2 palette.
5. Legend: Place inside the chart area if space permits. Eliminate if only one data series.
6. Data labels: Add for bar charts with few categories. Omit for scatter plots (too cluttered).

Survey Data Analysis: Likert Scale

A 5-point Likert scale measures agreement: 1=Strongly Disagree, 2=Disagree, 3=Neutral, 4=Agree, 5=Strongly Agree.

Example 1: Q1 responses: Mean = 3.8, Mode = 5 → Most respondents agree/strongly agree that UPI is easy to use.

Example 2: Q2 responses: Mean = 3.2, Mode = 2 → Mixed opinions on UPI security — the mean is misleading; look at distribution.

Example 3: Likert response distribution for Q1:

Creating Box Plots (Stacked Bar Trick)

Excel 2016+ has built-in box plots (Insert → Statistical Chart → Box and Whisker). For older versions, use the stacked bar trick:

1. Calculate: Minimum, Q1 (=QUARTILE(data,1)), Median, Q3 (=QUARTILE(data,3)), Maximum
2. Create derived values: Bottom whisker = Min, Box bottom = Q1–Min, Median line = Median–Q1, Box top = Q3–Median, Top whisker = Max–Q3
3. Create a stacked bar chart from these 5 derived values
4. Format: Bottom whisker = no fill, Box = colored fill, Top whisker = no fill. Add error bars for whiskers.

Scatter Plot with Regression

To test relationship between two variables (e.g., study hours vs exam marks):

1. Select both columns → Insert → Scatter (X Y) chart
2. Click on data points → Add Trendline → Linear → Check "Display Equation" and "Display R-squared"
3. R² value tells how much variance is explained: R² > 0.7 = strong, 0.4-0.7 = moderate, < 0.4 = weak

Example: Study hours vs Marks: =CORREL(Hours, Marks) = 0.78. R² = 0.61. Regression equation: y = 4.2x + 32.5 (each additional study hour adds ~4.2 marks).

Literature Review Tracker

Use Data Validation dropdowns for Methodology (Survey/Experiment/Case Study/Meta-analysis) and Relevance (High/Medium/Low). Use COUNTIF to summarize: how many survey-based, how many experiment-based, distribution by year.

Histogram for Continuous Data

Use FREQUENCY or the built-in histogram chart:

Example: Age distribution of 100 survey respondents:

Data Validation for Survey Forms

Create a clean survey input form in Excel:

• Gender: Data Validation → List → Male,Female,Other
• Age: Data Validation → Whole Number → Between 18 and 80
• Likert responses: Data Validation → Whole Number → Between 1 and 5
• Email: Data Validation → Custom → =ISNUMBER(FIND("@",A1))

Solved Examples (1–15)

Practice Exercises

1. Create a survey dataset with 100 respondents, demographics (Gender, Age Group, City), and 5 Likert-scale questions about online shopping preferences.
2. Calculate mean, median, mode, and standard deviation for each survey question. Present in a summary table.
3. Create a diverging stacked bar chart for Likert responses across all 5 questions.
4. Build a histogram showing age distribution of respondents with 5-year bins.
5. Create a scatter plot with regression line showing relationship between age and average Likert score.
6. Calculate the correlation matrix for all 5 questions (5×5 table using CORREL).
7. Build a literature review tracker for 20 papers with summary statistics: papers by year, by methodology, by relevance.
8. Create a publication-quality chart following APA formatting: no gridlines, proper axis labels, legend, and title.

MCQ Quiz

Your Dashboard Journey

You've now mastered 5 professional dashboard types — from executive KPI dashboards to research analytics. Each dashboard followed the same core principles: clean data → summarization (PivotTables/formulas) → visualization (charts) → interactivity (slicers/filters) → insight (KPI cards with traffic lights and trends).

The real skill isn't knowing Excel functions — it's knowing which visualization tells the right story for your audience. A CEO needs a one-screen overview. A sales manager needs drill-down capability. A researcher needs statistical rigour. A school principal needs actionable alerts.

Coming Up Next: Part XI — Advanced Excel & Automation

We'll explore Power Query for data transformation, Power Pivot for data modelling, VBA macros for automation, and advanced techniques that turn Excel into a full-fledged business intelligence platform.