Excel Data Visualisation and Analytics Training Course

Participants can create repeatable Power Query processes that prepare analytical data for reporting.

• Power Query imports CSV, workbook, folder and database extracts reliably
• Column profiling identifies nulls, errors, distinct values and distribution anomalies
• Applied Steps record repeatable transformations and support refreshable data preparation
• Text, date and numeric fields standardised using appropriate data types
• Duplicate records removed after defining business keys and retention rules
• Merge and Append queries combine reference data and periodic extracts
• Exception rows isolated through filters, conditional columns and error handling

Participants can build controlled analytical calculations using modern Excel formulas and structured references.

• Structured references create readable calculations within Excel Tables
• SUMIFS and COUNTIFS calculate filtered measures against defined criteria
• XLOOKUP retrieves approved attributes from reference and mapping tables
• IFERROR and validation checks identify failed lookups and invalid inputs
• Dynamic arrays use FILTER, UNIQUE and SORT for responsive report lists
• Date functions calculate reporting periods, ageing bands and rolling comparisons
• Formula auditing traces precedents, dependents and inconsistent calculation logic

Participants can summarise large datasets and investigate performance drivers using PivotTable tools.

• PivotTables summarise transactional data by category, period, region and owner
• Value Field Settings calculate percentages, differences and running totals
• Grouping dates creates month, quarter and year reporting hierarchies
• Slicers and timelines control consistent filtering across related analytical views
• Drill-down extracts reveal transactions behind aggregated values and exceptions
• PivotCharts link visual summaries directly to PivotTable filter selections
• Calculated fields distinguished from source calculations and data model measures

Participants can select and construct charts that communicate accurate findings with minimal cognitive effort.

• Bar charts compare ranked categories using a common zero baseline
• Line charts show time-series trends, seasonality and target performance
• Scatter plots reveal relationships, clusters, outliers and possible correlations
• Waterfall charts explain bridges between budget, forecast and actual results
• Small multiples compare repeated patterns without overcrowding a single chart
• Reference lines mark targets, averages, thresholds and control limits
• Titles and annotations state findings, exceptions and required management action

Participants can build an interactive Excel dashboard for a defined management audience.

• Dashboard wireframes organise KPIs, trends, drivers, filters and exceptions
• KPI cards display current values, targets, variance and directional indicators
• Conditional formatting applies data bars, icon sets and exception thresholds
• Linked shapes and text boxes create dynamic narrative reporting elements
• Slicers synchronised across PivotTables maintain consistent dashboard filtering
• Page layout settings prepare dashboards for screen viewing and printing
• Dashboard hierarchy directs attention from headline performance to root causes

Participants can create a basic Excel data model and write measures for consistent analysis.

• Power Pivot data models combine fact tables with shared dimensions
• Relationships connect transaction records to dates, products, regions and customers
• Star schema separates measurable events from descriptive lookup attributes
• DAX measures calculate totals, ratios, variances and filtered performance indicators
• CALCULATE changes filter context for controlled business measure definitions
• Time intelligence compares current periods with prior periods and year-to-date values
• Measure names and display folders support governed reporting and reuse

Participants can apply appropriate Excel statistical methods and interpret their limits.

• Descriptive statistics calculate mean, median, percentiles, range and standard deviation
• Histograms examine distribution shape, concentration, gaps and unusual observations
• Correlation coefficients quantify linear association without proving causal relationships
• Regression output evaluates coefficients, R-squared, residuals and significance values
• Forecast Sheet projects time-series values with confidence intervals and seasonality
• Moving averages smooth short-term variation before trend interpretation
• Outlier investigation distinguishes data errors from meaningful business exceptions

Participants can review an Excel analytical report for accuracy, usability and controlled publication.

• Reconciliation checks compare report totals with approved source control totals
• Data validation rules restrict invalid entries and inconsistent category values
• Calculation checks test denominators, date ranges, filters and refresh status
• Colour choices support colour-vision accessibility and meaningful visual distinction
• Chart integrity avoids truncated axes, distorted scales and misleading aggregation
• Workbook protection controls formulas, worksheet structure and authorised inputs
• Documentation records sources, definitions, transformations, assumptions and limitations

Participants can define a practical plan for applying Excel visual analytics to a live reporting need.

• Stakeholder interviews identify decisions, questions, reporting cadence and action thresholds
• Metric inventories distinguish outcome measures, drivers, targets and diagnostic indicators
• Source-to-dashboard mapping records data origins, transformations and ownership responsibilities
• Refresh plans assign extraction, validation, publication and review responsibilities
• Dashboard release criteria define accuracy, usability, accessibility and approval checks
• Feedback loops capture user questions, recurring requests and improvement priorities
• Ninety-day application plans schedule pilot reporting, review and controlled expansion