ADVANCED MS EXCEL & MIS TRAINING


MODULE ONE

Introduction and Basic Overview of Microsoft Excel understanding the use and purpose of using Excel for ReportingBasic functionality of formulas and type of formulas, AutoSum functionality How to format the reportsProfessionally in organized way – Like border applying, alignment, font colour/size. Auto Formats of the Reports,Working with Workbook and Worksheets Linking of Worksheets & Workbooks Formatting Cells, Formatting CellContents, 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, TextFormulas (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. MoreUseful 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 ofNested IF formula with examples Other Text Formulas (TEXT, CLEAN, TRIM, T, COUNT, COUNTA, COUNTBLANK,) QuickAccess Tool Bar – Data Form, Camera and Speak Cell. Defining Cell Range, Name Manager and Pasting Defined NamePurpose 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 singleWorksheet, Freezing and Unfreezing Panes, Use of Advanced Filter where AutoFilter seems to be limited and not worthEnough, displaying unique records by using Advanced Filter, Removing duplicates and creating and unique list DataValidation and its use in different scenarios, Summarizing the data using (COUNTIF, COUNTIFS, SUMIF, SUMIFS) OpenWebsites, existing files etc. by Inserting Hyperlink. Understanding the Page layout of the workbook (Orientation, PrintPreview, Page Break etc.)

MODULE FIVE

Statistical Functions: FREQUENCY, MODE, REPT, MIN, MAX, RANK, AVERAGEIF, AVERAGEIFS, Working with different kindsOf charts – understanding charts, and learns easier way of preparing complex, advanced and interactive charts to be usedFor presentation purpose, Inserting Sparkline charts within cells. Text to Column to fetch data and more CalculatingWeighted Average.

MODULE SIX

Working with dates and date functions, calculate age as per the date of birth or any pass date, Lookup & ReferenceFormulas (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 INDIRECTFunction in Data Validation Applying Subtotal to a Data Source. Export and Import Data from (Different Sources likeAccess, Web, Text, CSV etc.)

MODULE EIGHT

Calculation within PivotTable Month Wise / Quarter Wise Report Using Pivot Table Creating Range/Slab Wise ReportUsing 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 aNew Macro, See the recorded Syntax and Run the Recorded Macro

Importance of Advanced Excel Course

Advanced Excel Course has significant advantage to students as it helps them in learning, analyzing and comparing the information efficiently and effectively. During Advance Excel Training Many topics can be learned such as: Automated data can be built Products can be sort to perform text mining.It is mainly designed for students who want to gain skills necessary for audit and analyze worksheet, to use pivot tables, utilize data tools, create and manage macros, collaborate with others and much more. Edit, format, organize and undo applications can also be used in Excel applications. Advanced Excel Course provides highly valuable knowledge for any working manager, student, executive, CEO, technical professionals. It’s a must know for any business as it is easy to follow, give you the results you desire, give you an advanced method in a way you can quickly use and adapt it.

Advanced Excel Course features:

It gives an ease of using graphics and formulas;

Tables are used in the presentation of reports, sales, and schedules;

Inserting table functions;

The auto format of reports,

Customized formatting of cells with the absolute reference; Date and time function; Logical functions; Statistical functions and other functions;

Lookup and Reference functions; Text functions and data validation; Pivot table; V Lookup and H Lookup.

Some advanced techniques are- array formulae, tables, functions and super powers, superior range names.

Why you should Learn Advanced Excel?

As advance excel by Microsoft is used for personal as well as professional use for Reporting, analyzing data, graph presentation, data management, and inventory and much more. So people believe that training of advance excels to be costly but it’s not right, one can get it at nominal fee at True-commerce. Before doing the course, one needs to have knowledge of basic computer, MS Excel or MS Office

Advanced excel course provides information regarding technical areas Such as Statistical functions, mathematical, Data, time and year role, logical and looping functions, chart/graph techniques and so. True-commerce training institutes provide such courses to benefit data mining, sales marketing executive, entrepreneurs working professional and non-professionals. There is a huge demand for advanced excel executive in the market.

Advanced Excel Training

At True-commerce we focus on most qualitative advanced excel training. Our corporate trainer delivers the best training to the participants so that they can excel in their respective profession.

Topics Covered under Advanced Excel Course


  •  What is  MS Excel  
  •   How to Use of Shortcut keys.
  •  What are Formatting/ Conditional Formatting?
  •  What is Formulas?
  •  What is Logical & Text Functions?
  •  What is Filter and Data Validation
  •  How to use Pivot Tables for Data Analysis
  •  How to Protect Worksheet.
  •  What is Name Range function?
  •  How to draw Charts.  
  •  Trends and Scenarios using charts.
  •  What is Data Tool?
  •  How to record Macro.  
  •  Introduction to Dashboards.
  •  Advanced Charting Techniques using.
  •  Thermometer, Doughnut, Pareto, Panel, Step Chart.
  •  Designing Sample Dashboard using Form Controls.
  •  Tips and Tricks to enhance dashboard designing.