Advanced Microsoft Excel - theramansway.org

About Course

Module 1: Advanced Functions & Formulas

Module 2: Data Validation and Conditional Formatting

Module 3: Data Analysis Tools

Module 4: Charts and Data Visualization

Module 5: Excel Tables and Structured References

Module 6: Power Tools

Module 7: Macros and VBA (Introduction)

Module 8: Advanced Excel Techniques

Module 9: Collaboration and Security

Module 10: Dashboards and Reporting

Course Content

Module 1: Advanced Functions & Formulas
Nested IF, AND, OR, NOT functions Lookup & Reference functions (VLOOKUP, HLOOKUP, XLOOKUP, INDEX & MATCH) Text functions (LEFT, RIGHT, MID, LEN, TRIM, TEXT, CONCAT, TEXTJOIN) Date & Time functions (TODAY, NOW, DATEDIF, EDATE, EOMONTH) Logical & Error-handling functions (IFERROR, IFNA, ISBLANK, ISNUMBER)

Nested IF, AND, OR, NOT functions

Module 2: Data Validation and Conditional Formatting
Custom data validation rules Drop-down lists with dynamic ranges Conditional formatting with formulas Using icon sets, data bars, and color scales

Module 3: Data Analysis Tools
PivotTables: grouping, calculated fields, slicers PivotCharts Power Query (Get & Transform Data) What-If Analysis (Goal Seek, Scenario Manager, Data Tables) Solver for optimization

Module 4: Charts and Data Visualization
Advanced chart types: Combo, Histogram, Waterfall, Treemap, Sunburst, Sparklines Customizing chart elements Dynamic charts using named ranges and formulas

Module 5: Excel Tables and Structured References
Creating and managing Excel Tables Structured references in formulas Table-based dynamic dashboards

Module 6: Power Tools
Power Query: loading, transforming, appending, merging data Power Pivot: creating data models, relationships, KPIs, and calculated columns DAX (Data Analysis Expressions) basics

Module 7: Macros and VBA (Introduction)
ecording Macros Assigning Macros to buttons Basic VBA editor navigation Writing simple VBA scripts Automating tasks using VBA

Module 8: Advanced Excel Techniques
Dynamic named ranges Array formulas (including dynamic arrays and legacy CSE arrays) New Excel functions (e.g., FILTER, SORT, UNIQUE, SEQUENCE) Working with external data connections

Module 9: Collaboration and Security
Workbook sharing and protection Track changes and comments Protecting sheets and ranges File encryption and digital signatures

Module 10: Dashboards and Reporting
Creating interactive dashboards Using slicers and timelines

Student Ratings & Reviews

No Review Yet

About Course

Module 6: Power Tools

Module 10: Dashboards and Reporting

What Will You Learn?

Course Content

Nested IF, AND, OR, NOT functions

Module 2: Data Validation and Conditional Formatting Custom data validation rules Drop-down lists with dynamic ranges Conditional formatting with formulas Using icon sets, data bars, and color scales

Module 3: Data Analysis Tools PivotTables: grouping, calculated fields, slicers PivotCharts Power Query (Get & Transform Data) What-If Analysis (Goal Seek, Scenario Manager, Data Tables) Solver for optimization

Module 4: Charts and Data Visualization Advanced chart types: Combo, Histogram, Waterfall, Treemap, Sunburst, Sparklines Customizing chart elements Dynamic charts using named ranges and formulas

Module 5: Excel Tables and Structured References Creating and managing Excel Tables Structured references in formulas Table-based dynamic dashboards

Module 6: Power Tools Power Query: loading, transforming, appending, merging data Power Pivot: creating data models, relationships, KPIs, and calculated columns DAX (Data Analysis Expressions) basics

Module 7: Macros and VBA (Introduction) ecording Macros Assigning Macros to buttons Basic VBA editor navigation Writing simple VBA scripts Automating tasks using VBA

Module 8: Advanced Excel Techniques Dynamic named ranges Array formulas (including dynamic arrays and legacy CSE arrays) New Excel functions (e.g., FILTER, SORT, UNIQUE, SEQUENCE) Working with external data connections

Module 9: Collaboration and Security Workbook sharing and protection Track changes and comments Protecting sheets and ranges File encryption and digital signatures

Module 10: Dashboards and Reporting Creating interactive dashboards Using slicers and timelines