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
Get in touch with us
close slider