Advanced Excel Assignments
Module 1 – Excel Basics
Assignment 1 – Student List
Steps: Enter headings (Name, Roll No., Class) → Fill rows with sample data.
Solution: Simple table with 10 rows.
Expected Output: Well-aligned student table.
Assignment 2 – Marksheet Formatting
Steps: Select headings → Bold → Fill color (light yellow) → Borders for all cells.
Expected Output: A neatly formatted table with readable headings.
Assignment 3 – Total & Average Marks
Formula:
makefile
CopyEdit
=SUM(B2:D2) → Total Marks
=AVERAGE(B2:D2) → Average Marks
Expected Output: Each student’s total and average calculated automatically.
Assignment 4 – Sort & Filter Sales List
Steps: Sort sales column Z→A → Data → Filter → Number Filters → Greater than 500.
Expected Output: Only top-selling products appear.
Module 2 – Core Excel Functions
Assignment 5 – Full Name Creation
Formula:
=PROPER(CONCATENATE(A2," ",B2))
Expected Output: "rohan sharma" → "Rohan Sharma".
Assignment 6 – Pass/Fail Grading
Formula:
=IF(B2>=40,"Pass","Fail")
Expected Output: Each student labeled Pass or Fail.
Assignment 7 – Employee Service Years
Formula:
=DATEDIF(B2,TOASSIGNMENT(),"Y")
Expected Output: "10" if employee joined 10 years ago.
Assignment 8 – Highlight Overdue Payments
Steps: Conditional Formatting → New Rule → =C2<TOASSIGNMENT() → Fill color red.
Expected Output: Overdue dates highlighted.
Module 3 – Data Lookup & Organization
Assignment 9 – Product Price Lookup
Formula:
=VLOOKUP(E2,A2:C10,3,FALSE)
Expected Output: Correct product price shown.
Assignment 10 – Salary Search with INDEX+MATCH
Formula:
=INDEX(C2:C10,MATCH(E2,A2:A10,0))
Expected Output: Returns correct salary for given employee name.
Assignment 11 – Dept-wise Salary Pivot Table
Steps: Dept → Rows, Salary → Values (Sum).
Expected Output: Table showing total salary per department.
Assignment 12 – Monthly Sales Chart
Steps: Select Month & Sales columns → Insert → Column Chart.
Expected Output: Colorful bar chart showing monthly trend.
Module 4 – Advanced Analysis
Assignment 13 – Sales by Year & Region
Steps: Year → Columns, Region → Rows, Sales → Values (Sum). Add slicers for
filtering.
Expected Output: Pivot table showing regional sales per year.
Assignment 14 – Sales vs. Target Combo Chart
Steps: Insert → Combo Chart → Sales as columns, Target as line.
Expected Output: Dual chart comparing actual sales and targets.
Assignment 15 – Attendance Drop-Down
Steps: Data Validation → List: Present, Absent.
Expected Output: Click cell to select Present/Absent.
Assignment 16 – Loan EMI Goal Seek
Formula:
=PMT(Interest/12, Years*12, -LoanAmount)
Steps: Goal Seek → Set EMI to ₹5000 by changing Loan Amount.
Expected Output: Loan amount adjusted to match EMI.
Module 5 – Data Cleaning & Automation
Assignment 17 – Clean Customer Data
Steps: Data → Remove Duplicates → OK.
Expected Output: Only unique names remain.
Assignment 18 – Merge Multiple Sheets in Power Query
Steps: Data → Get Data → From Workbook → Append Queries.
Expected Output: Single table containing data from all sheets.
Assignment 19 – Macro to Format Report
Steps: Record Macro → Bold headers, apply colors, AutoFit columns → Stop
Recording.
Expected Output: Macro applies consistent formatting instantly.
Assignment 20 – VBA to Highlight High Salaries
Code:
vba
CopyEdit
Sub HighlightHighSalary()
For Each cell In Range("B2:B20")
If cell.Value > 10000 Then
cell.Interior.Color = RGB(255, 0, 0)
End If
Next cell
End Sub
Expected Output: Salaries above ₹10,000 in red.
Module 6 – Final Projects & Dashboards
Assignment 21 – Sales Dashboard Basics
Steps: Combine sales table, pivot table, and chart on one sheet.
Expected Output: Basic dashboard with key KPIs.
Assignment 22 – Interactive HR Dashboard
Steps: Use slicers for Dept & Gender → Link to pivot tables → Add charts.
Expected Output: Click slicers to see filtered results instantly.
Assignment 23 – Annual Business Report
Steps: Combine yearly data, pivot charts, and summary metrics.
Expected Output: Clean report for management.
Assignment 24 – Personal Finance Tracker
Steps: Table for income & expenses → Chart for spending breakdown → Summary
totals.
Expected Output: Dashboard showing total income, expenses, and savings.
