PROMILA'S INSTITUTE OF TECHNOLOGY AND EDUCATION

₹3,000 ₹5,000

Course Includes

Duration2 Months
Enrolled1
Lectures60
Videos0
Notes0
CertificateYes

What you'll learn

Advanced Excel courses are designed for intermediate users aiming to master complex data analysis, automation, and visualization. Key topics include pivot tables, VLOOKUP/INDEX-MATCH, What-If Analysis, VBA macros, and dynamic dashboards

Course Syllabus

Core Modules in Advanced Excel Syllabus

1. Advanced Formulas and Functions

Lookup Functions: VLOOKUP, HLOOKUP, XLOOKUP, INDEX & MATCH.
Logical & Conditional Functions: Nested IF, AND, OR, IFERROR, SUMIFS, COUNTIFS, AVERAGEIFS.
Text & Date Functions: LEFT, RIGHT, MID, CONCATENATE, TEXT, EOMONTH, NETWORKDAYS.
Array Formulas: Introduction to Array formulas (ctrl+shift+enter).

2. Data Analysis and Visualization

PivotTables: Creation, PivotTable options, grouping, calculated fields, and items.
Charts: Advanced graphing (Bar, Pie, Line, Combo), and Slicers for interactive reporting.
What-If Analysis: Goal Seek, Scenario Manager, and Data Tables.
Data Validation: Custom validation, creating dropdown lists.

3. Data Cleaning and Transformation (Power Query)

Connecting Data: Importing data from external sources (Web, CSV, SQL).
Data Transformation: Appending, combining, merging data, filling null values, and cleaning text.
Power Query Editor: Utilizing conditional columns, pivoting/unpivoting data.

4. Automation and Data Security

Macros: Recording, running, and editing simple Macros.
VBA Introduction: Basic VBA programming for automating tasks.
Data Protection: Protecting sheets, cells, workbooks, and securing formulas.