Excel Core Functions and Formula Mastery Questions

Strong proficiency with essential Excel functions: VLOOKUP and INDEX-MATCH for data lookups, IF and nested IF statements for logic, SUM/SUMIF/SUMIFS for conditional aggregation, PivotTables for data summarization, TEXT functions for formatting, IFERROR for error handling, COUNTIF/COUNTA for counting. Build efficient, understandable formulas that other people can audit and modify. Understand formula best practices and when to use each function type.

MediumTechnical

36 practiced

You need to count rows in table 'Transactions' where Status = "Open" and (Type = "A" OR Type = "B"). Provide two formulas: one using COUNTIFS and one using SUMPRODUCT. Explain the advantages and limitations of each approach when scaling to many OR values.

HardTechnical

33 practiced

You must consolidate quarterly sheets (Q1,Q2,Q3) that have inconsistent header order and occasional header name changes (e.g., 'Revenue' vs 'Sales'). Propose a formula-based approach that dynamically finds the correct column by header name for a given sheet name in A1 and returns the value for row 10 (account row). Provide a sample INDEX+MATCH (or INDEX+MATCH+INDIRECT) formula and discuss volatility and more robust alternatives.

HardTechnical

36 practiced

Create a non-volatile formula to compute a rolling 12-month average of monthly revenue in column B (starting at row 2) and place the result in column C. The formula should (a) return blank for the first 11 rows, (b) average the current and previous 11 months for subsequent rows, and (c) allow new months to be appended without adjusting fixed ranges (use a Table or INDEX-based approach; avoid OFFSET). Provide the formula using structured Table references or INDEX.

EasyTechnical

33 practiced

You're given a worksheet 'Products' with columns: A ProductID (numeric), B ProductName, C Price. On sheet 'Orders' cell B2 contains a ProductID. Write an Excel formula (in Orders!C2) using VLOOKUP to return the Price for the ProductID in B2 using an exact match, and return the friendly message "Price not found" if there is no match. Show the exact formula you would use.

EasyTechnical

24 practiced

You use the formula =A2/B2 which may return #DIV/0! when B2 is zero or blank. Write an Excel formula in C2 that returns the division result rounded to two decimals, or the string 'N/A' if a division error would occur. Also explain the difference between IFERROR and IFNA.

Unlock Full Question Bank

Get access to hundreds of Excel Core Functions and Formula Mastery interview questions and detailed answers.

Join thousands of developers preparing for their dream job.