Excel interview questions for MIS executive roles

The data described is hypothetical: a sales dump with columns Date, Branch, Executive, Product, Qty and Amount, in rows 2 to 5001.

Round one: conditional totals

Interviewer: Give me total Amount for the Noida branch in the month of August 2026.

Candidate: I'd use SUMIFS with a date range rather than filtering by hand:

=SUMIFS(F:F, B:B, "Noida", A:A, ">="&DATE(2026,8,1), A:A, "<="&DATE(2026,8,31))

If the report has the branch name in cell H2 and the month start in H3, I'd reference those instead: =SUMIFS(F:F, B:B, H2, A:A, ">="&H3, A:A, "<="&EOMONTH(H3,0)). That way the same formula works for any month.

What this checks: whether you know criteria are joined to operators with `&`, and whether you build reusable formulas driven by input cells instead of hardcoding.

Interviewer: The total doesn't match what the branch manager says. Where do you look first?

Candidate: Three places. First, whether the Date column is real dates or text — text dates are ignored by the date criteria. I'd check with =ISNUMBER(A2) or =COUNT(A:A) against the row count. Second, trailing spaces in Branch, so "Noida " doesn't match "Noida"; =COUNTIF(B:B,"Noida*") versus the exact count shows it quickly. Third, whether the manager's figure includes returns or cancelled orders that are in a different sheet.

What this checks: that you debug data before formulas. Most MIS mismatches are type and whitespace problems.

Round two: lookups

Interviewer: You have an Employee Master sheet with Employee ID and Name. Bring the executive's name into the sales dump using the ID.

Candidate: With VLOOKUP, exact match: =VLOOKUP(C2, Master!$A:$B, 2, FALSE). The FALSE is essential — without it Excel does an approximate match on unsorted data and returns wrong names without any error. If the office has Excel 365, I'd use =XLOOKUP(C2, Master!A:A, Master!B:B, "Not found"), which doesn't break when someone inserts a column in the master.

Interviewer: Some rows show #N/A. Should you wrap it in IFERROR?

Candidate: Not straight away. An #N/A means an ID in the dump isn't in the master — that could be a new joiner or a typo, and the manager should know. I'd first count them with =COUNTIF(D:D,"#N/A") — or more reliably =SUMPRODUCT(--ISNA(D2:D5001)) — and list them. After that, I'd use IFNA(..., "Not in master") so the gap is visible in the report instead of blank.

What this checks: judgement. IFERROR that hides every problem is the most common weak answer in MIS interviews.

Round three: dates and turnaround time

Interviewer: A complaints sheet has Logged Date and Closed Date. Calculate turnaround in working days, excluding Sundays and a holiday list.

Candidate: NETWORKDAYS excludes both Saturday and Sunday, which is wrong for a six-day office. I'd use NETWORKDAYS.INTL(B2, C2, 11, Holidays!A:A). Weekend code 11 means Sunday only. For open complaints where Closed Date is blank, I'd use =IF(C2="", "Open", NETWORKDAYS.INTL(B2, C2, 11, Holidays!A:A)), so they aren't counted as zero-day closures.

Interviewer: How would you show the month name from a date?

Candidate: =TEXT(A2,"mmm-yy") for a label. But for a pivot or sorting I'd keep a real date, like =EOMONTH(A2,-1)+1 for the first of the month, and format it to show the month, because text labels sort alphabetically — April before January.

What this checks: awareness of weekend conventions in Indian offices, handling blanks, and the difference between a date and a label.

Round four: cleaning

Interviewer: The Product column has values like " LAPTOP", "laptop " and "Laptop". Fix it.

Candidate: In a helper column: =PROPER(TRIM(CLEAN(E2))). TRIM removes extra spaces, CLEAN removes non-printing characters that come from system exports, and PROPER fixes the case. If there are non-breaking spaces from a web export, TRIM won't remove them, so I'd add SUBSTITUTE(E2, CHAR(160), " ") inside. Then paste as values and remove the helper.

Interviewer: How do you find duplicate invoice numbers without deleting them?

Candidate: =COUNTIF($G$2:$G$5001, G2)>1 in a helper column, or conditional formatting with Duplicate Values. I wouldn't use Remove Duplicates until I'd shown the list to someone, because two rows with one invoice number might be a genuine split or might be a double entry — the data can't tell me which.

Round five: the report itself

Interviewer: You send a daily sales MIS at 10 AM. What checks do you run before sending?

Candidate: A short list I do every time:

1. 1Row count of today's dump compared with yesterday's, so a half-exported file is caught.
2. 2Grand total in my report equal to SUM of the raw Amount column.
3. 3Sum of branch totals equal to the grand total, so no branch is missing from the lookup.
4. 4Any #N/A, #REF! or #VALUE! anywhere, using Go To Special, then Formulas, then Errors.
5. 5Date filter set to the right day.

Interviewer: Why absolute references like $A$1?

Candidate: So a reference stays fixed when I copy the formula. The lookup range in VLOOKUP must be absolute; the lookup value usually shouldn't be. Mixed references like $A2 are useful in a grid where the column is fixed but the row changes.

What this checks: that you own the accuracy of what you send. Interviewers for MIS roles often care more about this answer than any formula.

Short questions that come up in the same round

• Difference between COUNT, COUNTA and COUNTBLANK: numbers only, non-empty cells, empty cells.
• How to freeze the header row: View, Freeze Panes, Freeze Top Row.
• How to stop users typing anything except branch names: Data Validation with a list.
• Where a pivot table gets its data after you add new rows: it doesn't, unless the source is an Excel Table or you change the range and refresh. Converting the dump to a Table (Ctrl+T) is the fix.
• Shortcut to select a whole data block: Ctrl+A inside it, or Ctrl+Shift+End from the top-left cell.

If your interviews are moving past MIS into pivots, XLOOKUP and Power Query, continue with the advanced Excel question bank. Excel reporting is the starting module of the Data Analyst programme, which then moves the same reports into SQL and Power BI.