Learn Google Sheets interactively using openly shared data!
4.3 (27 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
827 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Learn Google Sheets interactively using openly shared data! to your Wishlist.

Add to Wishlist

Learn Google Sheets interactively using openly shared data!

Learn to become a Google Sheets master. Data used is shared, you are encouraged to follow along!
4.3 (27 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
827 students enrolled
Created by Erik Rood
Last updated 3/2016
English
Current price: $10 Original price: $25 Discount: 60% off
1 day left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1.5 hours on-demand video
  • 3 Articles
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Use Google Spreadsheets at an intermediate/advanced level
  • Format data, create charts and tables
  • Quickly manipulate and clean data to uncover trends and communicate actionable insights
  • Link data across multiple interdependent Google Sheets using ImportRange
  • Display stock price information and set up a portfolio tracker
  • Much, much more (think helpful tips + shortcuts + best practices)
View Curriculum
Requirements
  • You will need to have a Google account to follow along
  • Brush through the lectures before signing up, and if you're already a Google Sheets master (i.e. you are proficient with pivots, index/match, lookups, importing ranges) then this course may not be for you
Description

Save time at your job and build marketable skills by mastering Google Sheets. This course takes you from beginner level to intermediate/advanced (think industry analyst level). You'll learn how to:

  • Create, name, share sheets
  • Format sheets, best practices
  • Conditional formatting
  • Filter and sort data
  • Use TEXT functions to clean/manipulate data
    • CONCATENATE, LEFT, RIGHT, MID, SUBSTITUTE
  • Use basic math functions, including
    • SUM
    • AVERAGE
    • COUNT
    • MIN
    • MAX
    • ROUND
  • More advanced math functions
    • SUMIFS
    • COUNTIFS
    • AVERAGEIFS
  • Create and customize charts
  • Pivot tables
  • Lookups
    • VLOOKUP, INDEX/MATCH
  • Use logical functions such as IF, OR, AND
  • Use INDIRECT and pair with INDEX/MATCH
  • Link data together from multiple spreadsheets using IMPORTRANGE

This course will give you you everything you need to know about Google Spreadsheets, an increasingly important tool and rapidly growing in the enterprise sector.

Additionally, this course will provide you with the example sheets covered, so you can follow along! How fun :)

Who is the target audience?
  • This course is for all levels, I'll cover the entry level stuff up front and progress down in difficulty, so those who are more advanced can skip to lectures of interest
  • Anyone who is looking at being an analyst or simply wants to improve their skill with Google Sheets or Excel
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 20 Lectures Collapse All 20 Lectures 01:42:12
+
Introduction
2 Lectures 03:24
My background, high-level course offerings, and target audience.
Preview 03:22

The attached article contains the link to the data shared in the course (shared via Google drive)

Dataset location (download the data covered in the course and follow along!)
00:02
+
The Basics
3 Lectures 14:47

In this lecture, we cover the basics of creating a Google Spreadsheet, naming it, and sharing it with others.

If you're already comfortable with this portion feel free to skip down to the more advanced topics.

Creating, Naming, and Sharing Sheets
02:01

In this lecture, we cover the basics of formatting a Google Spreadsheet:

  • Changing text/background cell colors
  • Adding/removing borders
  • Add/remove gridlines
  • Freezing rows/columns

If you're already comfortable with this portion feel free to skip down to the more advanced topics.

Dataset(s) used:

  • McDonald's Menu - Nutrition + Prices
Formatting capabilities and best practices
08:23

Here I'll show you how to automatically format cells in large tranches of data to quickly uncover trends using both built in and user defined formatting rules.

If you're already comfortable with this portion feel free to skip down to the more advanced topics.

Dataset(s) used:

  • McDonald's Menu - Nutrition + Prices


Conditional formatting
04:23
+
Sort, Filter, and Clean Data (beginner level)
4 Lectures 23:30

Showing you how to filter data, both by filtering out specific values and setting a condition.

Using data covering McDonald's nutrition facts and price prices in an eventual effort to uncover the cheapest/most expensive items on a price per calorie basis. Data is stored in shared folder from Introduction, so feel free to follow along!

Dataset(s) used:

  • McDonald's Menu - Nutrition + Prices
Filtering and sorting data
02:54

Find and replace is used to both search spreadsheets and replace with an input of your choice. Tool can also be used to search formulas within your spreadsheet which can come in handy.

Using data covering McDonald's nutrition facts and price prices in an eventual effort to uncover the cheapest/most expensive items on a price per calorie basis. Data is stored in shared folder from Introduction, so feel free to follow along!

Dataset(s) used:

  • McDonald's Menu - Nutrition + Prices
Find and replace, revision history
07:07

Covering some of the more basic text formulas used for cleaning up data (removing unwanted spaces, joining fields together):

  • CONCATENATE
  • LEFT
  • RIGHT
  • MID
  • SUBSTITUTE

Using data covering McDonald's nutrition facts and price prices in an eventual effort to uncover the cheapest/most expensive items on a price per calorie basis. Data is stored in shared folder from Introduction, so feel free to follow along!

Dataset(s) used:

  • McDonald's Menu - Nutrition + Prices


Cleaning data (LEFT, RIGHT, MID, SUBSTITUTE, CONCAT)
07:51

Covering some of the more basic math functions, including:

  • SUM
  • AVERAGE
  • COUNT
  • MIN
  • MAX
  • ROUND


More advanced mathematical functions using logical operators can be found in "Pivots, Lookups, Logicals" section!

Dataset(s) used:

  • McDonald's Menu - Nutrition + Prices

Preview 05:38
+
Charts, charts, and more charts! (beginner, but good refresher for most)
2 Lectures 16:43

In this lecture, show you how to customize and create a variety of charts, including:

  • Column
  • Bar
  • Line
  • Combination (column + line)
  • Scatterplot
  • Stacked bar

Dataset(s) used:

  • Shark Tank Deals - Dataset containing deal statistics from the TV show Shark tank
Creating and customizing charts
15:30

Here (again using the Shark Tank data) I'll show you how to publish an interactive version of your chart to a stand alone web link, or embed it in a web page (blog, etc)

Dataset(s) used:

  • Shark Tank Deals
Publishing charts
01:13
+
Pivots, lookups, logicals (faster-paced, higher-level topics)
6 Lectures 40:15

Here you'll learn to:

  • Create pivot tables to quickly summarize information + uncover trends
  • Apply filters and manipulate the views

Dataset(s) used:

  • Caffeine Content - contains the amount of caffeine and half life (how long the caffeine lasts) for several popular beverages

Pivot Tables
05:04

In this lecture, I'll show you how to use the most popular LOOKUP functions to search data ranges and return specified values.

Dataset(s) used:

  • Caffeine Content - contains the amount of caffeine and half life (how long the caffeine lasts) for several popular beverages

Lookups (VLOOKUP, INDEX/MATCH)
11:02

Intro to building logical functions using IF, OR, and AND to create rules and create true/false test cases to manipulate data.

Dataset(s) used:

  • Caffeine Content - contains the amount of caffeine and half life (how long the caffeine lasts) for several popular beverages


Preview 07:25

Here I'll cover the following math functions:

  • SUMIFS
  • COUNTIFS
  • AVERAGEIFS


Dataset(s) used:

  • McDonald's Menu - Nutrition + Prices
Math functions with logical operators
05:05

Showing you an example of INDIRECT, which returns a cell reference specified by a string and is especially useful for aggregating data from multiple tabs having a similar layout.

Dataset(s) used:

  • Caffeine Content - contains the amount of caffeine and half life (how long the caffeine lasts) for several popular beverages

Pairing INDIRECT with lookups to save time!
07:36

IMPORTRANGE is used to import a range of cells from a specified spreadsheet, meaning you can link multiple, separate, Google sheets together.

Dataset(s) used:

  • Caffeine Content and Shark Tank Data
Linking data from multiple spreadsheets together using IMPORTRANGE
04:03
+
My favorite keyboard shortcuts/time savors (extra)
1 Lecture 00:08

Article contains my most used keyboard shortcuts; these are extremely valuable time savors and well worth putting the effort in to learn for those who use Sheets on a frequent basis.

The shortcuts
00:08
+
Bonus lectures!
2 Lectures 03:27
Further learning resources (links)
00:06

Further learning resources (video)
03:21
About the Instructor
Erik Rood
4.0 Average rating
145 Reviews
1,589 Students
5 Courses
Analyst at Google

Hi! My name is Erik. I currently work as an analyst at Google. I have a strong interest in data visualization and analysis, so the content I publish here will be more or less related to that.

Feel free to reach out via email with any questions, hope you find the courses useful!