Diploma in Advance Excel

Excel 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

• Currency Format

• Format Painter

• Formatting Dates

• Custom and Special Formats

• Formatting Cells with Number formats, Font formats, Alignment, Borders, etc

Basic conditional formatting

• 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

• EO Month, Weekday

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

• • Filtering on Text, Numbers & Colors

• • Sorting Options

• • Advanced Filters on 15-20 different criteria(s)

Printing Workbooks

• Setting Up Print Area

• Customizing Headers & Footers

• Designing the structure of a template

• Print Titles –Repeat Rows / Columns

Advance Excel with MIS REPORT

• What If Analysis

• Goal Seek

• Scenario Analysis

• Data Tables (PMT Function)

• Solver Tool

• Logical Functions

• If Function

• How to Fix Errors – if error

• Nested If

• Complex if and or functions

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

Mathematical Functions

• SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs, Nested IF, IFERROR Statement, AND, OR, NOT

• 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

Advanced Paste Special Techniques

• Paste Formulas, Paste Formats

• Paste Validations

• Transpose Tables

• Pivot Tables

• Creating Simple Pivot Tables

• Basic and Advanced Value Field Setting

• Classic Pivot table

• Choosing Field

• Filtering PivotTables

• Modifying PivotTable Data

• 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

VBA Macro

• 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

Message Box and Input box 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