MS Excel Lab Assignment
Objective
To understand the basic and advanced functionalities of MS Excel, including data entry, formatting,
mathematical, statistical, text, and financial functions, as well as data analysis using charts, Pivot
Tables, and Scenario Management.
Lab Tasks
Task 1: Introduction and Data Entry
Create a new workbook named “Excel_Practical_Assignment.xlsx”
In Sheet1, enter the following data:
Roll Business Date of Loan Interest Duration
Name Course Accounting Economics
No Law Exam Amount Rate (Years)
Aditi [Link]
101 85 78 90 10/10/2025 50000 8% 2
Sharma (H)
Rohit [Link]
102 72 81 76 10/10/2025 60000 9% 3
Mehra (H)
Sneha
103 BBA 91 88 82 10/10/2025 55000 7% 2
Kapoor
Karan [Link]
104 67 73 70 10/10/2025 45000 8% 2
Gupta (H)
Neha
105 BBA 88 92 85 10/10/2025 70000 10% 3
Verma
Arjun [Link]
106 74 76 80 10/10/2025 48000 9% 2
Malhotra (H)
Priya [Link]
107 91 85 89 10/10/2025 52000 8% 2
Singh (H)
108 Riya Patel BBA 83 84 77 10/10/2025 65000 9% 3
Manav [Link]
109 79 72 81 10/10/2025 62000 8% 3
Khurana (H)
Tanya
110 BBA 95 90 93 10/10/2025 75000 7% 2
Bansal
[Link]
111 Aarav Jain 82 77 84 10/10/2025 58000 9% 3
(H)
112 Ishita Nair BBA 89 91 86 10/10/2025 70000 10% 2
Dhruv [Link]
113 75 68 73 10/10/2025 48000 8% 2
Sethi (H)
Kritika [Link]
114 90 82 88 10/10/2025 53000 9% 3
Joshi (H)
Ananya
115 BBA 85 87 80 10/10/2025 62000 8% 2
Rao
Roll Business Date of Loan Interest Duration
Name Course Accounting Economics
No Law Exam Amount Rate (Years)
Harshita [Link]
116 70 74 69 10/10/2025 46000 9% 3
Patel (H)
Yash
117 BBA 93 88 90 10/10/2025 72000 7% 2
Agarwal
Meera [Link]
118 80 79 83 10/10/2025 54000 8% 3
Das (H)
Kabir
119 BBA 87 85 89 10/10/2025 69000 9% 3
Tandon
Naina [Link]
120 76 70 78 10/10/2025 50000 8% 2
Malhotra (H)
Task 2: Formatting and Editing
Make the headers bold and center-aligned.
Apply borders and background colors to headers.
Adjust column width for better readability.
Sort data alphabetically by Name.
Use Filter to display only [Link] (H) students.
Task 3: Using Formulas and Mathematical Functions
Create the following calculated columns:
1. Total Marks → =SUM(D2:F2)
2. Average Marks → =AVERAGE(D2:F2)
Below the table, calculate:
Class Total Marks: =SUM(G2:G21)
Class Average Marks: =AVERAGE(H2:H21)
Highest Marks: =MAX(G2:G21)
Lowest Marks: =MIN(G2:G21)
Task 4: Logical, Statistical and Date Functions
(a) Logical Function
Add a new column Result and use:
=IF(H2>=80, "Distinction", IF(H2>=60, "Pass", "Fail"))
This will categorize students based on their average marks.
(b) Statistical and Date Functions
Count number of students: =COUNT(A2:A21)
Count non-empty names: =COUNTA(B2:B21)
Display current date: =TODAY()
Display current date and time: =NOW()
Task 5: Text and Financial Functions
(a) Text Functions
Add new columns:
Full Detail: =CONCAT(B2, " - ", C2)
First 3 Letters of Name: =LEFT(B2,3)
Last 3 Letters of Course: =RIGHT(C2,3)
Length of Name: =LEN(B2)
(b) Financial Function
Calculate Monthly Loan Payment using:
=PMT(I2/12, J2*12, -H2)
(Rate divided by 12, Duration × 12, Loan amount as negative)
Task 6: Graphs and Charts
Create a Clustered Column Chart showing Average Marks vs Student Names.
Add Chart Title, Axis Titles, and Legend.
Format chart colors neatly.
Task 7: Pivot Table and Scenario Management
(a) Pivot Table
Insert a Pivot Table & Chart in a new sheet.
Rows → Course, Columns → Duration, Values → Max of Interest Rate
(b) Scenario Management
Implement Scenario Manager for Aditi Sharma.
Use What-If Analysis → Scenario Manager:
o Scenario 1: “Original Marks”
o Scenario 2: “Increased Marks by 10”
Compare both scenarios and observe the change in average.
Task 8: Summary Sheet
Create a new sheet for Scenario Summary.