Student Academic Analytics

Category: Educational Data Engineering & Interactive Reporting Tools Used: Microsoft Excel, Power Query, Pivot Tables, Advanced Logical Formulas Project Scope: Transforming raw assessment data into actionable classroom insights.

Data Acquisition & ETL (Extract, Transform, Load)

The project began with importing raw student assessment data from a CSV file. To ensure the integrity of the analysis, I utilized Power Query to perform a rigorous cleaning process:

• Duplicate Removal: Identified and removed redundant records (e.g., the "William Rooney" case) to prevent double-counting in performance averages.
• Null Value Treatment: Filtered out incomplete rows where core subjects or student IDs were missing, ensuring that the final dashboard reflects only verified data.
• Data Standardisation: Corrected data types (e.g., converting marks to 'Whole Numbers' and IDs to 'Text') to enable mathematical accuracy.

Logic Implementation (Advanced Formulas)

I engineered automated logic columns to remove manual grading effort and ensure 100% consistency:

• Grading Logic (Nested IF):

**=**IF([@Percentage]>=90%,"A",IF([@Percentage]>=75%,"B",IF([@Percentage]>=60,"C",IF([@Percentage]>=45%,"D","E"))))

This formula automatically assigns a tier based on the student's percentage.

• Result Logic (Pass/Fail):=IF([@Grade]="E", "Fail", "Pass")

  This secondary logic identifies "at-risk" students who fall into the lowest grade bracket.

Pivot Analysis & Insights

To extract meaningful trends from the 200+ rows of data, I developed 6 Specialized Pivot Tables. This allows for a multi-dimensional view of school health: