5 questions across Easy, Medium, and Hard levels
VLOOKUP looks up a value in the first column and returns a value from a specified column to the right. Limitations: can only look left-to-right, slow on large datasets. INDEX-MATCH is more flexible: MATCH finds position, INDEX returns value. Can look in any direction, faster, doesn't break when columns are inserted. Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).
SUMIF: =SUMIF(range, criteria, sum_range) - sums values where condition is met. Example: =SUMIF(A:A, "Sales", B:B) sums all B values where A is "Sales". COUNTIF: =COUNTIF(range, criteria) - counts cells meeting condition. Example: =COUNTIF(A:A, ">100") counts values greater than 100. SUMIFS/COUNTIFS allow multiple criteria.
PivotTables summarize, analyze, and present large datasets. Steps: 1) Select your data range, 2) Insert → PivotTable, 3) Drag fields to Rows (categories), Columns (cross-analysis), Values (aggregations like Sum, Average, Count), Filters (global filters). Right-click to change aggregation. Use slicers for interactive filtering. Refresh when source data changes.
Array formulas perform calculations on multiple values simultaneously. Enter with Ctrl+Shift+Enter (shows {} curly braces). Example: {=SUM(A1:A10*B1:B10)} multiplies each pair then sums. Modern Excel has dynamic arrays (FILTER, SORT, UNIQUE, XLOOKUP) that don't need Ctrl+Shift+Enter. Use when you need to perform row-by-row calculations without helper columns.
Steps: 1) Structure: separate Inputs (assumptions), Calculations (linked to inputs), Outputs (summaries/charts). 2) Income Statement: Revenue → Gross Profit → EBITDA → Net Income. 3) Balance Sheet: Assets = Liabilities + Equity, ensure it balances. 4) Cash Flow Statement: Operating + Investing + Financing activities. 5) Link all three statements. 6) Add scenario analysis (Base/Bull/Bear). 7) Sensitivity tables for key assumptions. 8) Audit: trace precedents/dependents.