Course Introduction

Microsoft Excel is more than a spreadsheet tool. It is a powerful platform for analysing business data, evaluating alternatives and supporting better decision-making.

This 1-day practical course is designed for participants who already have basic Excel knowledge and want to apply Excel to real-world business situations. Participants will learn how to use logical and lookup functions, analyse data using What-If Analysis, create effective charts, and use PivotTables and PivotCharts to identify trends and insights.

The emphasis is not simply on learning Excel functions, but on using Excel to analyse information and support practical business decisions.

This course covers features available in Excel 2016 and later. Some functions require Office 365 or Excel 2019 and later. Windows version required. The trainer will demonstrate all exercises using Microsoft Office 365.

Prerequisite

Basic Excel knowledge.

Course Objectives

By the end of the course, participants will be able to:

• Apply intermediate Excel functions to practical business problems.
• Use logical and conditional functions to analyse business information.
• Use lookup functions to retrieve and compare information.
• Apply What-If Analysis to evaluate alternative business scenarios.
• Create appropriate charts to communicate business information.
• Create PivotTables and PivotCharts to summarise and analyse datasets.
• Interpret Excel results and use them to support business decisions.

Course Benefits

Participants will:

• Strengthen their practical Excel and data-analysis skills.
• Improve their ability to analyse business information.
• Automate repetitive calculations and analysis.
• Transform raw data into meaningful business information.
• Improve reporting and decision-making capabilities.
• Gain greater confidence in using Excel as a business analysis tool.

Who Should Attend

• Office administrators, clerical staff and managers
• Staff in sales, HR, finance, operations and administration
• Professionals who use Excel in their daily work
• Fresh graduates with basic Excel knowledge
• Anyone who wants to progress from basic Excel to practical business analysis

Teaching Methodology

• Step-by-step demonstrations
• Practical business-focused examples
• Individual and group exercises
• Hands-on application of Excel tools
• Interactive discussions and Q&A

COURSE OUTLINE

Module 1: Logical & Conditional Functions for Business Analysis

Content

• IF
• Nested IF
• IFS
• AND / OR with IF
• COUNTIF
• SUMIF
• COUNTIFS
• SUMIFS
• Applying multiple conditions
• Using logical functions to create business rules
• Applying conditional calculations to business decisions

Activities

• Create business rules using IF
• Classify business information based on specific criteria
• Analyse sales or employee data using multiple conditions
• Use SUMIFS and COUNTIFS to answer business questions
• Evaluate information and make decisions based on defined criteria

Module 2: Lookup Functions for Business Applications

Content

• VLOOKUP
• HLOOKUP
• INDEX and MATCH
• Combining INDEX + MATCH
• Brief look at XLOOKUP – modern lookup function
• Selecting the appropriate lookup method
• Using lookup functions to retrieve and compare business information

Activities

• Retrieve information from customer, staff or product datasets
• Use lookup functions to combine information from different tables
• Compare business information using lookup functions
• Apply lookup functions to a practical business scenario

Module 3: What-If Analysis for Business Decision-Making

Content

• Overview of What-If Analysis
• Data Tables
  • One-variable analysis
  • Two-variable analysis
• Goal Seek
• Scenario Manager
• Comparing alternative business scenarios
• Using What-If Analysis to evaluate business decisions

Activities

• Use Goal Seek to evaluate a loan or target-based decision
• Create a Data Table to analyse changes in key assumptions
• Compare alternative outcomes using Scenario Manager
• Evaluate different scenarios and identify the most appropriate option

Module 4: Charts for Business Reporting

Content

• Creating charts from business datasets
• Choosing the appropriate chart for different types of information
• Column charts
• Bar charts
• Line charts
• Pie charts
• Combo charts
• Customising chart elements
• Creating charts from Excel Tables
• Presenting trends and comparisons effectively
• Interpreting information from charts

Activities

• Select an appropriate chart for a business dataset
• Create and customise business charts
• Convert business data into a clear visual report
• Identify trends, comparisons and key information from charts

Module 5: Data Analysis with Dynamic Arrays, PivotTables & PivotCharts

Content

Dynamic Array Functions

• UNIQUE
• FILTER
• TAKE
• Using dynamic arrays for data extraction and analysis
• Combining dynamic array functions for practical business analysis

Creating PivotTables from business datasets

• Understanding PivotTable fields:
  • Rows
  • Columns
  • Values
  • Filters
• Grouping data
• Filtering data
• Slicers
• Creating PivotCharts
• Using Excel Tables as PivotTable data sources
• Refreshing PivotTable results
• Basic calculated fields
• Using PivotTables to identify trends and patterns
• Using PivotTables to support business analysis

Activities

• Build a PivotTable from a business dataset
• Summarise and analyse business information
• Analyse information by category, period or department
• Create PivotCharts and apply slicers
• Identify trends and key insights from PivotTable results