ms excel

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

excel_vn

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
Please register for additional information
Internship
Get in touch with us
close slider