X
Excel Zero to Hero Interactive Learning Platform
0%
Active Recall & Retention

Revision & Active Recall Center

Reinforce mental models, retain technical syntax, and prepare for analytics interviews using 3D flip cards, senior Q&A, and diagnostic error logs.

🗂️ 3D Active Recall Flashcard Deck

Mastered: 0 / 7 (0%)
Card 1 of 7 Excel Core
Question Press Space or Click to Flip
Loading question...
Touch/Click card to reveal answer
Answer & Logic Solution
Loading answer...
Assess your confidence below

💼 Senior Data Analyst Interview Questions & Model Answers

💼 Senior Data Analyst Excel Interview Questions & Model Answers

Question 1: How do you handle and audit missing values in a 100,000-row dataset?

Model Answer:
"I first audit missingness across the six dimensions of data quality using Power Query or Pivot Table count comparisons (COUNT vs COUNTA). I determine whether missing values are structural/random errors or valid operational nulls—such as in call center logs where abandoned calls naturally have no answer speed or talk duration. For erroneous nulls, I choose between statistical imputation (mean/median for skewed distributions), categorical placeholder tagging ('Unassigned'), or filtering out records if the primary key is corrupted."

Question 2: Explain the difference between Row Context and Filter Context in DAX.

Model Answer:
"Row Context exists during row-by-row iteration, such as inside a Calculated Column or iterator function like SUMX; it only knows about values in the current row. Filter Context is the set of active filters applied to the data model by Pivot Table row headers, column headers, slicers, and report filters. Measures evaluate exclusively under Filter Context. When we wrap an expression in CALCULATE, it performs Context Transition, transforming the current Row Context into an equivalent Filter Context."

Question 3: Walk me through designing an executive dashboard in Excel.

Model Answer:
"I begin in the 'Ask' phase of DALC, clarifying stakeholder questions and defining 4-5 core KPIs. Structurally, I ingest data via Power Query, model it into an Excel Table or Star Schema in Power Pivot, and build explicit DAX measures. For the UI, I follow an F-pattern layout: top-tier KPI cards with clear comparison targets, a middle tier for comparative category and distribution charts, and a bottom tier for temporal trends. I synchronize interactive Slicers across all pivot tables using Report Connections and eliminate chart clutter to maximize the data-ink ratio."

⚠️ Diagnostic Error Log & Common Pitfalls

⚠️ Top 15 Excel Pitfalls & Diagnostic Error Log

Error Code / Pitfall Root Cause Proven Diagnostic Solution
#SPILL! A non-empty cell is blocking a dynamic array (UNIQUE, FILTER) from expanding. Clear all cells below and to the right of the formula cell.
#N/A in VLOOKUP Lookup value does not exist, or data type mismatch (e.g. Number stored as Text). Convert ID column to clean numbers, or use XLOOKUP with fallback [if_not_found].
#DIV/0! Dividing by zero or an empty cell in ratio calculations. Wrap formula in =IFERROR(A/B, 0) or use DAX =DIVIDE(A, B, 0).
#VALUE! Performing math operations on text strings (e.g. ="100" + "Text"). Inspect cell format; ensure clean numeric input using VALUE() or NUMBERVALUE().
#REF! A cell referenced by the formula was deleted. Undo deletion (Ctrl + Z) or reconstruct formula reference.
VLOOKUP Column Shift User inserted a column, causing col_index_num to point to wrong attribute. Migrate from VLOOKUP to XLOOKUP or INDEX & MATCH.
Pivot Table Out-of-Date Source table edited, but Pivot Table does not auto-refresh. Right-click -> Refresh (Alt + F5) or automate via Workbook open macro.
Summing Text Numbers as 0 Exported numbers have leading apostrophes or text format. Select column -> Data > Text to Columns -> Finish to coerce to numbers.
Unanchored Lookup Ranges Dragging formula down causes lookup table $A$1:$B$10 to drift. Press F4 to lock table reference with dollar signs ($A$1:$B$10).
Imputing Valid Operational Nulls Replacing abandoned call speed nulls with 0, distorting ASA. Maintain nulls or flag as "Abandoned" to avoid corrupting average metrics.

⚡ 15-Minute Technical Cram Sheet

⚡ High-Yield 15-Minute Technical Cram Sheet

1. Top 5 Formula Syntaxes to Remember

  1. XLOOKUP: =XLOOKUP(val, lookup_vec, return_vec, "Fallback")
  2. SUMIFS: =SUMIFS(sum_vec, crit_vec1, crit1, crit_vec2, crit2)
  3. COUNTIFS: =COUNTIFS(crit_vec1, crit1, crit_vec2, crit2)
  4. IFS: =IFS(cond1, res1, cond2, res2, TRUE, fallback)
  5. TEXTJOIN: =TEXTJOIN(", ", TRUE, text_array)

2. Table Syntax

  • [@Column]: Active row
  • Table[Column]: Whole column

3. Pivot Tables

  • Refresh shortcut: Alt + F5 (active table) or Ctrl + Alt + F5 (all tables).
  • Always use Show Values As for percentage compositions.

4. Power Query

  • Always check column datatypes after promoting headers.
  • Use Unpivot Other Columns to normalize wide reports.