Data Modeling and Integration
- Power Pivot: Enables creation of advanced data models and supports large datasets with fast calculations using DAX.
- Data Model: Integrates multiple tables into a single model for use with PivotTables and Power Pivot.
- Data Connections: Links external workbooks or databases so updates reflect dynamically when source data changes.
- Related: See also Powerquery for ETL capabilities.
Lookup and Retrieval
- VLOOKUP: Finds a value in the first column of a range and returns a value from a specified column in the same row.
- INDEX-MATCH / INDEX-MATCH-MATCH: More flexible alternatives to VLOOKUP, allowing lookups by row and column positions.
Analysis and Forecasting
- Excel pivot table: Summarizes, groups, and aggregates large datasets for interactive analysis.
- Forecast Sheet: Generates time series forecasts using historical data.
- What-If Analysis: Includes tools like Scenario Manager, Goal Seek, and Data Tables to explore how input changes affect outputs.
- Consolidate: Aggregates data from multiple sheets or ranges into a single summary.
Formula Tools and Validation
- Evaluate Formula: Walks through a formula step-by-step to debug or understand calculations.
- Data Validation: Restricts input values, enables drop-down lists, and enforces data entry rules.
Data Cleaning and Preparation
- Text to Columns: Splits text from one column into multiple columns based on delimiters or fixed width.