Description

Duration: 2 days

Course Overview

Data is everywhere — in sales records, HR files, finance reports, operations logs, and customer databases. Yet for most organisations, the true value of that data goes unrealised, not because the data isn't there, but because the tools to analyse it aren't being fully used. Microsoft Excel remains the most widely available and versatile data analysis tool in the workplace today, and professionals who know how to clean, analyse, visualise, and report on data using Excel hold a measurable advantage in their roles and careers. This programme is designed for beginners who are ready to move beyond basic spreadsheet use and start working with data analytically.

This two-day beginner programme covers seven practical modules — Data Cleaning & Structuring, Advanced Formulas, Pivot Tables for Analysis, Data Visualization in Excel, Basic Dashboard Creation, Scenario Analysis, and an Introduction to Automating Repetitive Tasks. Participants will develop the KNOW-WHATs of each Excel feature and analytical concept, the KNOW-WHYs of when each tool adds value in real-world data work, and the KNOW-HOWs through continuous hands-on exercises built around realistic workplace datasets. By Day 2, participants will integrate all seven modules in a Capstone Mini-Project where they build a complete, analytics-ready report and dashboard from scratch using a raw dataset.

 

Course Outcome

• Apply data cleaning, structuring, and text function techniques to prepare raw, unorganised data for reliable analysis using Microsoft Excel.
• Use advanced Excel formulas — including conditional aggregation (SUMIFS, COUNTIFS), logical functions (IF, IFS), and lookup functions (XLOOKUP) — to perform meaningful calculations and extract insights from datasets.
• Analyse and summarise large datasets using Pivot Tables and communicate findings clearly through professional charts, graphs, and conditional formatting.
• Build a basic analytics dashboard in Excel, apply scenario analysis tools (Goal Seek, Scenario Manager, Data Tables), and record simple macros to automate repetitive reporting tasks.

 

Course Module

Module 1: Data Cleaning & Structuring

• Why data quality is the foundation of all analytics
• Identifying common data quality problems: blanks, duplicates, inconsistencies
• Text to Columns — splitting combined data fields
• Flash Fill — pattern-based data filling and reformatting
• Removing duplicates and standardising inconsistent entries
• TRIM, CLEAN, PROPER, UPPER, LOWER — text cleaning functions
• Find & Replace for bulk data corrections
• Structuring data as a proper Excel Table — why this matters
• Data validation — controlling what gets entered into a dataset

 

Module 2: Advanced Formulas

• Recap of essential formulas: SUM, COUNT, AVERAGE, MAX, MIN
• Conditional aggregation: SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF
• Logical functions: IF, nested IF, IFS, AND, OR for data categorisation
• Error handling: IFERROR and IFNA — building robust formulas
• Lookup functions: VLOOKUP, XLOOKUP — joining datasets
• Text functions for data analysis: CONCAT, TEXTJOIN, LEFT, RIGHT, MID
• Date functions: TODAY, DATEDIF, MONTH, YEAR, WEEKDAY
• Named Ranges — making formulas readable and reusable

 

Module 3: Pivot Tables for Analysis

• What Pivot Tables are and why analysts rely on them
• Creating your first Pivot Table from a clean dataset
• Rows, Columns, Values, and Filters — understanding the layout
• Summarising data: SUM, COUNT, AVERAGE, MAX in Pivot Tables
• Grouping data by date: months, quarters, and years
• Filtering and sorting within a Pivot Table
• Calculated Fields — adding custom calculations inside a Pivot Table
• Slicers — interactive filters for Pivot Table exploration
• Refreshing Pivot Tables when source data changes

 

Module 4: Data Visualization in Excel

• Principles of effective data visualisation — clarity over decoration
• Choosing the right chart type for the right message
• Bar, column, and line charts — when and how to use them
• Pie and donut charts — limitations and proper use cases
• Combo charts — combining two chart types on one canvas
• Formatting charts for clarity: titles, labels, gridlines, and colour
• Sparklines — in-cell mini-charts for compact reporting
• Conditional Formatting — turning numbers into visual signals

 

Module 5: Basic Dashboard Creation

• What makes an effective analytics dashboard
• Planning a dashboard: defining the audience, purpose, and KPIs
• Designing a dashboard layout — spacing, alignment, and sections
• Linking Pivot Tables and charts to a single dashboard sheet
• Connecting Slicers to multiple Pivot Tables for unified filtering
• Using named ranges and dynamic references for live data
• Formatting techniques: removing gridlines, borders, and backgrounds
• Adding KPI summary cards using simple formula-driven cells
• Making the dashboard print-ready and presentation-clean

 

Module 6: Scenario Analysis

• What is scenario analysis and when to use it
• Goal Seek — working backwards from a target result
• What-If Analysis: Scenario Manager — comparing multiple scenarios
• Data Tables — one-variable and two-variable sensitivity tables
• Practical business scenarios: break-even, target revenue, cost modelling
• Documenting and presenting scenario results clearly

 

Module 7: Automating Repetitive Tasks (Introduction Level)

• What automation means in Excel at the beginner level
• Introduction to Macros — what they are and how they work
• Recording your first macro — step-by-step walkthrough
• Running, editing, and saving macros
• Using macro buttons for one-click execution
• Practical automation examples: formatting reports, clearing data, refreshing Pivot Tables
• Introduction to the VBA Editor — knowing what's there without coding it
• Safe macro practices: enabling macros, trusted locations, .xlsm files

 

Capstone Mini-Project: Build a Data Analytics Report