COURSES

Batch starts on

th

UPCOMING BATCH -

Course on Data Transformation, Analysis and Reporting.

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

View Schedule

## Supercharging Business Users with Next-Level of Excel Reporting.

### Taught by Microsoft Certified Trainers.

This is a 25-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.

### What will I learn?

Master the Excel essentials of the real world and accelerate your career.

### Quick Excel Brush-up (Gap Filing)

This section aims to bring all the candidates to the same level before moving to the most intensive, advanced and complex topics. It aims to equip you with a strong foundational knowledge of Excel to organize, analyze and work with data.

### 60+ Advanced Excel Formulas

This section will cover 60+ essential, productive time-saving functions and formula-building techniques through suitable examples, required to solve a variety of data analysis problems. You'll also learn how to debug a formula, audit them, and simplify the complex functions.

Mathematical Functions

1. Sum

2. Sumif

3. Sumifs

4. Average

5. Averageif

6. Averageifs

7. Max

8. Min

9. Sumproduct

10. Count

11. CountA

12. Countblank

13. Subtotal

Lookup Functions

1. Vlookup

2. Dynamic Vlookup

3. Hlookup

4. Dynamic Hlookups

5. Match

6. Index

7. Indirect

8. Offset

Text Functions

1. Istext

2. Code

3. Char

4. Exact

5. Len

6. Trim

7. Upper

8. Lower

9. Proper

10. Left

11. Right

12. Mid

13. Substitue

14. Replace

15. Find

16. Search

Date & Time Functions

1. Date

2. Datevalue

3. Day

4. Days360

5. EDate

6. Emonth

7. Month

8. Today

9. Weekday

10. Year

11. Now

12. Hour

13. Minute

14. Second

15. Time

16. Timevalue

Rounding Numbers

1. Ceiling

2. Even

3. Floor

4. INT

5. Mround

6. Odd

7. Round

8. Roundup

9. Rounddown

10. Trunc

Error Handling Functions

1. Isna

2. Iserr

3. Iserror

### Pivots, Charts & Dashboards

This section is designed for Excel users to crunch numbers substantially, create reports dynamically using PivotTable, and, present professional information through Dashboards in Microsoft Excel that are beautiful, precise, and clear.

Creating Pivot Table
Pivot table options Using slizers Charts & Pivot Charts
Excel Dashboard

### MIS Reporting & Dashboarding

Getting started with Power BI is simple. Then, once you want to leverage its full potential, you need a different set of skills. Learn how to develop these skills, unlock more insights through better data analysis and visualization with this section.

### Cleansing & Transforming Data

This is the most important step for all the reporting requirements. You'll learn to prepare raw data for analysis by removing bad data, organizing the raw data, and filling in the null values. Data cleaning in data mining has utmost value when working with big data.

Common data errors

### Data Transformation with Power Query

Whether you're consolidating multiple data sources, handling diverse data formats, or crafting dynamic queries, this versatile tool ensures efficiency and precision in your data preparation, spanning from data cleansing to advanced transformations.

What is Power Query?

### Data Analysis with Power Pivot

Explore the most powerful tool to build data models, forecast estimates, perform what-if-analysis scenario etc. If you are yet to have the Power BI license, you would still be able to create powerful & blended reports and share it internally using Power Pivot.

What is Power pivot?

### Course Content Overview

A snapshot of what you'll be learning in 2 weeks.

25 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

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 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

• 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 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 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
• 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
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
Creating Measures
• Creating Simple DAX Measures
• Explicit Measure Vs Implicit Measure
• Referencing Measures in Other Measures
• Formatting Measures
Manage Data Relationships Working with Multiple Tables Disconnected Tables Creating custom calendars using ADVANCED FILTER() 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
Creating Advanced Dashboards with PowerPivot
Introduction to Microsoft Power Query for Excel Import data from external data sources Import with Standard Connectors
• Connect to a Text or CSV file
• Connect to a Web page
• Connect to an Excel Table/Range
Import Data from a File
• Connect to an Excel workbook
• Connect to a Text or CSV file
• Connect to an XML file
• Connect to a JSON file
Import Data from a Database
• Connect to a SQL Server database
• Connect to an Access database
• Connect to a MySQL database
Import Data from Online Services
• Connect to Salesforce Objects
• Connect to a Sharepoint Online List
• Connect to Facebook
Import Data from Other Sources
• Connect to an Excel Table or Range
• Connect to a Web page
Shape data from Multiple Data Sources
• Shape or transform a query
• Refresh a query
• Combine data from multiple data sources
• Filter a table
• Sort a table
• Group rows in a table
• Expand a column containing an associated table
• Aggregate data from a column
• Insert a custom column into a table
• Combine multiple queries
• Merge columns
• Remove columns
• Remove rows with errors
• Promote a row to column headers
• Split a column of text
• Insert a query to the worksheet

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

Course Fee

\$ 399

## Data Transformation and Reporting Expert

Eligibility: On clearing post-training assessment

Sample Certificate: View here

1. Computer Requirements

• Operating System: Windows operating system
• RAM: Minimum 4GB (8GB or higher recommended)
• Processor: Dual-core processor or higher
• Internet Connection: Reliable internet connection for online sessions and downloads
2. Software Requirements

Microsoft Excel: Version 2010 or later should be installed on your computer.

3. Secondary Monitor (optional, but recommended)

Having a secondary monitor, will greatly enhance your learning experience by allowing you to keep up with the trainer's pace and work with multiple Excel workbooks simultaneously.

4. Webcam

A functional webcam is required for active participation in online sessions. Please ensure that your webcam is working properly and positioned appropriately for clear visibility during collaborative sessions and discussions.

Training Schedule

Session Date Time (ET)

Course Fee

\$399

### What is included?

• 25 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 Sample Certificate

## Connect with Us

Mr. Perrie Smith

Tel: +1 650 491 3131

Connect us on

### Mr. Sami

MCT, MCP, MEE, MOS

30,000+

Students Trained

18+

Year of Experience

4.9

Reviews

Mr. Sami is an exceptionally accomplished and certified Microsoft Trainer, possessing extensive expertise in the fields of Finance, HR, and Information Technology. With an impressive 14-year tenure in the industry, he has successfully trained and empowered over 23,000 professionals, and the number continues to grow.

Throughout his illustrious career, Mr. Sami has collaborated with esteemed organizations such as the IRS, The World Bank, Mercedez-Benz, Comcast, Standard Chartered, and ING Barings, to name just a few. His unwavering commitment to going the extra mile has earned him a stellar reputation among Excelgoodies' prominent clients.

With his wealth of knowledge and dedication to delivering exceptional training experiences, Mr. Sami consistently exceeds the expectations of his clients and leaves a lasting impact on the professionals he trains.

## Learner stories around the world

Total Reviews

4583

Overall growth till now

Average Rating

4.9

Average rating this year

### Industry Insights

Excelgoodies Consulting, inc.

575 7th Avenue, 5th Floor

New York City, NY 10018

Tel: +1 650 491 3131

inquiry@excelgoodies.com

## 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.