Excel Data Analysis 

Topic: Analyzing Student Enrollment Data in Microsoft Excel

Introduction to Data Analysis

Data analysis is the process of collecting, organizing, examining, and interpreting data to discover useful information and make decisions.

In Microsoft Excel, data analysis can be done using tools such as:

• Sorting – Arranging data in a specific order (e.g., alphabetical order or highest to lowest)
• Filtering – Showing only specific information from a dataset
• Pivot Tables – Summarizing large amounts of data quickly
• Charts – Presenting analyzed data visually

In this lesson, we will analyze a high school student enrollment record to find:

1. Total number of students from each county
2. Total number of students in each age group
3. Total number of students in each class

Example Dataset: High School Student Enrollment

Total Students: 18

Part 1: Preparing Data in Excel

Step 1: Open Microsoft Excel

1. Open Microsoft Excel
2. Select Blank Workbook
3. Enter the headings:

4. Enter all student records below the headings.

---

Step 2: Format the Data as a Table

1. Select the entire dataset
2. Click Insert
3. Select Table
4. Confirm My table has headers
5. Click OK

Benefits of using a table:

• Makes data easier to manage
• Allows filtering
• Helps create Pivot Tables easily

Analysis 1: Finding Total Students from Each County

Using Pivot Table

Steps:

1. Select any cell inside the data table
2. Go to Insert
3. Click PivotTable
4. Select New Worksheet
5. Click OK

The PivotTable Field List will appear.

Arrange Fields:

• Drag Origin County → Rows area
• Drag Student Name → Values area

Excel will automatically count students.

Expected Result:

Conclusion:
Nimba County has the highest number of enrolled students in this sample.

Analysis 2: Finding Students by Age Group

Steps:

1. Create another Pivot Table
2. Drag Age → Rows area
3. Drag Student Name → Values area

Expected Result:

Conclusion:
Most students in the dataset are between 16 and 17 years old.

Analysis 3: Finding Number of Students in Each Class

Steps:

1. Create another Pivot Table
2. Drag Class → Rows area
3. Drag Student Name → Values area

Expected Result:

Conclusion:
Grade 9 and Grade 10 have the highest enrollment.

Creating Charts from Analysis

Create County Enrollment Chart

Steps:

1. Select the County Pivot Table
2. Click Insert
3. Choose Column Chart
4. Select a suitable chart style
5. Add title:

"Student Enrollment by County"

Create Class Enrollment Chart

Steps:

1. Select Class Pivot Table
2. Click Insert
3. Choose Pie Chart
4. Add title:

"Students Distribution by Class"

Additional Excel Functions for Data Analysis

COUNT Function

Counts numbers in a range.

Example:

=COUNT(D2:D19)

Result:

18 students

COUNTIF Function

Counts students based on a condition.

Example:
Count students from Nimba County:
=COUNTIF(C2:C19,"Nimba")
Result:
5 students

COUNTIF for Classes

Count Grade 10 students:

=COUNTIF(B2:B19,"Grade 10")

Result:

5 students

AVERAGE Function

Find average student age:

=AVERAGE(D2:D19)

Practical Exercise for Students

Using the enrollment dataset:

1. Find the total number of students.
2. Identify the county with the highest enrollment.
3. Find the average age of students.
4. Create a chart showing enrollment by county.
5. Create a chart showing students in each class.
6. Write three conclusions from your analysis.

Summary

Excel data analysis helps users transform raw information into meaningful reports. By using tables, formulas, Pivot Tables, and charts, organizations can understand patterns and make better decisions.

In education, data analysis can help schools understand:

• Student population
• County representation
• Age distribution
• Class enrollment
• Planning and resource allocation