2 Day Advanced Microsoft Excel Training

Outline :

This program is designed for users who already have good knowledge of Microsoft Excel and who would like to work with more advanced features of Microsoft Excel that help in improving their efficiency of working with worksheets, analyzing data, creating MIS reports and automating various tasks using Macros [Recording and Editing Macro]

Objective :

This Advanced Excel training program will empower the participants to be able to do the following:

• Performing complex calculations more efficiently, using various Excel functions.
• Organizing and analyzing large volumes of data.
• Creating MIS reports.
• Consolidating and managing data from multiple workbooks.
• Recording and Editing Macros

Objective :

This program will help participants to:

• The influences of each generation and common generational motivations
• The cause of conflict and the preferred learning environment of each group
• The needed change from the leadership of the past to the leadership of the future
• Apply best practices for working with millennial preferences to increase motivation, performance and retention.

Day One :

Module 1 Statistics and Database Function
❖ Statistical Function

• Large, Small
• COUNTIF, COUNTIFS
• SUMIF, SUMIFS
• AVERAGEIF, AVERAGEIFS
• MAXIFS, MINIFS
• Use of Large and Small function along with SUMIF AND SUMIFS Functions
• Use of Indirect Function with Statistical Functions

❖ Database Functions

• DSUM, DAVERAGE,
• DMAX, DMIN.

Module 2: Organizing and Managing Data in Worksheet

• Multi-level Sorting
• Custom Sorting
• Using Custom Filter
• Advance Filter for complex criteria
• Subtotal to create detail summary report
• Using Nested Subtotal

Module 3: Creating Effective Reports with Pivot Table

• Creating Pivot Table
• Report Filter pages
• Creating Pivot Report with % of Grand Total
• Creating Pivot Report with % of Row Total
• Creating Pivot Report with % of Column Total
• Creating Pivot Report with % of Parent Total
• Grouping data within a Pivot table – (Year wise, Month Wise, Slab Number wise)
• Use of Slicer and Timeline
• Uses of Slicer to connect multiple Pivot reports.
• Creating Custom Fields in Pivot Reports
• Custom Subtotals in Pivot Report
• Creating an Interactive Dashboard by using Slicer with Pivot Table

Module 4: Working with Charts
Creating Charts, Column, Bar, Line, 2-Axis Chart, Pie Chart, Pie chart within Pie Chart, Bar chart within Pie Chart, Bubbles Chart, Waterfall Chart, Inserting Titles & Data labels in chart, Formatting charts.

DAY 2 :

Module 5: Lookup and Reference function

• Use of VLOOKUP to search / lookup a value in table / sheet
• FERROR Function with VLOOKUP to replace #N/A error with a value.
• Match Function to get auto column index number in VLOOKUP
• VLOOKUP with true (1) parameter / To find approximate match value.
• Use of Multiple Table arrays with VLOOKUP
• Two-way lookup with INDEX and MATCH
• Get LOOKUP value from the left / any side of the table array.
• Retrieving value from data by Using XLOOKUP
• Creating Dependent drop-down list by Using Indirect Function
• Using Indirect Function with VLOOKUP function to use dynamic Table array

Module 6: More Functions

• Date Functions
  ❖ Edate ❖ Datedif ❖ Networkdays ❖ Networkdays.Intl ❖ Workday ❖ Workday.INT ❖ EOMONTH
• Logical Functions
  ❖ IF, OR, AND ❖ Nested IF ❖ Solving Complex Criteria by Combining If with OR / AND ❖ Using OR / AND in Nested if Expression

Module 7: Conditional Formatting using Formula and Function

• Row level formatting [Highlight Entire row using conditional formatting]
• Conditional formatting using formula (using and (), or ())
• Conditional formatting using VLOOKUP ()
• Conditional formatting to highlight based on date or name of days

Module 8 Data Analysis with What if?

• Goal Seek to find out how to get a desired result
• Data tables to see the effects of one or two variables on a formula
• Scenarios to consider many different variables

Module 9: Introduction to Macros

• Recording Macros
• Absolute and Relative Macro
• Running Macros
• Reviewing the code
• Familiarization with VBA
• Running code from VBE
• Modules and Procedures
• Components of code
• Use of Input and Message box

Speaker :

VIJAY, Trainer – SpringPeople
Vijay has 14 years of experience in Corporate Training with exclusive exposure in MS Office 2007-2010-2013-2016-2019, Microsoft Project 2010-2013-2016-2019 and Office 365 (End User). He has 18+ years experience in the IT industry. He possesses technical skills of OS Windows 8 / 8.1 / 10 – User Level and MS Powerpoint. He is a Microsoft Certified Professional. He has successfully delivered trainings at Biocon, Yes Bank, Vodafone Idea Limited, MG Motors, Vadodara, Tata Motors Ltd, Bharat Serum and Vaccines Ltd, L’Oréal India, Great Ship India, Bank of India, Bajaj Electricals, Ashok Leyland, Godrej Consumers Product Ltd, Godrej & Boyce Mfg. Co. Ltd. and many more corporate houses.

Contact Details :

Chetna Surti 
Email : chetna.surti@bombaychamber.com
Tel – 49100228, 98926 86418