ADVANCE EXCEL CORPORATE TRAINING

These training is to develop practical working knowledge about accounts and Excel Training, to support in job & work

Course Features

1. An overview of the screen, navigation and basic spreadsheet concepts
2. Various selection techniques
3. Shortcut Keys

CUSTOMIZING EXCEL

1. Customizing the Ribbon
2. Using and Customizing AutoCorrect
3. Changing Excel’s Default Options

USING BASIC FUNCTIONS

1. Using Functions - Sum, Average, Max,Min, Count, Counta
2. Absolute, Mixed and Relative Referencing

FORMATTING AND PROOFING

1. Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
2. Basic conditional formatting

MATHEMATICAL FUNCTIONS

1. SumIf, SumIfs CountIf, CountIfs AverageIf, AverageIfs

PROTECTING EXCEL

1. File Level Protection
2. Workbook, Worksheet Protection

TEXT FUNCTIONS

1. Upper, Lower, Proper
2. Left, Mid, Right
3. Trim, Len, Exact
4. Concatenate
5. Find, Substitute

DATE AND TIME FUNCTIONS

1. Today, Now
2. Day, Month, Year
3. Date, Date if, Date Add
4. EOMonth, Weekday

ADVANCED PASTE SPECIAL TECHNIQUES

1. Paste Formulas, Paste Formats
2. Paste Validations
3. Transpose Tables

New in Excel 2013 / 2016 & 365

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

Sorting and FILTERING

1. Filtering on Text, Numbers & Colors
2. Sorting Options
3. Advanced Filters on 15-20 different criteria(s)

PRINTING WORKBOOKS

1. Setting Up Print Area
2. Customizing Headers & Footers
3. Designing the structure of a template
4. Print Titles –Repeat Rows / Columns

Advance Excel

WHAT IF ANALYSIS

1. Goal Seek
2. Scenario Analysis
3. Data Tables (PMT Function)
4. Solver Tool

LOGICAL FUNCTIONS

1. If Function
2. How to Fix Errors – I ferror
3. Nested If
4. Complex if and or functions

DATA VALIDATION

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

LOOKUP FUNCTIONS

1. Vlookup / HLookup
2. Index and Match
3. Creating Smooth User Interface Using Lookup
4. Nested VLookup
5. Reverse Lookup using Choose Function
6. Worksheet linking using Indirect
7. Vlookup with Helper Column

PIVOT TABLES

1. Creating Simple Pivot Tables
2. Basic and Advanced Value Field Setting
3. Classic Pivot table
4. Grouping based on numbers and Dates
5. Calculated Field & Calculated Items

Arrays Functions

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

CHARTS and slicers

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

EXCEL DASHBOARD

1. Planning a Dashboard
2. Adding Tables and Charts to Dashboard
3. Adding Dynamic Contents to Dashboard

VBA Macro

Introduction to VBA

1. What Is VBA?
2. What Can You Do with VBA?
3. Recording a Macro
4. Procedure and functions in VBA

Variables in VBA

1. What is Variables?
2. Using Non-Declared Variables
3. Variable Data Types
4. Using Const variables

Message Box and INPUTBOX FUNCTIONS

1. Customizing Msg boxes and Inputbox
2. Reading Cell Values into Messages
3. Various Button Groups in VBA

if and select statements

1. Simple If Statements
2. The Elseif Statements
3. Defining select case statements

Looping in VBA

1. Introduction to Loops and its Types
2. The Basic Do and For Loop
3. Exiting from a Loop
4. Advanced Loop Examples

Mail Functions – VBA

1. Using Outlook Namespace
2. Send automated mail
3. Outlook Configurations, MAPI

Worksheet / Workbook Operations

1. Merge Worksheets using Macro
2. Merge multiple excel files into one sheet
3. Split worksheets using VBA filters
4. Worksheet copiers

Batches Timming

Morning

M1-06.30am to 07.30am
M2-07.30am to 08.30am
M1-08.30am to 09.30am
M1-09.30am to 10.30am

Afternoon

A1-10.30pm to 12.30pm
A2-12.30pm to 01.30pm
A3-01.30pm to 03.30pm
A4-03.30pm to 04.30pm

Evening

E1-04.30pm to 05.30pm
E1-05.30pm to 06.30pm
E1-06.30pm to 06.30pm
E1-07.30pm to 07.30pm

Duration :

5 Months 1.5 hrs. daily

ELIGIBILITY :

12th+ Having basic knowledge of Computer

CERTIFICATION :

From A.I.A.T. Pvt. Ltd, the Associate with Tally Academy

Fees :