DATA ANALYSIS LAB ASSIGNMENT (EXCEL-BASED)
Level: Beginner to Intermediate
Duration: 2 Hrs
Total Marks: 100
Assignment Title:
Student Performance Data Analysis Using Microsoft Excel
Background Scenario
You are a data analyst hired by a secondary school to analyze student academic performance. The
school has provided raw data containing student names, subjects, marks, attendance, and gender.
Your task is to clean, analyze, and visualize the data using Microsoft Excel to help the school make
better academic decisions.
Learning Objectives
By completing this assignment, students should be able to:
• Enter and format data correctly in Excel
• Use formulas and functions (SUM, AVERAGE, IF, COUNT, etc.)
• Sort and filter data
• Perform data cleaning
• Analyze performance trends
• Create charts and dashboards
• Use Excel smart features (conditional formatting, suggestions, etc.)
Dataset Description
Create a dataset with at least 30 students and the following fields:
Student ID Name Gender Subject Marks Attendance (%)
Subjects should include:
• Mathematics
• English
• Science
• Geography
• History
PART A: DATA ENTRY AND FORMATTING (10 MARKS)
Tasks:
1. Open Microsoft Excel and create a new workbook.
2. Rename the sheet as "Student Data"
3. Enter the dataset manually or generate it.
4. Apply proper formatting:
o Bold headers
o Adjust column width
o Apply borders
o Use appropriate number formatting
PART B: DATA CLEANING (10 MARKS)
Tasks:
1. Check for:
o Missing values
o Duplicate records
2. Standardize text:
o Ensure consistent capitalization (e.g., "Male", "Female")
3. Remove or correct errors
4. Use:
o Find & Replace
o Remove Duplicates tool
PART C: BASIC DATA ANALYSIS (20 MARKS)
Perform the following calculations:
1. Total Marks per Student
o Use: SUM()
2. Average Marks
o Use: AVERAGE()
3. Grade Assignment
Use IF function:
4. =IF(Average>=80,"A",IF(Average>=60,"B",IF(Average>=40,"C","F")))
5. Pass/Fail Status
6. =IF(Average>=40,"Pass","Fail")
7. Count Students
o Use COUNT()
8. Highest and Lowest Marks
o Use MAX() and MIN()
PART D: SORTING AND FILTERING (10 MARKS)
Tasks:
1. Sort students:
o By Marks (Highest to Lowest)
2. Filter:
o Students who scored above 70
o Students who failed
3. Filter by Gender
PART E: CONDITIONAL FORMATTING (10 MARKS)
Tasks:
1. Highlight:
o Marks above 75 (Green)
o Marks below 40 (Red)
2. Apply data bars or color scales
PART F: DATA VISUALIZATION (15 MARKS)
Create the following charts:
1. Bar Chart
o Average marks per subject
2. Pie Chart
o Gender distribution
3. Column Chart
o Pass vs Fail
4. Customize charts:
o Titles
o Labels
o Legends
PART G: ADVANCED ANALYSIS (15 MARKS)
Tasks:
1. Pivot Table
o Analyze average marks by subject and gender
2. Pivot Chart
o Visualize performance trends
3. Attendance Analysis
o Compare attendance vs performance
PART H: EXCEL SMART FEATURES & AI (5 MARKS)
Tasks:
1. Use:
Flash Fill
o
o Recommended Charts
2. Try Excel AI features such as:
o Analyze Data (Ideas tool)
3. Write a short explanation of insights generated
PART I: INTERPRETATION & REPORT (5 MARKS)
Write a 1–2 page report explaining:
• Key findings
• Best-performing students
• Weak subjects
• Relationship between attendance and performance
• Recommendations for improvement
BONUS TASK (Optional – 5 Marks)
Create a simple dashboard using:
• Charts
• Slicers
• Summary metrics
SUBMISSION REQUIREMENTS
Students must submit:
1. Excel file (.xlsx)
2. Report document (Word/PDF)
3. Screenshots of charts