Course Outline

Module 1: Introduction to Business Data Analysis with Excel

• Introduction, Outline & Objectives
• Functions and Formulae
• Entering and Copying Formulae
• Creating Formulas using Names
• Managing Names using Name Manager
• Searching for Functions
• Naming Ranges
• Creating Named Ranges
• Using Names in Movement
• Inserting Names in Formulae
• Formatting
• Number Formatting
• Cell Styles
• Copying and Clearing Formatting
• Case Study/Practical Component: Analyzing a real-world business dataset to apply and practice Excel functions and formatting techniques.

Module 2: Calculating with Formulas and Functions

• Use Date and Time Functions
• Use Statistical Functions
• Use Database Functions
• Use Lookup Functions
• Use Logical Functions
• Case Study/Practical Component: Calculating key business metrics using a sample dataset to demonstrate the practical application of various Excel functions.

Module 3: Visualizing Data with Charts

• Use Recommended Charts Tool
• Create Chart with Quick Analysis
• Align Chart Axis Labels
• Display Value Axis in Millions
• Create Chart with Multi-Line Labels
• Create Combo Chart
• Create Chart Template for Sharing
• Build Dynamic Chart with Table
• Case Study/Practical Component: Creating a comprehensive data visualization report using a business case to illustrate the use of different charting tools in Excel.

Module 4: Analyzing Data with Tables

• Create Table with Quick Analysis
• Remove Duplicate Records
• Add Total Row to Table
• Insert Calculated Columns
• Filter Table Data with Custom Filter
• Use Slicer to Filter Data
• Apply Conditional Formatting
• Sort and Filter Data by Color
• Create In-Cell Chart with Sparklines
• Case Study/Practical Component: Managing and analyzing a large dataset using Excel tables and filters to derive actionable insights.

Module 5: Analyzing Data with PivotTables

• Overview of PivotTable & PivotChart
• Create PivotTable from Table
• Change Report Layouts
• Show and Hide Report Totals
• Create Custom Calculations
• Filter Pivot Data with Slicer and Timeline
• Group Fields in a PivotTable
• Create Calculated Fields in PivotTable
• Case Study/Practical Component: Developing a comprehensive sales report using PivotTables to summarize and analyze business performance.

Module 6: Importing External Data Sources

• Import Data from Text Files
• Create PivotTable from Microsoft Access
• Create PivotChart from Microsoft Access
• Manage Data Connections
• Case Study/Practical Component: Integrating and analyzing data from multiple external sources to create a unified business report.

Module 7: Data Manipulation in Excel

• How Excel Handles Different Data Types
• Data Consistency
• Building Datasheets
• Sorting
• Filtering
• Data Cleaning
• Data Formatting
• Case Study/Practical Component: Cleaning and organizing a messy dataset to prepare it for analysis in a business context.

Module 8: Creating Dashboard Reports

• Overview of Dashboard
• Create PivotTable and PivotChart
• Hide Field Buttons from PivotChart
• Use Slicer to Connect PivotTables
• Build a Slicer Dashboard Report
• Case Study/Practical Component: Building an interactive Excel dashboard to monitor key performance indicators (KPIs) for a business.

Module 9: Power BI Essentials

• Power BI Overview
• Setting Up a Power BI Account
• Installing Power BI Desktop
• Connecting to Multiple Data Sources
• Data Modelling
• Introduction to DAX
• DAX: Crossjoin
• DAX #2: Meet the Most Popular DAX Formula – CALCULATE
• DAX #3: VALUES, Another Must-Know DAX Formula
• DAX #4: CALENDAR, Every Sales/Operations Analyst's Friend
• DAX #5: SUMMARIZE
• Case Study/Practical Component: Creating a data model in Power BI and applying DAX formulas to solve a business problem.

Module 10: Business Data Analytics with Power BI

• Connecting to and Cleaning Data
• Creating Calculated Fields in Power BI
• Implicit vs Explicit Measures
• Building a Data Model
• Data Tables vs Lookup Tables
• Building Charts: Line, Bar, Geo Maps
• Creating a Slicer
• Creating a Dashboard
• Case Study/Practical Component: Developing a full Power BI dashboard to present insights from a complex business dataset.