Try the answers on a small dataset
| Order ID (A) | Region (B) | Revenue (C) |
|---|---|---|
| 1001 | East | 1200 |
| 1002 | West | 300 |
| 1003 | East | 250 |
| 1004 | West | 1000 |
This guide covers eight Excel questions for practicing how you explain and check your work. These are preparation examples, not a prediction of the questions an employer will ask.
Enter this table in A1:C5 in Excel. The header is row 1; the four orders occupy rows 2 through 5. Revenue is a numeric amount in any one consistent currency. Formula examples use English function names and commas; your Excel locale may use semicolons between arguments.
1. What is the difference between relative and absolute references?
A relative reference changes when you copy a formula; an absolute reference keeps its row and column fixed. A2 is relative, $A$2 is absolute, and $A2 or A$2 fixes only one part of the address.
For an example outside the table, put a rate of 10% in E1. Enter =C2*$E$1 in D2, then copy it to D3. The copied formula becomes =C3*$E$1: the revenue changes to the next order, while the rate stays in E1. The results are 120 and 30.
In an interview, explain which values should move before choosing where to put dollar signs. A formula can give the right first result and still fail when copied.
2. When should you use SUM, SUMIF or SUMIFS?
Use SUM to add numbers without a condition, SUMIF to total rows matching one condition, and SUMIFS to total rows matching several conditions. SUMIFS can also handle a single condition.
For the sample orders, =SUM(C2:C5) returns 2750. To total East orders, =SUMIF(B2:B5,"East",C2:C5) returns 1450. To total East orders with revenue of at least 1000, =SUMIFS(C2:C5,B2:B5,"East",C2:C5,">=1000") returns 1200.
Check the argument order: SUMIFS starts with the sum range, while SUMIF starts with the criteria range. Keep the corresponding ranges aligned so each condition checks the intended order.
3. How do you use IF to classify a result?
IF evaluates a logical test and returns one value when the test is true and another when it is false. Use it for a decision with two outcomes, such as whether an order meets a revenue threshold.
Enter =IF(C2>=1000,"Large","Standard") outside the source table. It returns Large for order 1001. Copying it down returns Standard for orders 1002 and 1003, and Large for order 1004.
Explain the boundary explicitly: >=1000 includes an order worth exactly 1000. Use AND or OR inside IF when the decision depends on more than one test.
4. What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches the first column of a table and returns a value from a specified column to its right. XLOOKUP uses separate lookup and return ranges, can return values on either side, and uses exact matching by default.
Using the sample table, =VLOOKUP(1003,A2:C5,3,FALSE) and =XLOOKUP(1003,A2:A5,C2:C5,"Not found") both return 250. FALSE explicitly requests an exact match for VLOOKUP.
XLOOKUP is not available in Excel 2016 or Excel 2019. Ask which Excel version the team uses before recommending it. Both examples use unique order IDs; duplicate keys require a clear rule about which record should be returned.
5. How do COUNTIF and COUNTIFS differ?
COUNTIF counts cells that satisfy one condition. COUNTIFS counts positions where all the supplied conditions are satisfied. These functions count matches; they do not total the revenue of those matches.
For the sample orders, =COUNTIF(B2:B5,"East") returns 2. Adding a revenue condition with =COUNTIFS(B2:B5,"East",C2:C5,">=1000") returns 1.
Explain the difference between asking how many orders qualify and how much revenue they generated. The first calls for a count; the second calls for a conditional sum.
6. How would you investigate a lookup that returns #N/A?
First check whether the key exists in the lookup range, whether the ranges point to the intended cells, and whether the key has the same data type as the source. Also check imported values for extra spaces. A missing match and a malformed key need different fixes.
Order 9999 does not exist in the sample table. =XLOOKUP(9999,A2:A5,C2:C5,"Not found") returns Not found without inventing a revenue value.
IFERROR can replace several kinds of errors, not just missing matches. Avoid wrapping every formula in IFERROR(...,0): zero can conceal an incorrect reference or a broken calculation. Diagnose the cause and choose a fallback that makes sense for the report.
7. When would you use a PivotTable instead of a formula?
Use a PivotTable when you want to group records and explore summaries by fields such as region or product. Use formulas when you need a calculation in a specific cell layout or a result that participates in another calculation.
For the sample orders, put Region in Rows and Revenue in Values, summarized by Sum. East totals 1450, West totals 1300, and the grand total is 2750.
Check that Revenue contains numbers and that the Values calculation is Sum rather than Count. When source records change, refresh the PivotTable and confirm its source includes the new rows.
8. How do you check that a spreadsheet answer is reliable?
Check the input data, formula references, copied formulas and expected edge cases, then reconcile the result with an independent total. A plausible number is not enough evidence that a formula is correct.
For the sample orders, the regional totals should reconcile: 1450 + 1300 = 2750. Test the order worth exactly 1000 against the IF threshold, confirm that order 9999 produces a missing-match message, and inspect the copied rate formula to ensure E1 remains fixed.
During an interview, describe these checks as you work. State assumptions about missing values, duplicate IDs and the Excel version instead of silently choosing a behavior.
How should you practice for an Excel interview?
Read a question, attempt it on a dataset, explain why the formula fits, and check the result before reading an answer. Repeat the task with a missing key, a boundary value or another row to check that your approach still works.
This guide and the linked lessons are public. The Practice library identifies Public, Member and Pro challenges. Longer interview assessments, CSV datasets and reference approaches are included in Pro; they are separate from the public questions on this page.
Start a formula practice challenge