COURSES

Batch starts on

th

UPCOMING BATCH -

Course on Management Reporting & Business Modeling using MS-Excel.

# Advanced Excel, MIS Reporting & Model Building

View Schedule

## Re-invented Excel Learning for Power Users.

### Taught by Microsoft Certified Trainers.

This is a 20-hour, activity-based program, designed to give you extensive exposure on mining, structuring and, modeling varied data to come up with the required analysis and MIS Reports using Microsoft Excel.

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

### Forecasting, Budgeting & Costing Models *

Budgeting and financial forecasting are tools that companies use to establish a plan for where management wants to take the business budgeting and whether it is heading in the right direction—financial forecasting. This section will guide you to build such models.

Budgeting Models
Forecasting Models
 * Please note that topics marked with an asterisk are advanced concepts beyond the regular Excel program and require an additional cost. Alternatively, our comprehensive Full Stack BI Reporting Course covers all these exciting topics.

### Course Content Overview

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

20 hours of Instructor-led training.

Real-time Excel Assignments

7 MIS Reporting Projects

30-Days post-training support (via Email)

MIS Reporting Specialist 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.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

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

\$ 499

## Advanced Excel & MIS Reporting Specialist

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?

• 20 hours of Instructor-led training.
• Real-time Excel Assignments
• 7 MIS Reporting Projects
• 30-Days post-training support (via Email)
• MIS Reporting Specialist 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.