Day 1:

Module 1: Advanced Excel Shortcuts 

• Extensive exercise on excel shortcuts
• Identify the different use of excel functions and shortcuts
• Mastering the shortcut window

Module 2: Data Presentation with charts 

• Extensive exercise on excel shortcuts
• Types of charts and their specific uses- Trend line and advanced charts.
• Modifying and formatting existing charts.

Module 3: Sorting And Filtering data

• Sort data as per value, color, icons
• Filter based on Font/Cell color
• Turning Auto Filter off
• Use subtotal features and work with database functions Understanding References
• Relative, Absolute & Mixed reference
• Referencing different workbooks & Merging
• Consolidating data, linking cells in different work books
• Utilizing Dynamic Named Ranges for writing formulas and functions

Module 4: Manipulating Dates

• Dates formats
• Extracting Day, Month or Year from a date value
• Extracting Day, Month or Year from a date value using TEXT Formula
• Extracting end month from a date value

Module 5: Advanced Excel Formulas  

• Logical Functions
• Ifs and Nested Ifs Statements
• AND, OR and NOT Functions
• Error handling using IFERROR, ISNA and ISERROR

Module 6: Text Formulas    

• Logical functions
• Ifs and nested ifs statements
• Text formulas
• Concatenate
• Find v/s search
• Left, right, mid
• Upper, proper, lower lookup formulas
• Vlookup & Hlookup
• Index & match
• Sumproduct function

Module 7: Data Validations and Protection      

• Specifying a valid range of values for a cell
• Specifying a list of valid values for a cell
• Creating Drop down list
• Managing Themes, creating and use of templates

Day 2:

Module 8: What-if Analysis

• Using the Scenario Manager
• Creating and editing scenario
• Creating a Scenario Report
• Using Goal Seek

Module 9: Data Tables

• The single-variable data table
• The two-variable data table
• Adding conditional formatting

Module 10:  Working with Outlines

• Applying an outline
• Automatic outlining
• Displaying and collapsing levels
• Modifying outline settings
• Creating subtotals

Module 11:  Analysing Data with Pivot Tables

• Creating a pivot table
• Defining the source range
• moving and deleting fields
• Showing and hiding fields
• Grouping and ungrouping fields
• Renaming fields
• Defining field properties
• Formatting a pivot table
• Refreshing a pivot table
• Drilling down

Module 12: Excel Macros

• About macros and VBA
• Macro security
• Recording macros
• Running a Macro
• Absolute and relative references
• Saving and distributing macros
• Assigning a macro to a button
• Editing macro code
• Automatic macros