ADVANCED EXCEL

Advanced Formulas and Functions
Array Formulas
➢ Introduction to array formulas
➢ Array constants
➢ Nesting functions within arrays
Advanced Lookup Functions
➢ INDEX and MATCH combination
➢ XLOOKUP function
➢ LOOKUP function
Database Functions
➢ DMAX, DSUM, DAVERAGE, DCOUNT
➢ Criteria usage in database functions
More functions
➢ Date and time functions
➢ Text functions
➢ Date and time functions
➢ Text functions
➢ Power Functions (CountIf, CountIFS, SumIF, SumIfS)
Data Analysis
Data Tables
➢ One-variable and two-variable data tables
➢ Goal Seek and Solver
PivotTables
➢ Advanced PivotTable options
➢ Grouping and ungrouping data
➢ Calculated fields and items
Data Visualization
Advanced Charting Techniques
➢ Combo charts
➢ Dynamic charts with data validation
➢ Sparklines
Dashboards
➢ Design principles for dashboards
➢ Interactive dashboard elements
➢ Data slicers and timelines
Data Validation and Protection
Data Validation
➢ Advanced data validation rules
➢ Custom validation formulas
Worksheet and Workbook Protection
➢ Password protection
➢ Protecting specific elements in a worksheet
➢ Workbook structure protection
Advanced Excel Tips and Tricks
Efficiency Tips
➢ Keyboard shortcuts
➢ Excel productivity add-ins
➢ Formula auditing tools
Advanced Data Analysis Techniques
➢ Scenario Manager
➢ Goal Seek and Solver for optimization
➢ Data profiling and cleansing techniques