Technical Interview Guide4 Practical Questions

Excel Interview Questions for Data Analysts

Q: How do modern Dynamic Array formulas (FILTER, UNIQUE, SORT) change data reporting?

Dynamic Array formulas allow a single formula in one cell to output a multi-row or multi-column result that automatically "spills" into neighboring cells. FILTER dynamically extracts sub-tables based on boolean conditions; UNIQUE extracts deduplicated lists; and SORT automatically orders the spilled array without manual copy-pasting.

Q: What is the operational purpose of IFERROR and IFNA in executive dashboards?

Uncaught errors like #N/A, #DIV/0!, or #VALUE! look unprofessional on executive dashboards and can break downstream summary formulas. IFNA traps only lookup missing values (allowing unexpected syntax errors to remain visible for debugging), while IFERROR catches all error types and returns a clean alternative value like 0, "N/A", or a fallback formula.

Spreadsheets remain the first testing filter in corporate analytics recruitment. Interviewers assess whether you write fragile, hardcoded formulas or build clean, dynamic models that scale. Review these core spreadsheet questions tested across commercial and finance analytics teams.

1Lookup & Reference

Why is XLOOKUP strongly preferred over VLOOKUP in modern analytics workflows?

What the Interviewer is Testing: Knowledge of modern Excel calculation engines and robust formula design.

Model Answer:

XLOOKUP eliminates three critical weaknesses of VLOOKUP: 1) It searches both left and right (lookup column does not need to be on the far left). 2) It defaults to exact match, avoiding catastrophic approximate-match errors. 3) It references specific column ranges directly rather than hardcoded column index numbers (e.g., column 4), meaning newly inserted columns never break existing formulas.

Code Implementation:

=XLOOKUP(E2, Orders[CustomerID], Orders[TotalAmount], "Not Found")

Common Candidate Mistake: Continuing to use VLOOKUP with hardcoded column numbers that silently corrupt data when someone adds a column to the source sheet.

2Conditional Aggregations

How do SUMIFS and COUNTIFS work, and how do they handle multiple conditional criteria?

What the Interviewer is Testing: Ability to aggregate multi-dimensional transaction records without PivotTables.

Model Answer:

SUMIFS calculates the sum of cells meeting multiple criteria across different ranges using AND logic. The syntax requires the sum range first, followed by pairs of criteria ranges and conditions. Wildcards (* and ?) can be used for text pattern matching, and logical operators (">=", "<=") must be enclosed in quotes and concatenated with cell references.

Code Implementation:

=SUMIFS(Orders[Amount], Orders[Region], "North", Orders[Status], "Delivered", Orders[Date], ">="&DATE(2026,1,1))

Common Candidate Mistake: Confusing SUMIF (which places criteria first and sum range last) with SUMIFS (which places sum range first), causing calculation errors.

3Dynamic Arrays

How do modern Dynamic Array formulas (FILTER, UNIQUE, SORT) change data reporting?

What the Interviewer is Testing: Familiarity with Excel dynamic spill mechanics introduced in Office 365.

Model Answer:

Dynamic Array formulas allow a single formula in one cell to output a multi-row or multi-column result that automatically "spills" into neighboring cells. FILTER dynamically extracts sub-tables based on boolean conditions; UNIQUE extracts deduplicated lists; and SORT automatically orders the spilled array without manual copy-pasting.

Code Implementation:

=SORT(FILTER(Employees[Name:Salary], Employees[Department]="Analytics"), 2, -1)

Common Candidate Mistake: Blocking the spill range with existing static cell contents, causing a #SPILL! error.

4Error Trapping

What is the operational purpose of IFERROR and IFNA in executive dashboards?

What the Interviewer is Testing: Defensive formula design and executive presentation polish.

Model Answer:

Uncaught errors like #N/A, #DIV/0!, or #VALUE! look unprofessional on executive dashboards and can break downstream summary formulas. IFNA traps only lookup missing values (allowing unexpected syntax errors to remain visible for debugging), while IFERROR catches all error types and returns a clean alternative value like 0, "N/A", or a fallback formula.

Code Implementation:

=IFERROR(Revenue / Customers, 0)

Common Candidate Mistake: Wrapping entire long calculation blocks blindly with IFERROR(..., 0), which hides critical formula typos and reference corruptions from the analyst.

Live Technical Interview Preparation

Practice Live Technical Assessments with SSSAM Faculty

Reading questions is the first step. Writing queries under timed screen-share tests with live mentor reviews builds genuine interview confidence.

Explore Full Data Analyst Course →Book Free Demo →

SQL Skill GuideMaster database queries, multi-table joins, and window functions →Portfolio ProjectsBuild practical case studies to present during interview rounds →2026 RoadmapThe full learning sequence to prepare for analyst hiring →