YoVDO

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

Offered By: LinkedIn Learning

Tags

Microsoft Excel Courses Data Analysis Courses Data Cleaning Courses Data Management Courses VLOOKUP Courses Power Query Courses PivotTables Courses XLOOKUP Courses

Course Description

Overview

Learn how to manage and analyze large amounts of data using easy-to-use commands, features, and functions within Microsoft Excel.

Syllabus

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

Taught by

Dennis Taylor

Related Courses

Using Covid-19 Data to Make Supply Chain Logistics Decisions in Spreadsheets
Coursera Project Network via Coursera
Microsoft Excel - Excel from Beginner to Advanced
Udemy
Excel Shortcuts, Excel Tips, Excel Tricks - Excel Skills!
Udemy
Learn Excel 2016 Intermediate Level - Beyond the Basics
Udemy
Microsoft Excel Mastery: From Beginner to Pro
Udemy