Users with a good understanding of Microsoft Excel 2007, 2010, 2013 or 2016 who want to expand on their capabilities in a short space of time. Delegates should have a good working knowledge and understanding of the concepts covered in the Introduction and Intermediate courses.

After completion of this program, the participants will be able to the following

• Create and use defined names in a workbook
• Work with logical function in excel
• Use a variety of data validation techniques
• Use a range of lookup and reference functions
• Create summaries in your spreadsheets using subtotals
• Understand and create simple pivot tables Analysis
• Construct and operate pivot tables using some of the more advanced Analysis
• Perform a variety of analytical tasks using PowerPivot Analysis
• Use the data consolidation feature to combine data from several workbooks into one
• Create and work with scenarios and the scenario manager Advanced Analysis
• Use solver to solve more complex and intricate problems
• Import data into excel and export data from excel
• Create and use a range of controls in a worksheet
• Create recorded macros in excel
• Import data into excel and export data from excel

• Online – live
• Combination of Lectures, Presentations, Case Discussions, Group Work/ Exercises
• Close interactions with faculty
• Chat with fellow learners
• Group work – Of-line
• Post training - reference e-reading and video references
• E – Certificate of participation

• INR 20,000/- per participant + 18% GST as applicable on the participation fee.
• 10% discount for AIMA Members, additional discount of Rs.1000 per participant for 2 participant or more from same institution.
• Registration on first-come basis, as the training is for limited number of participants

Module 1: Basics of Excel

• Introduction to Excel 2007
• Excel Structure
• Save file and their file extensions
• Excel Keyboard shortcuts
• Formatting rows and columns
• Applying number formats
• Paste Special
• Basic Filter and Advanced Filter
• Sorting

Module 2: Basics of referencing (absolute, relative and mixed)

Module 3: Excel functions

Basic functions

• Sum, Count, CountA, Average
• Operators (+,-,*,/)
• Max, Min

Logical functions

• IF, AND, OR Function
• Nested IF
• IF error

Date and Time functions (How date works in excel)

• Month, Year, Day, Date
• Today, Now
• Days360

Lookup Functions

• Vlookup, Hlookup
• Index, Match
• Offset, Choose
• Indirect

Information Functions

• Iserror, Istext, Isnumber
• Isodd, Iseven
• Cell
• Isblank

Conditional Functions

• Sumifs, Countifs, Averageifs

Text Functions

• Concatenate, Substitute
• Right, left, Mid
• Len
• Upper, lower, Proper
• Find, Search
• Trim, Trunc, Round

Formula Auditing

• Evaluate formula
• Trace precedents
• Trace dependents

Module 4: Working with named ranges

Module 5: Data validation

Module 6: Conditional formatting

Module 7: Protect Sheet

• Protect sheet
• Protect workbook
• Hide formulas

Module 8: Create hyperlinks

Module 9: Basics of charting and nuances

Module 10: Pivot Tables

• Understanding pivot tables
• Creating pivot tables
• Grouping /Ungrouping
• How to add calculated field and perform calculations within pivot table
• Sorting data
• Show value as % of row, % of column, % of total

Module 1: Introduction to VBA Macros

• About VBA Macros
• Record a Macro and Modify the Code
• Personal Macro Workbook

Module 2: The Visual Basic Editor

• Opening the VB Editor
• Project Explorer Window
• Inserting a Code Module
• Properties Window
• Toolbars in the VB Editor

Module 3: Code Structures, How to Enter Code

• Code Blocks
• Sub...End Sub Block
• Function...End Function Block
• Statements: Capturing and Assigning Values
• Making Decisions: If and Select Case
• Loops: For, For Each, and Do Loops
• Add comments to code

Module 4: Variables and Options

• Variable Types and Declarations
• Declaring Variables

Module 5: Coding Common Excel Objects

• Excel Object Model
• Workbook
• Worksheet
• Range

Module 6: Interactive Macros, Events

• Message Box
• Input Box
• Trigger Macro on Workbook Events
• Trigger Macro on Worksheet Events

Module 7: Applied VBA

• Creating Add-ins
• Exporting and Importing Code Modules
• Protecting Your VBA Project
• User Defined Functions