Advanced MS Excel
Excel has evolved from from addition of two cells to complex dashboards.
As the demand for data professionals are exponentially growing in every industry verticals, there is a need to keep up with innovations and advanced features of Excel. This program aims to cover tips, tricks, and techniques you need to maximize the power of Excel ( including latest version 2016 ) through the use of formulas, pivots, macros, VBA and even create complex dashboards. We are offering three programs, you may choose as per your need.
Target Audience:
Chartered accountants, Financial auditors, MBA, CS, Operations Managers,
Bankers, Business Analysts, HR

Intermediate Excel
Maths/Stats Basic Formulas
- Using Mathematical Functions
- The Basic SUM, COUNT, MIN, And MAX Preview
- AVERAGE, MODE, MEAN, And MEDIAN
- SUMIF For Selective Adding Up Preview
- COUNTIF For Selective Counting
- AVERAGEIF For The Mean Of Selected Cells
- Multiple Criteria Within SUMIF, COUNTIF, And AVERAGEIF
- Area And Volume Calculations
Conditions / Nesting
- IF Functionality
- IF Syntax And Uses
- Nesting The IF Statement
- Preview
- Use Of The AND Operator Within An IF
- Use Of The OR Operator Within An IF
- The NOT Operator Within AND And OR Statements
- Preview
- Display Cell Formulas In Another Cell
Lookups, Vlookup, Missing Data
- Performing Data Lookups
- Preview
- VLOOKUP In Live Action
- Using A Near Match In The Lookup
- Dealing With Missing Data In A Lookup
- Preview
- Managing The Lookup Table
- Lookups Nested Within Lookups
Financial Operations, Data/Time
- Further Mathematical Functions
- Working With Time In Excel
- Calculations Using Time
- Useful Time And Date Functions
- Rounding Decimal Places
- MOD And INT Functions And Uses
- Generate And Use A Random Number
- Loan And Investment Calculations
- Loan Calculation Elements And Functions
Text Handling, Arrays
- Functions For Manipulating Text
- Changing Case Functions
- Use Of CHAR Function For More Obscure Characters
- Formatting Numeric And Date Values Using TEXT
- Keeping The Values Created By String Manipulation
- Arrays
- Arrays And Creating A New Array Formula
- Array Formulas With IF Statements
- Conditional Evaluation With No IFs
- The Array-Only TRANSPOSE Function
Match, Index, Error Handling
- Useful Data Functions
- Using The MATCH Function
- How The INDEX Function Works
- Handling Out Of Range Index Requests
- The CHOOSE Lookup Function
- MATCH And INDEX Functions Working Together
- Some Other Useful Functions
- Introducing IS Functions
- Error Checking Using ISERR, ISERROR, And IFERROR
- OFFSET Function Syntax
- INDIRECT Function To Build Dynamic Formulas
- Dealing With INDIRECT Errors
- The CELL Function And Determining File Or Sheet Names
Advanced Excel
Sparklines, Outlining
- Sparklines
- Creating A Sparkline
- Altering The Design Of Sparklines
- Dealing With Empty Cells
- Comparing Sparklines Within A Sparkline Group
- Removing Sparklines From A Worksheet
- Outlining
- Create An Outline Automatically
- Adding An Outline Manually
- Editing And Removing Outlining
Scenarios, custom View
- Setting Up A Set Of Scenarios
- Displaying And Editing The Different Scenarios
- How To Work Out Which Scenario You Are Displaying
- Merging And Deleting Scenarios
- Producing A Summary Of Scenarios
- Custom Views
- Custom Views Explained
- Use Of Outlining To Help Setup Custom Views
- Editing And Deleting Custom Views
- Add Quick Access To Custom Views
Auditing, Troubleshooting
- Auditing And Troubleshooting Formulas
- What Are Tracer Arrows
- Adding And Removing Tracer Arrows
- Step-By-Step Formula Processing
- Using The Watch Window In Troubleshooting
PivotTables AtoZ, Data Slidcer
- What Is A PivotTable?
- The New Recommended PivotTable Route
- Creating Your Own PivotTables
- Changing The Formatting And Formulas In PivotTable Summaries
- Creating Multiple PivotTables On The Same Dataset
- Moving And Deleting PivotTables
- Making Use Of The Report Filter Options
- Sorting The PivotTable Columns
- Refreshing A PivotTable
- Drilling Down Behind The Pivot Numbers
- Multiple Fields In Row, Column, Or Data Sections
- Controlling Grand Totals And Subtotals
- Dealing With Empty Cells And Other Additional Options
- PivotTable Styles
PivotCharts
- Creating A PivotChart
- Changing The Fields Used In A PivotChart
- Formatting The PivotChart
- Changing The PivotChart Type
- Filtering A PivotChart
- Hiding The PivotChart Buttons
- Moving And Deleting PivotCharts
- Goal Seek And Solver
- What-If Analysis Using Goal Seek
- Activating The Solver Add-In
- Using Solver To Complete A What-If
- Adding Constraints To Solver
Excel with Macros and VBA
Basic Macros Programming
- Concept of VBA Programming Language
- Type of VBA Macros
- Recording Macros with Editions
- how can write Macros and Algorithm
- Workbooks,Sheets and Range Object with member
- File and Folder Handling with VBA
- Formatting,Style and Report Format Creation
- Report Format Project
Apply Conditions in VBA
- Formatting Tricks
- Data Type Declaration ( Variables)
- Simple IF Condition in VBA
- And,OR and XOR with if Condition
- Nested if or Complicate Condition with IF
- Select Case ans Switch Case
- Create Formulas for IF
- Live Project with VBA Macros for Report Automation
Loops with VBA
- For Loop
- Nested Loop
- For Each Loop
- Do While Loop
- Do Until Loop
- Show File - Folder List with Directory Function
- Loop with If Condition
- Live Project with Loop and Condition
Event Macros Programming
- Fundamental for Event Macros Programming
- WorkSheet and Range Event Macros Programming
- Workbooks Event Macros Programming
- Button and Object Macros Programming
- Add ActiveX Control in Sheets
- Live Project with Event Macros Programming
Excel VBA Macros Function(Formulas ) Creation
- Formulas / Function Creation with VBA Programming
- Advanced Formulas Method with VBA Programming
- String Function
- Number Function
- Date and Time Calculation using Data Time Formulas
- Information Formulas
- Lookup Method(VLookup,HLookup,Match,Index,Offset) with VBA
- Advanced VLookup and VLookup Listing with VBA
- File and Directory Function
- Data Summarization Formulas (AVERAGEIF,AVERAGEIFS,COUNTIF,COUNTIFS,LARGE,SMALL and more) with VBA
- Database and list management Formulas with VBA
- File, Folder Copy - Rename , Delete and Hide -unhide formulas
- Financial Formulas
- Live Project with VBA Function
Advanced Formulas/Function
- Advanced Array Formulas with VBA
- Complicated Large Formulas Creation with VBA
- Advanced Naming Method with Formulas
- Report Automation with Formulas
- Live Automation Project
- New Formulas Creation with VBA Programming
Charting and Dashboard Report Automation with VBA
- Chart Creation
- Chart Tricks
- use Button on chart
- Dynamic Chart with Offset Formulas
- Dashboard Example
- Live Project
Pivot Automation with VBA
- Create Pivot with VBA
- Links One more Data in VBA Macros
- Report Automation with VBA Macros
- SQL in Pivot with VBA Macros
- Dashboard Example with Pivot with VBA
- Live Project
UserForm in Excel
- Concept of Userform in VBA
- TextBox , Label, and Combo Box in VBA
- List Box Automation
- Filter Duplicate and Multi-Connection Listbox
- Multi Page, PDF and FLV , MediaPlayer and Other type of Emmbed Application
- Live Project
Picture / Image Automation with VBA Macros
- Show Picture in Userform
- Chart Tricks as image on Userform
- Show Google Image on Userform
- Dynamic Picture Automation
- Live Project with Picture Automation
Database Conectvity and Other Application with VBA
- E-Mail Automation with Outlook ( Send Mail Excel to Outlook )
- E-Mail Automation with Gmail/ Yahoo / AOL Mail/ Other POP 3 Mail
- PDF Mail Automation
- MS Word Handling - Create Letter accessing from Excel Data Sheet
- MS Access Database Connectivity
- Notepad and Web Data Connectivity
- Embed Other Application
- SQL or Oracle Connectivity
- SQL Query in MS Excel 2016
- File & Folder Directory Access
- Live Project For Final Certification
Automate with Excel
Dashboard with Pivot Table
- Data summarization with Pivot Table Charts
- Extranal Pivot
- Power Pivot
- SQL in Pivot Table
- Pivot Table whith 2 Data Sheet
- Dashboard with Slicer
- Dashboard with Data Table
Formulas for Dashboard
- Logical Formulas
- Data Summerization Formulas
- Array Formulas
- Dashboard Formulas Triks
Dashboard Report Automation
- Chart Creation
- Chart Triks
- use Button on chart
- Dynamic Chart with Offset Formulas
- Dashboard Example
- Live Project
VBA Macros Method for Dashboard
- Macros Creation
- Function Creation
- UserForm Methods
- Event Handling
- Format Handling with VBA
- File & Folder Handling with VBA for Dashboard
Formatting for Dashboard Automation
- Freming for Dashboard Formate
- Color Triks for Dashboard
- Image and Object
- Picture Linking For Dashboard
- Border and Outline
- Live Project
Dashboard Example
- Manufacturing Dashboard
- Human Resource Dashboard
- Financial Dashboard
- Performance Dashboard Customer Care Center
- Dashboard - Sales Performance
- Sales Analysis-region-product-wise
- Geographical Sales Report
- Project Status Report
- Team Performance Dashboard
- 5 Dashboard as per Your Requirement