Excel Interview Questions and Answers
Last updated 2026-11-19
Excel is still the first tool tested in many analyst, finance, operations and business roles. Interviews usually give you a small table and ask for lookups, conditional totals, a pivot table and some cleaning. The questions below focus on the formulas people actually write, with the logic spelled out so you can adapt it when the data layout changes.
Excel rarely stands alone: analyst interviews pair it with SQL and a BI tool. For the larger picture read how to crack the data analyst interview, and for the dashboards that often follow see Power BI interview questions.
| Typical rounds | Online Excel test, case with a dataset, discussion of past reports |
|---|---|
| Core skills | Lookups, SUMIFS, pivots, dynamic arrays, Power Query |
| Questions here | 14 worked questions |
Functions to be fluent in
- Lookup: XLOOKUP, INDEX with MATCH, and the older VLOOKUP.
- Conditional aggregation: SUMIFS, COUNTIFS, AVERAGEIFS and SUMPRODUCT.
- Logic and errors: IF, IFS, AND, OR and IFERROR.
- Text and dates: TRIM, LEFT, MID, FIND, TEXT, EOMONTH, NETWORKDAYS.
- Dynamic arrays: FILTER, SORT, UNIQUE and spilled ranges.
How to handle a live Excel test
Read the whole task first, convert the data to a Table with Ctrl+T so formulas use structured references that grow with the data, and build one formula at a time while checking a couple of rows by hand. Keep inputs, calculations and outputs on clear areas of the sheet, and note any assumption in a cell next to the result.
Excel Interview Questions and Answers: questions with worked answers
1 Compare VLOOKUP, INDEX with MATCH, and XLOOKUP.
easyVLOOKUP searches the first column of a range and returns a column by number, so it cannot look left and breaks if columns are inserted, and it needs FALSE as the last argument for an exact match. INDEX with MATCH separates the return range from the lookup range, so it can look in any direction and survives column changes. XLOOKUP is the modern replacement: it looks in any direction, defaults to exact match, takes a not-found value and can search from the bottom.
excel=VLOOKUP(F2, A2:D100, 3, FALSE) =INDEX(C2:C100, MATCH(F2, A2:A100, 0)) =XLOOKUP(F2, A2:A100, C2:C100, "Not found")2 How do you look up a value using two criteria?
mediumMultiply two Boolean tests so that only rows meeting both give 1, then look for 1. XLOOKUP handles this naturally with an array expression. With INDEX and MATCH in older Excel it must be entered as an array formula. Another route is to add a helper column that joins the keys, such as =A2&"|"&B2, and look up the joined key.
excel=XLOOKUP(1, (A2:A100=F2)*(B2:B100=G2), D2:D100, "None") =INDEX(D2:D100, MATCH(1, (A2:A100=F2)*(B2:B100=G2), 0))3 Write SUMIFS and COUNTIFS formulas with several conditions.
easySUMIFS takes the sum range first, then pairs of criteria range and criterion. All conditions must be true. Join an operator to a cell or a date with &. The first example totals North region sales from the start of 2026; the second counts orders above 1,000 in the same region. All ranges must have the same size.
excel=SUMIFS(D2:D100, A2:A100, "North", B2:B100, ">="&DATE(2026,1,1)) =COUNTIFS(A2:A100, "North", D2:D100, ">1000") =AVERAGEIFS(D2:D100, A2:A100, "North")4 What is a pivot table and how do you build one?
easyA pivot table summarises a table by dragging fields into rows, columns, values and filters. Select the data (as a Table), choose Insert then PivotTable, drop Region on rows, Month on columns and Sales in values with Sum. Group dates by month or year from the right-click menu. A pivot does not update by itself when the source changes, so refresh it, and a calculated field lets you add a formula such as profit divided by sales, which is computed on the totals, not on each row.
5 Compute a weighted average price with SUMPRODUCT. Quantities are 2 and 3; prices are 10 and 20.
mediumSUMPRODUCT multiplies matching elements of the arrays and adds them: 2 x 10 + 3 x 20 = 80. Dividing by the total quantity, 5, gives a weighted average price of 16, whereas the simple average of the prices is 15. SUMPRODUCT also does conditional sums without SUMIFS, for example counting rows meeting two conditions with --(A2:A100="North")*(B2:B100>1000).
excel=SUMPRODUCT(B2:B3, C2:C3) / SUM(B2:B3) =SUMPRODUCT(--(A2:A100="North"), --(D2:D100>1000))6 How do you find and remove duplicates?
easyTo flag them, use COUNTIF on the key: a result above 1 means the value repeats. To remove them, use Data then Remove Duplicates, choosing the columns that define a duplicate, or write =UNIQUE(A2:A100) to return distinct values in newer Excel. Check which of the duplicated rows is kept, because Remove Duplicates keeps the first occurrence, so sort by date first if you need the latest record.
excel=COUNTIF($A$2:$A$100, A2) > 1 =UNIQUE(A2:C100)7 Explain absolute, relative and mixed references.
easyA relative reference such as A1 changes when copied. An absolute reference $A$1 never changes. A mixed one fixes only the row (A$1) or the column ($A1). Press F4 to cycle through them. A multiplication table is the standard example: in B2, =$A2*B$1 fixes the row header column and the column header row, so the same formula can be filled across and down.
8 Extract the username from an email and clean extra spaces.
easyFind the position of the @ with FIND and take everything before it with LEFT. TRIM removes leading, trailing and repeated inner spaces, but not non-breaking spaces, which need SUBSTITUTE(A2, CHAR(160), " ") first. For an email like asha@example.com the first formula returns asha. In newer Excel TEXTBEFORE does the same job.
excel=LEFT(A2, FIND("@", A2) - 1) =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) =TEXTBEFORE(A2, "@")9 Which date functions are commonly used? Compute completed years of age and working days.
mediumDATEDIF with unit "Y" gives completed years: for a birth date of 10 May 2000 and a date of 19 November 2026 it returns 26. NETWORKDAYS counts working days between two dates, excluding weekends and an optional holiday list, so 2 November 2026 (Monday) to 6 November 2026 returns 5. EOMONTH(date, 0) returns the last day of the month, and EDATE adds months. Dates are stored as serial numbers, so subtracting two dates gives days.
excel=DATEDIF(DATE(2000,5,10), DATE(2026,11,19), "Y") =NETWORKDAYS(DATE(2026,11,2), DATE(2026,11,6)) =EOMONTH(A2, 0)10 What are dynamic array functions? Show a filtered, sorted list.
mediumA dynamic array formula returns many values that spill into neighbouring cells, and updates when the data changes. FILTER keeps rows meeting a condition, SORT orders them and UNIQUE removes duplicates. The formula below returns all North rows with sales above 1,000, sorted by the fourth column descending. If a cell in the spill range is occupied you get a #SPILL! error, and if nothing matches the if-empty argument is returned.
excel=SORT(FILTER(A2:D100, (A2:A100="North")*(D2:D100>1000), "No rows"), 4, -1)11 How do you handle errors such as #N/A and #DIV/0!?
easyFind the cause first: #N/A usually means a lookup did not find the key (extra spaces or text versus number mismatch), and #DIV/0! means a zero or empty denominator. Wrap formulas in IFERROR to return a chosen value, or use IFNA to hide only #N/A so that other errors still show. Hiding every error can mask real mistakes, so prefer a targeted fix and use IFERROR only at the reporting layer.
excel=IFNA(XLOOKUP(F2, A:A, C:C), "Missing") =IF(B2=0, "", A2/B2)12 Write an IF formula that grades marks, and its IFS version.
easyNested IF tests conditions in order, so put the highest threshold first. IFS lists condition and result pairs, and the final TRUE acts as the default. For marks of 82 the grade is B in the thresholds below, which are 90, 75 and 60. Keep thresholds in a small table and use a lookup with approximate match when there are many bands.
excel=IF(A2>=90,"A",IF(A2>=75,"B",IF(A2>=60,"C","D"))) =IFS(A2>=90,"A", A2>=75,"B", A2>=60,"C", TRUE,"D")13 How do you highlight rows in conditional formatting using a formula?
mediumSelect the range, choose Conditional Formatting then New Rule then Use a formula, and write the test for the first cell of the range with the column locked so the whole row follows. The formula below highlights the entire row from A2 to D100 where column D exceeds 1,000. Mixing up the dollar sign is the usual error: $D2 locks the column and lets the row change.
excel=$D2>100014 What are Power Query and macros used for in Excel?
mediumPower Query (Get and Transform) imports, cleans and reshapes data through recorded steps, such as unpivoting columns, merging files from a folder and changing types, and replays them on refresh, so a monthly report becomes a one-click refresh with no formulas to copy. Macros, written in VBA, automate repetitive actions and custom behaviour in the workbook. Prefer Power Query for repeatable data preparation, and VBA only when you need interface automation.
Frequently asked questions
Is VLOOKUP still asked in interviews?
Yes, often alongside XLOOKUP and INDEX with MATCH. Know all three and the trade-offs.
What advanced Excel topics should I know?
Pivot tables and charts, dynamic arrays, Power Query, SUMPRODUCT, array formulas and basic VBA.
How do I practise Excel for tests?
Take a public dataset, convert it to a Table and answer ten questions with formulas and pivots, timing yourself.
Do analyst roles need both Excel and SQL?
Mostly yes. Excel for quick analysis and sharing, SQL for pulling data. See the SQL guide for the other half.
Related guides
More in Role-wise Interview Questions by Job and Tech Stack
- Express.js Interview Questions and Answers (Express 5)
- FastAPI Interview Questions and Answers
- Flask Interview Questions and Answers
- Flutter Interview Questions and Answers (Flutter 3, Dart 3)
- Frontend Developer Interview Questions and Answers
- Full Stack Developer Interview Questions and Answers
- Generative AI and LLM Interview Questions and Answers
- Google Cloud (GCP) Interview Questions and Answers
- GraphQL Interview Questions and Answers
- Hibernate and JPA Interview Questions and Answers
- HTML and CSS Interview Questions and Answers
- iOS and Swift Interview Questions and Answers