A single Google Sheets workbook that turns raw team data into a graded scorecard and a live KPI dashboard — powered by VLOOKUP, conditional formatting, SUMIFS / COUNTIFS and a one-click Apps Script automation.
What it demonstrates
Each tab is built around a technique clients actually ask for — no add-ons, just core Google Sheets done properly.
Dropdown & numeric data validation keep every entry consistent and error-free.
VLOOKUP (exact + approximate) with weighted scoring and green / amber / red conditional formatting.
SUMIFS / COUNTIF / AVERAGEIFS roll-ups feeding native Google Sheets charts.
A custom menu that refreshes the dashboard and emails a KPI summary on click.
The scorecard
Every column below is a live formula. The productivity bars, attendance dots and grade pills mirror the conditional formatting in the sheet.
| Emp ID | Name | Department | Productivity | Quality | Attendance | CSAT | Score | Grade |
|---|---|---|---|---|---|---|---|---|
| E001 | Ayaan Sheikh | Sales | 92 | 100% | 4.8 | 95.3 | Excellent | |
| E002 | Zoya Iqbal | Support | 88 | 95% | 4.6 | 90.9 | Excellent | |
| E003 | Hamza Ali | Engineering | 90 | 100% | 4.5 | 93.0 | Excellent | |
| E004 | Fatima Noor | Marketing | 78 | 91% | 4.0 | 81.6 | On Track | |
| E005 | Bilal Aslam | Operations | 75 | 86% | 3.8 | 79.0 | On Track | |
| E006 | Aisha Siddiqui | Sales | 68 | 82% | 3.5 | 70.8 | On Track | |
| E007 | Usman Tariq | Support | 85 | 100% | 4.7 | 91.6 | Excellent | |
| E008 | Sana Javed | Engineering | 72 | 77% | 3.6 | 72.9 | On Track | |
| E009 | Ali Raza | Marketing | 60 | 68% | 3.0 | 59.7 | Needs Attention | |
| E010 | Maria Yousuf | Operations | 91 | 100% | 4.9 | 95.7 | Excellent | |
| E011 | Hassan Shah | Sales | 74 | 91% | 4.1 | 80.4 | On Track | |
| E012 | Iqra Nadeem | Support | 66 | 82% | 3.4 | 70.5 | On Track | |
| E013 | Danish Kamal | Engineering | 87 | 95% | 4.4 | 90.9 | Excellent | |
| E014 | Nida Farhan | Marketing | 70 | 86% | 3.7 | 73.8 | On Track | |
| E015 | Kamran Zafar | Operations | 64 | 73% | 3.2 | 66.7 | Needs Attention |
The dashboard
Headline numbers and charts recalculate the moment the data changes.
How the score works
| Metric | Source | Weight |
|---|---|---|
| Productivity | Completed ÷ Assigned | 30% |
| Quality | Quality score (0–100) | 30% |
| Attendance | Present ÷ Working days | 20% |
| CSAT | Rating 1–5 → 100 | 20% |
| Score | Grade |
|---|---|
| 85 – 100 | Excellent |
| 70 – 84.9 | On Track |
| 0 – 69.9 | Needs Attention |
Under the hood
The same functions that run the whole workbook.
// Pull a field from the master Data tab by ID (exact match) =VLOOKUP($A3, Data!$A$3:$J$17, 2, FALSE) // Weighted score, reading the weights from the Ref tab =ROUND($D3*100*Ref!$E$3 + $E3*Ref!$E$4 + $F3*100*Ref!$E$5 + ($G3/5*100)*Ref!$E$6, 1) // Grade from the score (approximate-match VLOOKUP) =VLOOKUP($H3, Ref!$A$3:$B$5, 2, TRUE) // Dashboard KPIs =COUNTIF(Scorecard!$I$3:$I$17, "Excellent") =AVERAGEIFS(Scorecard!$H$3:$H$17, Scorecard!$C$3:$C$17, $A12) =SUMIFS(Data!$F$3:$F$17, Data!$C$3:$C$17, $A12)
Try it