Advance Excel
Excel Introduction
• An overview of the screen, navigation and basic spreadsheet concepts • Various selection techniques • Shortcut Keys Customizing Excel • Customizing the Ribbon • Using and Customizing AutoCorrect • Changing Excel’s Default Options Using Basic Functions • Using Functions – Sum, Average, Max,Min, Count, Counta • Absolute, Mixed and Relative Referencing
Formatting and Proofing
• Currency Format • Format Painter • Formatting Dates • Custom and Special Formats • Formatting Cells with Number formats, Font formats, Alignment, Borders, etc • Basic conditional formatting
Mathematical Functions
• SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs, Nested IF, IFERROR Statement, AND, OR, NOT Protecting Excel • File Level Protection • Workbook, Worksheet Protection Text Functions • Upper, Lower, Proper • Left, Mid, Right • Trim, Len, Exact • Concatenate • Find, Substitute
Date and Time Functions
• Today, Now • Day, Month, Year • Date, Date if, DateAdd • EOMonth, Weekday Advanced Paste Special Techniques • Paste Formulas, Paste Formats • Paste Validations • Transpose Tables 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
Sorting and Filtering
• Filtering on Text, Numbers & Colors • Sorting Options • Advanced Filters on 15-20 different criteria(s) Printing Workbooks • Setting Up Print Area • Customizing Headers & Footers • Designing the structure of a template • Print Titles –Repeat Rows / Columns
What If Analysis
• Goal Seek • Scenario Analysis • Data Tables (PMT Function) • Solver Tool Logical Functions • If Function • How to Fix Errors – if error • Nested If • Complex if and or functions
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
Excel Dashboard
• Planning a Dashboard• Adding Tables and Charts to Dashboard • Adding Dynamic Contents to Dashboard
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
Mail Functions – VBA
• Using Outlook Namespace • Send automated mail • Outlook Configurations, MAPI • Worksheet / Workbook Operations
• Merge Worksheets using Macro • Merge multiple excel files into one sheet • Split worksheets using VBA filters • Worksheet copiers