Excel Interview Questions for Office Jobs (With Answers)

Short answer

For office roles in India (MIS executive, accounts assistant, data entry, back office, sales coordinator), Excel interviews usually test five things: lookups (VLOOKUP/XLOOKUP), conditional formulas (IF, SUMIFS, COUNTIFS), pivot tables, data cleaning, and basic formatting/shortcuts. Many companies also give a 15–30 minute practical test on a sample sheet. Practise on real-looking data, not just theory.

I put this list together from the topics we cover in YUNOVA's Excel skill and the tasks that show up again and again in office job posts. Read the answers, then close this page and say them in your own words.

How Excel is tested in interviews

1. Verbal questions – "What is the difference between COUNT and COUNTA?"
2. Screen test – a sheet with sales or attendance data and 4–6 tasks.
3. Scenario questions – "Your manager wants region-wise monthly sales. How will you do it?"

The answers below are short on purpose. Say them in your own words.

Basic questions

1. What is the difference between a formula and a function? A formula is anything that starts with =, like =A2+B2. A function is a built-in formula with a name, like SUM or VLOOKUP.

2. COUNT vs COUNTA vs COUNTBLANK? COUNT counts cells with numbers, COUNTA counts non-empty cells, COUNTBLANK counts empty ones.

3. What is absolute vs relative reference? A2 changes when you copy the formula; $A$2 stays fixed. I use $ for fixed cells like a tax rate.

4. How do you freeze the header row? View > Freeze Panes > Freeze Top Row.

5. How do you remove duplicates? Data > Remove Duplicates, after selecting the columns that define a duplicate. If I need to keep the original, I copy the data first or highlight duplicates with Conditional Formatting.

Formula questions

6. Explain VLOOKUP and its limitation. =VLOOKUP(value, table, column_number, FALSE) finds a value in the first column and returns data from another column. It cannot look to the left, and without FALSE it can return an approximate (wrong) match.

7. What is XLOOKUP, and why is it better? It takes separate lookup and return ranges, so it can look left, defaults to exact match, and has a built-in "if not found" argument. It is available in Microsoft 365 and Excel 2021 or later. More detail: VLOOKUP vs XLOOKUP.

8. How do you get total sales for one region and one month? =SUMIFS(Sales, Region, "North", Month, "Oct").

9. How do you handle #N/A in a lookup? Check for spaces (TRIM) and text-vs-number mismatch first, then wrap with IFERROR or IFNA to show a clear message.

10. Write a formula for grades: 75+ A, 50+ B, else C. =IF(B2>=75,"A",IF(B2>=50,"B","C")) or =IFS(B2>=75,"A",B2>=50,"B",TRUE,"C").

11. How do you join first and last name? =A2&" "&B2 or =TEXTJOIN(" ",TRUE,A2,B2).

12. How do you extract the first 5 characters of a code? =LEFT(A2,5).

Pivot table questions

13. What is a pivot table? A tool that summarises large data – totals, counts, averages – by category without formulas. Insert > PivotTable.

14. How do you show month-wise data when the sheet has dates? Put the date in Rows, then right-click > Group > Months (and Years if data spans years).

15. Pivot is not showing new rows. Why? The source range is fixed. Convert the data to a Table (Ctrl+T) so the pivot picks up new rows, then Refresh.

16. What is a slicer? A button-style filter for pivots and tables, useful for dashboards.

Data cleaning questions

17. Names have extra spaces and mixed case. Fix? =PROPER(TRIM(A2)), then paste as values.

18. Numbers are stored as text. Fix? Use the warning icon > Convert to Number, or Text to Columns > Finish, or =VALUE(A2).

19. Split "Rahul Sharma" into two columns? Data > Text to Columns > Delimited > Space. Flash Fill (Ctrl+E) also works.

Scenario questions

20. Manager wants a daily sales report that updates automatically. Approach? Keep raw data as a Table, build a pivot plus a chart on a separate sheet, add slicers for region and product, and refresh daily. If the data comes from the same source every day, I would look at Power Query.

21. Two lists of customer IDs – find who is missing in the second list. =IF(ISNA(MATCH(A2,List2,0)),"Missing","OK") or XLOOKUP with a "Missing" message. Conditional Formatting duplicates is a quick visual check.

Typical practical test (and how to prepare)

Task	What they check	Practise with
Fill price from a product master	Lookup	VLOOKUP + XLOOKUP
Region-wise total	SUMIFS or pivot	Both ways
Count present days	COUNTIF	Attendance sheet
Highlight values below target	Conditional formatting	Rule with formula
Make a chart	Chart choice	Column vs line
Clean a name column	TRIM, PROPER	Messy list

Preparation tip: Make one practice file with 200 rows of fake sales data (date, region, salesperson, product, qty, rate). Do every task above on it twice, once with formulas and once with a pivot. Time yourself.

For the full formula list behind these answers, read Excel formulas in Hindi. For general interview prep, see interview questions for freshers.

FAQ

Which Excel level is needed for an MIS executive job?

Comfort with lookups, SUMIFS/COUNTIFS, pivot tables, charts and data cleaning. Some roles also want basic macros or Power Query.

Do interviewers ask Excel shortcuts?

Sometimes: Ctrl+T (table), Ctrl+Shift+L (filter), Alt+= (AutoSum), Ctrl+E (Flash Fill), F4 (lock reference).

Should I mention an Excel certificate on my resume?

Yes, if it is verifiable and you can back it up in a practical test. Put the skills you can actually do next to it.

How long does it take to be interview-ready in Excel?

It depends on your starting point and daily time. A focused learner who practises daily on real data usually covers these topics in a few weeks.

Learn it free on YUNOVA

YUNOVA की Excel skill free है और interview वाले सभी topics cover करती है, quizzes और final test के साथ। Certificate पर verify link होता है, जिसे आप resume पर लगा सकते हैं।

Prepare with the free Excel skill on YUNOVA