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
Advanced Excel
Workbooks (File) & Worksheet Handling
- Excel Limitation
- Excel Shortcut use and benefits
- Excel Setting and Custom List Creation
- Excel Template and File Location System
- Advanced Paste Special
- Calculation with Paste Special
- Link Data range as Picture Link
- Lookup Picture in Excel Worksheet
Advanced Formatting for Reports and Dashboard
- Formatting Trikes
- Date & Time for Trunks
- Advanced Custom Formatting (Number Format using Code)
- Conditional Formatting
- Advanced Conditional Formatting using Formulas
- File, Sheets or Cells Protection Method
- Benefits of Data Table or Cells Style
Data Handle and Data Analysis
- Data Fill and Selection Method
- Advanced Sorting and Filter Trunks
- Data Summarization with Pivot Table
- Calculation with Pivot table
- External Data Pivot Table
- Relationship and Slicer in Pivot Table
- Power Pivot in Excel 2016
- Use SQL Query in Pivot Table
Page Setting and Form Creation
- Drow Form in Excel Sheet
- Hyperlink in Excel
- Text and Symbol
- Page Setup
- Manage View
- Sheets Option and Arrange
Calculation (more than 200 Formulas)
- Mathematical Calculation in Excel
- Logical Formulas (IF, and, Or, Xor, If Error Formulas)
- Nested If Formulas (Multi Condition in If, and, or)
- Text and Advanced Text Formulas
- Date and Time Calculation using Data Time Formulas
- Ref# Formulas and VLookup
- Lookup Method (VLookup, HLookup, Match, Index, Offset)
- Advanced VLookup and VLookup Listing
- Math & Trico. Formulas
- Data Summarization Formulas (AVERAGEIF, AVERAGEIFS, COUNTIF, COUNTIFS, LARGE, SMALL and more)
- Database and list management Formulas
- Information Formulas
- Financial Formulas
Advanced Formulas
- Advanced Array Formulas
- Complicated Large Formulas Creation
- Advanced Naming Method with Formulas
- Report Automation with Formulas
- Live Automation Project
- Use Formulas in Conditional Formatting
- New Formulas Creation with VBA Programming
Database and Other Application Connectivity
- 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
Dashboard Report Automation
- Chart Creation
- Chart Trikes
- use Button on chart
- Dynamic Chart with Offset Formulas
- Dashboard Example
- Live Project
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
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
What will you Learn!
- Generating Reports & Graphs with a Single Click
- Create A Dynamic Date Range Table
- Emailing Reports with A Single Click (PDF, Excel & Picture Formats)
- Saving Reports On Single Click (PDF, Excel & Picture Formats)
- Protecting Your Code, Hard Work & Intellectual Property
- Create Comprehensive & Powerful Advance Filters in VBA
- Advanced Drill Down & Drill Up Techniques to Embed a Table in A Table
- Auto-Filtering Just by Typing (Partial & Exact Matches, From & To Values)
- Adding, Editing, Updating & Deleting Custom Reports
- One Click Table Sorting (Ascending & Descending)
- Dynamic Conditional Formatting Based On Report Data & Position
- Getting A Unique Record List with Advanced Filters in VBA
- Create Dynamic One-Click Financial Reports (Profit & Loss, Sales & Balance)
- Generating Unique ID's using MAX Formula
- Clear Report Filters On Single Click
- Single Click to Alternate Between Reports & Graphs
- Error Handling & Prevention in VBA
- Accelerate Your VBA Programming with Unique Shortcuts
- Securing Your Application Against Loss, Corruption & Damage
- How to Restore a Corrupted or Damaged Excel Workbook?
- Clearing Advanced Filter Criteria Through VBA
- Setting & Updating Default Date Ranges for Reports
- Absolute & Relative Cell Formula Differentiation Defined
- Using Named Shapes & Groups to streamline code
- Calculating Income, Expense, COGS and Gross & Net Profits
- VBA Editor & Options Review
- Tested Techniques That Reduce Memory & Increase Speed
- Copying, Clearing & Updating Conditional Formatting in VBA
- 3 Proven Methods to Locate Records
- Refreshing Reports Automatically On Date Change
- Determining Last Used Row in A Table Via VBA
- Hiding Sheets (Standard & Very Well Hidden)
- Automatically Clearing & Resetting Field Placeholder On Tab in/Out
- Generating SUMIF & INDEX/MATCH Formulas in VBA
- Creating Dynamic Text for Both Shapes & Cells
- Hide & Show Shapes and Groups Using VBA
- Disabling Copy & Paste and Drag & Drop to Maintain Integrity
- Check / Un-check option box through cell selection
- Setting A Print Area Using VBA
