Managing and Analyzing Data in Excel (Office 365/Excel 2019)

Introduction

• Managing and analyzing data in Excel

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

2. Splitting Columns for Data Clean-Up

• Splitting columns with Flash Fill
• Splitting columns with Dynamic Arrays
• Splitting columns with Power Query
• Splitting columns into rows

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

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

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

6. Other Data Analysis Tools and Methods

• SUMIFS, COUNTIFS, and related functions for data analysis
• A quick word about PivotTables
• Using PivotTables for basic analysis
• Using tables to support PivotTable integrity
• Identify top or bottom values with Conditional Formatting

Conclusion

• Continue your data stewardship