Excel interview questions

What a strong answer covers

• VLOOKUP searches the leftmost column of a range and returns a value a fixed number of columns to the right; it cannot look to the left of the lookup column.
• INDEX/MATCH decouples the lookup column from the return column: MATCH finds the row position, INDEX returns the value from any column, so it can look left or right.
• INDEX/MATCH does not break when columns are inserted or deleted because it references columns directly rather than by a hardcoded offset number.
• Mention XLOOKUP as the modern replacement that handles both directions, has a built-in if-not-found argument, and defaults to exact match.

Where people lose the point

• ×Forgetting the fourth VLOOKUP argument (range_lookup) and getting approximate-match errors because it defaults to TRUE on unsorted data.
• ×Claiming VLOOKUP can look to the left, which it cannot.
• ×Not mentioning that a hardcoded column index in VLOOKUP silently breaks when columns are added or removed.

What a strong answer covers

• Relative references (A1) shift both row and column when a formula is copied; the reference is relative to the formula's position.
• Absolute references ($A$1) lock both row and column so the reference does not move when filled across or down.
• Mixed references lock only one part: $A1 locks the column, A$1 locks the row, which is essential for building multiplication-table style grids.
• Give a concrete case such as multiplying a column of values by a single tax-rate cell that must stay fixed as you fill down.

Where people lose the point

• ×Getting wrong totals because a rate or constant cell was left relative and drifted when the formula was copied.
• ×Not knowing the F4 key cycles through the reference types.
• ×Confusing which part $ locks (it locks the item immediately to its right, the column or the row).

What a strong answer covers

• Use SUMIFS(sum_range, criteria_range1, criteria1, ...) and COUNTIFS for multiple AND conditions; note SUMIFS puts the sum_range first while SUMIF puts it last.
• Criteria can use operators as text like ">100", "<>0", or wildcards (*, ?) for partial text matches, and can reference a cell with concatenation like ">"&B1.
• For OR logic across one field, add multiple SUMIFS together or use SUMPRODUCT; explain that all criteria ranges must be the same size as the sum range.
• Mention date-range summing by using two criteria on the same date column (>= start and <= end).

Where people lose the point

• ×Swapping the argument order between SUMIF and SUMIFS and getting a wrong or error result.
• ×Treating multiple criteria as OR when SUMIFS applies them as AND.
• ×Mismatched range sizes between criteria and sum ranges, which causes a #VALUE! error.

What a strong answer covers

• Convert the source to an Excel Table first so the PivotTable range expands automatically as rows are added, avoiding stale fixed ranges.
• Drag Region to Rows, Month to Columns, and Sales to Values, then set the value field to Sum and format it; use Group on the date field to roll days into months.
• Refresh with Refresh All (or right-click Refresh) to pull in new data; a Table source plus refresh keeps it current without rebuilding.
• Add slicers or a report filter for interactivity, and change Summarize Values By to Average, Count, or % of Column Total as the question demands.

Where people lose the point

• ×Pointing the PivotTable at a fixed A1:D500 range so new rows are silently excluded.
• ×Forgetting that PivotTables do not auto-update and must be refreshed after source data changes.
• ×Leaving blank rows or mixed data types in the source, which breaks grouping and field detection.

What a strong answer covers

• Remove duplicates via Data > Remove Duplicates or dedupe with COUNTIFS/UNIQUE; clarify whether a whole-row or single-key duplicate is meant before deleting.
• Fix text with TRIM to strip stray spaces, CLEAN for non-printing characters, and UPPER/LOWER/PROPER for casing; use Text to Columns or VALUE to convert text-numbers to real numbers.
• Use Flash Fill or LEFT/RIGHT/MID/TEXTSPLIT to parse combined fields, and Find & Replace or SUBSTITUTE to standardize codes.
• Prefer Power Query for a repeatable, auditable cleaning pipeline when the same messy export arrives regularly, since steps are recorded and re-runnable.

Where people lose the point

• ×Deleting duplicates in place without a backup or without confirming the dedupe key, losing legitimate rows.
• ×Not spotting that numbers stored as text break SUM and lookups even though they look numeric (a green triangle or left alignment is the tell).
• ×Doing everything by hand when the task recurs, instead of reaching for Power Query or a documented step list.

What a strong answer covers

• Separate inputs, calculations, and outputs into distinct areas or sheets, and drive all formulas off labeled input cells rather than hardcoding numbers inside formulas.
• Use named ranges or Table references for readability, and IFERROR to handle divide-by-zero and missing lookups gracefully.
• Support what-if analysis with Data Tables, Scenario Manager, or Goal Seek so multiple assumption sets can be compared without rewriting formulas.
• Keep it performant and correct: avoid volatile functions (TODAY, OFFSET, INDIRECT) in large models, use Trace Precedents/Dependents and Evaluate Formula to audit, and add data validation to constrain inputs.

Where people lose the point

• ×Hardcoding assumptions inside formulas so a rate change must be edited in dozens of cells.
• ×Overusing volatile or full-column array formulas that recalculate the whole workbook and make it slow.
• ×No error handling or validation, so one bad input cascades #DIV/0! or #N/A through the whole model unnoticed.