Advanced MS Excel Training in Madrid, Spain

This advanced-level course is designed to elevate participants’ Excel skills to professional levels. It focuses on data analysis, complex formulas, data visualization, automation with macros, and Excel’s powerful tools like Power Query, Power Pivot, and advanced charting. The course enables participants to handle large datasets efficiently and make informed, data-driven decisions.

Course Agenda

Advanced Formula Techniques

• Nested IF, AND, OR statements

• LOOKUP functions: VLOOKUP, HLOOKUP, XLOOKUP, INDEX & MATCH

• TEXT, DATE, and ARRAY formulas

• Error handling: IFERROR, ISERROR

Data Tools and Validation

• Data Validation and Conditional Formatting

• Sorting and advanced filtering techniques

• Flash Fill and custom lists

• Removing duplicates and data consistency checks

PivotTables and PivotCharts

• Creating PivotTables for summarizing large data

• Using Slicers and Timelines for interactivity

• Custom calculations in PivotTables

• Creating and formatting PivotCharts

Data Analysis Techniques

• Goal Seek, Scenario Manager, and Solver

• Data Tables and What-If Analysis

• Forecasting and Trendlines

• Statistical functions: STDEV, CORREL, NORM.DIST

Power Query for Data Transformation

• Connecting to external data sources

• Cleaning and shaping data

• Merging and appending queries

• Creating reusable queries and reports

Power Pivot for Data Modeling

• Creating data models and relationships

• Using DAX (Data Analysis Expressions)

• Building advanced PivotTables with Power Pivot

Dashboards and Data Visualization

• Dashboard design principles

• Dynamic charts and interactive controls

• Linking data with slicers and form controls

• Creating KPI visuals

Automation with Macros and VBA

• Recording and editing macros

• VBA basics: variables, loops, and procedures

• Automating repetitive tasks

• Creating custom functions with VBA

Hands-on Exercises