What We Offer

This advance Excel course syllabus is designed for the advance excel use who desire to learn more advance skills. Learn the most advance formulas, function charts and types of financial analysis to be an Excel Power User.

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 Auto Correct
• Changing Excel's Default Options

Using basic to Advance Functions

• Using Functions - Sum, Average, Max, Min, Count, CountA, CountBlank, Large, Small
• Advance Function - Vlookup, Hlookup, Subsititue, Find, Replace, Search, Formula Intigration

Formatting and Proofing

• Currency Format
• Format Painter
• Formatting Dates
• Custom and Special Formats
• Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
• Advance Conditional formating

Mathematical Function

• Sumif, Sumifs, Countif, Counifs, Averageif, Averageifs, Nested If, IFERROR, AND, OR NOT

Protecting Excel

• File Level Protection
• Workbook, Worksheet Protection, Cell Protection, Range Protection

Text Functions

• Upper, Lower, Proper
• Left, Mid, Right
• Trim, len, Exact
• concatinate

Date and Time Functions

• Today, Now
• Day, Month, Year
• Date, Dateif, DateAdd
• EOMonth, Weekday

Advance Paste Special Techniques

• paste Formulas, Paste Formats
• Paste Validations
• Transpose Table
• Paste Comments
• Add, Subtract, Divide, Multiply Numbers in Whole Numberic Data.

Sorting and Filtering

• Filtering on Text, Numbers & Color
• Sorting Options
• Advance Filters on 10 to 15 different Criterias

Print Workbooks

• Setting Up Print Area
• Customizing Headers and Footers
• Designing the structure of the template
• Print Titles - Repeat Rows / Columns

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 - Dependancy 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 Function
• 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 Function Formulas
• Advance Us 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

• Concept of Dashboard
• Planning a Dashboard
• Adding Tables and Charts to Dashboard
• Adding Dynamic Contents to Dashboard

Recording Macro

• Using Recording Macro
• Create Data Entry Form using Recording Macro
• Creating Button and Assign Macro