Financial Modeling
- Build 3-statement financial models (P&L, balance sheet, cash flow).
- Create budget vs. actual variance models, scenario/sensitivity analysis.
- Use driver-based forecasting and break-even analysis.
PivotTables & PivotCharts
- Summarize large data sets quickly.
- Slice data by dimensions (e.g., department, region, time period).
- Create dynamic dashboards with PivotCharts.
Advanced Formulas & Functions
Must-know formulas:
- VLOOKUP, HLOOKUP → For legacy sheets (but XLOOKUP is better).
- XLOOKUP, INDEX + MATCH → For dynamic, efficient lookups.
- SUMIFS, COUNTIFS → For conditional summaries.
- IF, IFS, AND, OR, NOT → For logical operations.
- TEXT, LEFT, RIGHT, MID, LEN, TRIM → For data cleaning.
- EOMONTH, WORKDAY, TODAY → For date logic in forecasts.
Power Query (Get & Transform)
- Import, clean, and reshape large data sets from multiple sources (CSV, ERP, database).
- Automate recurring data prep tasks.
- Merge and appaend tables without manual work.
Power Pivot & Data Models
- Create relationships between tables (like a database).
- Use DAX (Data Analysis Expressions) to build advanced KPIs and metrics.
- Handle millions of rows efficiently.
Dashboards & Data Visualization
- Build interactive dashboards using:
- PivotTables + Slicers
- Conditional Formatting
- Combo Charts (e.g., line + bar for trend vs. actual)
- Use named ranges and form controls (drop-downs, scroll bars).
Data Validation & Controls
- Create dropdown lists to standardize inputs.
- Prevent data entry errors with input messages and restrictions.
- Use ISERROR, IFERROR, and error-trapping formulas.