Course Detail

Course Description

Every analyst spends more time in Excel than they admit. Based on years of experience, we have compiled the most important and detailed Best Advanced Excel Course in Pune.

This course will give you a deep understanding of the advanced formulas and functions that transform Excel from a basic spreadsheet program into a dynamic and powerful analytics tool. While most Excel courses focus on simply what each formula does, we teach through hands-on, contextual examples designed to showcase why these formulas are awesome and how they can be applied in a number of ways.

Syllabus of this course:

• Introduction & Basic Brush-up: Benchmarking features of MS-Excel, MS-Excel Basic brush-up, Terminology & Concepts
• Cells, Range, Name Range:What is CELL and types of Cell references?, Usage of different type of cell reference, What is Range and usage of Range, Concept and usage of "Name Range"
• Data Handling & Risk Handling: Structured Data, Data Validation (Basic and Advance), Conditional Formatting (Basic and Advance), Handling Duplicate data with creating own rules, Subtotal as function and Subtotal as "Data Grouping", Data Protection
• Formulas & Functions: Difference between Formula and Function, What is “Nested” functions and how to write them, Understanding MS-Excel Function architecture and syntax
• MS-Excel Functions: Logical Functions: IF, AND, OR. Lookup & Reference Functions: VLOOKUP (Single and Two Dimensional), LOOKUP, INDEX, INDIRECT, MATCH, CHOOSE. Text Functions: EXACT, FIND, LEFT, RIGHT, LEN, MID. Math Functions: SUM, COUNT, SUMIF, COUNTIF, SUMIFS, COUNTIFS, SUBTOTAL, SUMPRODUCT. Statistical Functions: TREND, COREL, FREQUENCY. Date Functions: DATE, NETWORKDAYS, NOW
• Array Functions (Introduction): Introduction to array function, How to write array function
• Analytical Tool: Pivot Table & Slicer, Calculated fields, Pivot chart
• What IF Analysis Tool: Scenarios for data presentation, Goal Seek, Data Table for probabilities
• Working with Templates: Worksheet Templates, Charts Templates
• Charts: Overview of creating chart, Dynamic chart
• Working with Developer Object: Inserting Developer Tab, What is Developer Objects & their usage, Using Check Box and overview of other objects i.e. Radio button, Spin button
• Working with Comments: Add/Edit/Delete comments, Picture comments
• Macros: Introduction to Macros, Recording / Using Recorded Macros, Overview of VBE

Duration of Advanced Excel Course: 20 hrs.

Prerequisites for this course:

Knowledge of MS Excel with basic functions