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