1. Advanced Formulas & Functions

Mastering logic and lookups beyond basic VLOOKUP. [1, 2]

• Lookup Functions: INDEX and MATCH, XLOOKUP, and XMATCH.
• Logical & Information Functions: Nested IFs, IFS, IFERROR, and ISBLANK.
• Advanced Aggregations: SUMIFS, COUNTIFS, AVERAGEIFS, and AGGREGATE.
• Array Formulas: Dynamic arrays, LAMBDA, MAP, and FILTER functions. [1, 2, 3, 4, 5]

2. Data Analysis & PivotTables

Extracting deep insights from large datasets without coding. [1, 2]

• Advanced PivotTables: Calculated fields, calculated items, and grouping (by dates/numbers).
• Data Visualization: Creating PivotCharts and adding Timelines/Slicers.
• Conditional Formatting: Using formulas for custom highlights and dynamic thresholds.
• Data Validation: Creating dependent drop-down lists and custom data restrictions. [1, 2, 3, 4]

3. Data Transformation & Power Query

Extract, Transform, and Load (ETL) large datasets automatically. [1, 2]

• Connecting Data: Importing data from CSVs, web sources, databases, and folders.
• Data Cleaning: Unpivoting columns, removing duplicates, splitting text, and handling errors.
• Merging & Appending: Relational joins (inner, outer, etc.) and appending multiple files together. [1, 2, 3, 4, 5]

4. Dashboards & Visualization

Building professional, interactive executive reports. [1, 2]

• Advanced Charting: Waterfall charts, Pareto charts, and Gauge/Thermometer charts.
• Dashboard Design: Structuring a visual summary using form controls and dynamic linking.
• Formulas & Ranges: Using the OFFSET or INDIRECT functions to create dynamic named ranges. [1, 2, 3, 4, 5]

5. Automation & Macros (VBA)

Streamlining repetitive tasks and building custom Excel tools. [1, 2]

• Macro Recorder: Recording and executing basic macros.
• VBA Basics: Understanding the Visual Basic Editor (VBE) and the Object Model.
• Coding Concepts: Variables, If-Then statements, and loops (For Next, Do While).
• Event Handling: Automating actions when a workbook or worksheet updates. [1, 2, 3]

6. Modeling & Forecasting

Planning and evaluating business and financial outcomes. [1]

• What-If Analysis: Data tables, Scenario Manager, and Goal Seek.
• Forecasting: Using Excel’s built-in trendlines and forecast sheets.
• Financial Modeling: Structuring Income Statements, Balance Sheets, and Cash Flow models. [1, 2, 3, 4]

For tips on how to get the logic and consistency just right when diving into these advanced concepts: