Description

Duration: 2 days

Course Overview:

Professionals who have completed an Excel analytics foundation are often at a critical crossroads; they have the basics but find themselves hitting the ceiling of what standard formulas, manually prepared data, and static charts can deliver. The real productivity and analytical leap comes from the next layer: automated data pipelines that refresh with a click, formulas that think in arrays and adapt dynamically, dashboards that respond to user input, statistical models that support evidence-based decisions, and a data model that connects multiple related tables without a single VLOOKUP. This intermediate programme is designed specifically for professionals who are ready to make that leap.

Building directly on the foundations developed in the Beginner programme, this two-day intermediate course covers seven advanced modules: Advanced Formulas & Dynamic Arrays (LET, LAMBDA, FILTER, SORT, UNIQUE, SEQUENCE), Power Query for automated data transformation and pipeline building; Advanced Pivot Tables & Pivot Charts (Show Values As, Calculated Fields, GetPivotData), Statistical Analysis & Forecasting (ToolPak, correlation, regression, FORECAST.ETS), Advanced Data Visualisation with dynamic and dropdown-driven charts; Interactive Dashboard Development using form controls and dynamic named ranges; and an Introduction to Power Pivot & DAX for relational data modelling. Participants will develop the KNOW-WHATs, KNOW-WHYs, and KNOW-HOWs of each technique through structured, hands-on exercises built around realistic, multi-sheet workplace datasets across both days.

By the end of this programme, participants will be empowered to build automated Power Query data pipelines that eliminate manual data preparation, write advanced formulas that dynamically reshape and summarise data, create fully interactive dashboards driven by form controls and dynamic references, produce statistically sound trend lines and forecasts, and build a relational Power Pivot data model with DAX measures that go far beyond what standard Pivot Tables can deliver. The programme culminates in an end-to-end Capstone Project where participants build a complete intermediate-level analytics report and dashboard from a raw, multi-sheet dataset, integrating all seven modules into a single polished, refreshable, and interactive output.

 

Audience:

Working professionals across all industries and functions who have completed an introductory Excel analytics programme (equivalent to Wipdata's Data Analytics with Excel Beginner) and wish to develop advanced analytical, automation, and dashboard skills. Suitable for analysts, executives, finance professionals, operations staff, reporting specialists, and managers who regularly work with data.

 

Prerequisites:

Completion of Data Analytics with Excel (Beginner) or equivalent working knowledge, including: data cleaning with basic functions, SUMIFS and COUNTIFS, XLOOKUP or VLOOKUP, standard Pivot Tables, basic charting, and simple macros. Microsoft Excel 2019 or later required (Microsoft 365 strongly recommended for dynamic array and LAMBDA support).

 

Methodology:

The programme will maximise understanding and learning through Interactive Lectures, Discussions, Practical Exercises, Hands-on Workshops, Group Activities, and an end-to-end Capstone Analytics Project.

 

Objectives:

• Apply advanced Excel formulas including LET, LAMBDA, and dynamic array functions (FILTER, SORT, UNIQUE, SEQUENCE, BYROW) to build efficient, self-updating calculations, and use Power Query to automate end-to-end data transformation pipelines with one-click refresh.
• Perform advanced data analysis using Pivot Tables with Show Values As, Calculated Fields, and GetPivotData, and apply statistical tools including correlation, linear regression, and time-series forecasting to produce evidence-based insights from workplace datasets.
• Design and build advanced dynamic data visualisations and fully interactive Excel dashboards using form controls, dynamic named ranges, and dropdown-driven charts appropriate for management and stakeholder reporting.
• Build a relational Power Pivot data model with basic DAX measures, and integrate all seven modules into a complete end-to-end intermediate analytics report and dashboard from a raw, multi-sheet dataset.

 

Course Module:

Module 1: Advanced Formulas & Dynamic Arrays

• LET function defining variables inside formulas for clarity and efficiency
• LAMBDA creating custom, reusable functions without VBA
• Dynamic array spill behaviour: understanding how arrays populate ranges
• FILTER extracting subsets of data dynamically based on one or more conditions
• SORT and SORTBY sorting arrays without disturbing source data
• UNIQUE extracting distinct values for dropdown lists and reports
• SEQUENCE generating number and date series automatically
• BYROW and BYCOL applying calculations across rows and columns in one formula
• Array-based aggregation: combining FILTER with SUM, COUNT, and AVERAGE logic
• Formula efficiency: when to use dynamic arrays vs. helper columns

Module 2: Power Query for Data Transformation & Automation

• What Power Query is and why it replaces manual data preparation
• Connecting to data sources: Excel files, CSV, folders, and web pages
• The Applied Steps pane understanding the M code behind every transformation
• Removing columns, filtering rows, and correcting data types
• Splitting, merging, and pivoting/unpivoting columns
• Group By aggregating data by category without Pivot Tables
• Merging queries: the Power Query equivalent of XLOOKUP
• Appending queries combining multiple sheets or monthly files into one table
• Adding conditional columns without writing M code
• Refreshing queries, building a one-click automated data pipeline

Module 3: Advanced Pivot Tables & Pivot Charts

• Recap: Pivot Table fundamentals and addressing common gaps
• Show Values As: % of total, % of row/column, running totals, and rank
• Multiple Consolidated Ranges Pivot Tables across several data sources
• Grouping non-date fields: bucketing numeric data into custom ranges
• Advanced Calculated Fields and Calculated Items
• Creating and formatting Pivot Charts directly from Pivot Table data
• Connecting Pivot Charts to Slicers for fully interactive visual reports
• Drilling down and drilling into Pivot Table detail
• GetPivotData referencing specific Pivot Table values in external formulas
• Pivot Table design best practices for reporting and presentation

Module 4: Statistical Analysis & Forecasting in Excel

• Descriptive statistics: AVERAGE, MEDIAN, MODE, STDEV, VAR, SKEW, KURT
• Using the Data Analysis ToolPak for automated statistical summaries
• Understanding distribution: histograms and frequency analysis
• Correlation analysis: measuring the relationship between two variables
• CORREL and PEARSON calculating correlation coefficients in Excel
• Linear regression: FORECAST.LINEAR and LINEST
• Trend analysis: TREND, GROWTH, and LOGEST for forward projections
• ETS exponential smoothing for time-series data
• Building a forecast chart with confidence interval bands
• Presenting statistical findings clearly to non-technical audiences

Module 5: Advanced Data Visualization & Dynamic Charts

• Choosing the right chart for the message a refresher with intermediate depth
• Advanced chart types: waterfall, funnel, treemap, sunburst, and histogram
• Dynamic named ranges making charts self-update when data grows
• Using OFFSET and INDEX to build charts that auto-expand with new rows
• Dropdown-driven dynamic charts: changing what a chart displays via a list selection
• Combination charts with dual axes for datasets with different scales
• Chart annotations: adding reference lines, callout labels, and data callouts
• Building small multiples comparison charts across multiple categories
• Chart design principles for management and executive-level reporting

Module 6: Interactive Dashboard Development (Advanced)

• Advanced dashboard design: layout hierarchy, user flow, and visual balance
• Dynamic dashboard titles using formula-linked text boxes
• Form controls: dropdown lists, scroll bars, and option buttons
• OFFSET and INDEX-MATCH for control-driven dynamic data display
• Building a dashboard where one dropdown or Slicer controls all visuals
• Incorporating Sparklines and formula-driven KPI cards
• Protecting the dashboard: locking structure while preserving interactivity
• Performance optimisation: reducing calculation load on large dashboards
• Final formatting: removing gridlines, setting print areas, and adding branding

Module 7: Introduction to Power Pivot & Data Modelling

• What Power Pivot is and how it differs from standard Pivot Tables
• Loading multiple related tables into the Power Pivot Data Model
• Creating table relationships building a basic star schema
• Introduction to DAX: Data Analysis Expressions
• Core DAX measures: SUM, COUNT, CALCULATE, DIVIDE
• Calculated columns vs. measures: understanding the critical difference
• RELATED accessing values across related tables in a DAX formula
• Building a Pivot Table from the Power Pivot data model
• Advantages of the data model: no data duplication, no VLOOKUP limitations
• When to use Power Pivot vs. standard Excel Pivot Tables

Capstone Project: End-to-End Intermediate Analytics Report