Excelgoodies logo +91 9176633248
Course on Data Transformation, Analysis and Reporting.

Data Transformation & Advanced MIS Reporting using MS-Excel and Power Query

Adv. Excel MIS Reports Power Pivot Power Query

  • Both Online & Classroom | 3 Months (12 sessions, 2 hrs each)
  • 8 Excel MIS Reports | 6 Power Query Projects

(1.5K+ Professionals enrolled)

Prove you're human: Type the code shown.

=
Excelgoodies

Program Overview

Training Schedule

Tuesday, 11 Apr

View Schedule

3 Months | 24 Hours

12 Sessions, 2 Hrs Each

Online & Classroom, Instructor-Led

Pre-Requisite

Keyboard skills

(Windows OS)

Course Fee

₹15,000

Check what’s included?

Advanced Excel & Power Query

floating_menu floating_menu floating_menu

Batch starts on

th

From Basics to Pro

Supercharging Business Users with Next-Level of Excel Reporting.

This is a 24-hour, activity-based program, designed to make you an expert in extracting, transforming, and analyzing varied data for advanced insights and reporting using Excel and Power Query.

Tools You'll Learn

Data Analysis Tool

Microsoft Excel

Data Modeling Tools

Power Pivot

Data Transformation Tool

Power Query 

In just 12 weeks, you'll be able to:

Master Power Query for Automation – Clean, transform, and automate complex data workflows.

Work with Large, Real-World Data – Handle high-volume datasets with structured, efficient techniques.

Eliminate Manual Reporting – Build reusable, refresh-driven workflows that save hours of effort.

Integrate Multiple Data Sources – Combine Excel, files, and databases into a single reporting layer.

Deliver Business-Ready Insights – Create end-to-end reports that drive faster decision-making.

In just 12 weeks, you'll be able to:

Master Power Query for Automation – Clean, transform, and automate complex data workflows.

Work with Large, Real-World Data – Handle high-volume datasets with structured, efficient techniques.

Eliminate Manual Reporting – Build reusable, refresh-driven workflows that save hours of effort.

Integrate Multiple Data Sources – Combine Excel, files, and databases into a single reporting layer.

Deliver Business-Ready Insights – Create end-to-end reports that drive faster decision-making.

A snapshot of what you'll be learning in 9-Months.

Course Syllabus Overview

Core Fundamentals

Objective: The term "basic" is subjective based on levels of knowledge, experience, exposure, etc. Basic for one individual doesn't have to be basic for another. Nearly all participants in this training are self-taught and have some Excel skills as well as some gaps.

The objective of this module is to fill gaps, bring everyone to the same level and empower them with comfort and confidence to learn Excel as a reporting solution and not as a computer tool.

CORE FUNDAMENTALS

  • Understanding Excel Environment
  • Entering, Editing and Deleting Text, Numbers, Dates
  • How Excel Understand your information including Text, Numbers and Date values.
  • Fundamental Formatting Techniques & Best Practices
  • Understanding Cell Formats including Advanced Custom Formats
  • Copying and Clearing Formats
  • Working with Styles
  • Understanding Simple Conditional Formatting
  • Moving and Copying data
  • Quick Navigation Techniques
  • Inserting, Deleting and Hiding Rows & Columns
  • Inserting, Deleting, Moving and Copying Sheets
  • Working with multiple Excel Worksheets & Workbooks
  • Working with View Tab including freeze panes, split, new workbook, etc
  • Quick Data Entry Techniques including Auto Fills
  • Understanding & Working with Formulas
  • Mastering Referencing Techniques including Relative, Absolute & Mixed Reference
  • Working with fundamental functions including sum, count, average, max, min, etc
  • Fundamental Keyboard Shortcuts

Advanced Formulas & Functions

Objective: Master 60+ MS Excel formulas to dramatically simplify the work you do in Excel. By the end of the section, you'll be writing robust, elegant formulas from scratch.

MATHEMATICAL FUNCTIONS

  • SumIf, SumIfs
  • CountIf, CountIfs
  • AverageIf, AverageIfs
  • SumProduct, Subtotal

LOOKUP FUNCTIONS

  • Vlookup / HLookup
  • Match
  • Dynamic Two Way Lookup
  • Creating Smooth User Interface Using Lookup
  • Offset
  • Index
  • Dynamic Worksheet linking using Indirect

LOGICAL FUNCTIONS

  • Nested If ( And Conditions , Or Conditions )
  • Alternative Solutions for Complex IF Conditions to make work simple
  • And, Or, Not

TEXT FUNCTIONS

  • Upper, Lower, Proper
  • Left, Mid, Right
  • Trim, Len
  • Concatenate
  • Find, Substitute

DATE AND TIME FUNCTIONS

  • Today, Now
  • Day, Month, Year
  • Date, DateDif, DateAdd
  • EOMonth, Weekday

ROUNDING FUNCTIONS

  • Round
  • RoundUp
  • RoundDown
  • MRound

ERROR HANDLING FUNCTIONS

  • isNa
  • isErr
  • isError

Modern Functions (New)

Duration – 2 Hours

Dynamic Array Functions

  • FILTER
  • SORT
  • SORTBY
  • UNIQUE
  • SEQUENCE
  • RANDARRAY

Lookup & Reference Functions

  • XLOOKUP
  • XMATCH

Text Functions

  • TEXTSPLIT
  • TEXTJOIN
  • TEXTBEFORE
  • TEXTAFTER
  • CONCAT

Logical Functions

  • IFS
  • SWITCH
  • LET
  • LAMBDA

Math & Trigonometry Functions

  • MAP
  • REDUCE
  • SCAN

Array Manipulation Functions

  • VSTACK
  • HSTACK
  • CHOOSECOLS
  • CHOSEROWS
  • WRAPROWS / WRAPCOLS

Pivots, Charts & Dashboards

Objective: This module will help you set up a professional dashbaord - learn how to visualize data through graphs and charts, create data models, and add interactivity.

PIVOT TABLES

  • Creating Simple Pivot Tables
  • Basic and Advanced Value Field Setting
  • Sorting based on Labels and Values
  • Filtering based on Labels and Values
  • Grouping based on numbers and Dates
  • Drill-Down of Data
  • GetPivotData Function
  • Calculated Field & Calculated Items

CHARTS & PIVOT CHARTS

  • Bar Charts / Pie Charts / Line Charts
  • Dual Axis Charts
  • Dynamic Charting
  • Other Advanced Charting Techniques

EXCEL DASHBOARD

  • Bar Charts / Pie Charts / Line Charts
  • Planning a Dashboard
  • Adding Tables to Dashboard
  • Adding Charts to Dashboard
  • Adding Dynamic Contents to Dashboard

Miscellaneous Topics

Objective: This section is all about working with data - and making it easy to work with. It will walk you through the different features of Excel to get your data prepared for analysis.

ADVANCED PASTE SPECIAL TECHNIQUES

  • Paste Formulas
  • Paste Formats
  • Paste Validations
  • Paste Conditional Formats
  • Add / Subtract / Multiply / Divide
  • Merging Data using Skip Blanks
  • Transpose Tables

SORTING

  • Sorting on Multiple Fields
  • Dynamic Sorting of Fields
  • Bring Back to Ground Zero after Multiple Sorts

FILTERING

  • Filtering on Text, Numbers & Date
  • Filtering on Colors
  • Copy Paste while filter is on
  • Advanced Filters
  • Custom AutoFilter

PRINTING WORKBOOKS

  • Working with Themes
  • Setting Up Print Area
  • Printing Selection
  • Branding with Backgrounds
  • Adding Print Titles
  • Fitting the print on to a specific defined size
  • Customizing Headers & Footers

IMPORT & EXPORT OF INFORMATION

  • Using Text To Columns

WHAT IF ANALYSIS

  • Goal Seek
  • Scenario Analysis
  • Data Tables

GROUPING & SUBTOTALS DATA VALIDATION

  • Number, Date & Time Validation
  • Text Validation
  • List Validation
  • Handling Invalid Inputs
  • Dynamic Dropdown List Creation using Data Validation

PROTECTING EXCEL

  • File Level Protection
  • Workbook Level Protection
  • Sheet & Cell Level Protection
  • Setting Permissions for Specific Tasks
  • Track changes

CONSOLIDATION

  • Consolidating data with identical layouts
  • Consolidating data with different layouts
  • Consolidating data with different Sheets

ADVANCED CONDITIONAL FORMATTING

  • Working with advanced conditional formatting rules incorporating formulas

Power Pivot

Objective: Power Query and Power Pivot complement each other. Power Query is the recommended experience for discovering, connecting to, and importing data. Power Pivot is great for modeling the data you’ve imported.

INTRODUCTION TO POWERPIVOT

  • Limitation of Excel Functions
  • Limitation of Excel PivotTable
  • Why PowerPivot?
  • PowerPivot Features - Overview

POWERPIVOT ENVIRONMENT

  • Opening PowerPivot Environment
  • Understanding External Data Section
  • Understanding Formatting
  • PowerPivot Views
  • Understanding Measures

TAKING DATA INTO POWERPIVOT

  • From Excel
  • From MS Access
  • From Ms-SQL
  • From Custom Query
  • From Text Files
  • From Other Sources

CALCULATED COLUMNS

  • Calculated Columns
  • Entering Formulas
  • Using AutoComplete Feature
  • Renaming Columns
  • Understanding Tables

ADVANCED CALCULATED COLUMNS

  • Understanding Relationship Concept
  • JOIN TABLES
  • LEFT JOIN TABLES
  • RIGHT JOIN TABLES
  • Using UNION QUERIES

DATA VISUALIZATION USING

  • Power View
  • Charts, Score Cards and Dashboards
  • Slicers
  • Map Visualizations
  • Data Binding and Formatting

Power Query

Objective: To learn different methods to Transform Data before uploading it to the SQL Database.

Overview

  • Introduction
  • Loading & Refresh
  • Combine data from multiple data sources

Data Transformation

  • Editing Queries Created with Power Query
  • Editing Column Headers in Power Query
  • Splitting Column Data with Power Query
  • Sorting Data
  • Multi-Level Sorting
  • Filtering Data
  • Aggregate data from a column
  • Insert a custom column into a table
  • Merge columns
  • Remove columns
  • Remove rows with errors
  • Promote a row to column headers
  • Transforming Text Values
  • Replacing Data
  • Using the Fill command
  • Pivot and Unpivot Column
  • Transpose Query Data
  • Pivot Column Command in Action
  • Unpivot Columns Command
  • Grouping Data
  • Create a Duplicate Query
  • Group and Summarize Data
  • Advanced Data Grouping
  • Working with multiple sources in Power Query
  • Multiple Excel Tables
  • Expand a column containing an associated table
  • Understanding Table Relationships
  • Merging Queries

Loading Power Query Data to Destinations

  • Familiarity with the Load & Refresh Settings
  • Loading it to Workbook
  • Loading it to Data Model

Modeling & MIS Reporting

Objective: It is an activity-based section with the goal of collaborating on all the topics you have learned so far to build dynamic MIS Reports. You'll learn to model different scenarios based on input, and assumptions.

MODELING & MIS REPORTING

  • Creating advanced Excel Models
  • Creating Simple Professionally Formatted Data Models to Simulate Simple Business Scenarios
  • Importing, Cleansing and Normalization Data
  • Aging Reports and Other Complex Date & Time Calculations
  • Reconciling Complex Datasets
  • Develop Advanced Data Models to Simulate Complex Business Scenarios for What IF Analysis
  • Reporting Using Relational Data
  • Consolidation and Reporting of Datasets of Different Structures
fullstack courses

Taught by Microsoft Certified Trainers

All our classes are live,
hands-on and with
real-trainers.

fullstack courses

Training Schedule

Note : The first 8 sessions will be followed by an assessment. Successful participants will advance to the final 4 sessions on Power Pivot and Power Query. Dates for Sessions 9–12 will be communicated upon qualification.

Limited Seats. Registration Closing Soon

Have Questions?

Tel:

+91 9176633248

Email:

support@excelgoodies.com

Projects & Assignments

What's included?

  • 24 hours of Instructor-led training.
  • Real-time Excel Assignments
  • 8 MIS Reporting Projects
  • 6 Power Query Projects
  • MIS Reporting & Business Modeling Specialist using MS-Excel
  • Data Transformation and MIS Reporting Expert Using MS-Excel and Power Query

Upcoming Batch

Starts On

Time

Course Fee

₹15,000

Plus GST for Company-Sponsored.

FAQs

Power Query is a built-in data transformation and automation tool in Microsoft Excel (available with Office 365 and Excel 2016+). It lets you connect to multiple data sources, clean messy raw data, reshape it, and load it into Excel or a data model — all without writing complex formulas or code. It is increasingly in demand among MIS professionals, finance teams, and data analysts in Chennai's corporate sector.

This is a comprehensive 24-hour program covering:

  • Quick Excel brush-up to bridge knowledge gaps
  • 60+ Advanced Excel formulas (Lookup, Math, Text, Date, Error Handling)
  • Pivot Tables, Dynamic Charts, and Excel Dashboards
  • MIS Reporting — 8 real-world reporting projects
  • Data Cleansing and Transformation techniques
  • Power Query — data import, transformation, merging queries, pivot/unpivot, and loading to data models
  • Power Pivot — data modeling, calculated columns, DAX measures, and cross-source reporting
  • Advanced MIS Modeling — What-If analysis, aging reports, and reconciliation

By the end, you'll be capable of building automated, self-refreshing reports from scratch.

This course is ideal for:

  • MIS executives and reporting analysts
  • Finance, HR, procurement, and operations professionals
  • Fresh graduates and MBA students entering analytics roles
  • Anyone currently doing manual reporting who wants to automate it

No prior coding experience is required. A basic familiarity with Excel is sufficient, and the course begins with a structured brush-up session to ensure everyone starts on equal footing.

The total course fee is INR 15,000. You can register and reserve your seat by paying INR 500 upfront, which gets adjusted against the total fee. This makes it accessible for working professionals and fresh graduates alike without a heavy financial commitment upfront.

Advanced Excel formulas (like VLOOKUP, SUMIFS, INDEX-MATCH) work well for structured, static data within a single sheet. Power Query goes further — it connects to multiple data sources (Excel files, databases, text files, web), automatically cleans and transforms large volumes of messy data, and refreshes results with a single click. For modern MIS and reporting roles, knowing both is a major professional advantage.

For simple lookups within a single table, VLOOKUP works fine. But when dealing with large datasets from multiple sources — like consolidating monthly sales files or merging HR and finance data — Power Query is far more powerful, efficient, and scalable. This course teaches you when to use each tool and how to combine them for maximum productivity in real-world reporting.

This course is structured as a 24-hour, 12-session program spread over weekdays, giving you enough time to absorb concepts and practise on real datasets. After the first 8 sessions, there is a short assessment; upon clearing it, you progress to the Power Query sessions. This structured progression ensures strong foundations before tackling advanced transformation topics.

Upon successfully clearing the post-training assessment, you will receive the "Data Transformation and MIS Reporting Expert Using Excel and Power Query" and "MIS Reporting & Business Modeling Specialist using MS-Excel" certificate from Excelgoodies, adding strong credibility to your resume and LinkedIn profile.

  • Windows 10 or later
  • Microsoft Excel 2016 or higher (Office 365 recommended for full Power Query features)
  • Minimum 4GB RAM (8GB recommended)
  • Stable internet connection with Zoom access

Standard office or home laptops are perfectly suitable — no special hardware setup is needed.

Yes. The course includes:

  • 8 MIS Reporting Projects covering financial, sales, HR, inventory, and operations reports
  • 6 Power Query Projects focused on data transformation and automation scenarios

These hands-on projects are built from real-world datasets, helping you build a portfolio and apply skills directly in your workplace from day one.

Power Query and Power Pivot are complementary tools. Power Query is used to connect to, clean, and transform raw data from various sources — it's the data preparation stage. Power Pivot is used to build data models, define relationships between tables, and perform advanced calculations using DAX. This course covers both, giving you an end-to-end data analysis workflow entirely within Excel.

Yes, and this is one of its most valuable features for MIS professionals. Power Query allows you to connect to multiple Excel workbooks, CSV files, databases, and even websites, then merge or append that data automatically. Once your query is set up, a single click refreshes everything — eliminating hours of manual copy-paste work each month. This exact scenario is covered in the course's Power Query projects.

More questions ?

Gain industry-recognized credentials.

Certificates

Shareable certificate

Add to your LinkedIn profile

Gain industry-recognized credentials.

Certificates

Build Real-World Solutions During the Course

Key Skills You'll Master

Advanced Excel Reporting & Dashboards

Data Cleaning & Transformation (Power Query)

Structured Data Modeling in Excel

Handling Large & Complex Datasets

Multi-Source Data Integration

Automated Data Preparation & Refresh

Optimized Excel Performance

About The Trainer

Mr. Sami

MCT, MCSA

15,000+

Students Trained

19+

Year of Experience

4.9

Reviews

Mr. Sami, Microsoft Certified Trainer, with his qualifications in Finance, HR & Information Technology brings in 19 years of Industry experience. He has successfully trained 15,000+ professionals by now, and the counting is still on.

He has undertaken assignments with the renowned IRS, The World Bank, Tata Chemicals, Buckman Laboratories, Standard Chartered, ING Barings and much more. His nature of going that Extra Mile has got him the startling popularity amongst the Excelgoodies prominent clients.

Classroom Gallery

Google Reviews

Practical application

Two layers of practice. Built right into Excel.

Layer 1 · Daily practice

150+ system-graded assignments — inside Excel itself

A native Excel add-in opens a question pane next to your worksheet. Real business data, structured tasks, instant grading. Watch how it works:

Live demo

30-second demo · Plays automatically · Silent

150+

curated assignments across all course modules

Enough practice to build genuine fluency — not just a few demo problems.

Instant

grading the moment you click Submit

No waiting overnight, no trainer in the loop. Your answer is judged then and there.

Structured topic-by-topic

Every concept maps to its own assignment set, with multiple difficulty levels — Sets 1 through 4 for Logical Functions alone.

Topic list showing modules: Fundamental, Logical, Adv Logical Sets 1-4, Lookup, Date, Text, IF Error
9 industry domains
you'll practice across
Finance Sales HR & Payroll Inventory Banking Manufacturing Retail Project Management Operations & MIS
Layer 2 · Capstone projects

4 end-to-end Power Query projects

Bring everything together. Connect, clean, transform, and load — the way real analysts do.

PROJECT 01 Sales

Multi-region sales consolidation

Combine 12 monthly files from 4 regional offices into one auto-refreshing dashboard. Replaces a full day of manual work each month.

Append queries Folder import Auto-refresh
PROJECT 02 Finance

Bank statement reconciliation

Match thousands of bank transactions against the ledger, flag mismatches, produce a clean reconciliation — repeatable in one click.

Merge queries Conditional logic Cleansing
PROJECT 03 HR

Attrition & headcount tracker

Pull data from HR exports, payroll, and attendance into one model. Calculate attrition rate, tenure, and cost-per-hire automatically.

Unpivot Date intelligence Power Pivot
PROJECT 04 Operations

Messy raw data → clean MIS report

Take a deliberately messy dataset — broken headers, merged cells, mixed formats — and transform it into reporting-ready data using only Power Query.

Header promotion Fill down Pivot/Unpivot

Corporate Training

bi_report_automation_mob

Avail additional 10% Corporate Benefit on the total course fee for 5+participant.

Get you team BI ready, today.

Ms.Jayasree

Business Associate

Prove you're human: Type the code shown.

=
Excelgoodies

Esteemed Clientele

Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele Esteemed Clientele

Thousands Trained. Here’s What They Say.

Total Reviews 2080
Average Rating
4.5
Excelgoodies Excelgoodies Excelgoodies Excelgoodies Excelgoodies
Why Excelgoodies image

Thousands Trained. Here’s What They Say.

APPLICATION DEADLINE

Registration Closes
on .

Prove you're human: Type the code shown.

=
Excelgoodies

Industry Insights

Industry Insights