Duration : 2 Months

Advanced Excel

This advanced Excel course syllabus is designed for the beginner to intermediate Excel user who desires to learn more advanced skills. Learn the most advanced formulas, functions, like financial, logical etc. charts and types of financial analysis to be an Excel power user. To make the management information system.

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[

Mathematical Functions

SumIf, SumIfs, CountIf, CountIfs, AverageIf, AverageIfs,

Nested IF, IFERROR Statement, AND, OR, NOT

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, Date Add, datedif

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

Spark lines -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

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

Dynamic Dropdown List Creation using Data Validation – Dependency List

Lookup Functions

Lookup Family (Vlookup / HLookup / Xlookup etc.)

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

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 Msg boxes 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

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