New in Excel 2013 / 2016
• New Charts – Tree map & Waterfall
• Sunburst, Box and whisker Charts
• Combo Charts – Secondary Axis
• Adding Slicers Tool in Pivot & Tables
• Using Power Map and Power View
• Forecast Sheet
• Sparklines -Line, Column & Win/ Loss
• Using 3-D Map
• New Controls in Pivot Table – Field, Items and Sets
• Various Time Lines in Pivot Table
• Auto complete a data range and list
• Quick Analysis Tool
• Smart Lookup and manage Store
Advance Excel
What If Analysis
• Goal Seek
• Scenario Analysis
• Data Tables (PMT Function)
• Solver Tool
Data Validation
• Number, Date & Time Validation
• Text and List Validation
• Custom validations based on formula for a cell
• Dynamic Dropdown List Creation using Data Validation – Dependency List
Lookup Functions
• Vlookup / HLookup
• Index and Match
• Creating Smooth User Interface Using Lookup
• Nested VLookup
• Reverse Lookup using Choose Function
• Worksheet linking using Indirect
• Vlookup with Helper Column
Pivot Tables
• Creating Simple Pivot Tables
• Basic and Advanced Value Field Setting
• Classic Pivot table
• Choosing Field
• Filtering PivotTables
• Modifying PivotTable Data
• Grouping based on numbers and Dates
• Calculated Field & Calculated Items
• Arrays Functions
• What are the Array Formulas, Use of the Array Formulas?
• Basic Examples of Arrays (Using ctrl+shift+enter).
• Array with if, len and mid functions formulas.
• Array with Lookup functions.
• Advanced Use of formulas with Array.
Charts and slicers
• Various Charts i.e. Bar Charts / Pie Charts / Line Charts
• Using SLICERS, Filter data with Slicers
• Manage Primary and Secondary Axis
VBA Macro
Introduction to VBA
• What Is VBA?
• What Can You Do with VBA?
• Recording a Macro
• Procedure and functions in VBA
Variables in VBA
• What is Variables?
• Using Non-Declared Variables
• Variable Data Types
• Using Const variables
Message Box and Input box Functions
• Customizing Msgboxes and Inputbox
• Reading Cell Values into Messages
• Various Button Groups in VBA
If and select statements
• Simple If Statements
• The Elseif Statements
• Defining select case statements
Looping in VBA
• Introduction to Loops and its Types
• The Basic Do and For Loop
• Exiting from a Loop
• Advanced Loop Examples