Advanced Excel Online Beginner level by Nihas Learning Academy

Advance Excel Training Syllabus
1. EXCEL INTRODUCTION
➢ An overview of the screen, navigation and basic spreadsheet concepts
➢ Various selection techniques
➢ Shortcut Keys
2. CUSTOMIZING EXCEL
➢ Customizing the Ribbon
➢ Using and Customizing AutoCorrect
➢ Changing Excel’s Default Options
3. USING BASIC FUNCTIONS
➢ Using Functions – Sum, Average, Max,Min, Count, Counta
➢ Absolute, Mixed and Relative Referencing
4. FORMATTING AND PROOFING
➢ Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
➢ Basic conditional formatting
5. MATHEMATICAL FUNCTIONS
➢ SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs
6. PROTECTING EXCEL
➢ File Level Protection
➢ Workbook, Worksheet Protection
7. TEXT FUNCTIONS
➢ Upper, Lower, Proper
➢ Left, Mid, Right
➢ Trim, Len, Exact
➢ Concatenate
➢ Find, Substitute
8. DATE AND TIME FUNCTIONS
➢ Today, Now
➢ Day, Month, Year
➢ Date, Date if, Date Add
➢ Month, Weekday
9. ADVANCED PASTE SPECIAL TECHNIQUES
➢ Paste Formulas, Paste Formats
➢ Paste Validations
➢ Transpose Tables
10. New in Excel 2013 / 2016 & 365
➢ 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
11. Sorting and FILTERING
➢ Filtering on Text, Numbers & Colors
➢ Sorting Options
12. PRINTING WORKBOOKS
➢ Setting Up Print Area
➢ Customizing Headers & Footers
➢ Designing the structure of a template
➢ Print Titles –Repeat Rows / Columns
Advance Excel
13. WHAT IF ANALYSIS
➢ Goal Seek
➢ Scenario Analysis
➢ Data Tables (PMT Function)
➢ Solver Tool
14. LOGICAL FUNCTIONS
➢ If Function
➢ How to Fix Errors – if error
➢ Nested If
➢ Complex if and or functions
15. 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
16. LOOKUP FUNCTIONS
➢ VLOOKUP / Hookup
➢ Index and Match
➢ Creating Smooth User Interface Using Lookup
➢ Nested VLOOKUP
➢ Reverse Lookup using Choose Function
➢ Worksheet linking using Indirect
➢ VLOOKUP with Helper Column
17. PIVOT TABLES
➢ Creating Simple Pivot Tables
➢ Basic and Advanced Value Field Setting
➢ Classic Pivot table
➢ Grouping based on numbers and Dates
➢ Calculated Field & Calculated Items
18. 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.
19. CHARTS and slicers
➢ Various Charts i.e. Bar Charts / Pie Charts / Line Charts
➢ Using SLICERS, Filter data with Slicers
➢ Manage Primary and Secondary Axis