Excel Macro Online for beginners by JP INSTITUTE OF EXCELLENCE

MIS & Analytics Training
Module 1 - Advance Excel:
This course covers:
Using advanced graphs and presentation techniques to maximise impact
How macros and VBA automate your spreadsheets and increase interactivity
How superpower functions can solve complex problems
Using PivotTables and Power Pivots to turn raw data into clear information that supports key decisions
ADVANCED FORMULAS
formulas like SUMIFS, SUMPRODUCT, INDEX, MATCH, LOOKUP formulas. how to use which formula
TABLES & FORMATTING
Advanced Excel users know how to structure & present their data so that it looks impressive.
CONDITIONAL FORMATTING
By using conditional formatting, you can tell Excel to highlight portions of your data that meet any given condition.
ADVANCED CHARTING
Advanced users of Excel know that by using charts, we can communicate effectively and present results in a stunning manner.
PIVOT TABLES & PIVOT REPORTING
Pivot tables & pivot reporting allows us to analyze massive amounts of data & answer questions with just a few clicks. Some of the advanced pivot table features are grouping, slicers, calculations & summary by different type of metrics.
VBA & MACROS
Excels own language VBA, allows us to give instructions to Excel to get things done & can write macros to automate their day to day work, thus saving countless hours of time & money.
USING EXCEL PRODUCTIVELY
This includes knowing important keyboard shortcuts, mouse shortcuts, work-arounds, Excel customizations & how to make everything looks slick.
DATA TABLES, SIMULATIONS & SOLVER
Excel has many powerful & advanced features packaged in to it. Data tables: help us model practical problems & analyze massive amount of data for a solution. Solver: helps us model practical problems & find a solution by iterating thru all possibilities. For example, finding cheapest way to ship goods from one location to another.Simulations: We can simulate real world data & situations in Excel using various random functions & statistical methods.
Trend analysis: We can use built in functions & charting features to understand trend & forecast future values from available data.
INTEGRATING EXCEL WITH OTHER TOOLS & OPTIMIZING EXCEL
Combine the power of Excel with flexibility of other applications like MS Access, Outlook or PowerPoint,

Module 2- DASHBOARD & VBA / Macros Programming
Dashboards are Often Called as Management Information System (MIS), Which Provides Information that Organizations Require to Manage Themselves Efficiently and Effectively.
o Complete Review of the VBA Language (Subs, Functions, Variables, Arrays, Loops, Logic...etc.)
o Excel VBA Power Programming For VBA Macros
o Working with Dynamic Ranges. Protecting Worksheets, Cells and Ranges. Working with Multiple Files. Opening & Saving Files
o How to Analyze Data On Multi Worksheets And Build Summary Sheets
o How to Access The Windows File And Folder System To Open And Close Workbooks
o How to Protect Your Code Against Errors
o How to Use Excel And VBA To Create Basic Dash Boards
o How to Create Your Own Custom Business Worksheet Functions In VBA
o How to Create Basic Report Generation Tools Using Excel VBA, Microsoft Word And PowerPoint
o How to Use The Excel Visual Basic Macro Recorder To Record Excel Tasks In VBA And Then Interpret The Code
o Overview of Using User forms To Create Business Wizards
o Working with User Forms & User Forms Events like List box, Combo box, Option Buttons, Check box, Text box, Labels, Command button, Toggle button.
o How to create dynamic dashboard on user form with different controls
o How to link various user form with each other to create a complete interface between user and system
o Connection between Excel VBA & other platforms
o Testing and Debugging Your Code
o Effective Error Handling
Module3 - MS - Access & SQL
Introduction to MS - Access, Create Query, Tables and relationships, Basic Queries Using SQL
MS- Access
o Basic
o Import & Export the Data from Various Sources & files
o All 9 Quires
o Left Join | Inner Join | Right Joins
SQL
o Basic
o DDL | DML | DCL | DQL
o Import | Export | Joins | Truncate
o Database keys