Course Name:	Certificate in Advance Excel
Course Short Name:	CAE
Course Code::	ARJ0110
Course Type:	Certificate
Duration:	3Month
Eligibility:	10th Pass
Total Subjects:	1

Advance  Excel course is for all students because in this course Excel has been taught from basic to advanced.

In this course you will learn all these things

Such as :-

1. Advanced Formulas and Functions

 

This section involves moving beyond simple arithmetic to complex nested and array formulas.

Lookup Functions: Mastering advanced alternatives to VLOOKUP/HLOOKUP, such as INDEX and MATCH (or the newer XLOOKUP), and using INDIRECT and OFFSET for dynamic referencing.

Logical Functions: Utilizing complex nesting of IF, AND, OR, and implementing the IFERROR function for error handling.

Conditional/Statistical Functions: Advanced use of SUMIFS, COUNTIFS, and AVERAGEIFS with multiple criteria.

Text, Date, and Time Functions: Functions like CONCATENATE, TRIM, LEN, EOMONTH, and NETWORKDAYS.

Array Formulas: Understanding and implementing array formulas, often entered using $\text{Ctrl}+\text{Shift}+\text{Enter}$.

 

2. Data Tools and Validation

 

Techniques for managing, cleaning, and validating large datasets.

Data Validation: Creating dynamic dependent drop-down lists and using custom formulas for validation rules.

What-If Analysis: Utilizing features like Goal Seek, Scenario Manager, and Data Tables for forecasting and modeling.

Data Cleaning: Using Text to Columns, Remove Duplicates, and advanced filtering techniques.

Data Consolidation: Combining data from multiple worksheets or workbooks.

 

3. Data Analysis and Visualization

 

Methods for summarizing and presenting data effectively.

PivotTables and PivotCharts: Creating, modifying, and analyzing data using PivotTables, including calculated fields/items, grouping, and using Slicers and Timelines for interactive filtering.

Advanced Charting: Creating specialized charts like Combo Charts (with Secondary Axes), Waterfall, and Gantt charts, and using Sparklines.

Conditional Formatting: Applying advanced rules and formulas to highlight patterns and trends in data.

Dashboards: Principles and techniques for designing and building interactive analytical dashboards.

Power Tools: Introduction to Power Query (Get & Transform Data) for importing and cleaning data and Power Pivot for creating data models.

 

4. Automation with VBA Macros

 

Learning the basics of programming within Excel to automate repetitive tasks.

Macro Recording and Editing: Recording simple macros and editing the underlying VBA (Visual Basic for Applications) code.

VBA Fundamentals: Understanding the VBA Editor, variables, data types, and basic concepts like MsgBox and InputBox.

Control Structures: Implementing simple If/Else statements and Loops (Do/For Loop) for decision-making and repetition in macros.