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

49 practiced

Given 'Sales' with Date (A), ProductName (B), Region (C), Amount (D), write a SUMIFS formula that sums Amount for ProductName containing the substring 'Pro' (case-insensitive), for the Region specified in F1, and for dates on or after start date F2. Ensure that if F1 is blank the formula returns totals for all regions. Provide a practical formula that meets these requirements.

EasyTechnical

24 practiced

In a financial presentation you need to display an invoice date from A2 as 'Mar-2026' and an invoice amount from B2 as '$1,234,567.00' as text values. Provide the TEXT formulas to achieve these displays and explain when it's preferable to use a cell's Number Format instead of TEXT().

HardTechnical

34 practiced

You are asked to create a concise Excel formula/style guide for your finance team. Include concrete rules covering naming conventions for named ranges and tables, when to use Tables vs raw ranges, helper column guidelines, recommended max formula complexity (e.g., nesting depth), prohibition or permitted use of volatile functions, documentation and comment expectations, and a model-review checklist. Provide 8-12 clear rules and a short checklist.

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.