Course Outline
Advanced Fundamentals + Introduction to AI in Excel
1. Advanced Data Formatting
- Conditional formatting based on multiple criteria
- Custom rules with formulas
- Custom formats (Custom Format)
2. Efficient Work with Functions
- Essential functions for calculations (SUM, AVERAGE, MIN, MAX, etc.)
- Automating calculations through tables (structured references)
- Dynamic ranges and modern functions (LET, LAMBDA – intro)
3. Data Validation and Accuracy Verification
- Data Validation – complex rules
- Dependencies/ dynamic lists
- Error identification (Trace Dependents/Precedents)
**4. Introduction to AI in Excel
- What Copilot can do in Excel
- How to describe a task effectively (prompts for data and calculations)
- Limitations + best practices
Data Analysis + Modern Functions + AI
1. Advanced Filtering and Sorting
- Types of filters
- Custom sorting
- Filtering with the FILTER function (Excel 365)
2. Time and Date Functions
- WEEKDAY, WORKDAY, DATE, EOMONTH
- Automatic calendar generation
- Term and scheduling calculations
3. Working with Smart Tables
- Creating, using, and benefits of Table
- Automatic calculation, totals, derived columns
- Dynamic functions for data processing
**4. AI for Data Analysis and Transformation
- Cleaning data with Copilot (deduplication, structuring, conversions)
- Automatic formula generation based on descriptions
- Identifying patterns and anomalies in datasets
Advanced Functions + LAMBDA + AI for Formulas
1. Advanced Text Functions
- LEFT/RIGHT/MID, TEXTSPLIT, TEXTAFTER, TEXTBEFORE
- Formulas for data standardization
2. Complex Logical Functions
- IF / IFS
- AND, OR, XOR
- Nested functions vs simplification with LET
3. Modern Lookup Functions
- XLOOKUP (complete replacement for VLOOKUP/HLOOKUP)
- Lookup with multiple criteria
4. Advanced Functions with LAMBDA
- Creating custom functions
**5. AI for Formula Generation and Explanation
- “Write this formula for me”
- Step-by-step explanation of complex formulas
- Optimization of existing formulas
- Formula generation with multiple criteria (advanced prompting)
Advanced Analysis, Pivot Tables, Charts + AI
1. Advanced Statistical Functions
- SUMIF/SUMIFS, COUNTIFS, AVERAGEIFS
2. Pivot Tables – Advanced Level
- Building a Pivot from scratch
- Calculated Fields / Calculated Items
- Data grouping (intervals, months, categories)
- Top 10, Slicers, Timeline
3. Data Visualization
- Charts
- Customizing layout + professional design
- Sparklines for rapid monitoring
**4. AI for Analysis, Visualization, and Synthesis
- Automatic generation of analyses (trends, outliers, conclusions)
- Automatic chart creation from text
- Writing an executive summary with Copilot
Requirements
Intermediate to advanced level of Excel
Testimonials (1)
=XLOOKUP, table design(optiuni), IF, IFS, OR & AND, short cuts