+91 9176633248

Courses

Batch starts on

Feb 10th

Course on Data Transformation, Analysis and Reporting.

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

30-hrs Course | 8 Excel MIS Reports | 6 Power Query Projects |

30-hrs Course | 8 Excel MIS Reports | 6 Power Query Projects View Schedule

Program Overview

Total Course Duration

30-hours, 12 Sessions

Live Online / Classroom (Weekdays)

Training Schedule

Saturday, 10 Feb

Course Fee

INR 15,000

Enroll Now

Certification

Data Transformation and Reporting Expert
Using Excel and Power Query

Both Online & Classroom is available.*

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

No recorded sessions.

Course Content Overview

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.

Duration – 2.5 Hours (Rapid Session)

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

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.

Duration – 5 Hours

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

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.

Duration – 2.5 Hours

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

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.

Duration – 2.5 Hours

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

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

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

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.

Duration – 7.5 Hours

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

Data Transformation and Reporting Expert

Eligibility:On clearing post-training assessment.

Sample Certificate: View here

Training Schedule

Session Date Time
Session 1 Sat, 10 Feb 5:30 PM- 8:00 PM
Session 2 Sat, 17 Feb 5:30 PM- 8:00 PM
Session 3 Sat, 24 Feb 5:30 PM- 8:00 PM
Session 4 Sat, 02 Mar 5:30 PM- 8:00 PM
Session 5 Sun, 10 Mar 4:00 PM - 6:00 PM
Session 6 Sun, 17 Mar 4:00 PM-6:00 PM
Session 7 Sun, 24 Mar 4:00 PM - 6:00 PM
Session 8 Sun, 31 Mar 4:00 PM-6:00 PM
Session 9 Sun, 07 Apr 4:00 PM - 6:00 PM
Session 10 Sun, 21 Apr 4:00 PM-6:00 PM
Session 11 Sun, 28 Apr 4:00 PM - 6:00 PM
Session 12 Sun, 05 May 4:00 PM-6:00 PM

Course Fee

INR 15,000

CORPORATE
Training 5 or
more people?

Avail additional 10% Corporate Benefit*
on the total course fee for 5+ participants.
Get you team BI ready, today.

What is included?

30 hours of Instructor-led training.

Real-time Excel Assignments

8 MIS Reporting Projects

6 Power Query Projects

Data Transformation and MIS Reporting Expert Certificate

View Certificate

About the Trainer

Mr. Sami, MCT, MCSA Data Enthusiast & a Technophile

Mr. Sami, Microsoft Certified Trainer, with his qualifications in Finance, HR & Information Technology brings in 14 years of Industry experience. He has successfully trained 9500+ 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

APPLICATION DEADLINE

Application Deadline

Registration Closes on
Wednesday, 07 Feb.

Registration Closes on Wed, 07 Feb.

Google Reviews

Contact Us

Excelgoodies Software Private Limited

No.56, 1 & 2 Floor, North Boag Road,

T-Nagar, Chennai - 17

Tel: +91 9176633248

inquiry@excelgoodies.com

Industry Insights

Excelgoodies

Power Query

Power Query: For Powerful Financial Reporting

Excelgoodies

Power Query

Case Study: Transforming Raw Sales Data using Power Query

Excelgoodies

Power Query

10 Must-Know Features of Power Query That Excel Users Shouldn't Miss

Recommended Course (For Advanced Reporting Users Only)

Full Stack BI R
Reporting & Automation Course.

72 Sessions | 9-Specialist Certifications

You will learn to use the right and hybrid technology for end-to-end advanced power reporting.

Tools: Power BI, Power Pivot, VBA, Excel,
M-Programming, MS-SQL, SSIS, and many more.

Power BI

Micorsoft T-SQL

MS-SQL Integrated Services

Python

Microsoft Excel VBA

MS-Excel

Power Query