Excel: Managing and Analyzing Data

Introduction

• Introduction to managing and analyzing data in Excel
• A comment about AI

1. Checking Data Quality

• The importance of checking data quality
• Use the UNIQUE function to investigate data and data quality
• Check data quality with a PivotTable
• Check for duplicates with formulas
• Find duplicates and stray values with conditional formatting
• Deciding if your data is ready for use
• Challenge: Checking data quality in Excel
• Solution: Checking data quality in Excel

2. Splitting Columns for Data Clean-Up

• Splitting columns with Flash Fill
• Splitting columns with dynamic arrays
• Splitting columns with Power Query
• Splitting data into rows
• Challenge: Splitting columns in Excel
• Solution: Splitting Columns in Excel

3. Data Preparation

• Structure data for optimum usage in Excel
• The power and majesty of Excel's tables
• Using COUNTA to evaluate and monitor your data
• Power Query: Unpivot
• Convert an array to a column with TOCOL
• Convert a report to raw, useful data
• Challenge: Data preparation in Excel
• Solution: Data preparation in Excel

4. Working with Multiple Data Sets

• Find and retrieve data with VLOOKUP and XLOOKUP
• Import and combine data sets in Power Query
• Append data sets
• Overview of Power Query joins
• Using the Power Query joins feature
• Power Query vs. XLOOKUP
• Challenge: Multiple datasets in Excel
• Solution: Multiple datasets in Excel

5. Sorting and Filtering Data

• Sort with multiple criteria
• Sort by cell color, font color, or cell icon
• Sort by custom list
• Sort in Power Query
• Use dynamic arrays: SORT, SORTBY
• Use the FILTER dynamic array function
• Use text, numeric, and date filters
• Filter in Power Query
• Filter table data interactively with slicers
• Challenge: Sorting and filtering data in Excel
• Solution: Sorting and filtering data in Excel

6. PivotTables

• A quick word about PivotTables
• Preparing your data for a PivotTable
• Using PivotTables for basic analysis
• Using tables to support PivotTable integrity
• Challenge: PivotTables in Excel
• Solution: PivotTables in Excel

7. Other Data Analysis Tools and Methods

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

8. End of Chapter Challenge

• Challenge: Apply what you've learned in Excel
• Solution: Apply what you've learned in Excel

Conclusion

• Continue your data stewardship