Quick summary
Summarize this blog with AI
Excel interview preparation works best with a small dataset whose answers you can check. This guide uses five fictional orders to practice conditional aggregation, exact lookup, duplicate checks, tables, and a PivotTable. Formulas use English function names and comma separators; regional settings may require different names or separators.
Enter a complete practice dataset
Put these headers in A1:E1 and the five rows in A2:E6. Amount is the stated order amount; only paid orders count toward the paid-amount questions. A zero amount is an observed number, not a missing value.
| OrderID | Customer | Region | Status | Amount |
|---|---|---|---|---|
| 101 | A | East | paid | 100 |
| 102 | B | West | paid | 80 |
| 103 | A | East | cancelled | 50 |
| 104 | C | East | paid | 0 |
| 105 | B | West | paid | 120 |
1. Sum, count, and average the right records
Use these formulas in separate empty cells. Every range has the same five-row extent.
=SUMIFS(E2:E6,C2:C6,"East",D2:D6,"paid")
=SUMIFS(E2:E6,C2:C6,"West",D2:D6,"paid")
=SUMIFS(E2:E6,D2:D6,"paid")
=COUNTIFS(D2:D6,"paid")
=AVERAGEIF(D2:D6,"paid",E2:E6)
The results, in order, are 100, 200, 300, 4, and 75. The paid zero contributes to the count and average denominator. Summing all amounts would return 350 because it includes the cancelled order. Explain the condition instead of treating the grand total as automatically correct.
Microsoft’s SUMIFS reference documents the sum-range-first argument order and matching range dimensions. A blank amount is different from zero: an average ignores blank amount cells, while a count of paid statuses can still include those rows. Investigate incomplete amounts before interpreting an average or dividing totals by a status count.
2. Look up an order with an explicit missing result
In a version supporting XLOOKUP, these formulas return cancelled and Not found:
=XLOOKUP(103,A2:A6,D2:D6,"Not found",0)
=XLOOKUP(999,A2:A6,D2:D6,"Not found",0)
Match mode 0 requests an exact match. According to Microsoft’s XLOOKUP documentation, XLOOKUP is unavailable in Excel 2016 and Excel 2019. Check the interview environment. For an older compatible exact-lookup approach:
=IFNA(INDEX(D2:D6,MATCH(103,A2:A6,0)),"Not found")
This also returns cancelled. Exact lookup does not validate uniqueness; XLOOKUP’s default search returns the first match. If an order ID appears twice with conflicting statuses, investigate the records rather than accepting the first result as authoritative. Avoid approximate matching for identifiers.
3. Check duplicates without deleting evidence
Enter the formula below in F2 and fill it through F6. It returns 1 on every row of the supplied fixture. The fixed range stays the same while the current-row lookup value changes.
=COUNTIF($A$2:$A$6,A2)
A value greater than one flags a repeated order ID. Decide whether the data contract permits multiple rows per order, such as order lines, before calling the repetition an error. For one-row-per-order data, compare repeated records and resolve the cause; do not blindly use Remove Duplicates when values conflict.
4. Make a growing dataset easier to maintain
Convert A1:E6 to an Excel table with headers and name it Orders. Outside the table, the following formula returns 100:
=SUMIFS(Orders[Amount],Orders[Region],"East",Orders[Status],"paid")
Structured references adjust as table rows are added or removed. This is easier to inspect than an OFFSET/COUNTA range that assumes there are no gaps or stray cells. Append a paid East order for 25 as a new table row; the structured-reference result should become 125. The earlier fixed A2:E6 formulas still cover only their original range.
5. Build and reconcile a PivotTable
Using the original five rows, create a PivotTable with Region in Rows, Amount in Values summarized by Sum, and Status as a filter set to paid. Expect East 100, West 200, and grand total 300. If Excel shows Count of Amount, check whether amounts were imported as text and choose the intended aggregation after correcting the data.
Refresh the PivotTable after source changes and reconcile its total with SUMIFS. A chart should use the same filtered definition. A simple bar chart can compare paid amount by region; label the amount and avoid presenting the result as profit, which would require cost information.
Explain how you checked the workbook
State the row grain, inclusion rule, formula, and expected output. Test a missing lookup, a duplicate ID, a zero amount, and an appended row. When a result differs, inspect the data types, ranges, filters, and refresh state before changing the business definition. These checks demonstrate analytical judgment beyond memorizing function names.
