Ben Collins – Data Cleaning and Pivot Tables in Google Sheets

Ben Collins – Data Cleaning and Pivot Tables in Google Sheets

Archive : Ben Collins – Data Cleaning and Pivot Tables in Google Sheets

Master Google Sheets’ most powerful tool, Pivot Tables, to transform jumbled data into strong, usable datasets and obtain critical insights.

In the data community, there’s a joke that goes:

“You’ll spend 90% of your time cleaning data and the other 10% grumbling about cleaning data.”

Do you have to deal with improperly structured data that takes hours to clean up, stopping you from progressing on your “serious” work?

Do you ever find yourself manually repeating a task, such as collecting a zip code from an address?

Have you ever wondered what a Pivot Table is and why you should care?

This course is intended for beginners to intermediate-level Google Sheets users who want to learn how to work with messy, real-world datasets.

You’ll discover procedures and best practices for cleaning data and preparing it for analysis and reporting in four hours of video lessons, saving you hours of tiresome, repetitive labor and ensuring reliable results for your firm.

What exactly is data cleaning?

The act of discovering and correcting mistakes, completing missing data, or dealing with unnecessary data in your data sets is known as data cleaning.

The purpose of data cleaning is to establish a consistent, clean data collection that provides you confidence that any future analysis and conclusions you reach will be accurate and comprehensive.

Why should you be concerned?

Data cleansing is an important initial step in the data analysis pipeline that is frequently overlooked.

If you start with “poor” data — for example, if your data contains duplicate entries — you’ll definitely wind up with “bad” conclusions. For example, you may wind up double counting sales, which could have devastating ramifications for your firm in the long run.

This training course walks you through professional approaches and best practices for cleaning data in Google Sheets using formulae and pivot tables. Once you understand these ideas, you (and your boss!) will be sure that your findings are founded on solid evidence.

What is covered in this course?

Best practices for data manipulation in Google Sheets.
All formulae needed for data cleansing.
Tips and tactics, such as shortcut keys, to improve the efficiency of your productivity.
Professional approaches for transforming unstructured real-world data into clean, organized data collections for analysis.
Tables with pivot points! This course covers Pivot Tables from beginning to end. Even if you’ve never seen a Pivot Table before, you’ll quickly be able to design cutting-edge ones by utilizing advanced techniques such as calculated fields and data extraction with unique algorithms. I don’t believe you’ll find a more in-depth tutorial on Google Sheets Pivot Tables anyplace else on the internet.
Two in-depth case studies that demonstrate the use of all of these strategies in the context of a real-world situation.

This course does not cover the following topics:

How to evaluate your datasets and create dashboard reports
How to work with data using the Apps Script scripting language.
How to make data visualizations or charts out of your data.

This course provides the following benefits:

Over 4 hours of video courses teach you through ways for cleansing data and using pivot tables step by step.
All of the raw data files used in the examples, as well as copies of the formulae and connections to online documentation and other useful resources, are available.
Membership in our exclusive Facebook community, where you may ask questions and receive answers, exchange thoughts, and engage with other students.
All of the videos are available online, and you will have lifetime access.

Who is this course intended for?

Anyone who works with data in Google Sheets – whether you’re a data analyst, marketer, educator, scientist, or everything in between.
Anyone interested in learning more about data manipulation and Pivot Tables.
Anyone interested in learning best practices and data efficiency.
Anyone who wants to learn new skills to help them grow in their careers.

What are the requirements?

A Google account is required.
Google Sheets access
Basic knowledge with Google Sheets and spreadsheets.
There are no assumptions regarding past understanding of functions or pivot tables – all topics are addressed fully from the ground up, at a reasonable pace.

Curriculum for the Class

Introduction

Lesson 1: Course Introduction (2:20)

Lesson 2: About the Teacher (3:25)

Lesson 3: How is this Course Organized? (1:50)

Join the Facebook page.

Cleaning of Data

Lesson 4: Data Cleaning Fundamentals and Best Practices (5:12)

Data Types (Lesson 5) (8:01)

Lesson 6: Useful Data Table Shortcuts (3:58)

Lesson 7: Locate and Replace (7:19)

Lesson 8: Trimming, Cleaning, and Working with Spaces (1:58)

Lesson 9: Using the Lower, Upper, and Proper Functions to Handle Cases (2:35)

Lesson 10: Finding information inside data strings (5:08)

Lesson 11: Data extraction with the Left, Right, and Mid functions (11:26)

Lesson 12: Using Substitute and Replace Formulas to Change Data (8:53)

Lesson 13: Text Data Splitting to Columns (3:23)

Lesson 14: Integrating Data (4:42)

Lesson 15: Data Sorting and Filtering (8:59)

Dealing with Null Values in Lesson 16 (9:09)

Duplicates (Lesson 17) (7:07)

Duplicates Formula Method (Lesson 18) (15:40)

Lesson 19: Cleaning data using the IF function and logic (14:27)

Lesson 20: Cleaning Data with VLOOKUP (7:55)

Lesson 21: Spelling Tools and Data Transposition (5:32)

Tables with pivot points

Lesson 22: Pivot Table Fundamentals (5:41)

Pivot Table Fundamentals (Lesson 23) (11:09)

Pivot Table Fundamentals II (Lesson 24) (7:29)

Lesson 25: Data Preparation for Pivot Tables (10:09)

Advanced Pivot Table Features (Filters, Dates, and Calculated Fields) Lesson 26 (10:12)

Lesson 27: Display as a percentage (5:38)

Pivot Table Options (Lesson 28) (5:32)

Lesson 29: Other Applications for Pivot Tables (8:58)

Lesson 30: Correctly extracting data from Pivot Tables (7:57)

Case Studies for Data Cleaning and Pivot Tables

Case Study 1 Part I (Lesson 31) (18:09)

Case Study 1 Part II (Lesson 32) (6:19)

Case Study 2 Part I (Lesson 33) (13:38)

Case Study 2 Part II (Lesson 34) (14:16)

Case Study 2 Part III (Lesson 35) (6:23)

COURSE CONTENT