Data Cleaning and Pivot Tables in Google Sheets by Ben Collins

Data Cleaning and Pivot Tables in Google Sheets
Transform messy data into robust, usable datasets and gain crucial insights by mastering Google Sheets' most powerful tool, Pivot Tables.

There's a joke in the data community that goes:

"You'll spend 90% of your time cleaning data, and the other 10% of your time complaining about cleaning your data."

Do you deal with badly formatted data that takes you hours to tidy up, preventing you from making progress on your "real" work?

Do you ever find yourself repeating a process manually, like extracting a zip code from an address?

Ever wondered what the heck a Pivot Table is, and why you should care about them?

This course is designed to help you work with messy, real-world datasets, and it's a fit for beginner-to-intermediate level Google Sheets users.

Through four hours of video tutorials, you'll learn techniques and best practices for cleaning data and preparing it for analysis and reporting -- saving you hours of tedious, repetitive work, and helping you get accurate results for your company.


What is Data Cleaning?

Data cleaning is the process of identifying and correcting errors, fixing incomplete data or dealing with irrelevant data in your data sets.

The goal of data cleaning is to create a consistent, clean data set, which gives you the confidence that any subsequent analysis and conclusions you draw will be accurate and thorough.

Why should you care?

Data cleaning is a crucial first step in the data analysis pipeline, yet one that people often overlook.

If you start with "bad" data -- if your data contains duplicate records for example -- you're inevitably going to end up with "bad" conclusions. You might end up double counting revenue, for example, which could have disastrous consequences for your business down the line.

This training course runs through professional techniques and best practices, using formulas and pivot tables, to clean your data, in a Google Sheets setting. Once you've grasped these concepts, you (and your boss!) will feel confident that your conclusions are based on sound data.
What this course covers

Best practices for working with data in Google Sheets.
All the formulas required for data cleaning.
Tips and tricks, such as shortcut keys, to make your workflow more efficient.
Professional techniques for turning messy real-world data into clean, orderly data sets for your analysis.
Pivot Tables! This course covers Pivot Tables from top-to-bottom. Even if you've never seen a Pivot Table before, you'll soon create cutting-edge ones, using advanced techniques like calculated fields and data extraction with special formulas. I don't think you'll find a more comprehensive course on Google Sheets Pivot Tables anywhere else on the web.
Two in-depth case studies that show all these techniques being used together, in the wider context of a real-world problem.

What this course does not cover:

How to analyze your datasets or how to build dashboard reports (see my dashboard course if that's what you want to learn).
How to use the scripting language, Apps Script, to work with data.
How to create data visualizations or charts from your data.

What you get with this course:

Over 4 hours of video tutorials that walk you step-by-step through techniques for cleaning data and using pivot tables.
Access to all the raw data files used in the examples, copies of the formulas and links to online documentation and other great resources.
Membership to our dedicated Facebook group, where you can post questions and get answers, share insights and interact with other students.
All the videos are online and you'll have 24/7 access for life.

Who is this course for?

Anyone who works with data in Google Sheets -- maybe you're a data analyst, a marketer, an educator, a scientist or someone in between.
Anyones curious to learn more about working with data and Pivot Tables.
Anyone who wants to learn best practices and how to be efficient with data.
Anyone who wants to add new skills that will help you advance your career.

What are the prerequisites?

A Google account.
Access to Google Sheets.
Basic familiarity with Google Sheets and spreadsheets.
No assumptions are made about prior knowledge of functions or pivot tables -- all the topics are covered comprehensively from scratch, at a comfortable pace.


"Before this course I had used a few simple formulas (okay Average and Sum only) but now I know how to use a vlookup as well as create Pivot Tables.

I’ve been making “Pivot Tables” wrong for years, entering all of the raw data myself. I am now able to make a Pivot Table that will update as needed. I like how Ben shows multiple ways to do data cleaning and why you would choose one method over the other. I enjoyed the case studies at the end because they brought the entire course together.

I will definitely be on the lookout for additional courses from Ben."

- Rachelle L.

"The course starts with a data cleaning section which is highly actionable and relevant to real world data sets that we all work with. My pivot table knowledge was pretty basic before this course and I am confident to say that I am now going to be able to implement them into my data analysis workflow.

This course also took my efficiency to the next level which is exactly what I needed as more of my time and analysis is starting to take place in sheets. I can't recommend this enough."

- John R.

"I am an academic librarian who has worked for years with massive raw data sets and pivot tables, and even gives related presentations at our regional conferences, and yet I learned from Ben's course several very useful tips and techniques that I didn't already know about and that will save me a lot of work.

I plan to recommend it to my colleagues as a very thorough course in getting data ready for the kind of data visualization contexts we need to provide to our decision-makers and stakeholders."

- Melissa B.

Your Instructor

Ben Collins
Ben Collins
Who is teaching the course?

Ben Collins is an experienced data analytics instructor and freelance spreadsheet developer, helping businesses better understand their data through analytical insights and visualizations, including dashboards. He works primarily with Google Sheets, Apps Script and Data Studio, and also has extensive experience with Excel, SQL and Tableau. He teaches data courses and workshops for General Assembly in Washington, D.C.

Class Curriculum

Introduction

Preview
Lesson 1: Introduction to the Course (2:20)
Preview
Lesson 2: About the Instructor (3:25)
Preview
Lesson 3: How this Course is structured (1:50)
Start
Join the Facebook group

Data Cleaning

Preview
Lesson 4: Introduction to Data Cleaning and Best Practices (5:12)
Start
Lesson 5: Data Types (8:01)
Preview
Lesson 6: Handy Shortcuts for Data Tables (3:58)
Start
Lesson 7: Find and Replace (7:19)
Start
Lesson 8: Trim, Clean and Dealing with Spaces (1:58)
Start
Lesson 9: Lower, Upper and Proper functions to deal with Case (2:35)
Start
Lesson 10: Searching within data strings (5:08)
Start
Lesson 11: Extracting data with Left, Right and Mid functions (11:26)
Start
Lesson 12: Changing data with Substitute and Replace formulas (8:53)
Start
Lesson 13: Splitting Data with Text to Columns (3:23)
Start
Lesson 14: Bringing Data Together (4:42)
Start
Lesson 15: Sorting and Filtering Data (8:59)
Start
Lesson 16: Dealing with Null Values (9:09)
Start
Lesson 17: Duplicates (7:07)
Start
Lesson 18: Duplicates Formula Method (15:40)
Start
Lesson 19: Using the IF function and logic to clean data (14:27)
Start
Lesson 20: Using VLOOKUP to clean data (7:55)
Start
Lesson 21: Spelling Tools and Transposing Data (5:32)

Pivot Tables

Preview
Lesson 22: Introduction to Pivot Tables (5:45)
Start
Lesson 23: Pivot Table Fundamentals (11:39)
Start
Lesson 24: Pivot Table Fundamentals II (7:41)
Start
Lesson 25: Preparing Data for Pivot Tables (10:10)
Start
Lesson 26: Advanced Pivot Table Features (Filters, Dates and Calculated Fields) (12:14)
Start
Lesson 27: Advanced Pivot Tables Features Part II (Column Headings, Percentage of Total Calculated Fields) (13:12)
Start
Lesson 28: Formatting Pivot Tables (9:00)
Start
Lesson 29: Other uses for Pivot Tables (9:35)
Start
Lesson 30: Getting data out of Pivot Tables the right way (8:13)


Frequently Asked Questions

When does the course start and finish?
The course starts now and never ends! It is a completely self-paced online course - you decide when you start and when you finish.
How long do I have access to the course?
How does lifetime access sound? After enrolling, you have unlimited access to this course for as long as you like - across any and all devices you own.
What if I am unhappy with the course?
I would never want you to be unhappy! If you are unsatisfied with your purchase, contact me in the first 30 days and I will give you a full refund.
Will I get copies of the datasets?
Yes! All the raw data templates are provided, so you'll have your own copies. The raw data file links are underneath the videos for each lecture.
What if I discover a mistake or want to comment on the material?
Great, I'd really love to hear from you. Drop me a line at benlcollins [at] gmail.com
Can you provide an invoice for my company?
Yes, of course! After the purchase is complete, please forward me your receipt and the name/address you'd like to see on the invoice and I'll send one right back to you.
Why am I being charged VAT?
In order to comply with EU regulations, buyers in the EU will be charged VAT at checkout. This money is passed on to the relevant EU tax collection agency.