Google Sheets-Basic to Super Advance with Practical's(15hrs)

Name: Google Sheets-Basic to Super Advance with Practical's(15hrs)
Rating: 4.5 (56 reviews)

In-depth study of Functions, features and everything about G sheets

Created byajay parmar

Last updated 2/2023

English

What you'll learn

This course as on date has 14 Sections - A complete training on G sheets
You will learn everything about Google SHEETS- What are the fundamentals and how it is different from Microsoft Excel application
You will learn Basic, intermediate and advance functions like sum, average, count, sumif,sumifs, countif,countifs,indirect, offset, vlookup,match,hlookup
You will learn text functions from basic to advance including their practical use -Left, Right, Mid, Find,Search,Substitute,Split ,concatenate and Join function
How to share the sheets with others - how to provide read only or read-write rights.
You will be able to do simple, complex data structuring and analayis using Features like Filter, Sorting the data and filter views.
How to use SORT , UNIQUE functions with Filter .
How to use Data Validations, Data Importing, CHARTS, Conditional Formatting, PIVOTS, Date and Time functions
There are quizzes included in the course time to time to help you in getting ready for real data challenges.
How to use one function inside another function and create a long ,complex formulas like vlookup with match function , Index with match functions
How to use mix Text functions with index or vlookups. How to use CountIFs function with lookup functions and any type of combination you can think of.
Create surveys for team and collect data via google forms and take your data analysis onto a new level
How to use Query function and retrieve data like a pro using Select SQL Synatax
How to do WebScraping using Google Functions ImportXML, ImportHTML - Its amazing

Course content

15 sections • 69 lectures • 15h 37m total length

What are we covering - A quick demo11:13
Get Inside the Google Sheet and Start working32:54
What is a Google Sheet and how to install it. Pre-requisite for Google Sheets
What are Sheets, Cells, Columns, Rows and Cell Addresses
Formula bar and Name Box
Constants and Formulas - difference between them
How to give a name to your google sheet
How to save the data
How to delete or rename Google sheet files
How to view them
First and Foremost Rule to learn- Use Dollar sign in locking the formulas28:27
What is a $ sign and its significance in google Sheets
How to lock rows or Columns of your formula using dollar signs
Relative and Absolute referencing
Basic Functions - Let us begin19:34
Learn Basic features like Copy, Paste, Paste Values and their use in Sheets
RANDBETWEEN Function
SUM , AVERAGE , DIVIDE , COUNT, MULTIPLY and PRODUCT Functions
How to give Background color , text color
Important and essential shortcut keys

VLOOKUP And Match Functions42:02
What is a VLOOKUP and why it is called Lookup function
How to use Cell referencing concept in VLOOKUP function
How to follow VLOOKUP parameters and how to use it , when we should use it.
Learn VLOOKUP Limitations and its concepts like left to right direction and what happens if lookup is duplicate
Apply VLOOKUP with in sheets or from one sheet to another. See the difference between two approaches
What is a Match function and why we need this function
What the nature of Match function values - its data types
What is the advantage of using Match function with VLOOKUP
How to write Match inside the VLOOKUP or as a helper column
Know MATCH Function limitations
Practical examples for you
VLOOKUP and Match- Super fun and Versatile formual13:04
How to merge MATCH inside VLOOKUP and make the most powerful and dynamic , flexible function
What is a Trim function and what it does
Example of using TRIM and MATCH with VLOOKUP - It is practical
How to use TRIM in primary and secondary tables with logics and justication of why we doing so.
How to Use VLOOKUP from one sheet to another.
INDEX with Match function - Another super awesome combination31:49
What is an INDEX Function - another lookup family function
Which is better - INDEX or VLOOKUP and why
Advantages of INDEX over VLOOKUP with practical examples
Similarities and dissimilarities between both functions
INDEX without and with MATCH function - life changing knowledge
Learn COLUMN and ROW Function also and how to use them with INDEX together. its Awesome

FIND ,SEARCH.LEFT ,RIGHT & MID Functions10:41
Explore text functions in Google Sheets, including left, right, mid, find, and search, learning how to extract text, determine starting positions, handle case sensitivity, and apply practical exercises.
Questions -Practical use of Functions29:50
More unique and practical examples11:59
SPLIT Text function11:30
Master the split function in Google Sheets to split data by delimiters like hyphen and space, spill results automatically, and combine with substitute for clean multi-column outputs.
Know your Join Text Function8:00

Explore your SORT Feature11:59
How to Use FILTER10:54
More information on FILTER4:43
FILTER Views11:34
Use FILTER as a Function11:34
FILTER with Large and Small functions- Amazing knowledge15:40
Explore how to use filter with large and small functions to extract top three or top five values from data, build dashboards, and link results with conditional formatting.
Use FILTER and SORT in one formula- its fascinating3:06
FILTER Customize Formula option11:42

How to Create Name Range17:18
Learn how to create name ranges in Google Sheets using the name box or data menu, and manage them with name manager for dynamic data lookup.
Data Validation - Complete Tutorial28:48
Create Dynamic Drop Down Using INDEX and Filter10:27
Create dynamic drop-downs in Google Sheets using index with match and filter to display zone-specific employee lists through smart data validation and proper data structuring.
Challenge for you28:07

CountBlank,CountA,Count,CountIF - Let us begin15:17
COUNTIFS- More powerful9:01
SUMIFs and SUMIF11:30
Learn to use sumifs and sumif in Google Sheets to build multi-criteria formulas with if and look up, determining executive or senior executive status based on total.
AVERAGEIF and AVERAGEIFs5:01
Learn how to use AVERAGEIF and AVERAGEIFS in Google Sheets to compute averages by criteria, specify the range, and handle multiple criteria, with relative references for dashboards.
MAXIFs and MINIFs7:57
Learn to apply maxifs and minifs in Google Sheets to identify top and bottom performers by incentive within a date range, using dynamic criteria and date concatenation.
Quiz- Check your Progress11:28
Create unique identifiers for repeated names in Google Sheets by counting duplicates and concatenating with the name, enabling reliable quiz score lookups using lookup, index, and if functions.

Column Charts and detailed discussion on its components20:07
Line Chart to show Trends22:45
Pie Chart - Creation and Objective7:23
Demonstrates how to use a pie chart to show percentage contributions, compare with column charts, and customize slices, labels, and legend for clear data presentation.
Combo Chart - Good for showing Performance vs Target6:25
Learn to create a Google Sheets combo chart that compares performance to target, with the target shown as a line and performance as columns, plus customization tips.
Aggregate function and Table Chart7:13
Amazing SCORECARD Chart9:19
Gauge Charts - Like Speedometer5:42
The Slicers - Game Changer10:44

How to Create a PIVOT - Know from basics22:09
Difference between Grand Total and % Columns Total - Fundamentals7:41
How to use Filters in Pivot and set Dynamic Range12:40
CALCULATED FIELD in Pivots9:43
SLICERS for Pivot Reports6:08
Explore how to use slicers with pivot reports in Google Sheets, including default selections, data range alignment, and creating multiple slicers for separate bullets.
Challenge for you- Create Different Slicers for Different Pivots12:00
Challenge -Make Pivot on different Sheets - its Awesome14:24
How to use Group option in Pivots6:25

Requirements

You need to have internet and gmail id . Nothing else is required.

Description

This course will educate you about how to work with Google sheets. It has 14 sections as on date covering every minute detail about Sheets.
How to begin with Google sheets - from installing to using it.
How to use features like Cut paste, copy paste, insert rows, insert columns, sort data ,filter data
How to insert sheets, delete , rename sheets, protect the data
How to create duplicate copies of sheets or google sheet as a whole
How to share sheets with other colleagues in office
How to set up the language based on your country
How to use Filter Views
Learn Sort and filter functions.
How to lookup the data using VLOOKUP , MATCH , TRIM and INDEX functions
How to join more than one function and make a single large complex formula
How to use LARGE, SMALL function with other functions like FILTER and SORT
How to use logical functions like IF, IFAND, IFOR and new function IFS with the difference between them
How to use Error handlers like IFERROR & IFISERROR. - Use them with other lookup functions
How to use VLOOKUP inside VLOOKUP and infact more than 3 VLOOKUPs in one formula
Know your Date and Time functions
How to use INDIRECT Function - the most amazing and helpful function in sheets
How to define Name range and their practical use in G sheets
How to create drop down and dynamic drop down - two or three level dynamic drop down with the help of INDEX and FILTER
How to restrict data from entering anything you do not want by using data validation features
Practical Quizzes time to time to monitor your preference
How to do data analysis using Charts - Basic to advance - column chart, line chart, pie , doughnut chart, combo chart, scorecard chart, table chart etc.
How to control data using SLICERS
How to summarize data using Pivots and Slicers
Create an Awesome Dashboard which uses above all functions and features.
How to use SQL Query in Google sheets
How to work with websites to import their data into sheets. Webscraping without any programming. IMPORTXML/HTML
Last but not least, there are so many projects for your help to understand how all of this knowledge is used in Google sheets.
24*7 Support is available to answer your queries

Who this course is for:

Students who work with data all the day and create reports and do the analysis on different levels.
Google Sheet is now emerging as a favorite spreadsheet among Data analysts and because it is a free spreadsheet and can update online , it is much in demand than ever before.

Google Sheets-Basic to Super Advance with Practical's(15hrs)

What you'll learn

Explore related topics

Course content

Introduction to Google Sheet Environment4 lectures • 1hr 32min

Your LOOKUP Functions- Forward and Reverse3 lectures • 1hr 27min

Logical Functions - IF & New IFS function - Basic to Advance3 lectures • 37min

Error Handlers - Ignore errors and do meaningful things2 lectures • 42min

TEXT Functions - Helps in Data Extraction, replacements and Finding values5 lectures • 1hr 12min

Sort and Filters - Useful for Data Reporting8 lectures • 1hr 21min

Name Ranges & Data Validation - Data Control and Data Restrictions4 lectures • 1hr 25min

Count & Sum Family Functions - Complex Calculations made Easy6 lectures • 1hr

CHARTS - A wonderful way to visualize your Data8 lectures • 1hr 30min

Excellent PIVOTS- Create Dashboards and Summaries of reports8 lectures • 1hr 31min

Requirements

Description

Who this course is for: