Excel: The Complete Guide to Combining Data

Course details

In this course, Microsoft MVP Nabil Mourad guides you on a comprehensive journey to master data combination in Excel. Learn how to seamlessly integrate data from multiple sources using functions like CONCAT, TEXTJOIN, and Power Query. Explore consolidating, merging, and appending data techniques to optimize your workflow. Discover how to use Power Pivot for analyzing massive datasets that exceed Excel's row limit. Enhance your skills by developing dynamic queries and custom functions to handle data inconsistencies. Gain insights into using Excel’s full potential to address complex business questions. This course is perfect for intermediate Excel users, analysts, data enthusiasts, or anyone looking to streamline their data processes. Advance your skills and be prepared to tackle real-world data challenges efficiently.

Learning objectives
Integrate multiple data sources, tackle complex analysis, and make data-driven decisions with ease.
Articulate the importance of data combination as a foundational skill in Excel.
Combine data within cells using formulas and functions efficiently.
Merge tables and sheets to create unified datasets for analysis.
Utilize Power Query to automate and simplify data combination tasks.
Be able to combine data in Lists, tables and sheets.
Combine multiple files from a folder into a single, consolidated dataset.
Combine Inconsistent and unstructured data.
Apply learned techniques to real-world scenarios, improving productivity and accuracy.

Concepts

0. Introduction

01 - Essential skills when combining data in Excel
02 - What to know about Excel - Prerequisites

1. Excel Data Combination - The Basics

03 - Data combination types
04 - Data sources
05 - Choose the right Excel combination tool

2. Combining Excel Cell Data

06 - Excel CONCAT for cell combination
07 - Advanced cell combination with TEXTJOIN
08 - Dynamic Excel cell combination with BYROW

3. Consolidating Pivoted Lists in Excel

09 - Consolidate a pivoted list with a formula
10 - Consolidate a pivoted list using a function
11 - Using the Consolidate tool
12 - Consolidate inconsistent pivoted lists

4. Merging Data Using Functions or Power Query

13 - Merging lists using functions
14 - Merging tables using Power Query

5. Appending Lists and Tables with a Function

15 - Difference between lists and tables
16 - Using VSTACK with lists
17 - Using VSTACK with tables

6. Using Power Query to Combine Tables

18 - Append tables into the same workbook with Power Query
19 - Append tables into a different workbook

7. Using Power Query to Combine Sheets from Files in a Folder

20 - Same data structure, same sheet name
21 - Different sheet names
22 - Files with inconsistent headers
23 - Dynamic solution

8. Combining Only Excel Files From a Folder

24 - Excluding non-Excel files
25 - The Excel.Workbook function
26 - Sending data to Excel
27 - Analyze combined data with PivotTables
28 - Adding new files to the folder

9. Combining Data That Contains Problems

29 - Remove junk rows from source data
30 - Create a Power Query function
31 - Invoke the custom function to fix all files

10. Combining Excel Data with TXT Files

32 - Plan Excel and TXT integration
33 - Connect Excel to Power Pivot
34 - Cleaning and combining multiple TXT files in a folder
35 - Sending combined TXT files to Power Pivot
36 - Create relationships
37 - Analyze with PivotTables

Conclusion

38 - Excel data combination - Next steps and resources