Top 50+ Excel Interview Questions & Answers (2026)

Excel interviews usually test judgment as much as memorized functions. For finance, accounting, analyst, and operations roles, be ready to explain how you would clean transaction data, build a reliable lookup, reconcile totals, summarize a report, and troubleshoot a result that looks wrong.

The answers below assume current Excel for Microsoft 365 and Excel 2024 unless a version limitation is noted. Menu names can vary slightly by platform, and some newer functions are not available in Excel 2016 or 2019.

#	Preview	Product	Price
1		MS Excel Interview Questions and Answers: The Ultimate Interview Preparation Guide	$1.99	Buy on Amazon
2		650+ Data Science and Analytics Interview Questions and Answers: Excel, Power BI, TABLEAU, SQL,...	$20.00	Buy on Amazon
3		Mastering Excel 90 Interview Questions	$2.99	Buy on Amazon
4		Mastering Microsoft Excel - Basic to Advanced Practical Guide for Job & Office Work |: Excel Guide...	$9.99	Buy on Amazon
5		Quick Flip Questions for Critical Thinking	$7.82	Buy on Amazon

Excel fundamentals and formulas

1. What is the difference between a workbook and a worksheet?
   A workbook is the Excel file. A worksheet is an individual tab inside that file. One workbook can contain multiple worksheets.
2. What is a cell reference?
   A cell reference identifies a cell or range, such as A1, A1:A10, or Sheet2!B4. If a sheet name contains spaces, use single quotation marks: ='Quarterly Data'!D3.
3. What are relative, absolute, and mixed references?
   A1 is relative, $A$1 is absolute, $A1 fixes the column, and A$1 fixes the row. In desktop Excel, press F4 while editing a reference to cycle through the options.
4. What is the basic syntax of an Excel formula?
   A formula starts with =, followed by a calculation or function. For example: =SUM(A1:A10). Depending on regional settings, function arguments may use commas or semicolons.
5. What is the difference between COUNT, COUNTA, and COUNTBLANK?
   COUNT counts numeric values, COUNTA counts non-empty cells, and COUNTBLANK counts blank cells. A formula that returns an empty text string is not always treated the same way as a genuinely empty cell.
6. What is the difference between SUMIF and SUMIFS?
   SUMIF applies one criterion. SUMIFS applies multiple criteria and puts the sum range first: =SUMIFS(sum_range,criteria_range1,criteria1,criteria_range2,criteria2).
7. What is the difference between COUNTIF and COUNTIFS?
   COUNTIF counts matches against one criterion. COUNTIFS counts records meeting multiple criteria, such as a department and a date range.
8. What does IFERROR do?
   IFERROR(value,value_if_error) substitutes a result when a formula returns an error such as #N/A, #VALUE!, or #DIV/0!. It should not be used blindly because it can hide a real data problem.
9. What is the difference between IFERROR and IFNA?
   IFERROR catches several Excel error types. IFNA responds only to #N/A, making it preferable when other errors should remain visible for investigation.
10. What do AND and OR do?
    AND returns TRUE only when every test is true. OR returns TRUE when at least one test is true. For example: =IF(AND(B2="East",C2>1000),"Review","OK").
11. What does LET do?
    LET names intermediate calculations inside a formula. This improves readability and can avoid calculating the same expression repeatedly: =LET(revenue,B2*C2,cost,D2*E2,revenue-cost). It supports up to 126 name/value pairs.
12. What does LAMBDA do?
    LAMBDA creates reusable custom functions without VBA. It can be saved through the Name Manager and reused like a normal function. It supports up to 253 parameters.
13. What does TEXT do?
    TEXT(value,format_text) displays a number using a specified format, such as =TEXT(A2,"$#,##0.00"). The result is text, not a number, so it should not be used where further arithmetic is required.
14. How do you preserve leading zeros?
    Format the destination cells as Text before entering values, or use a custom number format such as 00000. The formula =TEXT(A1,"00000") also works, but returns text.
15. What do TRIM and CLEAN do?
    TRIM removes excess standard spaces. CLEAN removes nonprintable characters. They are useful when imported customer IDs or account names look identical but fail to match.
16. What do TEXTBEFORE, TEXTAFTER, and TEXTSPLIT do?
    These newer functions split or extract text using delimiters. For example, =TEXTBEFORE(A2,"-") returns the text before a hyphen, while =TEXTSPLIT(A2,",") separates comma-delimited data into cells.

Lookup and matching questions

17. What is the preferred alternative to VLOOKUP?
    XLOOKUP is generally preferred because it can search in any direction, returns exact matches by default, and does not require a numeric column index.
18. What is the XLOOKUP syntax?
    =XLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_mode],[search_mode]).
19. What is XLOOKUP’s default match behavior?
    The default match_mode is 0, meaning exact match. If no match is found and no fallback is supplied, Excel returns #N/A.
20. What do XLOOKUP match modes mean?
    0 means exact match, -1 means exact or next smaller item, 1 means exact or next larger item, and 2 enables wildcard matching.
21. What do XLOOKUP search modes mean?
    1 searches first to last, -1 searches last to first, 2 uses ascending binary search, and -2 uses descending binary search. Binary search requires correctly sorted data.
22. Is XLOOKUP available in Excel 2016 and Excel 2019?
    No. Those versions do not include XLOOKUP, although they may open a workbook containing formulas created in a newer version. For compatibility, use INDEX/MATCH or VLOOKUP.
23. What is the VLOOKUP syntax?
    =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The lookup value must be in the first column of the table array, and VLOOKUP returns a value from a column to its right.
24. What is the dangerous VLOOKUP default?
    If the fourth argument is omitted, VLOOKUP uses approximate matching, or TRUE. For ordinary finance lookups, explicitly use FALSE: =VLOOKUP(A2,$D$2:$F$100,3,FALSE).
25. Why might VLOOKUP return the wrong value?
    Common causes include omitting FALSE, using unsorted data with approximate matching, numbers stored as text, hidden spaces, nonprinting characters, dates with inconsistent formats, or an incorrect column index.
26. What are VLOOKUP’s main limitations?
    It can search only the first column of its table array and return values to the right. It cannot natively look left, and inserting columns can make a hard-coded column index unreliable.
27. What is the standard INDEX/MATCH pattern?
    =INDEX(return_range,MATCH(lookup_value,lookup_range,0)). The 0 requests an exact match. INDEX/MATCH can return values from either side of the lookup column.
28. Is the Excel Lookup Wizard still available?
    No. Microsoft’s current lookup guidance says the Lookup Wizard is no longer available.

Dynamic arrays and modern Excel

29. What is a spilled array?
    A dynamic-array formula entered in one cell can return multiple results into neighboring cells. For example, =SORT(D2:D11,1,-1) spills a sorted list downward.
30. What causes a #SPILL! error?
    The intended output area is blocked, often by existing values, merged cells, or another obstruction. Clear the blocked cells or move the formula.
31. Can a spilled formula be entered inside an Excel Table?
    Not in the table body. Place the dynamic-array formula outside the Table or convert the Table to a normal range.
32. Which cell of a spilled array can be edited?
    Only the top-left cell containing the formula is directly editable. The other cells are calculated spill results.
33. Do legacy Ctrl+Shift+Enter array formulas still work?
    Yes, they remain supported for compatibility. Microsoft recommends dynamic-array formulas for new work where the required functions are available.
34. What does the @ operator mean?
    @ requests implicit intersection: it selects the value relevant to the current row or cell instead of returning an entire array. Excel may add it when opening older formulas in newer versions.
35. What limitation applies to dynamic arrays across workbooks?
    Linked dynamic-array formulas between workbooks are supported only while both workbooks are open. If the source workbook is closed, the link can return #REF! when refreshed.
36. What is GROUPBY?
    GROUPBY creates formula-driven grouped summaries. It is a Microsoft 365 feature and should not be presented as available in every perpetual Excel edition.

Tables, sorting, and filtering

37. How do you create an Excel Table?
    Select a cell in the data and press Ctrl+T, or choose Home > Format as Table. Confirm the range and whether it includes headers.
38. Why use a Table instead of an ordinary range?
    Tables provide automatic expansion, filters, calculated columns, consistent formatting, and structured references. They are especially useful for transaction logs and monthly financial data.
39. What are structured references?
    Structured references use Table and column names rather than cell addresses, such as =SUM(Sales[Amount]). They adjust automatically as the Table changes.
40. How do you filter a range or Table?
    Select a column-header filter arrow and choose values or criteria. A Table automatically supplies filter controls in its header row.
41. What is the difference between sorting and filtering?
    Sorting changes the order of records. Filtering temporarily hides records that do not meet the selected criteria; it does not delete them.
42. What is a common sorting failure?
    Sorting only one column can separate amounts from the wrong account or date. Select the complete dataset, or choose Expand the selection when Excel prompts you.

PivotTables and PivotCharts

43. How do you create a PivotTable?
    Select the source data, choose Insert > PivotTable, select a destination, and place fields in Filters, Columns, Rows, and Values.
44. What is a PivotTable cache?
    It is the stored copy of source data Excel uses to build and operate a PivotTable or PivotChart.
45. How do you refresh a PivotTable?
    Right-click inside it and choose Refresh. If the source is an Excel Table, newly added Table rows are included when the PivotTable is refreshed.
46. Can you type directly over a PivotTable value?
    No. PivotTable cells are report output. Change the source data or the field arrangement instead.
47. What is a PivotChart?
    A PivotChart visualizes PivotTable results, making comparisons, trends, and category patterns easier to interpret.
48. Can a PivotTable use multiple related tables?
    Yes. Create relationships between tables and use the Data Model. This is useful when transactions, customers, and account categories are stored separately.

Formatting and data entry

49. How do you apply conditional formatting?
    Select the range and choose Home > Styles > Conditional Formatting > New Rule. Available rule types include Highlight Cells Rules, Top/Bottom Rules, Data Bars, Color Scales, and Icon Sets.
50. How do you create formula-based conditional formatting?
    Choose Home > Conditional Formatting > New Rule, select the formula option, and enter a formula that returns TRUE or FALSE, such as =AND($B3="Grain",$D3<500).
51. How do you control competing conditional-formatting rules?
    Open Home > Conditional Formatting > Manage Rules. Move rules up or down and use Stop If True when a higher-priority rule should prevent later rules from applying.
52. How do you create a drop-down list?
    Select the cells and choose Data > Data Validation. On Settings, select List under Allow, specify the Source, and enable In-cell dropdown.
53. Why use an Excel Table as a drop-down source?
    A Table-based list expands automatically when items are added or removed, reducing the risk that a validation list becomes outdated.
54. Does data validation prevent every invalid entry?
    No. It is designed mainly to guide or block direct entries. Copying, filling, or pasting data can bypass validation, so imported data still needs checking.
55. When might Data Validation be unavailable?
    The command may be unavailable when the worksheet is protected or the workbook is shared.
56. How do you freeze rows and columns?
    Select the cell below the rows and to the right of the columns that should remain visible, then choose View > Freeze Panes > Freeze Panes.
57. How do you freeze only the first row or column?
    Choose View > Freeze Panes > Freeze Top Row or Freeze First Column. To remove it, choose View > Freeze Panes > Unfreeze Panes.

Excel errors and troubleshooting

58. Why does a formula display as text instead of calculating?
    The cell may be formatted as Text. Change it to General through Home > Number Format > General or Ctrl+1, then press F2 and Enter.
59. Which multiplication operator does Excel use?
    Excel uses the asterisk, *. The letter x is not the multiplication operator and can produce #NAME? in a formula.

Power Query and data preparation

60. What is Power Query called in Excel?
    Microsoft refers to it as Get & Transform. It imports and reshapes data by changing types, removing columns, merging tables, and applying repeatable transformation steps.
61. What is the basic import path?
    Choose Data > Get Data > From File, then select a connector such as From Excel Workbook or From Text/CSV.
62. How do you launch Power Query Editor?
    Choose Data > Get Data > Launch Power Query Editor, or select an imported data cell and choose Query > Edit.
63. Why should you review automatically detected data types?
    Power Query may infer headers, delimiters, and types from a sample of imported data. It can mistake account IDs for numbers or interpret dates incorrectly, so set important types explicitly.

Macros, file formats, and 2026 features

64. Which file format stores VBA macros?
    .xlsm is the macro-enabled workbook format. .xlsx cannot store VBA macro code.
65. How do you save a workbook containing macros?
    Use File > Save As and select Excel Macro-Enabled Workbook (*.xlsm). Saving as .xlsx removes the macro code.
66. Do macros run in Excel for the web?
    No. An .xlsm workbook can be opened in the browser, but VBA macros do not run there.
67. What happens when a workbook is saved as CSV?
    CSV stores plain delimited text, not workbook formatting, formulas, charts, or multiple sheets. Excel saves only the active worksheet to CSV.
68. What is .xlsb?
    .xlsb is Excel’s binary workbook format. It can be useful for large workbooks, but it does not replace .xlsm when VBA must be preserved.
69. Is Python in Excel available in every Excel edition?
    No. Microsoft’s current guidance applies Python in Excel to Excel for Microsoft 365 on Windows and Mac. It can be started through Formulas > Insert Python or by entering =PY.
70. Can Python in Excel use pandas.read_csv or pandas.read_excel for external files?
    No. Those common external-file functions are not compatible with Python in Excel. Bring external data into the workbook through the worksheet or Power Query.
71. What changed with Excel Copilot App Skills in 2026?
    Microsoft says App Skills were being deprecated and would be removed from Excel by late February 2026. Depending on the license and rollout, alternatives include Agent Mode, Copilot Chat, and Analyst.
72. Is Copilot available to every Excel user?
    No. Availability depends on the Excel app, subscription or Copilot license, network, privacy settings, and organizational tenant configuration.
73. Can you rely on a Copilot-generated formula?
    No. Treat it as a draft. Microsoft warns that AI-generated analysis and formulas can be incorrect or misinterpret the request. Check the formula against known totals and business rules.
74. Which Excel functions should not be described as universal in 2026?
    Availability depends on the edition and release channel. For example, FILTER and LET are associated with newer versions, BYROW and BYCOL with Excel 2024, and GROUPBY with Microsoft 365. Confirm compatibility before distributing a workbook.

How to answer practical Excel interview tests

When an interviewer gives you a workbook, explain your checks instead of jumping straight to a formula:

1. Inspect the source: confirm headers, duplicate IDs, blanks, dates, and whether numbers are stored as text.
2. Make the data stable: convert the dataset to a Table with Ctrl+T, use clear column names, and avoid merged cells in the data area.
3. Choose the simplest reliable tool: use XLOOKUP for a single lookup, SUMIFS for criteria-based totals, PivotTables for summaries, and Power Query for repeatable imports and transformations.
4. Reconcile the result: compare totals with a control total, check row counts, test an expected match and a missing match, and investigate rather than hide errors.
5. Consider compatibility: ask which Excel version the recipient uses before relying on XLOOKUP, dynamic arrays, GROUPBY, Python, or Copilot.

FAQ

What Excel questions are most common in finance interviews?

Expect questions about relative and absolute references, SUMIFS, IFERROR, XLOOKUP or INDEX/MATCH, PivotTables, Tables, conditional formatting, data validation, Power Query, and troubleshooting formula errors. Finance employers also commonly test reconciliation and data-quality judgment.

#1 Best Overall

MS Excel Interview Questions and Answers: The Ultimate Interview Preparation Guide

$1.99

Buy on Amazon

Should I learn XLOOKUP or VLOOKUP for an Excel interview?

Know both, but lead with XLOOKUP for current Microsoft 365 roles because it supports exact matching by default and can look in any direction. Also know VLOOKUP’s limitations and explicitly use FALSE for exact matching when working with older Excel versions.

What is the best way to prepare for an Excel practical test?

Practice cleaning a transaction dataset, converting it to a Table, creating criteria-based totals, performing a lookup, building a PivotTable, and checking the output against control totals. Be prepared to explain why your method is reliable, not just produce a result.

Rank #2

650+ Data Science and Analytics Interview Questions and Answers: Excel, Power BI, TABLEAU, SQL, Python, ML, and AI Explained

$20.00

Buy on Amazon

Which Excel version matters for a 2026 interview?

Ask what the employer uses. XLOOKUP is unavailable in Excel 2016 and 2019, while functions such as GROUPBY, Python in Excel, and some dynamic-array features depend on Microsoft 365 or a newer release. Build a fallback using INDEX/MATCH, helper columns, or PivotTables when compatibility matters.

The Bottom Line

A strong Excel interview answer combines the formula with the reason for using it and the checks that make the result trustworthy. Know the modern tools—XLOOKUP, dynamic arrays, Tables, PivotTables, Power Query, Python, and Copilot—but demonstrate version awareness and never use IFERROR or AI output to conceal an unexplained result.

Quick Recap

Bestseller No. 1

MS Excel Interview Questions and Answers: The Ultimate Interview Preparation Guide

$1.99

Bestseller No. 2

650+ Data Science and Analytics Interview Questions and Answers: Excel, Power BI, TABLEAU, SQL, Python, ML, and AI Explained

$20.00

Bestseller No. 3

Mastering Excel 90 Interview Questions

$2.99

Bestseller No. 4

Mastering Microsoft Excel - Basic to Advanced Practical Guide for Job & Office Work |: Excel Guide with 250+ Shortcuts, 90+ Formulas & 150+ Interview Questions

$9.99

Bestseller No. 5

Quick Flip Questions for Critical Thinking

Hand-held flip chart; Make learning theories and planning lessons easy; Develop higher levels of thinking

$7.82

Best Value

Quick Flip Questions for Critical Thinking

• Hand-held flip chart
• Make learning theories and planning lessons easy
• Develop higher levels of thinking
• Use for classrooms, home schooling and tutoring
• Ideal for all grade levels

$7.82

Buy on Amazon

Rank #4

Mastering Microsoft Excel - Basic to Advanced Practical Guide for Job & Office Work |: Excel Guide with 250+ Shortcuts, 90+ Formulas & 150+ Interview Questions

$9.99

Buy on Amazon

Rank #3

Mastering Excel 90 Interview Questions

$2.99

Buy on Amazon

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.