Excel Data Analysis Case Study: Student Performance Analysis
Scenario: You are provided with a dataset containing details of 100 students, including their
course, age, grade, attendance percentage, and total marks. Your goal is to analyze the data to
draw insights into student performance.
Tasks:
1. Sorting Data:
Sort the data by Course in alphabetical order.
Then sort by Total Marks in descending order within each course.
2. Filtering Data:
Filter to show only students with grades A or B.
Identify students with an Attendance (%) below 75.
3. Formulas:
Calculate the average, maximum, Total Marks for each course.
Find the maximum Attendance (%) for students in each course.
4. Additional Analysis:
Create a new sheet summarizing the number of students per grade in
each course.
Highlight students with Total Marks greater than 450 using conditional
formatting.
Chart Creation Tasks
5. Bar Chart:
o Create a bar chart showing the number of students in each grade (A, B, C,
etc.).
6. Pie Chart:
o Create a pie chart to visualize the distribution of students count across
different courses.
7. Line Chart:
o Create a line chart to display the average Total Marks for students in each
course.
8. Scatter Plot:
o Create a scatter plot to analyze the relationship between Attendance (%) and
Total Marks.
Steps to Complete:
1. Open the provided Excel file.
2. Perform the tasks listed above using Excel features such as sorting, filtering, and
formula functions.
3. Perform each task and sub-task in a separate sheet
