Advanced Excel Training
Learn the most in-demand skill in industry.
Duration: 30 Working Days | Certified Training
About Advanced Excel Training
The Advanced Excel is a 30 working days training program covering Basic and Advanced modules of
MS-Excel. The objective of this training is to make students proficient with Excel by practicing
everyday industry usage of MS-Excel.
During the course of this training, students will work on various projects. Students will be
presented with interesting real-life scenarios and have to use the Excel tools, functions and data
visualization techniques learned to solve problems, make inferences and present data in a
meaningful and effective manner using Excel.
Table of Contents
Getting Started
• Introduction to Excel
• Orientation / Tour
• Interface & Terminology
• Excel worksheets
• Excel Ribbon
• Backstage View
• Quick Access Toolbar
• Keyboard shortcuts
Understanding and Using Basic Functions
Using Functions – Sum, Average, Max, Min, Count, Counta
Absolute, Mixed, and Relative Referencing
Text Functions
Upper, Lower, Proper
Left, Mid, Right
Trim, Len, Exact
Concatenate
Find, Substitute
Arithmetic Functions
SumIf, SumIfs, SumProduct, CountIf, CountIfs AverageIf, AverageIfs
Use of Wildcards in SumIf, SumIfs, SumProduct, CountIf, CountIfs AverageIf, AverageIfs
Proofing and Formatting
Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
Basic conditional formatting
Protecting Excel- Excel Security
File Level Protection
Workbook, Worksheet Protection
Printing Workbooks
Setting Up Print Area
Customizing Headers & Footers
Designing the structure of a template
Print Titles –Repeat Rows / Columns
Advance Paste Special Techniques
Paste Formulas, Paste Formats
Transpose Tables
Paste Validations
Time and Date Functions
Today, Now
Date, Datedif, , WorkDays
Day, Month, Year
Month, Weekday
IN Excel 2013 / 2016 & 365
New Charts – Tree map & Waterfall
Combo Charts – Secondary Axis
Sunburst, Box, and whisker Charts
Using Power Map and Power View
Adding Slicers Tool in Pivot & Tables
Sparklines -Line, Column & Win/ Loss
Forecast Sheet
Smart Lookup and manage Store
New Controls in Pivot Table – Field, Items, and Sets
Using 3-D Map
Auto complete a data range and list
Various Time Lines in Pivot Table
Quick Analysis Tool
Filtering and Sorting
Filtering on Text, Numbers & Colors
Sorting Options
Advanced Filters on 15-20 different criteria(s)
Printing Workbooks
Setting Up Print Area
Print Titles –Repeat Rows / Columns
Designing the structure of a template
Customizing Headers & Footers
What-If Analysis
Goal Seek
Data Tables (PMT Function)
Solver Tool
Scenario Analysis
Data Validation
Number, Date & Time Validation
Dynamic Dropdown List Creation using Data Validation – Dependency List
Custom validations based on a formula for a cell
Text and List Validation
Logical Analysis
If Function
Complex if and or functions
Nested If
How to Fix Errors – iferror
Lookup Functions
Vlookup / HLookup
Vlookup with Helper Columns
Index and Match
Reverse Lookup using Choose Function
Nested VLookup and HLookup
Worksheet linking using Indirect
Arrays Functions
What are the Array Formulas, Use of the Array Formulas?
Array with if, len, and mid functions formulas.
Basic Examples of Arrays (Using ctrl+shift+enter).
Advanced Use of formulas with Array.
Array with Lookup functions.
Pivot Tables
Creating Simple Pivot Tables
Classic Pivot table
Basic and Advanced Value Field Setting
Calculated Field & Calculated Items
Grouping based on numbers and Dates
Excel Dashboard
Planning a Dashboard
Adding Dynamic Contents to Dashboard
Adding Tables and Charts to Dashboard
Slicers and Charts
Using SLICERS, Filter data with Slicers
Various Charts i.e. Bar Charts / Pie Charts / Line Charts
Manage Primary and Secondary Axis
