Advanced Excel, Macros and VBA Table of Contents


Table of Content
 

 

Module 1. Microsoft Excel 2021/365 Fundamentals

  • Introduction to Excel 2021/365
  • Excel Interface and Ribbon
  • Workbooks and Worksheets
  • Cells, Rows and Columns
  • Data Entry and Editing
  • Basic Cell Formatting
  • Number, Date and Time Formatting
  • Managing Worksheets
  • Page Layout and Printing
  • Excel Options and Customization

Module 2. Excel Formulas and Functions

  • Introduction to Excel Formulas
  • Formula Operators
  • Relative and Absolute Cell References
  • Named Ranges
  • Basic Mathematical Functions
  • Statistical Functions
  • SUM, AVERAGE, MIN and MAX
  • COUNT, COUNTA and COUNTBLANK
  • IF Function
  • Nested IF Functions

Module 3. Excel Logical, Text and Date Functions

  • Logical Functions
  • AND, OR and NOT Functions
  • IFERROR Function
  • Text Functions
  • LEFT, RIGHT and MID
  • LEN and TRIM
  • CONCATENATE and Text Concatenation
  • Date and Time Functions
  • Working with Dates
  • Working with Times

Module 4. Excel Data Management

  • Sorting Data
  • Filtering Data
  • Advanced Filtering
  • Excel Tables
  • Creating and Managing Tables
  • Table Formatting
  • Structured Table References
  • Removing Duplicate Data
  • Find and Replace
  • Working with Large Data Sets

Module 5. Data Validation and Conditional Formatting

  • Introduction to Data Validation
  • Creating Validation Rules
  • List-Based Data Validation
  • Custom Validation
  • Managing Validation Rules
  • Conditional Formatting
  • Highlight Cell Rules
  • Top and Bottom Rules
  • Data Bars
  • Color Scales
  • Icon Sets
  • Formula-Based Conditional Formatting

Module 6. Excel Lookup and Reference Functions

  • Introduction to Lookup Functions
  • VLOOKUP
  • VLOOKUP with Exact and Approximate Match
  • Advanced VLOOKUP Techniques
  • XLOOKUP
  • XLOOKUP Options and Arguments
  • XMATCH
  • Combining Lookup Functions
  • Lookup and Reference Techniques
  • Comparing VLOOKUP and XLOOKUP

Module 7. Excel Charts and Data Visualization

  • Introduction to Excel Charts
  • Creating Charts
  • Selecting Appropriate Chart Types
  • Column and Bar Charts
  • Line Charts
  • Pie Charts
  • Advanced Chart Formatting
  • Chart Elements and Labels
  • Formatting Chart Data
  • Working with Multiple Data Series
  • Creating Effective Excel Visualizations

Module 8. PivotTables and PivotCharts

  • Introduction to PivotTables
  • Creating PivotTables
  • PivotTable Fields
  • Rows, Columns, Values and Filters
  • Sorting and Filtering PivotTables
  • Grouping PivotTable Data
  • Calculations in PivotTables
  • Formatting PivotTables
  • Refreshing PivotTable Data
  • Creating PivotCharts
  • Working with PivotChart Filters

Module 9. Advanced PivotTables

  • Advanced PivotTable Techniques
  • Advanced Filtering
  • Grouping Data
  • Date and Number Grouping
  • PivotTable Calculations
  • Calculated Values
  • Working with Multiple Fields
  • PivotTable Formatting Options
  • PivotTable Reporting
  • Advanced PivotChart Techniques

Module 10. Dynamic Arrays and Advanced Excel Functions

  • Introduction to Dynamic Arrays
  • Dynamic Array Formulas
  • Spill Ranges
  • SORT Function
  • SORTBY Function
  • FILTER Function
  • UNIQUE Function
  • SEQUENCE Function
  • RANDARRAY Function
  • Combining Dynamic Array Functions

Module 11. Advanced Excel Formulas

  • Advanced Formula Techniques
  • XLOOKUP and Dynamic Arrays
  • XMATCH
  • LET Function
  • LAMBDA Function
  • Creating Reusable Formulas
  • Combining Advanced Functions
  • Nested Formulas
  • Advanced Formula Applications
  • Improving Formula Efficiency

Module 12. Excel Form Controls and Advanced Features

  • Introduction to Form Controls
  • Working with Form Controls
  • Buttons and Interactive Controls
  • Linking Controls to Worksheet Data
  • Creating Interactive Excel Solutions
  • Using Controls with Formulas
  • Creating User-Friendly Worksheets
  • Advanced Excel Worksheet Features

Module 13. Power Query and Data Transformation

  • Introduction to Power Query
  • Accessing Power Query
  • Importing Data
  • Connecting to Data Sources
  • Power Query Editor
  • Transforming Data
  • Changing Data Types
  • Filtering and Cleaning Data
  • Splitting and Combining Columns
  • Removing Unwanted Data
  • Loading Query Results into Excel
  • Refreshing Power Query Data

Module 14. Excel Macros, VBA and Automation

  • Introduction to Excel Macros
  • Understanding Macro Automation
  • Recording Macros
  • Running Recorded Macros
  • Macro Security
  • Relative and Absolute Macro References
  • Creating Multi-Step Macros
  • Working with the VBA Editor
  • Assigning Macros to Buttons
  • Creating Macro Buttons
  • Customizing the Macro Ribbon
  • Automating Repetitive Excel Tasks



Apply for Certification

https://www.vskills.in/certification/certified-advanced-excel-macros-and-vba-professional

 For Support