Test Function Class 1

Test Function Class 2

Lookup functions: VLOOKUP, HLOOKUP, INDEX, MATCH, XLOOKUP.

Text functions: LEFT, RIGHT, MID, CONCAT, TEXTJOIN.

Financial functions: PV, NPV, IRR.EDATE, DATEDIF, NETWORKDAYS.

Build a dynamic inventory tracker using advanced formulas.

Advance Chart and Waffle chart

Spreadsheet navigation, shortcuts, and customization.

Data types, formatting, and conditional formatting basics.

Essential functions: SUM, AVERAGE, IF, COUNTIF, VLOOKUP, HLOOKUP.

Data validation and error-checking techniques.

Introduction to tables and structured references.

Hands-On: Create a formatted sales tracker with basic calculations.

AI Teaser: Overview of AI-driven data cleaning tools (e.g., Power Query with AI suggestions).

Data cleaning

Master complex formulas for data manipulation and analysis.

Logical functions: IFERROR, IFS, AND, OR, nested IFs.

Lookup functions: INDEX, MATCH, XLOOKUP, XMATCH.

Text functions: LEFT, RIGHT, MID, CONCAT, TEXTJOIN, FIND, SEARCH.

Date and time functions: EDATE, EOMONTH, DATEDIF, NETWORKDAYS.

Array formulas and dynamic arrays (FILTER, SORT, UNIQUE, SEQUENCE).

Financial functions: PV, FV, NPV, IRR for business calculations.

Hands-On: Build a dynamic employee payroll system using advanced formulas.

AI Integration: Use Excel’s built-in AI (e.g., Ideas/Insights) to suggest formula optimizations.

Objective

Creating and customizing PivotTables.

Grouping, filtering, and sorting data in PivotTables.

Calculated fields and items in PivotTables.

Slicers and timelines for interactive dashboards.

PivotCharts: Types (bar, line, pie) and formatting.

Connecting multiple PivotTables for complex analysis.

Hands-On: Analyze a retail dataset to identify sales trends using PivotTables/Charts.

AI Integration: Use Microsoft Copilot (if available in Excel) to generate PivotTable suggestions or Power BI for enhanced visuals.

Advanced charting: Combo charts, sparklines, waterfall, funnel charts.

Conditional formatting for visual insights (data bars, color scales, icon sets).

Designing interactive dashboards using slicers and form controls.

Best practices for dashboard layout and storytelling with data.

Linking charts with PivotTables for dynamic updates.

Hands-On: Build a sales performance dashboard with multiple KPIs.

AI Integration: Use AI tools like Tableau Public or Power BI (connected to Excel) to suggest visualizations or predict trends.

Importing data from various sources (CSV, Excel, databases, web).

Handling missing data, duplicates, and inconsistencies.

Advanced transformations: Pivoting, unpivoting, custom columns.

Automating repetitive tasks with Power Query scripts.

Hands-On: Clean and transform a messy customer dataset for analysis.

AI Integration: Explore AI-driven data profiling in Power Query or third-party tools like Alteryx for automation suggestions.

Introduction to Power Pivot and data models.

Creating relationships between tables.

Introduction to DAX (Data Analysis Expressions): SUMX, CALCULATE, FILTER.

Time intelligence functions: TOTALYTD, SAMEPERIODLASTYEAR.

Building measures and KPIs for advanced analysis.

Optimizing Power Pivot models for performance.

Hands-On: Create a financial dashboard with Power Pivot and DAX calculations.

AI Integration: Use AI to suggest DAX formulas or optimize data models (e.g., via Copilot or external tools).

Why?

Excel + Copilot Tutorial For Beginners! Master AI-Powered Spreadsheets in Minutes

How to Use AI in Excel | Complete Beginner Tutorial (Step by Step)

AI-driven forecasting and trend analysis in Excel.

Automating repetitive tasks with AI-powered macros or Power Automate flows.

Hands-On: Build an AI-assisted sales forecasting model using Excel and Python/Power Automate.

AI Focus: Explore how AI can predict outcomes or automate report generation.

Capstone project: End-to-end analysis (e.g., retail, finance, or HR dataset) using all learned tools.

Resume building with Excel and AI skills highlighted.

nterview preparation: Common Excel-related questions and case studies.

Presentation skills for showcasing dashboards to stakeholders.

Best practices for collaboration (e.g., OneDrive, SharePoint integration).

Hands-On: Present capstone project to peers, simulating a corporate meeting.