Every MIS Executive, Data Analyst, and Accountant interview in India includes an Excel test. Companies use platforms like Mercer Mettl and iMocha to test your formula skills. Over 46,000 jobs on Foundit.in list Excel as a required skill. Here are the 50 most-asked questions — with answers.
Basic Excel Interview Questions
1. What is the difference between COUNT, COUNTA, and COUNTBLANK?
COUNT counts only numeric values. COUNTA counts all non-empty cells (text, numbers, errors). COUNTBLANK counts empty cells. Example: If A1:A5 has {10, "hello", "", 20, ""} then COUNT = 2, COUNTA = 3, COUNTBLANK = 2.
2. What does VLOOKUP do? Write the syntax.
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
It searches for a value in the first column of a table and returns a value from a specified column. Always use FALSE for exact match in interviews.
3. What is the difference between relative and absolute cell references?
Relative (A1) changes when copied. Absolute ($A$1) stays fixed. Mixed ($A1 or A$1) locks one dimension. Interviewers test this by asking you to drag a formula across rows/columns.
4. How do you remove duplicates in Excel?
Data tab → Remove Duplicates → Select columns → OK. For formula-based approach, use COUNTIF to flag duplicates: =IF(COUNTIF($A$1:A1, A1)>1, "Duplicate", "Unique")
5. What is the difference between SUM, SUMIF, and SUMIFS?
SUM adds all values. SUMIF adds values matching ONE condition. SUMIFS adds values matching MULTIPLE conditions. In interviews, they always ask SUMIFS with 2-3 criteria.
Intermediate Questions (Most Common in Interviews)
6. Write a VLOOKUP formula to find an employee's salary from a table.
=VLOOKUP(D2, A2:C100, 3, FALSE) — looks up the value in D2, searches column A of the table, returns column 3 (salary), exact match.
7. What is INDEX-MATCH and why is it better than VLOOKUP?
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Advantages over VLOOKUP: can look left, doesn't break when columns are inserted, faster on large datasets.
8. How do you use COUNTIFS with multiple conditions?
=COUNTIFS(A:A, "Delhi", B:B, ">5000", C:C, "Sales") — counts rows where city is Delhi AND amount > 5000 AND department is Sales.
9. What does IFERROR do?
=IFERROR(VLOOKUP(...), "Not Found") — wraps any formula and returns a custom value instead of showing #N/A, #REF!, or #VALUE! errors.
10. How do you concatenate text in Excel?
Three ways: =A1 & " " & B1 or =CONCATENATE(A1, " ", B1) or =TEXTJOIN(" ", TRUE, A1, B1). TEXTJOIN is the modern approach and handles arrays.
11. What is the TEXT function used for?
=TEXT(A1, "DD-MMM-YYYY") converts a date to text in a specific format. Common in MIS reports where you need dates as "20-Jun-2026" instead of serial numbers.
12. How do you extract the year, month, day from a date?
=YEAR(A1), =MONTH(A1), =DAY(A1). For interview: "How many months between two dates?" → =DATEDIF(A1, B1, "M")
13. What is conditional formatting?
Automatically changes cell color/font based on rules. Home tab → Conditional Formatting → New Rule. Example: Highlight all sales above ₹10,000 in green.
14. How do you use the IF function with AND/OR?
=IF(AND(A1>100, B1="Yes"), "Approved", "Rejected") — checks both conditions. Replace AND with OR to check if either condition is true.
15. What is a Pivot Table?
A summary tool that groups, counts, sums, and averages large datasets without formulas. Insert → Pivot Table → drag fields to Rows, Columns, Values. Every MIS interview asks this.
Advanced Questions (Senior Roles / Data Analyst)
16. Explain XLOOKUP and its advantages.
=XLOOKUP(lookup, lookup_array, return_array, "Not Found", 0)
Replaces VLOOKUP + HLOOKUP + INDEX-MATCH. Searches any direction, has built-in error handling, supports wildcards.
17. What is SUMPRODUCT and when do you use it?
=SUMPRODUCT((A2:A100="Delhi")*(B2:B100>5000)*(C2:C100)) — multiplies arrays and sums the result. Used for multi-condition calculations without helper columns.
18. How do you use INDIRECT?
=INDIRECT("Sheet"&A1&"!B2") — creates a cell reference from a text string. Used for dynamic sheet references in MIS dashboards.
19. What is the difference between FIND and SEARCH?
FIND is case-sensitive. SEARCH is not. Both return the position of a character in a string. SEARCH also supports wildcards (* and ?).
20. How do you handle errors in nested formulas?
Wrap with IFERROR: =IFERROR(INDEX(MATCH(...)), "Not Found"). For specific error types, use IFNA (catches only #N/A) or ERROR.TYPE.
21-50: Practice These on Verma Learning
The remaining 30 questions cover: OFFSET, CHOOSE, SWITCH, NETWORKDAYS, WORKDAY, DATEDIF, AGGREGATE, SUBTOTAL, nested IF with IFS, array formulas, UNIQUE, FILTER, SORT, SEQUENCE, LET, LAMBDA, Power Query basics, and data validation.
Instead of just reading answers, practice solving them with real datasets. That's what interviewers actually test.
Practice These Questions for Free
Verma Learning gives you 7,000+ questions across 90 formulas with real datasets. Type the formula yourself and get instant feedback.
Download Free — vermalearning.comTips for Excel Interview Tests
- Speed matters — most tests are timed (30-60 minutes). Practice daily to build muscle memory.
- Always use exact match — in VLOOKUP, always set the last argument to FALSE.
- Know keyboard shortcuts — Ctrl+Shift+L (filter), Alt+= (autosum), Ctrl+D (fill down).
- Practice with Indian datasets — interview data uses Indian names, cities, and ₹ amounts.
- Don't just memorize — practice on real datasets until formulas feel automatic.