Course objective: to provide advanced Excel skills: in-depth use of formulas, pivot tables, task automation using macros, and the basics of data analytics.
Format: online lessons + practical assignments
Duration: 12–14 lessons (1–1.5 hours each)
Level: advanced user
Certificate: upon completion of the course
Course program
Module 1. Advanced formulas
- Using functions: IF, SUMIF, COUNTIF, AVERAGEIF.
- Logical functions and nested conditions.
- Working with text functions: CONCAT, LEFT, RIGHT, MID, TRIM.
- Date and time: TODAY, NOW, DATE, YEAR, MONTH, DAY.
Module 2. References and ranges
- Absolute, relative, and mixed references.
- References to other Excel sheets and workbooks.
- Working with named ranges.
Module 3. Pivot tables and analytics
- Creating and configuring pivot tables.
- Grouping data, filters, and sorting in pivot tables.
- Pivot charts.
- Calculated fields and elements.
Module 4. Working with large data sets
- Filters, conditional formatting, and highlighting duplicates.
- Finding and fixing errors.
- Techniques for working with large tables and databases.
Module 5. Data visualization
- Advanced charts: combination, line, trend charts.
- Using Sparklines for mini-charts in cells.
- Configuring chart formats and visualization elements.
Module 6. Macro basics and automation
- Introduction to macros: what they are and why they are used.
- Recording a simple macro.
- Editing macros using VBA (Visual Basic for Applications).
- Automating repetitive actions.
Module 7. Working with array formulas
- Array formulas and dynamic arrays.
- Functions: UNIQUE, SORT, FILTER, SEQUENCE.
- Practical application for reporting and analytics.
Module 8. Data protection and error control
- Locking cells and sheets.
- Using data validation.
- Checking formulas and searching for errors.
Module 9. Practice and case studies
- Creating a comprehensive report with formulas and pivot tables.
- Automating reports with macros.
- Working with real data: importing, filtering, analytics.
Module 10. Final assignment
- Developing an independent project using all the tools covered in the course.
- Applying advanced formulas, pivot tables, charts, and macros.
- Final test and certificate issuance.
