A multi-term assessment dashboard that transforms raw learner scores into clear performance insights, identifies recurring learning gaps, and recommends targeted academic interventions.
This Excel-based dashboard was designed to replace a fragmented, score-only reporting system with a structured analytical tool that a classroom teacher or academic coordinator could actually use. Rather than simply showing totals and positions, it answers the questions that matter most: Which learners are falling behind? Is a gap isolated or recurring? What should happen next?
The dashboard processes three terms of assessment data for a cohort of 46 learners across 17 subjects — 1,150 individual score records in total — and produces four analytical outputs: a summary dashboard, a learner-level analysis sheet, a subject-and-term breakdown, and a ready-to-use intervention plan with recommended support actions for every flagged learner.
Data analysis · Dashboard design · Intervention planning
Microsoft Excel · Data Cleaning · Data Visualisation · Learning Analytics
The source workbook stored assessment scores across three separate term sheets. Each sheet showed a learner's scores by subject, their total marks, and their class position — but nothing more. There was no way to see patterns across terms, no mechanism for identifying recurring gaps, and no structure for deciding which learners needed support and what that support should look like.
A teacher looking at Term 1 scores could see that a learner scored 50 in IGBO. But was that a one-off? Had it happened in Term 2 as well? Was IGBO a class-wide weakness or an individual struggle? Without a cross-term view, those questions had no answer — and without answers, interventions were guesswork.
Before any cleaning, the data was a flat list of learner names, subject scores, totals, and positions — repeated across three separate sheets with no consistent learner identifiers, no cross-term linking, and no performance flags. The sample below shows a representative extract of what the analysis data looked like once it was restructured into one unified table.
| Term | Learner ID | Subject | Score (%) | Below 70% | Performance Band |
|---|---|---|---|---|---|
| Term 1 | L017 | FRENCH | 58 | Yes | Priority |
| Term 1 | L017 | IGBO | 50 | Yes | Priority |
| Term 1 | L017 | VERBAL | 66 | Yes | Developing |
| Term 1 | L017 | ENGLISH | 70 | No | Secure |
| Term 2 | L017 | IGBO | 37 | Yes | Priority |
| Term 2 | L017 | YORUBA | 53 | Yes | Priority |
| Term 2 | L017 | FRENCH | 66 | Yes | Developing |
| Term 1 | L029 | NATIONAL VALUES | 56 | Yes | Priority |
| Term 2 | L029 | IGBO | 23 | Yes | Priority |
| Term 2 | L029 | PRE-VOCATIONAL | 42 | Yes | Priority |
| Term 1 | L018 | MATHEMATICS | 100 | No | High |
| Term 1 | L018 | MUSIC | 100 | No | High |
| Term 3 | L039 | HISTORY | 58 | Yes | Priority |
| Term 3 | L039 | CREATIVE ARTS | 52 | Yes | Priority |
| Term 1 | L002 | MATHEMATICS | 100 | No | High |
Sample extract from the 1,150-record Analysis Data sheet. Learner names replaced with stable anonymised IDs. Full dataset spans Terms 1–3, 17 subjects, 46 learners.
Before any analysis could begin, the source data needed to be restructured, standardised, and de-identified. The three term sheets were formatted differently, names were inconsistently spelled across terms, and there was no cross-term learner identifier. The preparation process involved four key steps.
Learner names were reviewed across all three terms. Where the same individual appeared in multiple terms, a stable anonymised ID (L001–L046) was assigned. Names were fully replaced in all analysis and output sheets, with IDs used consistently throughout.
The original format had one row per learner with each subject as a column. This was reshaped into a long-format table — one row per subject-score record — with columns for Term, Learner ID, Subject, Score, Below-Threshold Flag, and Performance Band. This structure makes analysis and cross-filtering reliable.
A 70% support threshold was applied to all scores. Each record was classified into one of four performance bands: High (85–100%), Secure (70–84%), Developing (60–69%), and Priority (<60%). These bands were used consistently across all analysis views.
Where a learner's ID could be reliably matched across consecutive terms, subject-level patterns were checked — distinguishing isolated low scores from recurring difficulties. Third-term learner identities were not always matchable, so term-change analysis was only applied where identity confidence was high.
The analysis was structured across three levels: cohort level (class averages, distribution, term trends), subject level (which subjects are class strengths vs. areas of concern), and learner level (who needs support, how urgently, and in which areas).
At learner level, a four-tier classification system was used to differentiate between levels of need and generate appropriate intervention recommendations automatically:
The charts below are rendered from the actual dashboard data. They replicate the four core visualisation panels built in Excel: subject performance ranking, term-by-term cohort trend, performance band distribution, and the learner support tier breakdown.
The class average across all three terms and 17 subjects was 87.3%, with Term 1 at 87.1%, Term 2 at 87.4%, and Term 3 at 87.3% — indicating stable, consistent performance at cohort level.
IGBO was the weakest subject overall at 76.3% average, with 20 below-threshold records across the cohort — the highest of any subject. This is a class-wide signal, not an individual one. Creative Arts (78.6%) and French (80.1%) also showed relative weakness.
Nine learner-term combinations met the Priority threshold. L029 (Terms 1 & 2) and L017 (all three terms) were the most consistently at-risk learners, with multiple recurring gaps in language subjects across terms.
7 learner-term records showed recurring subject difficulties — the same subject appearing below threshold in consecutive terms. L029's recurring gaps in French, Yoruba, and Igbo across Terms 1–2 is the clearest example, with Term 2 average falling to 59.7%.
Music (93.8%), Literature (93.0%), and Religious Studies (91.8%) were consistently the strongest subjects across all three terms. These can serve as confidence anchors and peer-learning opportunities for struggling learners.
L018 achieved 97.7% average across two terms — the highest in the cohort. L040 (97.6%) and L027 (97.5%) followed closely. These learners are strong candidates for extension tasks and structured peer-support roles.
The intervention plan assigns every learner-term record to one of four tiers, with a specific recommended response. This is not a static list — it is designed to be reviewed after each assessment cycle. Owners and review dates can be added directly in the Excel sheet.
Applied to learners with average below 70% or 3+ critical subjects below threshold. Involves identifying the root cause of difficulty in each flagged subject before assigning practice.
Applied to learners with 2–3 subjects below threshold. Focuses on the specific flagged subjects rather than whole-class re-teaching.
Applied to learners with one subject below threshold. Light-touch monitoring with subject-specific revision materials. Reviewed at the next assessment point.
Applied to learners with no subjects below threshold. Keeps high-performing learners engaged and creates structured opportunities for them to support peers — benefiting both parties.
Being transparent about the limits of an analysis is as important as the findings themselves. This dashboard is a strong starting point — but it makes clear what additional data would make it more powerful.