Microsoft Excel (Corporate) Training

M&HR Solutions Private Limited
In association with Nirathanka, NGO
Presents

Online Microsoft Excel (Corporate)
Training Programme

Basic Excel 7-7/5 hours of Training

Starting Excel: Different methods
Overview of Workbooks and Worksheets, Navigating Worksheets, Renaming sheet Tabs
Number of Columns and Rows, R1C1 referencing style
Knowing the Name box, Ribbon and status bar, customizing the quick access tool bar and ribbon
Freeze panes, wrap text, CTRL ENTER technique
Basic cell references, operations & using the Function Wizard (Function Arguments screen)
Selecting Ranges (Basic shortcuts of Selection)
Basic Functions: SUM, COUNT, COUNTA, AVERAGE, MAX, MIN, VLOOKUP, COUNTIF, SUMIF, TODAY
Basic Text Functions: PROPER, UPPER, LOWER, LEFT, RIGHT, MID, LEN, TRIM, CONCATENATE
Using the ‘equal to’ operator to check for a condition, Basic IF function
Simple VLOOKUP of exact match nature to fetch only one column of data, Properties of VLOOKUP
Creating simple drop-down lists using Data Validation
Basic Conditional Formatting to apply color, Top-Bottom Rules
Introduction to Sorting and Filtering
Creating a Simple Pivot Table and a Chart

Intermediate Excel: 7 to 15 hours of Training (1 day or 2 days)

Name box and its uses, properties associated with Named Ranges, using Name Manager
Creating Custom Lists. For e.g.: type Ambarish and his list pops up
Referencing: Absolute and Partial(using the dollar symbol with F4 effectively)
Using the OPTIONS Tab while Finding and Replacing Data
Statistical functions: SUMIFS, COUNTIFS, AVERAGEIFS, LARGE, SMALL and others
Other important functions: MROUND, FLOOR, CEILNG,
Database functions: DCOUNT, DCOUNTA, DSUM, DMAX, DMIN, DAVERAGE
Logical functions: IF, IF with OR, IF with AND, IF with NOT, IF with OR with AND combination
NESTED IF scenarios, IFS, SWITCH
VLOOKUP with Named Range v/s Regular VLOOKUP with manual selection
VLOOKUP with Exact match (False or 0) v/s VLOOKUP with Range (True or 1)
DATE functions (Using DATEDIF and other functions)
Applying drop down lists using Validation, exploring other options in the Validation window
Multi-Level, Custom Sorting &Text Filtering along with an overview of Advanced Filter
Consolidation
Conditional Formatting: Data Bars, Icon Sets, Color Scales
Pivot Tables, Grouping and ungrouping, Multiple sheets from a Pivot in one go, Dynamic Pivot
Working with Charts: Column, Bar, Pie
Recording a Macro and Properties associated with a Macro

Advanced Excel: 7 to 15 hours Training

Quick review on Various Referencing Techniques, Named Ranges and Custom Lists
GO-TO window (Various options within the GO-TO SPECIAL window)
CTRL ENTER method and CTRL SHIT ENTER (Array Method)
Nested IF with OR AND combination, NESTED IF with DATE VALUE, Multiple scenarios
VLOOKUP with MATCH with Partial Referencing and Drop-Down Validation
When to use VLOOKUP With TRUE over Nested IF
INDEX MATCH combination
CHOOSE function
INDEX with MATCH with Partial Referencing and Drop-Down Validation
Data Validation: Dependent and independent drop-down lists, custom functions, input & error
Multi-level sorting, Custom sorting, Advance Filtering with AND OR BETWEEN NOT scenarios
Subtotal (Single level and multi-level), Removing Subtotals
New rules within Conditional Formatting, Customizing functions within New Rules of formatting
Pivot Table: Advanced Features of Pivot. Using Slicers and Timelines in Pivot,
Creating multiple Pivot Tables and linking them with a slicer
Examples of PowerPivot
Example of Power Query
Working with Charts: Combination Charts
Recording a Macro and assigning short cuts and/or button to it, editing the recorded Macro
Writing a VBA code to generate a user defined function

TRAINER

Ambarish Tarte
Corporate Trainer for Microsoft Office suite/Office 365 user apps.

Ambarish Tarte is a Corporate Trainer for Microsoft Office suite/Office 365 user apps. He conducts corporate training workshops for various corporate giants/multinational companies across the globe. He operate as a Corporate Training Consultant catering to M.S Office training requirements of various organizations.

Professional Background:
Started his career with the B.P.O industry in July 2004, moved up the hierarchy from a call handling executive to a Supervisor and then a Manager. Good people management skills and effective training/coaching abilities enabled his gain experience into Operations, Quality as well as Training. Eventually moved into Corporate Training in July 2011 due to prior experience of conducting behavioral training & Process training within the organization. Took up Microsoft Office as the mainstream training module as it is the most commonly &extensively used tool by corporate users.

Experience: