Advanced Excel with Power BI

The Advanced Excel with Power BI – 2 Month Program is a practical, job-oriented course designed to develop professional skills in data analysis, data management, reporting, and business intelligence.

The program covers Advanced Microsoft Excel, data cleaning, formulas, Pivot Tables, dashboards, data visualization, Power Query, Power Pivot, DAX fundamentals, and Microsoft Power BI. Learners work with practical datasets and real-world business scenarios to create interactive reports and dashboards.

By the end of the program, learners will be able to analyze large datasets, automate reporting tasks, build professional Excel dashboards, and create interactive Power BI reports for business decision-making.

Module 1 – Advanced Excel Fundamentals

• Excel Interface & Workbook Management

• Data Entry and Data Formatting

• Cell Referencing

• Relative, Absolute & Mixed References

• Named Ranges

• Data Sorting & Filtering

• Advanced Filtering

• Find & Replace

• Data Validation

• Conditional Formatting

• Excel Tables

• Freeze Panes & View Management

• Worksheet & Workbook Protection

Module 2 – Advanced Excel Formulas & Functions

• SUM, AVERAGE, MIN & MAX

• COUNT, COUNTA & COUNTBLANK

• SUMIF & SUMIFS

• COUNTIF & COUNTIFS

• AVERAGEIF & AVERAGEIFS

• IF, IFS & Nested IF

• AND, OR & NOT

• IFERROR

• VLOOKUP

• HLOOKUP

• XLOOKUP

• INDEX & MATCH

• LEFT, RIGHT & MID

• LEN, TRIM & CLEAN

• CONCATENATE & TEXTJOIN

• TEXT Functions

• DATE & TIME Functions

• ROUND, ROUNDUP & ROUNDDOWN

• UNIQUE, FILTER & SORT

• Practical Formula Exercises

Module 3 – Data Analysis with Excel

• Data Cleaning Techniques

• Removing Duplicates

• Handling Blank & Incorrect Data

• Data Transformation

• Advanced Sorting & Filtering

• Excel Tables

• Subtotals

• What-If Analysis

• Goal Seek

• Scenario Manager

• Basic Data Analysis Techniques

Module 4 – Pivot Tables & Pivot Charts

• Introduction to Pivot Tables

• Creating Pivot Tables

• Rows, Columns, Values & Filters

• Grouping Data

• Calculated Fields

• Slicers

• Timelines

• Pivot Charts

• Interactive Reports

• Sales Analysis

• Expense Analysis

• Customer Analysis

• Product Analysis

Module 5 – Excel Dashboard Development

• Dashboard Fundamentals

• Dashboard Planning

• KPI Creation

• Charts & Visualizations

• Interactive Charts

• Slicers & Timelines

• Dynamic Reports

• Sales Dashboard

• Finance Dashboard

• HR Dashboard

• Management Dashboard

• Dashboard Design Best Practices

Module 6 – Power Query

• Introduction to Power Query

• Importing Data

• Connecting Excel Files

• Connecting CSV Files

• Data Cleaning

• Removing Duplicates

• Splitting & Merging Columns

• Changing Data Types

• Filtering Data

• Replace Values

• Append Queries

• Merge Queries

• Transforming Data

• Refreshing Data

• Automated Data Preparation

Module 7 – Power Pivot & Data Modeling

• Introduction to Power Pivot

• Creating Data Models

• Importing Multiple Tables

• Relationships Between Tables

• Primary & Foreign Keys

• One-to-Many Relationships

• Calculated Columns

• Measures

• Basic Data Modeling Concepts

• Working with Multiple Data Sources

Module 8 – DAX Fundamentals

• Introduction to DAX

• DAX Syntax

• Calculated Columns vs Measures

• SUM & SUMX

• COUNT & COUNTROWS

• CALCULATE

• FILTER

• IF

• DISTINCTCOUNT

• DIVIDE

• Basic Time Intelligence

• Date-Based Calculations

• KPI Measures

• Practical DAX Exercises

Module 9 – Power BI Fundamentals

• Introduction to Power BI

• Power BI Desktop

• Power BI Interface

• Importing Data

• Excel Data Connection

• CSV Data Connection

• Data Cleaning with Power Query

• Data Transformation

• Creating Data Models

• Relationships

• Basic DAX Measures

Module 10 – Power BI Data Visualization

• Charts and Visuals

• Bar & Column Charts

• Line Charts

• Pie & Donut Charts

• Area Charts

• Cards

• KPI Visuals

• Tables & Matrix

• Slicers

• Filters

• Map Visualizations

• Drill Down

• Drill Through

• Tooltips

• Interactive Reports

Module 11 – Power BI Dashboard & Report Development

• Report Layout Planning

• Interactive Dashboard Design

• KPI Dashboard

• Sales Dashboard

• Finance Dashboard

• HR Dashboard

• Customer Dashboard

• Product Performance Dashboard

• Monthly & Yearly Analysis

• Dynamic Filtering

• Report Navigation

• Bookmarks

• Buttons

• Professional Report Design

Module 12 – Power BI Service

• Introduction to Power BI Service

• Publishing Reports

• Workspaces

• Dashboards

• Sharing Reports

• Exporting Reports

• Data Refresh Concepts

• Scheduled Refresh Concepts

• Basic Collaboration Features

Module 13 – Real-World Projects

Project 1 – Sales Dashboard

• Sales Data Cleaning

• Product & Customer Analysis

• Monthly Sales Analysis

• Sales KPIs

• Interactive Sales Dashboard

Project 2 – Finance Dashboard

• Revenue Analysis

• Expense Analysis

• Profit Analysis

• Monthly Financial Trends

• KPI Reporting

Project 3 – HR Analytics Dashboard

• Employee Data Analysis

• Department Analysis

• Attendance Analysis

• Employee Trends

• HR Dashboard

Project 4 – Complete Business Intelligence Project

• Raw Data Collection

• Data Cleaning

• Data Transformation

• Data Modeling

• DAX Calculations

• Dashboard Development

• Final Business Report

• Program Duration: 2 Months

• Course Type: Job-Oriented Professional Program

• Training Approach: Practical + Project-Based Learning

• Software Covered: Microsoft Excel & Microsoft Power BI

• Excel Level: Advanced

• Data Analysis: Professional Data Analysis Techniques

• Data Cleaning: Excel & Power Query

• Data Modeling: Power Pivot & Power BI

• DAX: Fundamentals & Practical Calculations

• Reporting: Interactive Excel & Power BI Reports

• Dashboards: Professional Business Dashboards

• Projects: Real-World Data Analytics Projects

• Assignments: Regular Practical Assignments

• Case Studies: Business-Based Data Analysis

• Interview Preparation: Excel & Power BI Interview Questions

• Career Guidance: Resume & Career Guidance

• Assessment: Tests, Assignments & Final Project

• Basic computer knowledge

• Basic understanding of Microsoft Excel is recommended

• No programming experience is required

• Basic Mathematics and logical reasoning skills

• Interest in Data Analysis and Business Intelligence

• Willingness to practice with real-world datasets

• Suitable for beginners as well as working professionals