Advance Excel and Power BI Training Course

The Advance Excel and Power BI course develops practical skills for organising, analysing, and presenting business data. Participants begin with Excel fundamentals, formulas, data cleaning, PivotTables, and dashboards before progressing to Power BI, Power Query, data modelling, DAX, and interactive reporting.

The course follows a practical structure with exercises in every stage. Participants work with employee, customer, and sales datasets before completing a final Sales Performance Analysis project.

Course modules

The nine modules move from spreadsheet fundamentals to complete business reporting in Power BI. Each module combines essential concepts with a practical exercise so participants can apply the tools to realistic datasets.

The Excel section builds calculation, cleaning, analysis, and dashboard skills. The Power BI section develops data transformation, modelling, measures, visualizations, and interactive reporting capabilities.

Module 1: Advanced Excel Formulas & Business Logic

• Multi-condition calculations using SUMIFS, COUNTIFS and AVERAGEIFS.
• Applying business rules using IF, IFS, AND and OR.
• Building tiered commission calculations and performance classifications.
• Weighted averages and conditional calculations using SUMPRODUCT.
• Working-day calculations using NETWORKDAYS.INTL and WORKDAY.INTL.
• Managing missing matches and calculation errors using IFNA and IFERROR.
• Simplifying repeated calculations using LET.
• Formula auditing using Evaluate Formula and Trace Precedents/Dependents.

Practical Exercise

Develop a sales performance model to calculate target achievement, tiered commissions and follow-up turnaround times, with exception flags for records requiring review.

Module 2: Advanced Lookups, Dynamic Arrays & Data Reconciliation

• Exact, approximate and reverse searches using XLOOKUP.
• Two-way lookups using INDEX and MATCH.
• Multi-criteria lookups using combined conditions.
• Retrieving multiple matching records using FILTER.
• Creating dynamic lists using UNIQUE, SORT and SORTBY.
• Combining dynamic array functions to produce reports based on user selections.
• Understanding spilled ranges and resolving #SPILL! errors.
• Reconciling datasets to identify missing records, duplicate keys and mismatched values.
• Selecting the appropriate approach: lookup, aggregation or filtering.

Practical Exercise

Reconcile sales transactions against a customer master and approved price list. Identify missing customers and pricing discrepancies, then create a dynamic report by salesperson and region.

Module 3: Advanced Data Quality, Validation & Reporting Controls

• Structuring datasets using Excel Tables, structured references and calculated columns.
• Formula-based data validation for mandatory fields, permitted dates and unique IDs.
• Creating dependent dropdown lists for controlled data entry.
• Standardizing text using TRIM, CLEAN and SUBSTITUTE.
• Correcting numbers stored as text and inconsistent dates.
• Preserving leading zeros and identifier formats.
• Detecting duplicate transactions using multiple fields.
• Formula-based conditional formatting for overdue items and data exceptions.
• Reconciling record counts and financial totals before and after corrections.
• Creating a data-quality summary for missing, duplicate and unmatched records.
• Separating inputs, calculations and outputs, and protecting formula cells.

Practical Exercise

Prepare a raw sales dataset for reporting. Standardize entries, apply validation rules, flag potential duplicates, reconcile totals and produce an exception list.

Module 4: Excel Data Analysis & Dashboard Creation

• Organizing business data for PivotTable analysis.
• Creating PivotTables using rows, columns, values and filters.
• Summarizing sales by product, salesperson, region and period.
• Grouping dates into months, quarters and years.
• Displaying percentages, rankings and period comparisons.
• Creating PivotCharts.
• Using slicers and timelines for interactive filtering.
• Selecting suitable column, bar and line charts.
• Dashboard layout, formatting and readability.
• Building and refreshing an Excel sales dashboard.

Practical Exercise

Build a monthly sales performance dashboard showing sales trends, target achievement and performance by salesperson, product and region.

Module 5: Introduction to Microsoft Power BI

• Business applications of Power BI.
• Understanding when to use Excel and Power BI.
• Overview of Power BI Desktop and Power BI Service.
• Navigating Report, Table and Model views.
• Understanding reports, semantic models and dashboards.
• Connecting to Excel and CSV files.
• Selecting source tables and reviewing imported data.
• Choosing between loading data and transforming it first.

Practical Exercise

Connect Power BI to the prepared Excel sales dataset and review the imported tables and fields.

Module 6: Data Transformation with Power Query

• Navigating the Power Query Editor.
• Understanding applied steps and repeatable transformations.
• Promoting headers and assigning appropriate data types.
• Renaming, removing and reordering columns.
• Handling blank values, errors and duplicate records.
• Replacing values and splitting columns.
• Filtering rows and standardizing text.
• Appending tables with the same structure.
• Merging tables using matching keys.
• Unpivoting monthly columns into a reporting-friendly structure.
• Applying transformations and loading data.

Practical Exercise

Combine monthly sales files, merge product information and transform the resulting dataset into a consistent structure for analysis.

Module 7: Data Modelling & Basic DAX

• Understanding fact and dimension tables.
• Creating a simple sales data model.
• Establishing one-to-many relationships.
• Checking matching keys and relationship direction.
• Understanding measures versus calculated columns.
• Introduction to DAX syntax.
• Creating measures using SUM, AVERAGE, DIVIDE and DISTINCTCOUNT.
• Creating basic business measures:
  • Total sales.
  • Total quantity.
  • Total orders.
  • Average order value.
• Understanding how report filters affect measures.
• Formatting measures as currencies, numbers and percentages.

Practical Exercise

Create relationships between sales, customer and product tables, then develop and validate essential business measures.

Module 8: Power BI Visualizations & Report Design

• Creating column, bar and line charts.
• Using cards to display key performance indicators.
• Presenting detailed information through tables and matrices.
• Selecting visuals appropriate to the business question.
• Adding slicers and visual-, page- and report-level filters.
• Configuring interactions between visuals.
• Applying conditional formatting.
• Using consistent themes, titles and number formats.
• Designing clear report layouts and visual alignment.
• Checking report totals and filter behaviour.

Practical Exercise

Build an interactive sales report showing overall performance, monthly trends and results by product, salesperson and region.

Module 9: Capstone Project & Final Assessment

Participants will:

• Clean and validate a raw business dataset.
• Apply advanced Excel formulas to calculate performance and commissions.
• Reconcile transactions against reference data.
• Create a dynamic Excel exception report.
• Build an Excel dashboard using PivotTables, charts, and slicers.
• Import and transform data in Power BI.
• Create table relationships and essential DAX measures.
• Develop an interactive Power BI report.
• Validate calculations and report totals.
• Present findings and practical business recommendations.

Assessment Criteria

• Accuracy of calculations and reconciliation.
• Quality and consistency of prepared data.
• Appropriate use of formulas and reporting tools.
• Correct relationships and measures.
• Report usability and clarity.
• Ability to explain findings and business implications.
• Clean and prepare business data in Excel
• Perform calculations using Excel formulas
• Analyse data using PivotTables and charts
• Import data into Power BI
• Create essential DAX measures
• Build an interactive dashboard
• Identify top performing products, salespeople, and regions
• Present key business insights

What you will be able to do

By the end of the course, participants will have practised a complete workflow from spreadsheet preparation to interactive business reporting. The course develops practical skills that can be applied to structured operational and sales datasets.

• Create and format structured Excel worksheets.
• Apply commonly used Excel formulas and functions.
• Clean and organise business datasets.
• Analyse data using PivotTables, PivotCharts, and charts.
• Create Excel dashboards with slicers.
• Import Excel data into Power BI.
• Transform data using Power Query.
• Create relationships between tables.
• Develop basic DAX measures.
• Create interactive Power BI reports and dashboards.
• Analyse sales performance by product, salesperson, and region.
• Present key findings from business data.

Related courses

Participants who want to develop broader spreadsheet capabilities can continue with advanced Microsoft Excel training. This provides a closely related progression for professionals who use Excel extensively for calculations and business reporting.

For a stronger focus on reporting and visual interpretation, data analysis and visualization in Microsoft Excel connects directly with the PivotTable, charting, and dashboard topics included in this programme.