+91 72087 69880 info@landdedutech.com
Advanced Excel & MIS
Advanced_excel_training_by_Talent_Magnifier

Course Enquiry

What you'll learn

⦁ Good understanding of Excel at MIS level
⦁ Expertise in Text Function
⦁ Expertise in Logical Function
⦁ Expertise in Math Function
⦁ Expertise in Lookup and Reference Function
⦁ Expertise in Date and Time Function
⦁ Mastery in Pivot Table and Chart Preparation
⦁ Mastery in ‘What if Analysis’ tools
⦁ Print Option in Excel
⦁ Data Validation, Filter and Conditional Formatting
⦁ Mastery in Data organising Tools in Excel
⦁ Mastery in Data creation and Data Manipulation in Excel
⦁ Managing data protection and data sharing in Excel
⦁ Work with Macro Recording

Pre-Requisite

Basic Computer Knowledge

Description

Microsoft Excel is a spreadsheet application developed by Microsoft Inc for Microsoft Windows and MAC OS X. Its use is to do advanced calculation, graphing tools, pivot tables, and a macro programming language referred to as Visual Basic for Applications. The course curriculum is one of the most comprehensive and most advanced.

INTRODUCTION

⦁ An overview of the screen, navigation and basic spreadsheet concepts
⦁ Various selection techniques
⦁ Shortcut Keys

CUSTOMIZING EXCEL

⦁ Customizing the Ribbon
⦁ Using and Customizing AutoCorrect
⦁ Changing Excel’s Default Options

USING BASIC FUNCTIONS

⦁ Using Functions – Sum, Average, Max,Min, Count, Counta
⦁ Absolute, Mixed and Relative Referencing

FORMATTING AND PROOFING

⦁ Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
⦁ Basic conditional formatting

MATHEMATICAL FUNCTIONS

⦁ SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs

PROTECTING EXCEL

⦁ File Level Protection
⦁ Workbook, Worksheet Protection

TEXT FUNCTIONS

⦁ Upper, Lower, Proper
⦁ Left, Mid, Right
⦁ Trim, Len, Exact
⦁ Concatenate
⦁ Find, Substitute

DATE AND TIME FUNCTIONS

⦁ Today, Now
⦁ Day, Month, Year
⦁ Date, Date if, DateAdd
⦁ EOMonth, Weekday

ADVANCED PASTE SPECIAL TECHNIQUES

⦁ Paste Formulas, Paste Formats
⦁ Paste Validations
⦁ Transpose Tables

New in Excel 2013 / 2016 & 365

⦁ New Charts – Tree map & Waterfall
⦁ Sunburst, Box and whisker Charts
⦁ Combo Charts – Secondary Axis
⦁ Adding Slicers Tool in Pivot & Tables
⦁ Using Power Map and Power View
⦁ Forecast Sheet
⦁ Sparklines -Line, Column & Win/ Loss
⦁ Using 3-D Map
⦁ New Controls in Pivot Table – Field, Items and Sets
⦁ Various Time Lines in Pivot Table
⦁ Auto complete a data range and list
⦁ Quick Analysis Tool
⦁ Smart Lookup and manage Store

Sorting and FILTERING

⦁ Paste Formulas, Paste Formats
⦁ Paste Validations
⦁ Transpose Tables

PRINTING WORKBOOKS

⦁ Setting Up Print Area
⦁ Customizing Headers & Footers
⦁ Designing the structure of a template
⦁ Print Titles –Repeat Rows / Columns

WHAT IF ANALYSIS

⦁ Goal Seek
⦁ Scenario Analysis
⦁ Data Tables (PMT Function)
⦁ Solver Tool

LOGICAL FUNCTIONS

⦁ Goal Seek
⦁ Scenario Analysis
⦁ Data Tables (PMT Function)
⦁ Solver Tool

DATA VALIDATION

⦁ Number, Date & Time Validation
⦁ Text and List Validation
⦁ Custom validations based on formula for a cell
⦁ Dynamic Dropdown List Creation using Data Validation – Dependency List

LOOKUP FUNCTIONS

⦁ Vlookup / HLookup
⦁ Index and Match
⦁ Creating Smooth User Interface Using Lookup
⦁ Nested VLookup
⦁ Reverse Lookup using Choose Function
⦁ Worksheet linking using Indirect
⦁ Vlookup with Helper Column

PIVOT TABLES

⦁ Creating Simple Pivot Tables
⦁ Basic and Advanced Value Field Setting
⦁ Classic Pivot table
⦁ Grouping based on numbers and Dates
⦁ Calculated Field & Calculated Items

Arrays Functions

⦁ What are the Array Formulas, Use of the Array Formulas?
⦁ Basic Examples of Arrays (Using ctrl+shift+enter).
⦁ Array with if, len and mid functions formulas.
⦁ Array with Lookup functions.
⦁ Advanced Use of formulas with Array.

CHARTS and slicers

⦁ Various Charts i.e. Bar Charts / Pie Charts / Line Charts
⦁ Using SLICERS, Filter data with Slicers
⦁ Manage Primary and Secondary Axis
 

EXCEL DASHBOARD

⦁ Planning a Dashboard
⦁ Adding Tables and Charts to Dashboard
⦁ Adding Dynamic Contents to Dashboard

Introduction to VBA

⦁ What Is VBA?
⦁ What Can You Do with VBA?
⦁ Recording a Macro
⦁ Procedure and functions in VBA

Variables in VBA

⦁ What is Variables?
⦁ Using Non-Declared Variables
⦁ Variable Data Types
⦁ Using Const variables

MessageBox and INPUTBOX FUNCTIONS

⦁ Customizing Msgboxes and Inputbox
⦁ Reading Cell Values into Messages
⦁ Various Button Groups in VBA

If and select statements

⦁ Simple If Statements
⦁ The Elseif Statements
⦁ Defining select case statements

Looping in VBA

⦁ Introduction to Loops and its Types
⦁ The Basic Do and For Loop
⦁ Exiting from a Loop
⦁ Advanced Loop Examples

Mail Functions – VBA

⦁ Using Outlook Namespace  
⦁ Send automated mail
⦁ Outlook Configurations, MAPI

Worksheet / Workbook Operations

⦁ Merge Worksheets using Macro
⦁ Merge multiple excel files into one sheet
⦁ Split worksheets using VBA filters
⦁ Worksheet copiers