Microsoft Excel Expert – tools and methods for data analysis and presentation

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Faster data matching - You will learn when to use XLOOKUP, INDEX, and MATCH, so you can combine data from multiple sheets faster and reduce errors in reports, reconciliations, and routine business calculations.

Efficient work with large datasets - You will master workbook and formula optimization techniques, allowing you to handle large tables without slowdowns. This will make day-to-day accounting and operational analysis much smoother.

Clean, analysis-ready data - You will learn how to clean unstructured data, remove inconsistencies, and prepare datasets for analysis. This will cut manual file preparation time and improve the quality of your reporting.

Advanced formula-driven analysis - You will build complex logical, text, and date formulas that automate checks, classifications, and what-if scenarios. This gives you more precise and reliable business analysis results.

Expert pivot tables - You will create pivot tables based on multiple data sources, calculated fields, and the data model. This will help you produce more flexible reports for finance, accounting, and business teams.

Automated data import and cleanup - You will use Power Query to combine files, parameterize queries, and build repeatable processes. This lets you refresh data automatically instead of preparing it manually every time.

Data models and DAX measures - You will learn table relationships, hierarchies, KPIs, and advanced DAX measures, so you can analyze data across many dimensions. This supports consistent and scalable management reporting.

Automation with VBA - You will learn to create macros, forms, and event-based actions that automate reporting and user tasks. This will help you build Excel tools that save time for you and your entire team.

Training programme

1. Advanced techniques for working with data

VLOOKUP / XLOOKUP, INDEX + MATCH – comparison and application,
array formulas (dynamic and traditional) – practical applications,
operations on large data sets – optimization of operating speed,
working with unstructured data and data cleaning.

2. Expert formulas and their combinations

creating complex conditional formulas (IF, IFS, SWITCH),
advanced text and date/time functions in data analysis,
logical and array functions in what-if analyses,
creating custom formulas using named ranges.

3. Expert Pivot Tables

advanced pivot table settings (grouping, custom calculations),
calculated fields and calculated items,
combining multiple data sources in one pivot table (data model),
working with slicers and timeline.

4. Power Query – advanced data transformation

transforming and combining multiple files,
advanced M steps (M language),
query parameterization and dynamic data sources,
creating automatic data retrieval and cleansing processes.

5. Power Pivot – data modeling and analysis

creating relationships between tables,
creating hierarchies and KPIs,
writing advanced measures in the DAX language,
optimizing data model performance.

6. Work automation with VBA

introduction to VBA – how to write and run macros,
creating forms and automating the user interface,
automatic reporting and building tools in Excel,
handling events and user interaction.

7. Security, collaboration and integration

protection of sheets and data, version control,
integration with Power BI and other data sources,
real-time collaboration and advanced sharing options.

What are the prerequisites for participating in the training?

Good Excel knowledge - You should feel comfortable working in worksheets, using basic formulas, filtering, sorting, and tables. This course expands advanced skills rather than teaching Excel from scratch.

Formula experience - You should be able to create and edit basic formulas and understand cell and range references. This is necessary to move smoothly into more complex Excel solutions.

Experience with tabular data - You should have experience working with data tables, lists, and reports in Excel. This will make it easier for you to learn pivot tables, Power Query, and data modeling.

Readiness for hands-on work - You should be ready to complete exercises on your own and test solutions on data. The course is highly practical and includes intensive work on analysis and automation.