Summarising Student Database: Excel Dashboard Project
This project is a practice project for creating a dashboard in Excel using the COUNT and SUM formulas. This project is an exercise from the Excel Skills for Business: Intermediate I course. This exercise aims to summarize student data information from a dataset. Apart from that, it can also be visualized in a pie, bar or line chart.
Dataset
The data comes from the Excel Skills for Business: Intermediate I Coursera which I have uploaded to the drive here.
The student database consists of 16 columns and 248 rows, which contain student data, campus data, 3 semester grade data and 3 semester payments.
Objective
Prepare a report about the student at college which contains:
- Number of students at each Campus
- Number of students in each Course
- Total Payments by Campus
- Number of units by Semester
Data Analysis
Before we create a report, we first create a name range for the data
Formula → create from selection → crete name in: Top row
To identify the number of students at each campus we use the COUNTIF formula.
=COUNTIFS(Campus;Dashboard!A5)Then drop the formula down
To calculate the total number of students for each course, you can use the COUNTIF formula
=COUNTIFS(Course;A13)These two tables can be visualized with a pie chart to make it easier to understand and the values can be converted into percentages
Next, to find out the Number of students who Enrolled in more than 4 units and one unit only in each semester we can use the formula
=COUNTIFS(Number_of_units__Semester_1; ">4")
=COUNTIFS(Number_of_units__Semester_1; "=1")Then, to find out every student who fails in each semester, you can use the COUNTIFS formula where students who fail have a score of less than 50.
=COUNTIFS(Average_mark_Semester_1;"<50")
=COUNTIFS(Average_mark_Semester_1;"<50";Course;"Accounting")After that, a calculation is carried out to find out the total payment for each course using the SUMIFS formula
=SUMIFS(Total_Payment;Campus;A26)
=SUMIFS(Total_Payment;Campus;$A26;Course;C$25)
The total payment for each course can be visualized using a bar chart like this one below
Next, to find out the number of units for each semester, calculations can be made using the following formula:
=SUMIFS(Number_of_units_Semester_1;Course;B$33)Dan untuk total payment semester 1
=SUM(Payment_Semester_1)In addition, a trend line is added with insert → sparksline. This trend line shows the trend number of units each semester.
Calculating the number of units each semester can be visualized with a combo chart showing the total number of units on the bar chart and total students on the line chart.
Final Dashboard
Overall, this is a simple report and summary from students at college using the COUNTIF and SUMIF formulas in Microsoft Excel. This dashboard can still be improved. If you have suggestions and criticism, it will be very helpful in my learning, thank you
