Skip to content
Tutorials Center
My learning

Topics

Lesson 92 of 93 ยท Practice and Review

Excel Interview Questions and Answers

Forty common Excel interview questions, from cell references and IF to VLOOKUP, XLOOKUP, PivotTables and Power Query, with short answers and runnable demos.

Excel interviews rarely ask for obscure tricks. They check whether you understand a short list of ideas deeply: references, conditional logic, lookups, dates and text, tables and PivotTables. A good answer is short, correct, and backed by a small example, because an interviewer wants to hear that you have actually used the feature.

This lesson gives forty questions in the order they usually come up, each with a compact answer and a link to the lesson that explains it in depth. Excel cannot run on this site, so the formulas are shown as text. Two live JavaScript demos, labelled as stand-ins, let you check the behavior behind the trickiest answers: how the counting functions treat blanks, how exact and approximate lookups differ, how criteria like ">=50" and wildcards work, and how dates and text really behave.

What interviewers test

Basics Cells, references, formats.
Logic IF, SUMIFS, errors.
Lookups VLOOKUP to XLOOKUP.
Analysis Tables, pivots, queries.

Explain the why, then give a tiny example.

1 / 2

Basics (questions 1 to 10)

1. What is the difference between a workbook and a worksheet? A workbook is the whole file (.xlsx); a worksheet is one tab inside it. A workbook holds one or many worksheets. See workbooks and sheets.

2. What is a cell, and what is a range? A cell is one box with an address such as B3. A range is a block of cells, written B3:D10. See ranges and named ranges.

3. What is the difference between a formula and a function? A formula is anything that starts with =, like =A1*2+5. A function is a built-in named calculation such as SUM that you can use inside a formula.

4. Explain relative, absolute and mixed references. A1 changes when copied, $A$1 never changes, and $A1 or A$1 lock only the column or only the row. F4 cycles through them. See cell references.

5. What does the order of operations look like in Excel? Parentheses first, then percent, exponent, multiplication and division, then addition and subtraction, and finally concatenation and comparisons. So =2+3*4 is 14 and =(2+3)*4 is 20. See your first formulas.

6. What is the difference between COUNT, COUNTA and COUNTBLANK? COUNT counts numbers (dates included), COUNTA counts anything that is not empty, and COUNTBLANK counts empty cells and cells that show "". See SUM and statistics basics.

7. A number is left-aligned and SUM ignores it. Why? It is stored as text. Convert it with VALUE, Text to Columns, or multiply by 1, then fix the cause (often an import or a leading apostrophe). See TEXT, VALUE and number-as-text problems.

8. What is the difference between formatting a cell and changing its value? Formatting changes only the display. A cell showing 3.14 may hold 3.14159265, and formulas use the stored number. ROUND changes the value. See formatting cells.

9. What is a named range and why use one? A name like TaxRate points to a cell or range, making formulas readable (=Price*TaxRate) and easier to update in one place.

10. What are Autofill and Flash Fill? Autofill extends a series or copies formulas by dragging the fill handle. Flash Fill (Ctrl + E) detects a pattern from your example, such as splitting first names, and fills the rest. See entering and editing data.

Logic, aggregates and errors (questions 11 to 20)

11. How does IF work? =IF(test, value_if_true, value_if_false). Example: =IF(B2>=50,"Pass","Fail"). See IF and nested IF.

12. When would you use IFS or SWITCH instead of nested IF? When there are many cases. IFS takes test and result pairs in order; SWITCH compares one value against exact matches. Both read better than five nested IFs. See IFS and SWITCH.

13. How do you test two conditions at once? AND(a,b) is true when both are, OR(a,b) when either is: =IF(AND(B2>=50,C2="Yes"),"OK","Check"). See AND, OR and NOT.

14. What is the difference between SUMIF and SUMIFS? SUMIF(range, criteria, sum_range) has one condition; SUMIFS(sum_range, range1, crit1, range2, crit2, ...) has many, and the sum range comes first. See conditional aggregates.

15. How do you count cells that start with "A"? =COUNTIF(A2:A100,"A*"). The * matches any characters and ? matches exactly one.

16. How do you count values greater than 50 with COUNTIF? Put the operator inside the criteria text: =COUNTIF(B2:B100,">50"). To use a cell, join them: ">"&D1.

17. What does IFERROR do, and what is its risk? It returns a fallback when a formula errors: =IFERROR(A2/B2,0). The risk is hiding real bugs such as a wrong range. Prefer IFNA when you only expect #N/A. See handling errors and blanks.

18. What causes #N/A, #REF!, #VALUE! and #DIV/0!? #N/A: a lookup found nothing. #REF!: a reference points at a deleted cell. #VALUE!: the wrong type of argument, such as text in arithmetic. #DIV/0!: division by zero or by an empty cell. See formula errors.

19. What is the difference between ROUND and a number format? ROUND(2.567,1) returns 2.6 as a stored value; a one-decimal format only displays 2.6 while the cell still holds 2.567. See percentages and rounding.

20. How do you calculate a percentage change? =(New-Old)/Old, formatted as a percentage. Example: from 80 to 100 is =(100-80)/80, or 25%.

Lookups (questions 21 to 28)

21. How does VLOOKUP work? =VLOOKUP(key, table, col_index, FALSE) finds the key in the first column of the table and returns a value from the given column. See VLOOKUP.

22. What is the difference between exact and approximate match? FALSE (or 0) demands an exact key. TRUE (or omitted) finds the largest value not above the key and needs the first column sorted ascending. Forgetting the fourth argument is a classic bug.

23. What are VLOOKUP's limits? It looks only to the right of the key column, it breaks when columns are inserted because the index is a number, and it returns only the first match.

24. How do INDEX and MATCH fix that? MATCH finds the row position and INDEX returns the value from any column: =INDEX(C2:C100,MATCH(F2,A2:A100,0)). It can look left and survives inserted columns. See INDEX and MATCH.

25. Why is XLOOKUP better? It needs no column number, defaults to exact match, can return a custom not-found value, searches from the end, and returns several columns. It exists only in Excel 2021 and Microsoft 365. See XLOOKUP.

26. How do you look up a grade from a score? Use an approximate match on a sorted band table: =VLOOKUP(B2,Bands,2,TRUE), or XLOOKUP with match mode -1. See approximate-match lookups.

27. How do you look up with two criteria? Join the keys into one helper column, use XLOOKUP with arrays (A:A&B:B), or sum with SUMIFS when the result is numeric. See lookups with multiple criteria.

28. What is Power Query and when do you choose it over formulas? Power Query imports and reshapes data through recorded, repeatable steps that refresh on demand. Use it for messy or recurring imports. See Power Query basics.

Text and dates (questions 29 to 33)

29. How do you remove extra spaces? =TRIM(A2) removes leading, trailing and repeated spaces; CLEAN removes non-printing characters; SUBSTITUTE(A2,CHAR(160)," ") handles non-breaking spaces. See text functions.

30. How do you split "Lovelace, Ada" into two columns? Use Text to Columns with a comma delimiter, Flash Fill, or the TEXTSPLIT function in current Excel. See joining and splitting text.

31. How does Excel store dates? As serial numbers counting days from 1900 (1 is 1 January 1900), with times as fractions of a day. Formatting turns the number into a readable date. See dates and times.

32. How do you calculate someone's age? =DATEDIF(A2,TODAY(),"Y") gives complete years. DATEDIF works but is undocumented in the function list. See date calculations.

33. How do you count working days between two dates? =NETWORKDAYS(start,end,holidays) excludes weekends and listed holidays. WORKDAY adds working days to a date.

Tables, validation and analysis (questions 34 to 40)

34. What are the benefits of an Excel Table? Automatic expansion, named structured references like [@Sales], banded styles, filter buttons, a total row and formulas that fill themselves. Create one with Ctrl + T. See Excel Tables.

35. How do you remove duplicates? Data > Remove Duplicates changes the data in place, while UNIQUE creates a separate list. Flag duplicates first with COUNTIF if you need to review them. See finding and removing duplicates.

36. How do you build a dropdown list? Data > Data Validation > Allow: List, pointing to a range or a table column. See data validation.

37. How do you highlight rows past their deadline? Conditional formatting with a formula rule such as =$D2<TODAY(), applied to the whole row range. See conditional formatting.

38. What is a PivotTable? A tool that summarizes a table by dragging fields into Rows, Columns, Values and Filters, without writing formulas. See PivotTable basics.

39. Your PivotTable does not show new data. Why? PivotTables do not refresh automatically on every change. Use Refresh (Alt + F5), or make the source a Table so new rows are included.

40. What is a spill range? Modern Excel formulas that return several values fill neighboring cells automatically, for example =SORT(A2:A20). Refer to the whole result with A2#. A #SPILL! error means something blocks the area. See dynamic arrays and FILTER, SORT, SORTBY and UNIQUE.

See the behavior in code

The first stand-in runs three of the questions that people often get wrong: the three counting functions (question 6), exact versus approximate lookups (question 22) and IFERROR (question 17). It is a teaching model in JavaScript, not Excel's code.

JS Counting, VLOOKUP modes and IFERROR

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last line is worth remembering. A real VLOOKUP with a column number beyond the table returns #REF!, and wrapping it in IFERROR would hide that mistake behind a harmless-looking 0.

The second stand-in covers criteria strings, SUMIFS, dates and text (questions 14 to 16, 29 and 31).

JS COUNTIF criteria, SUMIFS, serial dates and TRIM

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Scenario questions (questions 41 to 52)

Interviewers for finance, HR, marketing and project roles like situations more than definitions: "Here is a sheet; how would you work out X?" A strong answer names the formula, states the assumption and mentions a check. The answers below give the shape of a good reply; adapt the numbers to your case.

Marketing

41. How do you measure a campaign's return? ROI is (Revenue - Cost) / Cost. If a campaign cost 5,000 and brought 18,000 revenue, ROI is =(18000-5000)/5000, which is 260%. Say whether revenue means sales or profit; ROI on profit is the honest figure. Conversion rate is Orders / Visitors, and CAC (customer acquisition cost) is Spend / New customers. Put the inputs in cells and never hard-code them in formulas, so the sheet can answer "what if conversion rises one point?" (What-If Analysis).

42. A campaign table has Channel, Spend, Clicks, Orders. How do you rank channels? Add cost per click =Spend/Clicks and CAC =Spend/Orders, wrap them in IFERROR for zero divisions, and sort by CAC ascending (Error-Handling Formulas). Pivot by channel to get totals, and divide the totals rather than averaging row ratios.

HR

43. How do you calculate employee turnover and attendance? Turnover rate is Leavers during the period / Average headcount, where average headcount is (Start + End) / 2. Attendance rate is Days present / Days scheduled; use NETWORKDAYS for scheduled days and COUNTIFS on a status column for absences (Conditional Aggregates). Present rates as percentages and compare departments with a pivot.

44. How would you build a simple payroll calculation? Gross pay is Hours * Rate, with overtime =Regular*Rate + Overtime*Rate*1.5 where overtime is MAX(0, Hours-40). Deductions come from a tax table: use XLOOKUP with match mode -1 (or VLOOKUP approximate) for flat bands, and a marginal calculation for progressive tax (each band taxes only the slice inside it). Net pay is gross minus deductions. Round money with ROUND(..., 2) at the end of each line, and keep rates in a table, not in formulas.

Finance

45. How do you compute EBITDA and test it? EBITDA = Revenue - Operating costs (excluding depreciation and amortization), equivalently Net income + Interest + Taxes + Depreciation + Amortization. Build both ways and check that they agree. For a sensitivity table, vary two drivers (price and volume) with a Data Table (What-If Analysis), or fill a grid with a formula that uses mixed references =($B5*C$4)-Costs.

46. How do you build a depreciation schedule? Straight-line: =SLN(cost, salvage, life). Declining balance: =DB(cost, salvage, life, period) or =DDB(cost, salvage, life, period). Sum-of-years: =SYD(cost, salvage, life, period). List periods down the sheet, compute each method in a column and check that the book value never falls below salvage (Financial Functions).

47. How do you compute tax with brackets? Put the bracket thresholds and rates in a table. With a marginal system, tax is the sum over bands of MAX(0, MIN(income, upper) - lower) * rate, which you can compute in one line with SUMPRODUCT. A common mistake is applying the top rate to the whole income.

48. How do you reconcile a bank statement with your ledger? Put both lists in tables. Mark matches with COUNTIFS on amount (and date or reference): =COUNTIFS(Bank[Amount], [@Amount], Bank[Date], [@Date]). Rows with a count of 0 are unreconciled; filter them. For items present on one side only, XLOOKUP returning "Missing" works too. Check that Ledger total - Bank total equals the sum of the unmatched items.

Project management

49. How do you build a project schedule that updates itself? Keep Start, Duration (working days), and End columns: End = WORKDAY(Start, Duration - 1, Holidays), and the next task's start =WORKDAY(PreviousEnd, 1, Holidays). Changing a duration then moves every following task. Add a status formula (IFS) and a Gantt-style bar (Gantt, Timeline and Milestone Charts).

50. How do you allocate people to tasks without overloading them? Make a grid of people by week with hours allocated (SUMIFS from a task list), compare with capacity, and flag overload: =IF(Allocated>Capacity,"Over","OK") plus conditional formatting. A pivot by person and week gives the same view with a few clicks.

51. How do you track budget versus actual for a project? Table of Task, Budget, Actual; variance =Actual - Budget and percentage =IFERROR(Variance/Budget, 0); a status icon from conditional formatting; a pivot by phase. Report totals by SUMIFS, not by adding cells one by one.

52. A colleague's workbook is slow and nobody knows why. What do you do? Check calculation mode (Formulas > Calculation Options), look for volatile functions (OFFSET, INDIRECT, TODAY) and whole-column array formulas, replace lookups over thousands of rows with XLOOKUP or INDEX/MATCH on exact ranges, convert unnecessary formulas to values, and remove unused formatting. See Performance and Best Practices.

Run it: the numbers behind four of these answers

JS ROI, turnover, marginal tax and depreciation, by formula

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A range holds 10, the text x, an empty cell, 25, a formula returning an empty string, and 7. What does COUNTA return?

    COUNTA counts every non-empty cell, including the formula that returns an empty string. Only the truly empty cell is skipped, so the answer is 5.
  2. 2. Which VLOOKUP fourth argument forces an exact match?

    FALSE (or 0) requests an exact match. Leaving the argument out means approximate match, which silently needs sorted data.
  3. 3. Why can wrapping every formula in IFERROR be a bad habit?

    IFERROR returns the fallback for every error type, so genuine bugs look like normal results. Handle only the error you expect, for example with IFNA.
  4. 4. What does the criteria text "<>North" mean in COUNTIF?

    <> is the not-equal operator. COUNTIF counts every cell whose value is not North, blanks included.
  5. 5. Which answer best describes how Excel stores a date?

    Dates are numbers: day 1 is 1 January 1900, and times are fractions of a day. Formatting decides how the number looks.

Summary

  • Interview answers work best as definition, tiny example, and pitfall.
  • Know references ($ and F4), the counting functions, IF with AND and OR, and the SUMIFS family cold.
  • Be able to compare VLOOKUP, INDEX/MATCH and XLOOKUP, including exact versus approximate matching.
  • Explain that dates are serial numbers and that formatting changes the display, not the value.
  • Mention Tables, PivotTables, refresh behavior, spill ranges and Power Query to show you work with real data.
  • Never hide errors blindly: understand what each error means before using IFERROR.

Finish the track with the Excel Cheat Sheet.