Advanced Excel and MIS

Advanced Excel Training, MIS Course & Corporate Excel Training Institute in Indore

ADVANCED MS EXCEL & MIS TRAINING

DURATION : 1 MONTH (25 HOURS)

Module ONE
Introduction and Basic Overview of Microsoft Excel understanding the use and purpose of using Excel for Reporting Basic functionality of formulas and type of formulas, AutoSum functionality How to format the reports Professionally in organized way – Like border applying, alignment, font color/size. Auto Formats of the Reports, Working with Workbook and Worksheets Linking of Worksheets & Workbooks Formatting Cells, Formatting Cell Contents, Customized formatting of Cells Other Features (Paste Special and finding and replacing data etc.),Shortcut Keys
Module TWO
Types of Cell References (Absolute, Relative & Mixed) Prepare Budget Allocation Report, Joining Text using functions, Text Formulas (UPPER, LOWER, PROPER, MOD, MODE, RIGHT, LEFT, FIND, LEN) Calculations on Excel – VAT %age, Discount %age, Interest %age Inserting Comments, hiding and show comments, selecting and deleting or finding comments. More Useful formulas (SEARCH, CHOOSE, REPLACE, SUBSTITUTE), Logical formula (IF function and its use)
Module THREE
Conditional Formatting and its Use in different scenarios, Logical Complex Formulas (IF, OR, AND & IFERROR) Use of Nested IF formula with examples Other Text Formulas (TEXT, CLEAN, TRIM, T, COUNT, COUNTA, COUNTBLANK,) Quick Access Tool Bar – Data Form, Camera and Speak Cell. Defining Cell Range, Name Manager and Pasting Defined Name Purpose and tricks of using AutoFilter
Module FOUR
Formatting data as Table or insert fresh table for smarter data management, How to apply multiple filters in a single Worksheet, Freezing and Unfreezing Panes, Use of Advanced Filter where AutoFilter seems to be limited and not worth Enough, displaying unique records by using Advanced Filter, Removing duplicates and creating and unique list Data Validation and its use in different scenarios, Summarizing the data using (COUNTIF, COUNTIFS, SUMIF, SUMIFS) Open Websites, existing files etc. by Inserting Hyperlink. Understanding the Page layout of the workbook (Orientation, Print
Preview, Page Break etc)
Module FIVE
Statistical Functions: FREQUENCY, MODE, REPT, MIN, MAX, RANK, AVERAGEIF, AVERAGEIFS, Working with different kinds Of charts – understanding charts, and learns easier way of preparing complex, advanced and interactive charts to be used For presentation purpose, Inserting Sparkling charts within cells. Text to Column to fetch data and more Calculating Weighted Average
 Module SIX
Working with dates and date functions, calculate age as per the date of birth or any pass date, Lookup & Reference Formulas (CELL, LOOKUP, VLOOKUP, HLOOKUP, MATCH, OFFSET and INDEX) , Customized Protection of worksheet, Encrypting Document, Sharing Workbook and Track/Highlight Changes Formula Auditing (Trace Precedents/Dependents, Error Checking, Show/Evaluate Formula), Watch Window to be applied on Complex Spreadsheets
Module SEVEN
What if Analysis (Goal Seek, Scenario Manager and Data Table) Understanding & Preparing Dashboards. Use of INDIRECT Function in Data Validation Applying Subtotal to a Data Source, Export and Import Data from (Different Sources like Access, Web, Text, CSV etc.)
Module EIGHT
Calculation within PivotTable Month Wise / Quarter Wise Report Using Pivot Table Creating Range/Slab Wise Report Using Pivot Table Group and Ungroup Data Working with Slicers, Financial Formulas (STEDV, PMT, PV, DIC, DB, IRR, NPV) Activating the Developer Tab in Excel Ribbon Understanding the use of Form Controls, Understanding Macros, Recording a New Macro, See the recorded Syntax and Run the Recorded Macro
Module NINE
Introduction to Visual Basic Form Controls vs. ActiveX Controls Getting into the Code VBA Programming Variables, Data Types, Constants and Variables Private Subroutine – Worksheet Selection Change, Worksheet Activate. Object Oriented Programming concepts and Illustration Create Object, Assigning Object to a Variable, Object Properties, and Object Methods Worksheet Activate Object
Module TEN
Operators: Arithmetic Operators, Conditional Operators, Comparison Operators and Concentration Operators. IF Statement, Else and ELSE IF Statement, With and End With Statement, Looping Statements: For-Next, For-Each, Do-While, Do until, Do Loop Decision-Making