Even in SQL-heavy roles, many interviews include Excel questions, because Excel is still the common language with business teams. These are the questions that come up most.
Formulas
VLOOKUP vs INDEX-MATCH vs XLOOKUP
- VLOOKUP looks up a value in the first column of a range and returns a column to its right. It cannot look left, and inserting columns can break the column number.
- INDEX-MATCH combines two functions:
MATCHfinds the row position,INDEXreturns the value. It can look in any direction and does not break when columns are inserted. - XLOOKUP (newer versions) does both directions, has a built-in "if not found" value and defaults to exact match.
Always mention exact match: VLOOKUP(..., FALSE) or match type 0. Approximate match by mistake is a classic source of wrong numbers.
SUMIF vs SUMIFS
SUMIF takes one condition; SUMIFS takes several. Note the argument order: in SUMIFS the sum range comes first.
=SUMIFS(Sales[Amount], Sales[City], "Pune", Sales[Month], "Oct")COUNT vs COUNTA vs COUNTBLANK vs COUNTIF
COUNT counts numbers, COUNTA counts non-empty cells, COUNTBLANK counts empty ones, COUNTIF counts cells meeting a condition.
Absolute vs relative references
A1 changes when copied; $A$1 stays fixed; $A1 fixes the column only, A$1 the row only. Press F4 to cycle through them.
IF, nested IF and IFS
=IF(B2>=40, "Pass", "Fail"). For several bands, IFS is cleaner than deeply nested IFs.
IFERROR
Wraps a formula and returns a fallback on error: =IFERROR(XLOOKUP(...), "Not found"). Use it carefully; hiding errors can hide real problems.
Text functions
TRIM, LEFT, RIGHT, MID, LEN, FIND, SUBSTITUTE, UPPER, PROPER, TEXT, and TEXTJOIN or & to combine.
Date functions
TODAY, EOMONTH, DATEDIF, NETWORKDAYS, YEAR, MONTH, WEEKDAY.
Pivot tables
Expect questions like:
- How do you create one and change sum to count or average?
- How do you show values as a percentage of the column or grand total?
- How do you group dates by month or quarter?
- How do you add a calculated field?
- Why might a pivot not show new data? (The source range was not updated; using an Excel Table as the source fixes this.)
Data cleaning tasks
- Remove duplicates (Data → Remove Duplicates).
- Split a full name into first and last (Text to Columns, or text functions).
- Fix numbers stored as text.
- Highlight duplicates or outliers with conditional formatting.
- Standardise inconsistent entries (find and replace,
TRIM,PROPER).
Other common questions
- What is an Excel Table and why use it? It expands automatically, keeps formulas consistent and makes references readable (
Sales[Amount]). - Data validation: restrict input to a list or a range to stop bad entries.
- Freeze panes, filters, sorting by multiple columns.
- Which chart would you use for…? Line for trends, bar for comparison, scatter for relationships.
Practise the thinking, not only the buttons
Every Excel operation has a SQL equivalent, and interviewers like candidates who see the connection. Read Excel to SQL and pandas, then practise GROUP BY and CASE WHEN, the SQL versions of pivot tables and IF.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: