Excel guide · 10 min read
Excel interview questions and answers
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Order | Rep | Region | Date | Amount | Status | Commission rate | |
| 2 | A-1001 | Ana | East | Mar 2, 2026 | 1200 | Paid | 0.05 | |
| 3 | A-1002 | Ben | West | Mar 3, 2026 | 950 | Open | ||
| 4 | A-1003 | Cara | East | Mar 5, 2026 | 1430 | Paid | ||
| 5 | A-1004 | Dev | North | Mar 9, 2026 | 800 | Paid | ||
| 6 | A-1005 | Ana | West | Mar 12, 2026 | 1100 | Open | ||
| 7 | A-1006 | Ben | East | Mar 16, 2026 | 640 | Open | ||
| 8 | A-1007 | Cara | North | Mar 20, 2026 | TBC | Open | ||
| 9 | A-1008 | Dev | East | Mar 27, 2026 | 1000 | Paid |
Download this sheet as an Excel practice file (.xlsx)
Basic Excel interview questions
These check that you can work in a sheet without help. Answer in one or two sentences, then give a tiny example. Every formula below runs on the order sheet above: order A-1007 has no amount yet, so it says TBC.
What is the difference between a formula and a function?
A formula is anything you type that starts with =. A function is a named, built-in calculation you use inside a formula, like SUM or IF. =E2*H2 is a formula with no function in it; =SUM(E2:E9) is a formula that uses one.
Result: 7120
The total of every amount. SUM skips the text TBC in E8 without an error.
How do you count how many orders are paid?
COUNTIF counts cells that meet one condition: the range to check, then the condition in quotes.
Result: 4
Four orders have the status Paid.
How do you add up sales for one region?
SUMIF: the range to test, the condition, then the range to add. I would point the condition at a cell rather than typing "East", so the same formula works for any region.
Result: 4270
The four East orders add up to this amount.
How would you find the largest order?
MAX returns the largest number in a range and MIN the smallest. To find which order it was, I would add a lookup: =XLOOKUP(MAX(E2:E9),E2:E9,A2:A9).
Result: A-1003
The order with the largest amount.
What do #N/A, #VALUE!, #DIV/0! and #REF! mean?
#N/A: a lookup found no match. #VALUE!: math on something that is not a number, such as text. #DIV/0!: dividing by zero or an empty cell. #REF!: a reference points at a cell that was deleted. Naming the cause shows you can fix it, not just hide it.
How do you keep the header row visible while you scroll?
View > Freeze Panes > Freeze Top Row. To freeze the first column as well, select B2 and choose Freeze Panes. See how to freeze panes.
What is the difference between sorting and filtering?
Sorting changes the order of the rows. Filtering hides the rows that do not match and leaves the order alone. Before either, click one cell inside the list so Excel takes every column, or rows get mixed up.
"Explain the difference" questions
Interviewers use these to see whether you understand a function or only remember it. A good answer has three parts: what each one does, the one practical difference, and which one you reach for.
What is the difference between relative and absolute references?
A relative reference like E2 moves when you copy the formula: copied down a row it becomes E3. An absolute reference like $H$2 stays fixed. I use $ for any single input the whole column shares, such as a rate. Mixed references ($A2 or A$1) lock only one part. More in absolute vs relative references.
Result: 60
Ana's commission: her 1,200 times the 5% rate in H2. Copied down, E2 becomes E3, E4 and so on, while $H$2 stays on the rate.
What is the difference between COUNT and COUNTA?
COUNT counts cells that hold numbers. COUNTA counts every cell that is not empty, text included. The gap between them is a quick way to spot text in a number column. See COUNT vs COUNTA.
Result: COUNT 7, COUNTA 8
COUNTA is one higher because of the text TBC in E8.
What is the difference between VLOOKUP and XLOOKUP?
Both find a value in one column and return a value from the same row. VLOOKUP only looks to the right, needs a column number that breaks when columns are inserted, and does approximate match unless you add FALSE. XLOOKUP takes the return range directly, can look left, matches exactly by default and has a built-in "if not found" value. XLOOKUP needs Excel 2021 or Microsoft 365, so I would still know VLOOKUP. Full comparison: VLOOKUP vs XLOOKUP.
Result: A-1003
The order number for Cara's first order. The rep is in column B and the order is in column A, to its left, which VLOOKUP cannot return.
What is the difference between SUMIF and SUMIFS?
SUMIF handles one condition and puts the range to add last. SUMIFS handles one or more conditions and puts the range to add first. I use SUMIFS even for one condition, so adding a second never means rewriting the formula. See SUMIF vs SUMIFS.
Result: 3630
Only orders that are both East and Paid. Ben's East order is still open, so it is left out.
What is the difference between IFERROR and IFNA?
IFERROR replaces any error with your value. IFNA replaces only #N/A, the "not found" error. Around a lookup I prefer IFNA, because IFERROR would also hide a real mistake, like a wrong range, and make it look like a valid result.
Result: Not found
Order A-1010 does not exist, so IFNA shows the text instead of #N/A.
What is the difference between nested IF and IFS?
Both test conditions in order and return the value for the first one that is true. IFS lists the pairs flat instead of nesting them, so it is easier to read, but it has no default: if nothing matches it returns #N/A, so end it with TRUE and a fallback value. See IFS vs nested IF.
Result: Large
Ana's first order is 1,200, so the first test is true.
Intermediate Excel interview questions
These are the everyday tasks of an analyst, coordinator or admin role: lookups, multi-condition totals, dates and text.
Walk me through how VLOOKUP works.
Four arguments: the value to find, the table (with that value in its first column), which column of the table to return, and FALSE for an exact match. The common traps are leaving out FALSE, a column number that counts from the wrong place, and IDs stored as text in one table and numbers in the other.
Result: 800
Column 5 of A2:F9 is Amount, so this returns the amount of order A-1004.
Why do people use INDEX and MATCH?
MATCH finds the position of a value; INDEX returns the item at that position from another range. Together they look in any direction and do not break when columns are inserted, and they work in every version of Excel. See INDEX MATCH.
Result: A-1003
MATCH finds 1,430 in the third position of E2:E9, and INDEX returns the third order number.
How would you count rows that meet two conditions?
COUNTIFS, with a range and condition for each test. Every condition must be true for a row to count.
Result: 2
East orders over 1,000. The order of exactly 1,000 does not count, because > means strictly greater.
How do you write an IF with more than one condition?
Put AND or OR inside the IF test. AND needs every condition true; OR needs at least one. More in IF with AND and OR.
Result: Priority
Ana's order is paid and at least 1,000, so it is flagged Priority.
How do you calculate the percent change between two numbers?
(new - old) / old, then format the cell as a percentage. Dividing by the new number instead is the classic slip.
Result: -0.20833333333 shows as -20.8% with Percent format
Ben's order compared with Ana's: a negative result means a decrease.
How do you count working days between two dates?
NETWORKDAYS counts Monday to Friday between two dates, including both ends, and takes an optional list of holidays. Plain subtraction counts every calendar day.
Result: 20
Working days from the first order date to the last.
How would you join a list of names into one cell?
TEXTJOIN takes a delimiter, TRUE to skip empty cells, and the range. For two cells, & is enough: =B2&" "&C2.
Result: Ana, Ben, Cara, Dev
The first four reps in one cell, separated by a comma and a space.
How would you stop people typing wrong values into a column?
Data validation. Select the cells, then Data > Data Validation, Allow: List, and point the Source at a range that holds the allowed values. People pick from a drop-down, and anything else is rejected. See how to create a drop-down list.
Numbers will not add up and are aligned to the left. What is wrong?
They are stored as text, usually from an export. SUM skips text, so the total is too low. I would check with ISNUMBER, then convert them with Data > Text to Columns > Finish, or the warning icon > Convert to Number. See numbers stored as text.
Advanced Excel interview questions
For analyst and finance roles, expect questions about summarizing data and building sheets other people can trust.
What is a PivotTable and when would you use one?
A PivotTable summarizes a list by category without formulas: drag Region to Rows and Amount to Values and you have totals by region in seconds. I use one to explore data or answer a one-off question, and formulas like SUMIFS when the report needs a fixed layout. Remember to refresh it when the data changes. See how to create a PivotTable.
What are dynamic array functions?
In Excel 365 and 2021, one formula can return many values that spill into the cells below. FILTER, SORT and UNIQUE replace a lot of manual copying. If something is in the way, you get #SPILL!.
Result: spills 4 rows × 1 column
| A-1002 |
| A-1005 |
| A-1006 |
| A-1007 |
Every open order, spilled into the cells below the formula.
How would you look something up using two conditions?
Multiply the tests so a row scores 1 only when both are true, then XLOOKUP the 1. Another option is a helper column that joins the two keys. See XLOOKUP with multiple criteria.
Result: A-1005
Ana's West order. Her East order is skipped because only one test is true for it.
What does SUMPRODUCT do?
It multiplies ranges row by row and adds the results. With TRUE/FALSE tests multiplied together, it works as a multi-condition SUMIFS that also accepts calculations inside the test, and it works in every version. See SUMPRODUCT vs SUMIFS.
Result: 3630
The same East and Paid total as the SUMIFS answer above, which is a quick way to check one against the other.
How would you count the unique values in a column?
In Excel 365, COUNTA around UNIQUE. In older versions, Remove Duplicates on a copy of the column, or a PivotTable, gives the same count.
Result: 4
Four different reps appear in the list.
Why turn a range into an Excel Table?
Ctrl+T makes it a Table: formulas fill down by themselves, new rows are included in every formula and PivotTable that uses the Table, and you can use readable names like Orders[Amount]. It is the simplest way to stop a report breaking when data grows.
How do you check a formula someone else wrote?
Formulas > Evaluate Formula steps through it one part at a time. Trace Precedents draws arrows to the cells it uses, and F2 colors each reference. I also test it on a row I can check by hand.
How do you make sure a report is right before you send it?
Cross-check totals two ways: the sum of the regions should equal the grand total, and a PivotTable should match the SUMIFS. Then look for #N/A, blanks and text in number columns, and check the last row is inside every range.
How to handle a live Excel task
Some interviews hand you a file and a task, like "summarize sales by region and flag anything overdue", with someone watching or a short deadline. How you work counts as much as the result.
- Ask one or two questions before you start: what the output should look like, and whether you can use any version of Excel (FILTER and XLOOKUP need 365 or 2021).
- Talk through your approach as you go: "I'll make it a Table first, then SUMIFS by region." Silence makes a pause look like being stuck.
- Look at the data before writing formulas: blanks, text in number columns, extra spaces, duplicate rows.
- Point formulas at cells, never type the numbers in. Put inputs like a rate or a target in their own labeled cells.
- Check with a quick total: the parts should add up to the whole, and select a range to read the sum in the status bar.
- If you get stuck, say what you would try next, and use the simpler method that you know works.
- Finish with a readable layout: headers, number formats and one sentence on what the numbers show.
Common mistakes
Reciting syntax with no example
Describe what the function is for, then give a one-line example from a real situation: "SUMIFS to total paid East orders".
Claiming skills you cannot show
If the job says PivotTables and you have only seen one, practice building one before the interview. A live task finds out quickly.
Typing numbers into formulas during a live task
Reference the cells. A formula like =E2*0.05 hides the rate; =E2*$H$2 shows it and updates when it changes.
Not checking the answer
Finish every task with a sanity check: a quick total, a count of rows, or the same number calculated another way.
FAQ
What Excel skills are tested in interviews?
It depends on the role. Office and admin roles tend to focus on formulas, SUM and COUNT, IF, sorting and filtering. Analyst and finance roles add lookups, SUMIFS and COUNTIFS, PivotTables and checking your work. The job posting is the best guide to what you will be asked.
Will I have to use Excel live in the interview?
Sometimes. Some interviews include a live task, a timed test or a take-home file; others only ask questions. Ask the recruiter what format to expect so you can practice the right thing.
Do I need to know macros or VBA?
Only if the posting mentions them. Formulas, lookups and PivotTables come first; macros are a separate skill that some roles list and many do not.
How can I practice Excel interview questions?
Answer the questions on this page out loud, then take the free Cellwise Excel practice test at basic, intermediate and advanced level. It scores you by skill so you know what to review.
Functions used
VLOOKUPVertical lookupXLOOKUPModern lookup in any directionINDEXValue at a positionMATCHPosition of a valueSUMIFSAdds with several conditionsCOUNTIFSCounts with several conditionsIFPicks a result from a testIFNAFallback for #N/A onlyTEXTJOINJoins with a separatorNETWORKDAYSWeekdays between datesFILTERKeeps matching rows, spillsUNIQUERemoves duplicates, spillsSUMPRODUCTMultiply then add
Related guides
- VLOOKUP vs XLOOKUP
- COUNT vs COUNTA vs COUNTBLANK
- SUMIF vs SUMIFS: what is the difference?
- Absolute vs relative cell references in Excel
- Excel skills for your resume
- Excel formulas cheat sheet: the 25 everyone should know
- Inventory tracking formulas in Excel
Updated