Course Introduction

This course is designed to help learners understand the fundamentals of Data Analytics and develop practical skills using Microsoft Excel and Microsoft Power BI.

Learners will start with basic concepts such as data collection, cleaning, and analysis, and gradually move toward advanced Excel techniques, Power Query, Power Pivot, DAX, interactive dashboards, data visualization, and business reporting.

The course focuses on hands-on learning and real-world business scenarios, enabling students to transform raw data into meaningful insights and professional reports.

Course Objectives

By the end of this course, learners will be able to:

• Understand the fundamentals of Data Analytics.
• Work confidently with Excel for data analysis.
• Clean and transform raw datasets.
• Use Excel formulas and functions for analysis.
• Create PivotTables, PivotCharts, and interactive reports.
• Understand Power Query for data transformation.
• Understand data modeling and relationships.
• Use Power Pivot and DAX for advanced analysis.
• Create professional dashboards using Power BI.
• Connect Power BI to different data sources.
• Build interactive reports and visualizations.
• Apply filters, slicers, drill-downs, and bookmarks.
• Publish and share Power BI reports.
• Work on real-world data analytics projects.

Course Structure

Module 1: Introduction to Data Analytics

Topics Covered

• What is Data?
• What is Information?
• What is Data Analytics?
• Importance of Data Analytics in Business
• Types of Data Analytics
  • Descriptive Analytics
  • Diagnostic Analytics
  • Predictive Analytics
  • Prescriptive Analytics
• Data Analytics Life Cycle
• Data Collection
• Data Cleaning
• Data Transformation
• Data Analysis
• Data Visualization
• Reporting and Decision Making
• Role of a Data Analyst
• Skills Required for a Data Analyst
• Introduction to Excel and Power BI

Practical

• Understanding a real-world business dataset
• Identifying raw data, structured data, and useful information
• Basic data analysis exercise

Module 2: Excel Fundamentals

Topics Covered

• Introduction to Microsoft Excel
• Excel Interface
• Workbook and Worksheets
• Rows, Columns, and Cells
• Data Entry
• Data Types
• Formatting Cells
• Number Formatting
• Conditional Formatting
• Sorting and Filtering
• Find and Replace
• Freeze Panes
• Tables
• Basic Charts
• Page Setup and Printing

Practical

• Create an employee dataset
• Create a sales dataset
• Format and organize business data

Module 3: Excel Formulas & Functions

Topics Covered

Basic Functions

• SUM
• AVERAGE
• COUNT
• COUNTA
• MAX
• MIN

Logical Functions

• IF
• AND
• OR
• NOT
• IFERROR
• IFS

Text Functions

• LEFT
• RIGHT
• MID
• LEN
• TRIM
• UPPER
• LOWER
• PROPER
• CONCAT
• TEXTJOIN

Date & Time Functions

• TODAY
• NOW
• DATE
• DAY
• MONTH
• YEAR
• DATEDIF
• EOMONTH
• NETWORKDAYS

Lookup Functions

• VLOOKUP
• HLOOKUP
• XLOOKUP
• INDEX
• MATCH

Other Useful Functions

• SUMIF
• SUMIFS
• COUNTIF
• COUNTIFS
• AVERAGEIF
• AVERAGEIFS
• UNIQUE
• FILTER
• SORT

Practical

• Employee salary analysis
• Sales analysis
• Customer lookup system
• Monthly sales calculations

Module 4: Excel Data Cleaning & Preparation

Topics Covered

• Understanding Dirty Data
• Missing Values
• Duplicate Data
• Incorrect Data Types
• Blank Rows and Columns
• Removing Duplicates
• Text to Columns
• Data Validation
• Flash Fill
• Handling Errors
• Standardizing Data
• Cleaning Names and Addresses
• Cleaning Dates
• Cleaning Numeric Data

Practical Project

Customer Data Cleaning Project

Learners will receive a raw customer dataset containing:

• Duplicate records
• Missing values
• Incorrect formats
• Inconsistent names
• Incorrect dates

They will clean and prepare the dataset for analysis.

Module 5: Excel Data Analysis

Topics Covered

• Data Analysis Techniques
• Sorting and Filtering
• Advanced Filters
• Excel Tables
• Named Ranges
• Conditional Formatting
• What-If Analysis
• Goal Seek
• Scenario Manager
• Data Tables
• Basic Statistical Analysis

Practical

Sales Performance Analysis

Analyze:

• Total Sales
• Total Profit
• Sales by Region
• Sales by Product
• Monthly Sales
• Top Customers
• Best-Selling Products

Module 6: PivotTables & PivotCharts

Topics Covered

• Introduction to PivotTables
• Creating PivotTables
• Rows, Columns, Values, and Filters
• Grouping Data
• Date Grouping
• Calculated Fields
• Sorting PivotTables
• Filtering PivotTables
• Slicers
• Timelines
• PivotCharts
• Interactive Reports

Practical Project

Sales Dashboard using Excel PivotTables

Create a dashboard showing:

• Total Revenue
• Total Profit
• Total Orders
• Sales by Region
• Sales by Category
• Monthly Sales Trend
• Top 10 Products

Module 7: Introduction to Power Query

Topics Covered

• What is Power Query?
• Power Query Interface
• Connecting to Data Sources
• Importing Excel Data
• Importing CSV Files
• Importing Multiple Files
• Data Types
• Removing Columns
• Renaming Columns
• Filtering Data
• Removing Duplicates
• Splitting Columns
• Merging Columns
• Replacing Values
• Handling Missing Values
• Conditional Columns
• Custom Columns
• Append Queries
• Merge Queries
• Refreshing Data

Practical Project

Automated Data Cleaning using Power Query

Build a process that automatically imports and cleans monthly sales files.

Module 8: Introduction to Power BI

Topics Covered

• What is Power BI?
• Why Power BI?
• Power BI Ecosystem
• Power BI Desktop
• Power BI Service
• Power BI Mobile
• Power BI Report
• Dashboard vs Report
• Power BI Workflow
• Connecting Power BI to Data

Data Sources

• Excel
• CSV
• Text Files
• Folder
• Web
• SQL Server
• Other Databases

Practical

• Import an Excel dataset into Power BI
• Create your first Power BI report

Module 9: Power Query in Power BI

Topics Covered

• Power Query Editor
• Data Transformation
• Data Cleaning
• Filtering
• Sorting
• Removing Duplicates
• Changing Data Types
• Splitting Columns
• Merging Columns
• Appending Tables
• Merge Queries
• Conditional Columns
• Custom Columns
• Parameters
• Query Dependencies
• Refreshing Data

Practical Project

Retail Data Transformation

Import multiple raw datasets and transform them into an analysis-ready dataset.

Module 10: Data Modeling in Power BI

Topics Covered

• What is Data Modeling?
• Tables and Relationships
• Primary Keys
• Foreign Keys
• Relationship Types
  • One-to-One
  • One-to-Many
  • Many-to-Many
• Fact Tables
• Dimension Tables
• Star Schema
• Snowflake Schema
• Date Tables
• Active and Inactive Relationships
• Relationship Filtering

Practical

Create a Sales Data Model containing:

• Sales
• Customers
• Products
• Employees
• Regions
• Date

Module 11: DAX Fundamentals

Topics Covered

• What is DAX?
• DAX Syntax
• Calculated Columns
• Measures
• Calculated Tables
• Row Context
• Filter Context
• Context Transition

Basic DAX Functions

• SUM
• AVERAGE
• COUNT
• DISTINCTCOUNT
• MIN
• MAX
• DIVIDE

Logical Functions

• IF
• SWITCH
• AND
• OR

Filter Functions

• CALCULATE
• FILTER
• ALL
• ALLEXCEPT
• REMOVEFILTERS

Practical

Create measures for:

• Total Sales
• Total Profit
• Total Orders
• Average Sales
• Profit Margin
• Customer Count

Module 12: Advanced DAX

Topics Covered

• CALCULATE in Detail
• FILTER Context
• Time Intelligence
• Date Functions
• TOTALYTD
• TOTALMTD
• TOTALQTD
• SAMEPERIODLASTYEAR
• DATEADD
• PREVIOUSMONTH
• PREVIOUSYEAR
• Running Total
• Year-over-Year Growth
• Month-over-Month Growth
• Percentage Contribution
• Ranking
• Top N Analysis

Practical

Create advanced business KPIs:

• YoY Sales Growth
• MoM Sales Growth
• YTD Sales
• Previous Year Sales
• Profit Growth
• Customer Growth
• Product Ranking

Module 13: Data Visualization in Power BI

Topics Covered

• Principles of Data Visualization
• Choosing the Right Chart
• Bar Chart
• Column Chart
• Line Chart
• Pie Chart
• Donut Chart
• Area Chart
• Scatter Chart
• Treemap
• Waterfall Chart
• Funnel Chart
• Maps
• Cards
• KPI Visuals
• Tables
• Matrix

Visualization Best Practices

• Choosing appropriate colors
• Using consistent formatting
• Avoiding misleading charts
• Creating clear titles
• Highlighting important KPIs
• Improving dashboard readability

Module 14: Power BI Dashboard Development

Topics Covered

• Dashboard Design Principles
• Page Layout
• KPI Cards
• Slicers
• Filters
• Drill Down
• Drill Through
• Tooltips
• Bookmarks
• Buttons
• Page Navigation
• Interactive Charts
• Conditional Formatting
• Themes
• Custom Visuals

Practical Project

Interactive Sales Dashboard

Dashboard sections:

• Executive Summary
• Sales Analysis
• Product Analysis
• Customer Analysis
• Regional Analysis
• Profitability Analysis

Module 15: Advanced Power BI

Topics Covered

• Advanced Filters
• Visual-Level Filters
• Page-Level Filters
• Report-Level Filters
• Sync Slicers
• Drill Through Pages
• Report Tooltips
• Bookmarks
• Buttons
• Dynamic Titles
• Dynamic Measures
• Field Parameters
• What-If Parameters
• Row-Level Security
• Performance Optimization
• Report Optimization

Module 16: Power BI Service

Topics Covered

• Introduction to Power BI Service
• Publishing Reports
• Workspaces
• Reports and Dashboards
• Sharing Reports
• Apps
• Scheduled Refresh
• Data Gateways
• Permissions
• Row-Level Security
• Collaboration
• Exporting Reports
• Managing Published Reports

Module 17: Excel + Power BI Integration

Topics Covered

• Excel vs Power BI
• When to use Excel
• When to use Power BI
• Importing Excel Data into Power BI
• Power Query in Excel and Power BI
• Power Pivot
• DAX in Excel
• Connecting Excel to Power BI
• Refreshing Data
• Building an End-to-End Analytics Workflow

Module 18: Real-World Business Analytics

Learners will analyze different business scenarios.

Sales Analytics

• Revenue
• Profit
• Orders
• Products
• Customers
• Regions
• Sales Trends

HR Analytics

• Employee Count
• Attrition
• Salary Analysis
• Department Analysis
• Employee Performance
• Hiring Trends

Finance Analytics

• Revenue
• Expenses
• Profit
• Budget vs Actual
• Financial Trends

Customer Analytics

• Customer Segmentation
• Customer Revenue
• Repeat Customers
• Customer Retention
• Customer Trends

E-Commerce Analytics

• Orders
• Revenue
• Products
• Customers
• Conversion
• Average Order Value

Module 19: Data Analytics Projects

Project 1: Sales Analytics Dashboard

Tools: Excel + Power BI

Analyze a retail company's sales data and create an interactive dashboard.

Key KPIs:

• Revenue
• Profit
• Orders
• Quantity Sold
• Average Order Value
• Profit Margin

Project 2: HR Analytics Dashboard

Analyze employee data to understand:

• Total Employees
• Attrition Rate
• Department-wise Employees
• Gender Distribution
• Salary Analysis
• Employee Experience
• Hiring Trends

Project 3: E-Commerce Analytics

Analyze:

• Orders
• Revenue
• Customers
• Products
• Categories
• Locations
• Monthly Trends
• Top Products

Project 4: Financial Performance Dashboard

Analyze:

• Revenue
• Expenses
• Profit
• Budget
• Actual Performance
• Variance
• Monthly Financial Trends

Project 5: Customer Analytics

Analyze:

• Customer Segments
• Customer Revenue
• Repeat Customers
• New Customers
• Customer Lifetime Value
• Customer Retention

Module 20: Capstone Project

At the end of the course, learners will complete a full end-to-end Data Analytics project.

Project Workflow

Raw Data → Excel → Power Query → Data Cleaning → Data Model → DAX → Power BI → Dashboard → Business Insights

Capstone Deliverables

• Raw Dataset
• Cleaned Dataset
• Data Model
• DAX Measures
• Interactive Power BI Dashboard
• Business Insights
• Final Presentation

Recommended Course Duration

SectionSuggested DurationData Analytics Fundamentals4 HoursExcel Fundamentals8 HoursExcel Functions12 HoursExcel Data Cleaning & Analysis8 HoursPivotTables & Dashboards8 HoursPower Query8 HoursPower BI Fundamentals6 HoursData Modeling8 HoursDAX12 HoursData Visualization6 HoursPower BI Dashboard8 HoursAdvanced Power BI8 HoursPower BI Service4 HoursReal-World Projects15 HoursCapstone Project10 HoursTotal~125 Hours

Tools Covered

• Microsoft Excel
• Power Query
• Power Pivot
• DAX
• Microsoft Power BI Desktop
• Power BI Service

Course Outcome

After completing this course, learners will have the practical knowledge required to work on real-world data analytics tasks, including data cleaning, transformation, analysis, visualization, dashboard development, and business reporting using Excel and Power BI.

They will also have multiple portfolio projects that can be used for job applications, interviews, freelancing, or academic projects.

‍