+91 72087 69880 info@landdedutech.com
Advanced Excel

Duration 10 hrs (10 Session)

WHAT IF ANALYSIS

1. Goal Seek
2. Scenario Analysis
3. Data Tables (PMT Function)
4. Solver Tool

LOGICAL FUNCTIONS

1. If Function
2. How to Fix Errors – iferror
3. Nested If
4. Complex if and or functions

DATA VALIDATION

1.Number, Date & Time Validation
2. Text and List Validation
3. Custom validations based on formula for a cell
4. Dynamic Dropdown List Creation using Data Validation – Dependency List

LOOKUP FUNCTIONS

1. Vlookup / HLookup
2. Index and Match
3. Creating Smooth User Interface Using Lookup
4. Nested VLookup
5. Reverse Lookup using Choose Function
6. Worksheet linking using Indirect
7. Vlookup with Helper Column

PIVOT TABLES

1. Creating Simple Pivot Tables
2. Basic and Advanced Value Field Setting
3. Classic Pivot table
4. Grouping based on numbers and Dates
5. Calculated Field & Calculated Items

Arrays Functions

1. What are the Array Formulas, Use of the Array Formulas?
2. Basic Examples of Arrays (Using ctrl+shift+enter).
3. Array with if, len and mid functions formulas.
4. Array with Lookup functions.
5. Advanced Use of formulas with Array.

CHARTS and slicers

1. Various Charts i.e. Bar Charts / Pie Charts / Line Charts
2. Using SLICERS, Filter data with Slicers
3. Manage Primary and Secondary Axis

POWER QUERY

POWER PIVOT

DASHBOARD

1. Planning a Dashboard
2. Adding Tables and Charts to Dashboard
3. Adding Dynamic Contents to Dashboard