18 questions · 8 topics
Excel interview questions with answers
Last reviewed
Lookups
- Easy
What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches only the first column of a range and returns a column by index number, defaulting to approximate match. XLOOKUP takes separate lookup and return ranges, can look left, defaults to exact match, has a built-in 'not found' value and can search from the bottom. Prefer XLOOKUP where available.
Read the full answer → - Medium
How does INDEX-MATCH work, and why was it preferred over VLOOKUP?
MATCH finds the position of a value in a range; INDEX returns the value at that position in another range. Combined, they perform a lookup in any direction, survive column insertions and are efficient on large sheets. It was the standard robust lookup before XLOOKUP.
Read the full answer →
Aggregation
- Easy
How do SUMIFS and COUNTIFS work with multiple conditions?
SUMIFS adds values that meet all of several criteria; COUNTIFS counts rows that meet them. The first argument of SUMIFS is the range to sum, followed by pairs of criteria range and criterion. Criteria can use operators such as ">=" combined with cell references.
Read the full answer → - Easy
What is the difference between relative, absolute and mixed references?
A relative reference (A1) shifts when copied; an absolute reference ($A$1) stays fixed; a mixed reference ($A1 or A$1) fixes only the column or row. Mixed references are what make a single formula fill a two-way grid correctly.
Read the full answer →
PivotTables
- Easy
When would you use a PivotTable, and what are its main parts?
Use a PivotTable to summarise large tables quickly — totals, counts and averages by category — without writing formulas. Its parts are Rows, Columns, Values and Filters. Slicers add interactive filtering, and grouping lets you roll dates up to months or quarters.
Read the full answer → - Medium
How do you get a distinct count in a PivotTable?
Tick 'Add this data to the Data Model' when creating the PivotTable; Value Field Settings then offers Distinct Count. Alternatively, in Microsoft 365, use =COUNTA(UNIQUE(FILTER(...))) with a formula outside the pivot.
Read the full answer →
Text & dates
- Easy
Which text functions do you use most for cleaning data?
TRIM removes extra spaces, CLEAN strips non-printing characters, UPPER/LOWER/PROPER fix case, LEFT/RIGHT/MID extract parts, TEXTBEFORE/TEXTAFTER split on delimiters, SUBSTITUTE replaces text, and TEXTJOIN combines values. VALUE converts numeric text into numbers.
Read the full answer → - Medium
How does Excel store dates, and how do you calculate the difference between two dates?
Excel stores dates as serial numbers (days since a base date) and times as fractions of a day. Subtracting two dates gives days; multiply by 24 for hours. DATEDIF gives complete years, months or days, and NETWORKDAYS counts working days excluding weekends and listed holidays.
Read the full answer →
Logic
- Easy
When would you use IFS instead of nested IFs?
IFS evaluates conditions in order and returns the value for the first true one, avoiding deeply nested IFs that are hard to read. Add TRUE as the final condition for a default. For banding numeric values, a lookup table with approximate-match XLOOKUP is often more maintainable still.
Read the full answer → - Easy
What does IFERROR do, and when can it be dangerous?
IFERROR returns an alternative value when a formula errors. It is handy for presentable reports, but it hides every error type — including genuine mistakes such as wrong ranges. Prefer handling the specific case, for example XLOOKUP's not-found argument or IFNA for lookups.
Read the full answer →
Dynamic arrays
- Medium
What are dynamic array functions? Give examples.
Dynamic array functions return multiple values that spill into neighbouring cells automatically. FILTER returns rows matching conditions, UNIQUE removes duplicates, SORT and SORTBY order results, and SEQUENCE generates numbers. They replace many helper columns and copy-down formulas.
Read the full answer →
Data cleaning
- Easy
How do you find and remove duplicates?
Highlight them with Conditional Formatting › Duplicate Values or a COUNTIFS helper column, review them, then use Data › Remove Duplicates on the right key columns. Decide first what makes a row a duplicate — the same email, or the same email and date?
Read the full answer → - Medium
What is Power Query and when should you use it?
Power Query is Excel's built-in tool for importing and transforming data with recorded, repeatable steps — split columns, change types, merge and append tables, unpivot. Use it whenever you clean the same kind of file repeatedly; next time you just click Refresh.
Read the full answer → - Easy
How would you stop people entering bad data in a shared sheet?
Use Data Validation to restrict entries to a list, number range or date range, with an input message and a clear error alert. Combine it with a structured Table, locked formula cells and sheet protection, so collaborators can enter data only where intended.
Read the full answer →
Analysis tools
- Easy
How do you choose the right chart?
Choose by the question: line charts for trends over time, bar charts for comparing categories, stacked bars for composition with few parts, scatter plots for relationships, and histograms for distributions. Avoid 3D effects and pie charts with many slices, and title charts with the takeaway.
Read the full answer → - Medium
What are Goal Seek and Data Tables used for?
Goal Seek finds the input value that makes a formula hit a target — for example, the price needed to reach a revenue goal. A Data Table shows how an output changes across a range of one or two inputs, which is useful for sensitivity analysis.
Read the full answer → - Hard
A workbook has become slow. How do you speed it up?
Find the expensive parts: volatile functions such as OFFSET, INDIRECT and TODAY recalculating constantly, whole-column references in thousands of formulas, and repeated lookups. Replace them with Tables, helper columns, XLOOKUP on bounded ranges, Power Query or the Data Model, and switch to manual calculation while editing.
Read the full answer → - Medium
When would you move an analysis from Excel to SQL or Python?
Move when data outgrows a sheet, when the same analysis must be repeated reliably, when several large sources need joining, or when you need version control and review. Excel remains excellent for quick exploration, small models and sharing results with business users.
Read the full answer →