1.

Creating a Student Marksheet

• Input student names and marks for 5 subjects.
• Use formulas to calculate total, average, percentage, and grade

Roll No Name Web Based DSA Soft Skills Web Designing Maths Total Average Percentage Grade
1 Sohail Sheikh 85 78 92 88 76 419 139.66667 83.8 A
2 Mahavir Pandey 67 74 81 69 72 363 121 72.6 B+
3 Nandini Pathak 95 89 96 91 94 465 155 93 A+
4 Rohan Kumar 54 61 58 64 59 296 98.666667 59.2 C+
5 Swasti Jain 40 35 50 45 38 208 69.333333 41.6 C
6 Alka 32 68 98 56 78 332 110.66667 66.4 B
7 Abhinav Sharma 37 48 75 47 88 295 98.333333 59 C+
 2. Personal Budget Planner
• Monthly income vs expenses.
• Use formulas to calculate savings and visualize with pie charts.

1. Income Section
[Link] Source Amount (₹)
1 Salary 40,000
2 Freelance 10,000
3 Other income 2,000
Total Income 52,000

Total Income 52,000/-

2,000

10,000
Salary
Freelance
Other income

40,000
 2. Expense Section

[Link] Category Amount (₹)

1 Rent 12,000
2 Groceries 4,000
3 Transport 2,000
4 Subscriptions 500
5 Shopping 3,000
6 Others 1,500
Total Expenses 23,000

Chart Title

1,500

3,000 Rent
500 Groceries
Transport
2,000
12,000 Subscriptions
Shopping

4,000 Others
 3. Sales Record Table
• Enter daily sales for a week.
• Calculate total sales, average, highest, and lowest sale using functions.

Table: Weekly Sales Record:-

Day Sales (₹)

Monday ₹ 5,000

Tuesday ₹ 6,200

Wednesday ₹ 4,800

Thursday ₹ 7,000

Friday ₹ 5,300

Saturday ₹ 8,200

Sunday ₹ 6,000

Analysis using Functions:-

Metric Value

Total Sales ₹ 42,500

Average Sales ₹ 6,071

Highest Sale ₹ 8,200

Lowest Sale
₹ 4,800
 4. Attendance Sheet
• Track attendance for a month.
• Use conditional formatting to highlight absentees.

Name 01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan 07-Jan Total Present

Sohail A A P P P A P 4

Rohan P P A A A A A 2

Mahavir P P P P A P A 5

Nandini A P A P A P P 4

Swasti A A P A A A A 1

Alka P A P A P A P 4

Abhinav P A A A A P P 3
 5. Simple Invoice Generator
• Product list with quantity, price, and total.
• Include taxes using percentage formulas.

Product Name Quantity Price per Unit Total Tax (18%) Grand Total

Mouse 2 ₹ 500 ₹ 1,000 ₹ 180 ₹ 1,180

Keyboard 1 ₹ 1,200 ₹ 1,200 ₹ 216 ₹ 1,416

Headphones 3 ₹ 800 ₹ 2,400 ₹ 432 ₹ 2,832

 6. Loan EMI Calculator
• Use PMT function to calculate monthly EMI based on principal,
rate, and time

Input Value
Principal Amount (P) ₹ 5,00,000

Annual Interest Rate (R) 10%

Loan Tenure (Years) (T) 5

Monthly Interest Rate(M) 0.008333333

Number of Months (N) 60

Output Result

Monthly EMI ₹ 10,623.52

 7. Employee Database
• Store employee data with name, ID, department, salary, joining date.
• Use FILTER, SORT, and VLOOKUP.

Employee ID Name Department Salary (₹) Joining Date

EMP101 Ayesha Khan HR 40,000 01-01-2022

EMP102 Ravi Mehra IT 55,000 15-03-2021
EMP103 Sneha Sharma Finance 52,000 10-07-2020
EMP104 Arjun Patel Marketing 48,000 22-09-2019
EMP105 Nidhi Verma HR 42,000 05-06-2022

Sample Operations:-

1. SORT by Salary (Descending):

#VALUE!

2. FILTER by Department = "HR":

EMP101 Ayesha Khan HR 40000 44562
EMP105 Nidhi Verma HR 42000 44717

3. VLOOKUP for Employee Name and

Department of ID EMP103:
Sneha Sharma
Finance
 8. Sales Chart Dashboard
• Create bar, column, line, and pie charts for monthly or quarterly sales data.

Month Product Sales Amount (₹)

Sales Amount (₹)
Jan Laptop 50,000 80,000 70,000
70,000 60,000
60,000 55,000
Feb Laptop 60,000 50,000
50,000 45,000
40,000
40,000
Mar Laptop 70,000 30,000
20,000
Jan Mobile 40,000 10,000
0
Laptop Laptop Laptop Mobile Mobile Mobile
Feb Mobile 45,000
Jan Feb Mar Jan Feb Mar
Mar Mobile 55,000

Sales Amount (₹) Sales Amount (₹)

80,000
70,000
60,000
Sales Amount (₹) 50,000
40,000
30,000
20,000
0 20,000 40,000 60,000 80,000
10,000
Mar Mobile Feb Mobile Jan Mobile 0
Laptop Laptop Laptop Mobile Mobile Mobile
Mar Laptop Feb Laptop Jan Laptop
Jan Feb Mar Jan Feb Mar

Sales Amount (₹)

Jan Laptop Feb Laptop Mar Laptop

Jan Mobile Feb Mobile Mar Mobile
 9. Highlight Failing Students
• Use conditional formatting to highlight scores < 35 in red.

WB
Roll No Name DSA Maths Linux EVS
Programming
101 Sohail 89 67 60 89 90

102 Rohan 25 40 33 38 60

103 Mahavir 55 50 47 20 36

104 Nandini 70 80 90 85 78

105 Alka 29 34 87 28 35

106 Swasti 33 77 36 42 31

107 Abhinav 50 60 32 29 48
 10. Using Pivot Table for Analysis
• Analyze product sales by region and category using pivot tables.

Product Category Region Units Sold Unit Price (₹) Total Sales (₹)

Laptop Electronics North 50 40,000 20,00,000

Mobile Electronics West 100 20,000 20,00,000
Headphone
Accessories East 150 2,000 3,00,000
s
Printer Electronics South 30 8,000 2,40,000
Keyboard Accessories North 80 1,500 1,20,000

Row Labels Sum of Units SoldSum of Unit Price (₹)

Sum of Total Sales (₹)
Headphones 150 2,000 3,00,000
Accessories 150 2,000 3,00,000
Keyboard 80 1,500 1,20,000
Accessories 80 1,500 1,20,000
Laptop 50 40,000 20,00,000
Electronics 50 40,000 20,00,000
Mobile 100 20,000 20,00,000
Electronics 100 20,000 20,00,000
Printer 30 8,000 2,40,000
Electronics 30 8,000 2,40,000
Grand Total 410 71,500 46,60,000

Sum of Units SoldSum of Unit Price (₹) Sum of Total Sales (₹)
2500000

2000000

1500000

1000000 Values
Sum of Units Sold
500000
Sum of Unit Price (₹)
0
Sum of Total Sales (₹)
Accessories

Accessories

Electronics

Electronics

Electronics

HeadphonesKeyboard Laptop Mobile Printer

Product Category Region