Are you ready to push your Excel skills beyond the basics? In Part-1 we covered 20 practical Excel questions that every beginner and intermediate user should practice. Now, in this Part-2, we’ll take things to the next level with advanced Excel test questions.
These are not just theory-based questions – they are real-life MIS and reporting problems that you’ll actually face in offices, job interviews, and while working on complex datasets.
If you’re preparing for an Excel test, MIS/Data Analyst interview, or simply want to sharpen your Excel logic with nested functions, this set of questions is for you.
Question 1: Employee Performance Bonus Calculation
Company XYZ has a policy to calculate bonuses based on employee ratings. Ratings and bonus % are defined in a lookup table.
If Rating = 5 → 25% of Salary
If Rating = 4 → 15% of Salary
If Rating = 3 → 8% of Salary
Else → No Bonus.
Employee Data Table:
| Emp ID | Name | Salary | Rating |
|---|---|---|---|
| E101 | Aditi | 40,000 | 5 |
| E102 | Rajesh | 35,000 | 3 |
| E103 | Sneha | 45,000 | 4 |
| E104 | Anil | 50,000 | 2 |
Question:
Write a formula to calculate Bonus Amount for each employee.
Show Answer:
=IF(D2=5,C2*25%,IF(D2=4,C2*15%,IF(D2=3,C2*8%,0)))
👉 (Or, use VLOOKUP with a separate table for flexibility.)
Question 2: Identify Duplicate Transactions
You are given a bank transaction table. The task is to flag duplicate transactions (same Date + Amount + Account).
Transactions Table:
| Date | Account No | Amount |
|---|---|---|
| 01-09-2025 | 10001 | 5000 |
| 01-09-2025 | 10001 | 5000 |
| 02-09-2025 | 10002 | 8000 |
| 02-09-2025 | 10001 | 2000 |
| 01-09-2025 | 10001 | 5000 |
Question:
Write a formula in Excel to return “Duplicate” or “Unique” for each row.
Show Answer:
=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)>1,"Duplicate","Unique")
Practice More: Try These 20 Real-World Excel Questions
Question 3: Dynamic Quarter-to-Date Sales
You are tracking sales across months. Create a formula to calculate Quarter-to-Date Sales dynamically, where the user can select the month (say “March”), and the formula should sum sales from Jan to March.
Sales Table:
| Month | Sales |
|---|---|
| Jan | 25,000 |
| Feb | 30,000 |
| Mar | 40,000 |
| Apr | 50,000 |
| May | 45,000 |
Question:
If user selects “March”, show Q1 total = 95,000.
Show Answer:
=SUM(OFFSET(B2,0,0,MATCH("Mar",A2:A13,0),1))
Question 4: Salary Bracket Classification
You want to classify employees based on their salary:
- Below 30,000 → “Low”
- 30,000–50,000 → “Medium”
- Above 50,000 → “High”
Employee Salary Table:
| Name | Salary |
|---|---|
| Ankit | 25,000 |
| Divya | 40,000 |
| Karan | 55,000 |
Show Answer:
=SWITCH(TRUE, B2<30000,"Low", B2<=50000,"Medium", B2>50000,"High")
Practice More: Top 50 Essential MIS Executive Interview Questions and Sample Answers
Question 5: Pull 2nd Highest Sale per Region
A company wants to know the 2nd highest sale in each region.
| Region | Sales |
|---|---|
| East | 25000 |
| East | 32000 |
| East | 28000 |
| West | 40000 |
| West | 35000 |
| West | 45000 |
Show Answer:
=LARGE(FILTER(B2:B7,A2:A7="East"),2)
Question 6: Extract Domain from Email IDs
You have a list of email IDs, and you want to extract the domain name (after “@”).
Show Answer:
=RIGHT(A2,LEN(A2)-FIND("@",A2))
Question 7: Create Dynamic Report – Employee Names in a Single Cell
HR wants a report that lists all employees from “IT Department” in a single cell, separated by commas.
| Name | Department |
|---|---|
| Ravi | IT |
| Sneha | HR |
| Karan | IT |
| Divya | Finance |
Show Answer:
=TEXTJOIN(", ",TRUE,FILTER(A2:A10,B2:B10="IT"))
Question 8: Show Employee Manager Name using INDIRECT
You have multiple sheets: Dept1, Dept2. Each sheet contains employee and manager details. In the summary sheet, user enters Dept name (say “Dept1”), and Employee ID. You need to return Manager Name dynamically.
Dept1 Sheet:
| Employee ID | Employee Name | Manager Name |
|---|---|---|
| D101 | Aditi | Mr. Sharma |
| D102 | Rajesh | Ms. Nair |
| D103 | Sneha | Mr. Verma |
| D104 | Anil | Ms. Mehta |
| D105 | Kavita | Mr. Gupta |
Dept2 Sheet:
| Employee ID | Employee Name | Manager Name |
|---|---|---|
| D201 | Kiran | Mr. Rajesh |
| D202 | Abhishek | Ms. Kashyap |
| D203 | Harish | Mr. Verma |
| D204 | Anil Kumar | Ms. Sharma |
| D205 | Mohan | Mr. Markam |
Summary Sheet:
| Dept Name | Employee ID | Manager Name |
|---|---|---|
| Dept1 | D102 | = |
| Dept2 | D205 | = |
Show Answer:
=VLOOKUP(B2,INDIRECT("'"&A2&"'!A2:C100"),3,FALSE)
👉 Here,A2 contains Dept Name,B2 contains Employee ID.Question 9: Find First Space Position in a String
Given full names, extract the First Name.
| Full Name |
|---|
| Amit Sharma |
| Rajeev Kumar |
Show Answer:
=LEFT(A2,SEARCH(" ",A2)-1)
Question 10: Create Dynamic Attendance Report
An HR team maintains an attendance sheet with Employee Name, Date, and Status (Present/Absent/Leave). They want a summary that shows:
- Total Present Days per employee
- List of Absent Dates in a single cell (comma-separated)
Attendance Data:
| Employee | Date | Status |
|---|---|---|
| Aditi | 01-09-2025 | Present |
| Aditi | 02-09-2025 | Absent |
| Aditi | 03-09-2025 | Present |
| Rajesh | 01-09-2025 | Leave |
| Rajesh | 02-09-2025 | Present |
| Rajesh | 03-09-2025 | Absent |
| Sneha | 01-09-2025 | Present |
| Sneha | 02-09-2025 | Present |
| Sneha | 03-09-2025 | Present |
Question:
- How to calculate total Present days for each employee?
- How to return a list of all Absent Dates for each employee in one cell?
Show Answer:
1. Total Present Days (COUNTIFS):
=COUNTIFS(A:A,"Aditi",C:C,"Present")
2. List of Absent Dates (TEXTJOIN + FILTER + IFERROR):
=IFERROR(TEXTJOIN(", ",TRUE,FILTER(B:B,(A:A="Aditi")*(C:C="Absent"))),"No Absents")
These are 10 very practical, advanced-level Excel test questions.
They are realistic MIS/Interview problems requiring nested logic, advanced lookups, and dynamic reporting skills.