Advance MS Excel for Managers

Course Instructor: Ajay Aggarwal

₹5000.00 ₹500.00

To enroll in this course, please contact the Admin

Contact Admin

Course Overview

Schedule of Classes

Course Curriculum

13 Subjects

Introduction to Modern Excel

Overview of Microsoft Excel (Microsoft 365 / Excel 2024–2025 interface)

Understanding the Ribbon, Quick Access Toolbar, and Navigation Pane

Exploring the new Home Screen and Cloud Sync Features

Introduction to Excel Copilot (AI Assistant)

Using Natural Language Queries for Formula Suggestions and Data Insights

Mathematical and Statistical Functions

Core Mathematical Functions: SUM, AVERAGE, MIN, MAX, PRODUCT, SUMPRODUCT

Random Data Generation: RAND, RANDBETWEEN

Advanced Aggregations: SUBTOTAL, AGGREGATE

Descriptive Statistics: MEDIAN, MODE.SNGL, STDEV, VAR.S

AI Tip: Using Copilot to Automatically Summarize and Analyze Numeric Data

Text and Data Cleaning Functions

Text Manipulation: TRIM, TEXTJOIN, CONCAT, SUBSTITUTE, UPPER, LOWER, PROPER

String Extraction: LEFT, RIGHT, MID, LEN, SEARCH, FIND

New AI-Powered Text Functions: TEXTSPLIT, TEXTBEFORE, TEXTAFTER for flexible parsing

New AI-Powered Text Functions:AI Copilot for cleaning messy datasets automatically

Data Cleaning Tools: Remove Duplicates, Flash Fill, Power Query Clean-up

Logical and Conditional Formulas

Logical Functions: IF, AND, OR, NOT, IFS, SWITCH

Conditional Aggregations: COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF

Nested Logic for Data Validation and Flagging Errors

AI Use Case: Predictive Highlighting of Logical Errors

Date and Time (Temporal) Functions

Basic Date-Time Functions: TODAY, NOW, DAY, MONTH, YEAR, WEEKDAY

Calculations: DATEDIF, NETWORKDAYS, EDATE, EOMONTH, WORKDAY

AI-Automated Date Cleaning and Forecasting Insights

Timeline and Calendar Visualization with Dynamic Formulas

Lookup and Reference Formulas

Classic Lookup Functions: VLOOKUP, HLOOKUP, INDEX, MATCH

Modern Replacements: XLOOKUP, XMATCH

Dynamic Array Functions: FILTER, SORT, UNIQUE, SEQUENCE

AI Copilot Use: Auto-generating Lookup Formulas Based on Context

Real-World Applications in Reporting and Dashboarding

Data Tools and Transformation

Data Sorting, Filtering, and Advanced Filters

Data Validation Rules and Drop-Down Lists

Importing Data from Text, CSV, Web, and Power Query

Text-to-Columns and Merge Queries

Introduction to Power Query Editor for Data Transformation

AI Integration: Smart Data Cleansing and Auto-Categorization

Formatting, Visualization, and Tables

Data Formatting: Fonts, Alignment, Number Formats, Conditional Formatting

Table Design: Creating Structured Tables, Header Styles, Banded Rows, and Total Rows

Dynamic Formatting with Custom Rules

AI Copilot: Auto-formatting Reports and Suggesting Chart Designs

Pivot Tables and Pivot Charts

Creating and Customizing Pivot Tables

Grouping, Filtering, and Calculated Fields

Using Slicers and Timelines

Creating Pivot Charts for Interactive Data Visualization

AI Insights: Copilot-Generated Summaries and Trends from Pivot Data

Data Visualization and Charting

Charts Overview: Column, Bar, Line, Pie, Doughnut, Area, and Combo Charts

Advanced Visuals: Waterfall, Funnel, Sparklines, Histogram, and Maps

Dynamic Charting with Named Ranges and Data Tables

Using AI for Chart Recommendations and Trend Forecasting

Excel Shortcuts, Automation, and Customization

Essential Keyboard Shortcuts for Productivity

Creating Custom Ribbons and Quick Access Commands

Introduction to Excel Macros and Basic VBA Automation

Using Power Automate for Workflow Automation

AI Shortcut: Command Prompts via Copilot for Instant Actions

Analytics, Forecasting, and Business Intelligence

What-If Analysis: Goal Seek, Scenario Manager, and Data Tables

Regression and Correlation Analysis

Forecasting and Trend Analysis using AI and FORECAST.ETS

Solver for Optimization Problems (Minimization/Maximization)

Introduction to Power Pivot and Data Model

Integrating Excel with Power BI for Advanced Reporting

Capstone Project – AI-Driven Business Dashboard

Creating a Dynamic Excel Dashboard from Raw Data

Applying AI Copilot for Insights and Trend Summaries

Automating Reports and Email Triggers

Presenting Data Using Modern Visualization Techniques

Course Instructor

Ajay Aggarwal

14 Courses • 2 Students