Advanced Microsoft Excel Course 17th February – 21st Apr-21st May 2025 (Mon & Wed 8pm-9pm)

Description

Table of Contents Module 1: Advanced Formulas and Functions

1. Introduction to Advanced Formulas
2. Logical Functions
   • IF, AND, OR, IFS Functions, etc.
3. Lookup and Reference Functions
   • VLOOKUP, HLOOKUP, XLOOKUP
   • INDEX, MATCH and a Combination of both
4. Text Functions
   • CONCAT, CONCATENATE, LEFT, RIGHT, MID
   • TEXTJOIN, TEXT Formatting
5. Date and Time Functions
   • DATE, EOMONTH, WORKDAY
   • NETWORKDAYS, TODAY, NOW
6. Statistical Functions
   • SUMIFS, COUNTIFS, AVERAGEIFS
   • MEDIAN, RANK
7. Dynamic Arrays
   • SORT, FILTER, UNIQUE, SEQUENCE
8. Error Handling Functions
   • IFERROR, IFNA, ISERROR

Module 2: Data Analysis and Visualization

1. PivotTables and PivotCharts
   • Creating and Customizing PivotTables
   • Using Slicers and Timelines
   • Advanced PivotChart Options
2. Power Query for Data Transformation
   • Importing and Transforming Data
   • Combining Multiple Data Sources
3. Data Validation Techniques
   • Setting Up Rules and Dropdown Lists
   • Handling Errors in Data Validation
4. What-If Analysis Techniques
   • Goal Seek
   • Data Tables (One-Variable and Two-Variable)
   • Scenario Manager
5. Using Conditional Formatting for Insights
   • Creating Custom Rules
   • Using Icon Sets and Data Bars
6. Sparklines and Mini-Charts
   • Creating and Customizing Sparklines
   • Using Mini-Charts for Trend Analysis

Module 3: Advanced Charting Techniques

1. Customizing Charts in Excel
   • Adding Secondary Axes
   • Creating Combo Charts
2. Dynamic Charts
   • Creating Charts That Update Automatically
3. Advanced Chart Types
   • Waterfall, Gauge, Funnel Charts
   • Heat Maps and Histograms
4. Infographics in Excel
   • Creating Gauges and Speedometers
   • Building Dashboard Components

Module 4: Automation with Macros and VBA

1. Getting Started with Macros
   • Recording and Running Basic Macros
2. Introduction to VBA (Visual Basic for Applications)
   • Understanding the VBA Editor
   • Writing Simple VBA Scripts
3. Custom Functions (User Defined Functions)
   • Creating and Using UDFs in Excel
4. Automating Tasks Using VBA
   • Writing Scripts to Automate Repetitive Tasks
5. Form Controls and UserForms
   • Creating Interactive Forms for Data Input

Module 5: Power Pivot and DAX

1. Data Modeling Basics
   • Understanding Relationships Between Tables
2. Using DAX Functions for Calculations
   • Calculated Columns and Measures
   • Time Intelligence Functions
3. Power Pivot Tables for Advanced Analysis
   • Creating PivotTables with Power Pivot
4. Creating Hierarchies and KPIs
   • Setting Up Hierarchies for Drill-Down Analysis
   • Key Performance Indicators in Power Pivot

Module 6: Advanced Data Management Techniques

1. Working with Large Data Sets
   • Best Practices for Handling Big Data in Excel
2. Using Excel’s Database Functions
   • DSUM, DCOUNT, DAVERAGE, DMAX, DMIN Functions
3. Protecting Data and Worksheets
   • Password Protection, Cell Locking, and Sheet Hiding
4. Data Cleaning Techniques
   • Removing Duplicates
   • Trimming Spaces and Splitting Text

Module 7: Integration with Other Tools

1. Connecting Excel to Power BI
   • Importing Excel Data into Power BI
   • Creating Visualizations Using Power BI
2. Using Excel Data in Word and PowerPoint
   • Linking and Embedding Excel Data
   • Updating Linked Data Across Applications
3. Importing and Exporting Data
   • Working with CSV, XML, and JSON Formats
4. Connecting to External Data Sources
   • Accessing Databases, Web Services, and Cloud Storage

Module 8: Advanced Troubleshooting and Optimization

1. Debugging Formulas and Errors
   • Using Auditing Tools
   • Tracing Precedents and Dependents
2. Performance Optimization Techniques
   • Improving Speed and Efficiency in Excel
3. Error Tracking and Handling
   • Resolving Common Errors
4. Version Control in Excel
   • Managing Multiple Versions of Excel Files

Capstone Project

1. Real-World Case Study
   • Analyzing a Business Problem
   • Developing a Comprehensive Solution Using Excel
2. Project Presentation and Reporting
   • Presenting the Analysis
   • Creating a Report with Visuals and Insights