Master advanced Excel 2021 techniques in this comprehensive 4-hour tutorial designed for power users. Dive deep into complex functions like XLOOKUP, INDEX-MATCH, and OFFSET to enhance data mining capabilities. Learn to create dynamic ranges, manipulate large datasets, and build sophisticated Pivot Tables and charts. Explore data analytics tools, including conditional formatting, custom sorting, and power features. Gain proficiency in macros, Power Pivot, and advanced data analysis techniques. Develop problem-solving skills to tackle challenging Excel tasks efficiently, pushing the boundaries of what's possible with spreadsheet software. Perfect for experienced users looking to elevate their Excel expertise and streamline complex data processes.
Overview
Syllabus
Start
Excel Power User Introduction
DB Functions, XLOOKUP, VLOOKUP, INDEX, MATCH
Excel XLOOKUP Two-Way Search
Text Functions
Advanced Pivot Tables
INDEX MATCH Array Function
Advanced Conditional Formats
Excel Charts
Introduction to Macros
Macro Examples
Excel Power User Conclusion
Pivot Tables Introduction
Organizing Data in Tables
More Data Organization Techniques
Data Mining
Custom Sort Command and Functions
Merge Data Using XLOOKUP
Pivot Tables - Setup
Pivot Tables - Building Reports
Pivot Tables - Formats, Drilldowns, and Refreshing
Pivot Tables - Designing for Reports and Charts
Pivot Tables - Cal Fields, Items, and Grouping
Power Pivot - Turning It On
Power Pivot - Adding Data and Establishing Relationships
Power Pivot - RELATED Function and Pivot Tables
Pivot Tables Conclusion
Intro to Data Analysis Introduction
Working with Tables
Conditional Formats
Using the IF FUNCTION
Database Functions
Unique, Sort, and Filter Functions
Charts and Sparklines
Working with Pivot Tables
Intro to Data Analysis Conclusion
XLOOKUP Introduction
VLOOKUP Refresher
XLOOKUP Basics
INDEX and MATCH
XLOOKUP - Approx Match Results
XLOOKUP - Returning Multiple Values
XLOOKUP - Two Way Search
XLOOKUP Conclusion
Taught by
Learnit Training
