Google Sheets from Beginner to Advanced

Name: Google Sheets from Beginner to Advanced
Rating: 4.1 (176 reviews)

Manage your work and life with Google Sheets

Created byTony Hat

Last updated 3/2021

English

English [Auto],

What you'll learn

Understanding of Google Ecosystem management
Build a solid understanding on the Google Sheets Basics
Create amazing visualisation with Google Charts
Link sheets to Internet and Web data to the Sheets
Creating dynamic reports with Pivot Tables
Manage Surveys by using Google Forms
Master the data by Lookup, IF, SUM & FILTER, GETPIVOTDATA and others..
Analyze Data with Google Queries
Automate your work by Macros and modify it by Scripting

Course content

7 sections • 40 lectures • 4h 57m total length

Introduction3:23
Master Google Sheets from beginner to advanced, visualize data with charts, link data from the internet, manage surveys with forms, and automate tasks with macros and scripts for dynamic reports.

4.1.1 Create new spreadsheet9:07
Define spreadsheet title
Moving and Copying Data in the worksheet
Share spreadsheet
Inserting and Deleting Rows and Columns
Changing the Width and Height of Cells
Hiding and Unhiding Rows and Columns
Work with the versions
4.1.2 Organize sheets4:50
Add sheet
Delete sheet
Rename sheet
Duplicate sheet
Move sheet
Change sheet color
Hide sheet
Unhide sheet
4.2.1 Organize Data: Basic formatting7:39
Copy and paste data
Create Header
Use Text wrapping
Format text and fill colors
Freeze rows and columns
Use borders
4.2.2 Working with data formats11:41
Set Number/Text (Order number to text)
Set Date formats
Set currency formats
Copy the formats
4.2.3 Basic formulas and cell references6:39
Hide unimportant columns
Prepare lines for formulas
Use basic formulas (+)
Use simple cell reference
4.2.4 Data filtering9:33
Create new filter
Filter by values
Filter by conditions
Save filter
Use multiple filter view
4.3.1 How to use basic formulas13:09
Use the calculations SUM, AVERAGE, COUNT, MAX, MIN
AutoFill to Copy functions
Absolute versus relative reference (A$1$ v A1)
4.3.2 Managing the formulas7:13
Get functions Syntax and Samples
Write the formula (SUMPRODUCT)
4.3.2.1 Show the colors and formula syntax
Get the formula help
Solve the issues
4.4.1 Conditional formatting14:51
Merging and centering cells
Conditional formatting for numbers
Conditional formatting for dates
Conditional formatting for numbers by using color scale
Conditional formatting by using Custom formula
Prioritize formats
4.4.2 Filtering Data9:49
Create filter
Filter by text (Orders)
Filter by Date (Schedule Ship Date is before 12/31/2017)
Filter by price (price is greater than $150)
Multiple filters (filter in two columns)
Create filter views
4.5.1 Inserting Images7:34
Add image by inserting URL
Use “IMAGE” function
Add image from Drive
4.5.2 Gsheet Drawings7:12
Create flowchart / orgchart
Format the Drawings
Downloading the drawings
4.6.1 Creating a Column Chart7:58
Define the Data for the chart
Insert chart
Customize the chart
4.6.2 Modify and Publish the Chart5:27
Modify the series
Publishing the Charts
Modify the Chart Titles
4.6.3 Charts Auto creation5:49
Auto Create the charts
Modify the charts
4.6.4 Sparklines charts3:20
Explain Sparklines - showing trend
Create Line Sparkline chart - open the function
Create Column Sparkline chart
Modify the colors
4.7 Publishing and Printing Gsheet Worksheet5:45
Publishing one sheet
Stop publishing
Publish whole Spreadsheet
Viewing your Document in Print Preview
Changing the Margins, Scaling and Orientation
Adding Header and Footer Content
Printing a Specific Range of Cells
4.8 Working with Sheet Templates4:38
Intro to Gsheet Templates
Opening an Existing Template from menu
Use the Add-ons templates to get professional styles

5.1.1 Import Data to GSheet8:32
Importing Data from Text Files (*.csv)
Importing Data from Excel
Importing data from Google Drive (excel in Drive)
Getting data from web (IMPORTHTML
5.1.2 Export Data from GSheet4:44
Export the sheet to different formats
Publish the sheet: Link (keep linked} or embed (no linked)
Export Google sheet from Drive
5.2.1 Splits, Duplicates, Comparisons by Add-ons9:22
Add-ons introduction
Power Tools
Split the names
Split by position (Country)
Duplicate the sheet, make changes and compare two sheets
Find Duplicated rows
5.3 Using Gsheet Forms6:02
Define the Forms
Review the Responses
5.4 Gsheet Database Functions10:11
Gsheet Function: DSUM()
Gsheet Function: DCOUNT()
Gsheet Function: DAVERAGE()
Gsheet Function: SUBTOTAL()
5.5 Gsheet Data Validation4:39
Creating and practicing Validation Rules
5.6.1 Create basic Pivot Table8:25
Take suggestion from Explore (Use more..)
Using suggested Pivots
Creating Pivot Table
Formatting Pivot Table Data
Filtering Pivot Table Data
5.6.2 Enhanced Pivot Features8:09
Grouping Pivot Table Data
Expand / Collapse the Data
Creating Pivot Charts
Pivot Calculated Fields
5.7.1 Consolidating Data from multiple sheets5:31
Using the Freeze Panes Tool
Consolidating Data from Multiple Worksheets
5.7.2 Consolidating Data from multiple Worksheets5:26
Linking Worksheets by function IMPORTRANGE

6.1 Working with Conditional Functions8:14
Using IF() Function
Nesting Functions
Using COUNTIF() Function
Using SUMIF() Function
6.2.1 Lookup functions13:09
Using LOOKUP() Function
Use absolute reference for data range
Transpose the data to sheet “b”
Using HLOOKUP() Function
Using IFERROR() Function
Use Conditional formatting to identify “Not found” results
6.2.2 Other Lookup Functions9:42
Using INDEX() and MATCH() Functions
Using GETPIVOTDATA
6.3 Working with Text Functions9:16
Using Gsheet's LEFT(), RIGHT() and MID() Functions
Using Gsheet's LEN() Function
Using Gsheet's SEARCH() Function
Using Gsheet's CONCATENATE() Function
6.4 Protecting Data and Sheets3:51
Protecting Data Range in a Worksheet
Protecting the whole Worksheet
6.5 Retrieving Data by Query Function9:36
Using query for easier way to analyze data
Practicing various Queries
6.6 Automating Repetitive Tasks with Macros6:56
Understanding Gsheet Macros
Creating a Macro with the Macro Recorder
6.7 Simple Scripting6:58
Modify the Macros

Requirements

PC, Chromebook or other device to work with Google Sheets
A Google or Gmail account

Description

This course will lead you from Basic to Advanced level. You will be able to manage your data, task, analysis, reports, queries, and other activities easily. You will learn great tools as a Pivot Tables and Vlookup Functions. By mastering the Queries you can see how Google Sheets can be better than Excel. You will also learn how to work with Adds-on templates.

The course contains practical training assignments so you can develop your knowledge by practice. Links to the Data, Assignments & Results are available in Bonus Section #7.

**Include Google Sheets May 2018 Updates**

Who this course is for:

Anyone who wants to improve the Google Sheets knowledge
Anyone who wants to enhance own life with this tool

Google Sheets from Beginner to Advanced

What you'll learn

Explore related topics

Course content

1 Introduction1 lecture • 3min

2 The Best approach to Google Sheets Training1 lecture • 6min

3 Google Ecosystem1 lecture • 6min

4 Google Sheets Beginners18 lectures • 2hr 22min

5 Google Sheets Intermediate10 lectures • 1hr 11min

6 Google Sheets Advanced8 lectures • 1hr 8min

7 Bonus section1 lecture • 1min

Requirements

Description

Who this course is for: