Excel: Managing and Analyzing Data

Course details

In this course, Oz du Soleil—an Excel MVP, author, and trainer with a passion for clean data and reliable reporting—equips you with the skills to effectively manage and analyze large or complex datasets in Excel. Learn how to check data quality, clean and organize data, and leverage Excel’s advanced tools like Power Query and dynamic arrays. Find out how to split columns, structure data, and combine datasets while maintaining integrity and scalability. Plus, get an introduction to sorting, filtering, and PivotTable techniques, along with essential functions and visualization tools for clear data representation. When you complete the course, you will be proficient in transforming raw data into actionable insights and enhancing decision-making processes.

Learning objectives
Identify common data quality issues and describe tools like Power Query, PivotTables, and dynamic arrays to address them.
Use Excel functions and features to clean, split, and structure datasets for analysis.
Combine and compare multiple datasets using Power Query joins and advanced functions like XLOOKUP.
Design PivotTables, dashboards, and visual summaries to present data insights effectively.
Assess data readiness, quality, and structure to determine its suitability for accurate analysis and reporting.

Concepts

0. Introduction

01 - Introduction to managing and analyzing data in Excel
02 - A comment about AI

1. Checking Data Quality

03 - The importance of checking data quality
04 - Use the UNIQUE function to investigate data and data quality
05 - Check data quality with a PivotTable
06 - Check for duplicates with formulas
07 - Find duplicates and stray values with conditional formatting
08 - Deciding if your data is ready for use
09 - Challenge - Checking data quality in Excel
10 - Solution - Checking data quality in Excel

2. Splitting Columns for Data Clean-Up

11 - Splitting columns with Flash Fill
12 - Splitting columns with dynamic arrays
13 - Splitting columns with Power Query
14 - Splitting data into rows
15 - Challenge - Splitting columns in Excel
16 - Solution - Splitting Columns in Excel

3. Data Preparation

17 - Structure data for optimum usage in Excel
18 - The power and majesty of Excel's tables
19 - Using COUNTA to evaluate and monitor your data
20 - Power Query - Unpivot
21 - Convert an array to a column with TOCOL
22 - Convert a report to raw, useful data
23 - Challenge - Data preparation in Excel
24 - Solution - Data preparation in Excel

4. Working with Multiple Data Sets

25 - Find and retrieve data with VLOOKUP and XLOOKUP
26 - Import and combine data sets in Power Query
27 - Append data sets
28 - Overview of Power Query joins
29 - Using the Power Query joins feature
30 - Power Query vs. XLOOKUP
31 - Challenge - Multiple datasets in Excel
32 - Solution - Multiple datasets in Excel

5. Sorting and Filtering Data

33 - Sort with multiple criteria
34 - Sort by cell color, font color, or cell icon
35 - Sort by custom list
36 - Sort in Power Query
37 - Use dynamic arrays - SORT, SORTBY
38 - Use the FILTER dynamic array function
39 - Use text, numeric, and date filters
40 - Filter in Power Query
41 - Filter table data interactively with slicers
42 - Challenge - Sorting and filtering data in Excel
43 - Solution - Sorting and filtering data in Excel

6. PivotTables

44 - A quick word about PivotTables
45 - Preparing your data for a PivotTable
46 - Using PivotTables for basic analysis
47 - Using tables to support PivotTable integrity
48 - Challenge - PivotTables in Excel
49 - Solution - PivotTables in Excel

7. Other Data Analysis Tools and Methods

50 - SUMIFS, COUNTIFS, and related functions for data analysis
51 - Identify top or bottom values with conditional formatting
52 - Building a basic chart
53 - Building a summary sheet or basic dashboard

8. End of Chapter Challenge

54 - Challenge - Apply what you've learned in Excel
55 - Solution - Apply what you've learned in Excel

Conclusion

56 - Continue your data stewardship