Microsoft Excel - Advanced

Course details

Advanced Excel Course - Overview of the Basics of Excel
~ Customizing common options in Excel
~ Absolute and relative cells
~ Protecting and un-protecting worksheets and cells
 
Advanced Excel Course - Working with Functions
~ Writing conditional expressions (using IF)
~ Using logical functions (AND, OR, NOT)
~ Using lookup and reference functions (VLOOKUP, HLOOKUP, MATCH, INDEX)
~ VlookUP with Exact Match, Approximate Match
~ Nested VlookUP with Exact Match
~ VlookUP with Tables, Dynamic Ranges
~ Nested VlookUP with Exact Match
~ Using VLookUP to consolidate Data from Multiple Sheets
 
Advanced Excel Course - Data Validations
~ Specifying a valid range of values for a cell
~ Specifying a list of valid values for a cell
~ Specifying custom validations based on formula for a cell
 
Advanced Excel Course - Working with Templates
~ Designing the structure of a template
~ Using templates for standardization of worksheets
 
Advanced Excel Course - Sorting and Filtering Data
~ Sorting tables
~ Using multiple-level sorting
~ Using custom sorting
~ Filtering data for selected view (AutoFilter)
~ Using advanced filter options
 
Advanced Excel Course - Working with Reports
~ Creating subtotals
~ Multiple-level subtotals
~ Creating Pivot tables
~ Formatting and customizing Pivot tables
~ Using advanced options of Pivot tables
~ Pivot charts
~ Consolidating data from multiple sheets and files using Pivot tables
~ Using external data sources
~ Using data consolidation feature to consolidate data
~ Show Value As ( % of Row, % of Column, Running Total, Compare with Specific Field)
~ Viewing Subtotal under Pivot
~ Creating Slicers ( Version 2010 & Above)
 
Advanced Excel Course - More Functions
~ Date and time functions
~ Text functions
~ Database functions
~ Power Functions (CountIf, CountIFS, SumIF, SumIfS)
 
Advanced Excel Course - Formatting
~ Using auto formatting option for worksheets
~ Using conditional formatting option for rows, columns and cells 

 
Advanced Excel Course - Macros
~ Relative & Absolute Macros
~ Editing Macro's 

 
Advanced Excel Course - WhatIf Analysis
~ Goal Seek
~ Data Tables
~ Scenario Manager

 
Advanced Excel Course - Charts
~ Using Charts
~ Formatting Charts 
~ Using 3D Graphs 
~ Using Bar and Line Chart together 
~ Using Secondary Axis in Graphs 
~ Sharing Charts with PowerPoint / MS Word, Dynamically 
~ (Data Modified in Excel, Chart would automatically get updated)

 
Advanced Excel Course - New Features Of Excel
~ Sparklines, Inline Charts, data Charts
~ Overview of all the new features

 
Advanced Excel Course - Final Assignment
~ The Final Assignment would test contains questions to be solved at the end of the Course Updated on 21 July, 2020