MS 365 Excel Basics: Power Query, FlashFill, XLOOKUP, PivotTables, Sort and Filter - 7 Data Analysis Examples

Overview

Coursera Plus Monthly Sale: All Certificates & Courses 40% Off!

Grab it

Dive into essential Excel data analysis tools in this comprehensive 89-minute tutorial from ExcelIsFun. Master seven practical data analysis examples covering sorting, filtering, FlashFill, PivotTables with various calculations, Power Query for importing and transforming data, M Code fundamentals, XLOOKUP functions, and Excel chart visualizations. Download practice files from the provided link to follow along as you learn how to clean, transform, and refresh data, combine multiple tables, create PivotTable reports, and build effective visualizations. The tutorial includes a complete introduction to Power Query, demonstrates how to update queries with new data, and explains data modeling concepts. Three practice problems are provided at the end to reinforce your learning. Available with subtitles in multiple languages and audio translations in French, German, Hindi, Indonesian, Italian, Japanese, Portuguese, and Spanish.

Syllabus

1. 00:00 Introduction
2. 01:05 Download practice files from zipped folder
3. 02:05 On-Premise folder and file paths
4. 03:52 Preview of all examples in videos 10, 11, 12, and 13
5. 05:42 What is Data Analysis?
6. 06:25 Sorting Feature Example 1
7. 10:23 Filtering. Feature Example 2
8. 15:07 FlashFill. Example 3.
9. 17:41 Standard PivotTable with Summarize Values By and Show Values As calculations. Example 4.
10. 22:03 Power Query to import, clean, transform and refresh data and load to Excel Worksheet. Complete Introduction to Power Query. Example 5.
11. 33:27 First look at Power Query’s Functional Language: M Code
12. 48:19 Adding Worksheet formulas to a query in the worksheet
13. 51:30 Updating query when you get new data
14. 54:30 Power Query to Import and combine 12 tables into one table and load directly to a PivotTable Report & Excel Chart Visualization. Example 6.
15. 01:04:10 Load Query to PivotTable
16. 01:05:55 Show Values As Calculations in PivptTable
17. 01:09:45 Excel Line Chart Visualization based on PivotTable
18. 01:13:20 Look at similarities between the Data Models for the Examples 7, 8 & 9
19. 01:17:06 XLOOKUP function, PivotTable and Excel Charts to build report and visualization. Example 7.
20. 01:27:25 Summary of video
21. 01:27:55 Three Practice Problems on the worksheets: HW1, HW2, HW3
22. 01:28:32 Closing