Embark on a comprehensive 7-hour tutorial covering advanced Excel data analysis techniques. Master list design, table manipulation, and conditional formatting. Explore powerful functions like IF, SUMIF, and XLOOKUP. Dive deep into Pivot Tables, learning to build, filter, and visualize data effectively. Discover the capabilities of Excel Copilot for enhanced data insights and automation. Advance your skills with array formulas, macros, and VBA basics. Perfect for experienced Excel users seeking to elevate their data analysis proficiency, this course covers everything from fundamental concepts to advanced features, ensuring you can leverage Excel's full potential for complex data tasks.
Overview
Syllabus
Start
Introduction - Excel Intro to Data Analysis
List Design Basics
Inserting Tables for Analysis
Filtering Data in Tables
Using the Total Row
Conditional Formatting
IF Function
SUMIF and AVERAGEIF
SUMIFS
Inserting Recommended Charts
Adjusting Charts
Sparklines
Inserting Pivot Tables
Displaying Data as Count
Filtering Pivot Tables
Inserting Pivot Charts
Conclusion - Intro to Data Analysis
Introduction - Excel Pivot Tables
What are Pivot Tables?
Preparing Data for Analysis
Pivot Table Components
Building Pivot Tables to Show Different Values
Adding Fields to Pivot Tables
Using Built-In Filters
Filtering Data with Slicers
Displaying New Values from Data Sources
Inserting Pivot Charts
XLOOKUP
Joining Data Sets with XLOOKUP
Introduction to Advanced Pivot Tables
Inserting Pivot Tables from Tables
Calculated Fields
Using the Timeline Tool
Report Filter Pages
Pivot Table Layouts
Creating Pivot Table Designs
Adding Power Pivot Tabs to the Ribbon
Adding Tables to Power Pivot Data Model
Creating Table Relationships
Creating Columns with DAX Expressions
Displaying New Source Data in Power Pivot Tables
Data Mining with Flash Fill
Conclusion - Excel Pivot Tables
Introduction - Excel Copilot
What is Copilot?
Setting Up Copilot in Excel
Copilot Pane and Insights
Visualizing Data with Copilot
Sorting, Highlighting, and Filtering
Adding Formulas with Copilot
Asking Copilot Questions
Accessing Copilot Lab
Copilot Lab
Copilot with Excel Web Version
Conclusion - Excel Copilot
Introduction - Excel Power User
IF Function Basics
IF Functions with Calculations
Nesting AND with IF
Naming Ranges and COUNTIF
SUMIF and AVERAGEIF
SUMIFS
XLOOKUP
Populating Forms with XLOOKUP
Displaying Pivot Table Fields with COUNT Function
Calculated Fields
Slicers
Using the Timeline
Inserting Report Filter Pages
Flash Fill for Text Functions
Introduction to Advanced Conditional Formatting and Macros
Array Formula Basics
Looking Up Multiple Values with XLOOKUP
Array Functions
Advanced Conditional Formatting
Combo Charts
Developer Tab
Macro Basics
Relative Reference Macros
VBA Basics
Cleaning Up Macros
Conclusion - Excel Power User
Taught by
Learnit Training
