Excel: Managing and Analyzing Data

Focused View

Oz du Soleil

3:42:46

346 View

01 - Introduction

01 - Managing and analyzing data in Excel.mp4

01:00

02 - 1. Checking Data Quality

01 - The importance of checking data quality.mp4

05:22

02 - Use the UNIQUE function to investigate data and data quality.mp4

05:54

03 - Check data quality with a PivotTable.mp4

04:45

04 - Check for duplicates with formulas.mp4

04:49

05 - Find duplicates and stray values with Conditional Formatting.mp4

06:57

06 - Deciding if your data is ready for use.mp4

01:53

03 - 2. Splitting Columns for Data Clean-Up

01 - Splitting columns with Flash Fill.mp4

04:31

02 - Splitting columns with Dynamic Arrays.mp4

07:12

03 - Splitting columns with Power Query.mp4

06:34

04 - Splitting columns into rows.mp4

06:00

04 - 3. Data Preparation

01 - Structure data for optimum usage in Excel.mp4

06:05

02 - The power and majesty of Excel's Tables.mp4

11:28

03 - Using COUNTA to evaluate and monitor your data.mp4

08:17

04 - Power Query Unpivot.mp4

06:16

05 - Convert an array to a column with TOCOL.mp4

05:17

06 - Convert a report to raw, useful data.mp4

07:10

05 - 4. Working with Multiple Data Sets

01 - Find and retrieve data with VLOOKUP and XLOOKUP.mp4

09:21

02 - Import and combine data sets in Power Query.mp4

07:00

03 - Append data sets.mp4

07:33

04 - Overview of Power Query joins.mp4

04:12

05 - Using the Power Query joins feature.mp4

07:00

06 - Power Query vs. XLOOKUP.mp4

07:36

06 - 5. Sorting and Filtering Data

01 - Sort with multiple criteria.mp4

02:40

02 - Sort by cell color, font color, or cell icon.mp4

06:42

03 - Sort by custom list.mp4

01:36

04 - Sort in Power Query.mp4

03:01

05 - Use Dynamic Arrays SORT, SORTBY.mp4

05:40

06 - Use the FILTER dynamic array function.mp4

04:20

07 - Use text, numeric, and date filters.mp4

08:27

08 - Filter in Power Query.mp4

05:56

09 - Filter table data interactively with slicers.mp4

06:35

07 - 6. Other Data Analysis Tools and Methods

01 - SUMIFS, COUNTIFS, and related functions for data analysis.mp4

06:43

02 - A quick word about PivotTables.mp4

04:35

03 - Using PivotTables for basic analysis.mp4

11:39

04 - Using tables to support PivotTable integrity.mp4

06:00

05 - Identify top or bottom values with Conditional Formatting.mp4

05:39

08 - Conclusion

01 - Continue your data stewardship.mp4

01:01

Description

Ready to start working with spreadsheets like a pro? Join Excel MVP Oz du Soleil as he shares how to clean, organize, and analyze large amounts of data with ease using commands, features, and functions within Microsoft Excel. Instead of focusing on pure analysis (forecasting, running regressions, etc.), Oz covers how to use built-in Excel tools to prepare your data for analysis and extract the information that supports your mission.

Oz dedicates the first portion of the course to an often-overlooked part of data analysis: checking data quality. He then steps through how to prepare your data for analysis by setting it up into sortable, filterable columns and rows. Next, Oz shares how to work with multiple data sets at once; sort and filter data; and use functions to isolate and analyze data. Plus, learn how to work with the newest features in Excel, including Power Query and dynamic arrays, which make it so much easier to get what you need from your data without clumsy, complicated, and delicate formulas. With these techniques, you'll be prepared to quickly and effectively extract important information from even your largest spreadsheets.

More details

User Reviews

Rating

average 0

Total votes0

Focused display

Excel

Excel Analytics

Oz du Soleil

Instructor's Courses

Microsoft Excel MVP | YouTube Channel: Excel on Fire | Author, Guerrilla Data Analysis Version 2. Co-authored with Mr Excel, Bill Jelen | Excel Trainer & Instructor | Speaker. I've been working with data and Excel for many years, and have developed a passion for clean data and reliable reporting. An even greater passion is that of teaching, sharing knowledge and empowering people to do handle their data more efficiently and with greater skill to turn their data into useful information. Presented at: #Northwestern University #University of Illinois at Chicago #IgniteChicago #ExcelTV Courses taught: Excel Formulas Data Visualization in Excel 2013 Pivot Tables & Dashboards Excel for Nonprofits: Measuring Fundraising Effectiveness Introduction to Programming VBA

Linkedin Learning

View courses Linkedin Learning

LinkedIn Learning is an American online learning provider. It provides video courses taught by industry experts in software, creative, and business skills. It is a subsidiary of LinkedIn. All the courses on LinkedIn fall into four categories: Business, Creative, Technology and Certifications. It was founded in 1995 by Lynda Weinman as Lynda.com before being acquired by LinkedIn in 2015. Microsoft acquired LinkedIn in December 2016.