Excel: Managing and Analyzing Data

Contents

• Introduction Introduction

  • Introduction to managing and analyzing data in Excel

    1m 3s
  • A comment about AI

    1m 31s
• 1. Checking Data Quality 1. Checking Data Quality

  • (Locked)
    The importance of checking data quality

    5m 22s
  • (Locked)
    Use the UNIQUE function to investigate data and data quality

    5m 54s
  • (Locked)
    Check data quality with a PivotTable

    4m 45s
  • (Locked)
    Check for duplicates with formulas

    4m 49s
  • (Locked)
    Find duplicates and stray values with conditional formatting

    6m 57s
  • (Locked)
    Deciding if your data is ready for use

    2m 49s
  • (Locked)
    Challenge: Checking data quality in Excel

    2m 9s
  • (Locked)
    Solution: Checking data quality in Excel

    4m 55s
• 2. Splitting Columns for Data Clean-Up 2. Splitting Columns for Data Clean-Up

  • Splitting columns with Flash Fill

    4m 31s
  • (Locked)
    Splitting columns with dynamic arrays

    7m 12s
  • (Locked)
    Splitting columns with Power Query

    6m 34s
  • (Locked)
    Splitting data into rows

    6m
  • (Locked)
    Challenge: Splitting columns in Excel

    1m 8s
  • (Locked)
    Solution: Splitting Columns in Excel

    5m 5s
• 3. Data Preparation 3. Data Preparation

  • Structure data for optimum usage in Excel

    6m 8s
  • (Locked)
    The power and majesty of Excel's tables

    11m 28s
  • (Locked)
    Using COUNTA to evaluate and monitor your data

    8m 17s
  • (Locked)
    Power Query: Unpivot

    6m 16s
  • (Locked)
    Convert an array to a column with TOCOL

    5m 17s
  • (Locked)
    Convert a report to raw, useful data

    7m 10s
  • (Locked)
    Challenge: Data preparation in Excel

    1m 8s
  • (Locked)
    Solution: Data preparation in Excel

    3m 10s
• 4. Working with Multiple Data Sets 4. Working with Multiple Data Sets

  • (Locked)
    Find and retrieve data with VLOOKUP and XLOOKUP

    9m 21s
  • (Locked)
    Import and combine data sets in Power Query

    8m 32s
  • (Locked)
    Append data sets

    7m 33s
  • (Locked)
    Overview of Power Query joins

    4m 12s
  • (Locked)
    Using the Power Query joins feature

    5m 51s
  • (Locked)
    Power Query vs. XLOOKUP

    7m 36s
  • (Locked)
    Challenge: Multiple datasets in Excel

    1m 36s
  • (Locked)
    Solution: Multiple datasets in Excel

    4m 45s
• 5. Sorting and Filtering Data 5. Sorting and Filtering Data

  • (Locked)
    Sort with multiple criteria

    2m 40s
  • (Locked)
    Sort by cell color, font color, or cell icon

    6m 42s
  • (Locked)
    Sort by custom list

    1m 36s
  • (Locked)
    Sort in Power Query

    3m 1s
  • Use dynamic arrays: SORT, SORTBY

    5m 40s
  • (Locked)
    Use the FILTER dynamic array function

    4m 20s
  • (Locked)
    Use text, numeric, and date filters

    8m 27s
  • (Locked)
    Filter in Power Query

    5m 56s
  • (Locked)
    Filter table data interactively with slicers

    6m 35s
  • (Locked)
    Challenge: Sorting and filtering data in Excel

    52s
  • (Locked)
    Solution: Sorting and filtering data in Excel

    2m 31s
• 6. PivotTables 6. PivotTables

  • (Locked)
    A quick word about PivotTables

    4m 35s
  • (Locked)
    Preparing your data for a PivotTable

    6m 58s
  • (Locked)
    Using PivotTables for basic analysis

    11m 39s
  • (Locked)
    Using tables to support PivotTable integrity

    6m
  • (Locked)
    Challenge: PivotTables in Excel

    48s
  • (Locked)
    Solution: PivotTables in Excel

    4m 42s
• 7. Other Data Analysis Tools and Methods 7. Other Data Analysis Tools and Methods

  • (Locked)
    SUMIFS, COUNTIFS, and related functions for data analysis

    6m 43s
  • (Locked)
    Identify top or bottom values with conditional formatting

    5m 39s
  • (Locked)
    Building a basic chart

    3m 4s
  • (Locked)
    Building a summary sheet or basic dashboard

    5m 23s
• 8. End of Chapter Challenge 8. End of Chapter Challenge

  • (Locked)
    Challenge: Apply what you've learned in Excel

    2m 54s
  • (Locked)
    Solution: Apply what you've learned in Excel

    10m 17s
• Conclusion Conclusion

  • (Locked)
    Continue your data stewardship

    58s