Module 1: Advanced Formulas and Functions
Logical Functions: IF, AND, OR, NOT.
Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX, MATCH.
Text Functions: LEFT, RIGHT, MID, CONCATENATE, TEXT.
Date and Time Functions: DATE, TIME, YEAR, MONTH, DAY, DATEDIF, NETWORKDAYS.
Statistical Functions: SUMIFS, COUNTIFS, AVERAGEIFS.
Array Formulas: Using arrays for complex calculations.
Module 2: Data Analysis and Visualization
PivotTables and PivotCharts: Creating, formatting, and analyzing data with PivotTables and PivotCharts.
Data Analysis Tools: Using Solver, Goal Seek, Scenario Manager, and Data Tables.
Conditional Formatting: Creating rules to format cells based on criteria.
Charts and Graphs: Advanced chart types and customization.
Module 3: Data Validation and Protection
Data Validation: Creating drop-down lists, input messages, and error alerts.
Protecting Worksheets and Workbooks: Locking cells, protecting formulas, and securing workbooks.
Module 4: Advanced Data Management
Working with Large Datasets: Sorting, filtering, and using advanced filter options.
Importing and Exporting Data: Importing data from various sources (CSV, XML, databases) and exporting data.
Data Cleaning Techniques: Removing duplicates, text-to-columns, and using Flash Fill.
Module 5: Advanced Charting Techniques
Combination Charts: Creating charts that combine different types of data.
Dynamic Charts: Using named ranges and OFFSET function for dynamic charts.
Sparklines: Adding mini charts within cells to visualize data trends.
Introduction to Macros: Recording and running macros.
VBA Basics: Writing simple VBA code, using the VBA editor, and debugging.
Automating Tasks: Creating custom functions and automating repetitive tasks with VBA.
Module 7: Advanced PivotTable Techniques
Calculated Fields and Items: Adding calculated fields and items in PivotTables.
Grouping and Slicing: Grouping data and using slicers for better data analysis.
PivotTable Options and Settings: Customizing PivotTable settings for optimal performance.
Module 8: What-If Analysis
Using What-If Analysis Tools: Data Tables, Scenario Manager, and Goal Seek.
Creating and Analyzing Scenarios: Building different scenarios to analyze data outcomes.
Module 9: Collaboration and Sharing
Sharing Workbooks: Collaborating with others using shared workbooks.
Track Changes and Comments: Tracking changes, adding comments, and reviewing changes.
Co-Authoring in Excel: Real-time collaboration with co-authoring.
Module 10: Advanced Formatting Techniques
Custom Number Formats: Creating and applying custom number formats.
Conditional Formatting with Formulas: Using formulas to apply conditional formatting.
Themes and Styles: Applying and customizing themes and styles.
Module 11: Power Query and Power Pivot
Power Query: Importing, transforming, and loading data.
Power Pivot: Creating data models, using DAX for calculations, and building relationships between tables.
Module 12: Dashboard Creation
Designing Dashboards: Principles of effective dashboard design.
Interactive Dashboards: Using form controls, slicers, and timelines.
Connecting to External Data Sources: Integrating data from multiple sources into a single dashboard.
