Excel Interview Questions For Data Analyst and Business Roles

VLOOKUP searches for a value in the first column of a range and returns a value from a column to the right (vertical lookup). HLOOKUP does the same across rows (horizontal lookup). VLOOKUP is far more common because most data is arranged in columns.

A relative reference (A1) shifts when you copy the formula. An absolute reference ($A$1) stays fixed. Mixed references lock only the row or column ($A1 or A$1). Use absolute references for constants you don't want to move.

A pivot table summarizes large datasets by dragging fields into rows, columns, values, and filters — letting you group, aggregate, and cross-tabulate data (like sales by region and month) without formulas. It's one of Excel's most powerful analysis tools.

SUMIF adds values that meet a single condition, and COUNTIF counts cells that meet one. Their plural versions, SUMIFS and COUNTIFS, handle multiple conditions at once — for example summing sales for a specific region and month.

INDEX-MATCH looks up a value with MATCH and returns a cell with INDEX. It's more flexible than VLOOKUP because it can look to the left, isn't broken by inserting columns, and is often faster on large sheets.

XLOOKUP is the modern lookup function that can search in any direction, return an exact match by default, handle 'not found' with a built-in argument, and return multiple columns — replacing both VLOOKUP and INDEX-MATCH in newer Excel.

Use Data > Remove Duplicates to delete duplicate rows based on chosen columns, or use conditional formatting to highlight them first. For a non-destructive approach, use UNIQUE() (in newer Excel) or a pivot table.

Data validation restricts what can be entered in a cell — a dropdown list, a number range, or a date — to keep data clean and consistent. It's set via Data > Data Validation and can show input prompts and error messages.

LEFT, RIGHT, and MID extract characters; LEN counts them; TRIM removes extra spaces; UPPER/LOWER/PROPER change case; CONCAT or TEXTJOIN combine text; and FIND/SEARCH locate a substring. These clean and reshape text data.

Conditional formatting automatically styles cells based on their values or a formula — highlighting numbers above a threshold, color scales, data bars, or duplicates. It makes patterns and exceptions visible at a glance without manual formatting.

Power Query (Get & Transform) connects to and cleans data with repeatable steps — merging files, splitting columns, unpivoting, and filtering — before loading it into Excel or the data model. It automates the data-prep work that would otherwise be manual.

VLOOKUP can only look to the right of the lookup column, breaks when columns are inserted, needs the lookup column first, and defaults to approximate match (a common source of errors). INDEX-MATCH or XLOOKUP avoid these issues.

Wrap formulas in IFERROR (or IFNA) to replace errors like #N/A or #DIV/0! with a friendly value or blank. Understanding each error type — #N/A (not found), #REF! (bad reference), #VALUE! (wrong type) — helps you fix the root cause.

COUNT counts cells containing numbers, COUNTA counts all non-empty cells (numbers and text), and COUNTBLANK counts empty cells. Choosing the right one avoids miscounting when a column mixes text, numbers, and blanks.