Practice Excel interview questions on lookups, PivotTables, duplicate IDs, SUMIFS and report validation, using a small reconciliation exercise. Start with the question, explain the mechanism, and then state an assumption or tradeoff. The scenarios below are original practice examples, not questions supplied by an employer.
How do you choose a lookup formula?
Clarify the lookup key, required match type, return column and behavior when no match exists. An exact identifier lookup differs from selecting a band in a threshold table. Confirm the Excel version available to the team before relying on a newer function. A strong interview answer explains how you would test missing keys and duplicate keys rather than assuming a formula returning a value means the value is correct.
When would you use SUMIFS rather than a lookup?
Use a conditional aggregation when multiple matching rows should contribute to a total. A lookup commonly returns one matching result; that is insufficient for a customer's many orders. Describe the criteria and the grain of each row. Make sure all relevant rows are included as the table grows. Avoid mixing subtotal rows with transaction rows, because the same amount can then be counted twice.
What does a PivotTable help you investigate?
It lets you summarize records by dimensions such as region, customer or period. Select the correct aggregation and verify the source range and refresh state. A count where you expected a sum can reveal text-formatted numbers or the wrong field configuration. Explain how you would reconcile the grand total with the source before presenting a result. A polished chart is not evidence that its input is complete.
How do you find spreadsheet data-quality problems?
Check identifiers, blanks, unexpected categories, text versus numeric types and dates stored in inconsistent formats. Keep the original input available and document transformations. Use a small reconciliation sheet with row counts and control totals. When a number looks wrong, inspect the source and formula references before applying formatting or manually replacing cells. Separate a display problem from a calculation problem.
How do you make a workbook maintainable?
Use clear input, calculation and output areas, meaningful table names and documented assumptions. Prefer formulas that another analyst can inspect without guessing why a cell contains a constant. Record the reporting period and data refresh process. Consider whether a repeated workflow belongs in Power Query or another reproducible pipeline. Explain a handoff test: can a colleague refresh the report using your instructions without asking you which cells to edit?
Worked example
Original reconciliation exercise: customer A has two transactions, 100 and 150; customer B has one transaction, 80. A customer-level report should show A = 250, B = 80 and a total of 330. A first-match lookup returning 100 for A is unsuitable for this aggregation.
Add an empty customer ID on a fourth transaction for 20. The overall transaction total becomes 350, but named-customer totals remain 330. Decide whether the 20 appears in an 'unassigned' bucket or blocks publication. Explain the difference visibly so a reader does not mistake a missing identifier for missing revenue.
Practice plan
Create the exercise as a small table and solve it with conditional sums and a PivotTable. Reconcile both outputs. Then insert a new row, change a number to text and add a duplicate transaction ID. Explain which check catches each change and whether the report should still be sent.
Use Cluegent during preparation to review your own answer: ask for one incorrect assumption and one follow-up question, then respond again without suggestions. Check current plans before choosing a subscription. Follow the employer's rules during the actual interview.
Sources checked
These official references support the guide. Product details and technical documentation can change; check the linked source for current information.
Where Cluegent helps
Cluegent supports permitted live workflows with transcript context, typed prompts, screenshot-aware answers, resume context, custom response behavior, quick action buttons, and a private desktop overlay. It is most useful when you already understand the subject and need help staying structured under pressure.
Frequently asked questions
Do I need the newest Excel functions?
Know the functions supported by the employer's environment. Explain a compatible approach when a newer lookup function is unavailable.
What matters most in an Excel interview task?
A correct business calculation, visible validation and an understandable workbook matter more than using the most complicated formula.