Learn Google Sheets interactively using openly shared data!

Name: Learn Google Sheets interactively using openly shared data!
Rating: 4.3 (38 reviews)

Learn to become a Google Sheets master. Data used is shared, you are encouraged to follow along!

Created byE R

Last updated 4/2016

English

What you'll 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)

Course content

7 sections • 20 lectures • 1h 42m total length

What we'll cover, why Google Sheets, and my background3:22
My background, high-level course offerings, and target audience.
Dataset location (download the data covered in the course and follow along!)0:02
The attached article contains the link to the data shared in the course (shared via Google drive)

Creating, Naming, and Sharing Sheets2:01
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.
Formatting capabilities and best practices8:23
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
Conditional formatting4: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

Filtering and sorting data2:54
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
Find and replace, revision history7:07
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
Cleaning data (LEFT, RIGHT, MID, SUBSTITUTE, CONCAT)7:51
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
Math functions - part I5:38
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

Creating and customizing charts15:30
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
Publishing charts1:13
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

Pivot Tables5:04
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
Lookups (VLOOKUP, INDEX/MATCH)11:02
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
Logical functions7:25
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
Math functions with logical operators5:05
Here I'll cover the following math functions:

SUMIFS
COUNTIFS
AVERAGEIFS

Dataset(s) used:

McDonald's Menu - Nutrition + Prices
Pairing INDIRECT with lookups to save time!7:36
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
Linking data from multiple spreadsheets together using IMPORTRANGE4:03
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

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 this course is for:

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

Learn Google Sheets interactively using openly shared data!

What you'll learn

Explore related topics

Course content

Introduction2 lectures • 3min

The Basics3 lectures • 15min

Sort, Filter, and Clean Data (beginner level)4 lectures • 24min

Charts, charts, and more charts! (beginner, but good refresher for most)2 lectures • 17min

Pivots, lookups, logicals (faster-paced, higher-level topics)6 lectures • 40min

My favorite keyboard shortcuts/time savors (extra)1 lecture • 1min

Bonus lectures!2 lectures • 3min

Requirements

Description

Who this course is for: