Google Spreadsheet: From Level Zero with 18 Projects (2023)

Name: Google Spreadsheet: From Level Zero with 18 Projects (2023)
Rating: 4.6 (319 reviews)

Start from Scratch and learn how to create dashboards

Highest Rated

Created byShahbaz Haidar

Last updated 3/2023

English

What you'll learn

Get to know the elements of Google Spreadhseet
Format the text and cell to add aesthetic sense
Arranging Rows and Columns for easy navigation
Sharing the Spreadsheet with others for collaboration
Using powerful functions to get desired result
Perform various operations like concatenate, replace, substitute on bulk data
Combine multiple functions to get more complex result easily
Creating a dashboard to handle restaurant details
Create an HR tracker that can be used in any office
Creating Sales Dashboard
Using a set of data to create any dashboard with visual appeal

Course content

11 sections • 73 lectures • 14h 17m total length

New Course Content1:09
Introduction to the Course2:29
Data is the present and the future. Most of us have an assumption that handling data and creating reports, tracking processes is a difficult task. My motive of this course is to bust this myth.
Google spreadsheet is a very handy tool to track all the processes, progresses and plans. It is very easy to learn and very interesting to implement.
By the end of this course, you will realize that the dashboards and reports you have been looking at, made by others, were so easy to make and so awarding to follow.
Automation of data is yet a great benefit of using google spreadsheet because it allows multiple stake holders to contribute at one place.
Welcome to the journey !!!!
Getting to Spreadsheet and Text Formatting10:07
Starting from level zero, we will create a gmail account and passing through Google Drive, we will land on to our first spreadsheet.
We will also look at options available for text formatting, like bold, italics, background color, font color, size etc.
Advance Formatting10:55
After completing this video, you would be able to format text on the spreadsheet like changing font size, color, font, back ground color etc. This will help you in designing the report or dashboard, so that it is user friendly and soothing to the eyes.
Cell Freeze, Filters and Data Format11:15
Filter and data formatting, are tools for viewing data in an ordered way. For example, if you have 10,000 rows of data, that has list of schools in your country. Now if you want to see list of schools that are in your city, instead of going through entire list of the country, you will add filter of your city, and the list will now show schools of your city only.
Knowing about Google Spreadsheet

Sum, Sumif and Sumifs14:02
Going forward, you will be learning about different functions available for use in google spreadsheet. If you have knowledge of Microsoft Excel, learning Google spreadsheet would be very easy as 90% of the functions are same.

In this video, we cover three functions:

SUM() : This enables you to find sum of numbers, series of numbers or combination of both.
SUMIF(): When you want to find sum of a series of number based on a condition, you use SUMIF. For example, from a sheet containing all employees' attendance of a company, you want to see number of leaves for Finance department.
SUMIFS(): When you want to find sum of a series of number based on more than one condition, you use SUMIFS. For example, from a sheet containing all employees' attendance of a company, you want to see number of leaves for Finance department, who are Male.

These are widely used functions in reporting and dashboard creation.
Average, Avergaeif and Averageifs7:43
This lesson covers three functions:
AVERAGE()
AVERAGEIF()
AVERAGEIFS()
All the above functions works exactly the same way as SUM, SUMIF and SUMIFS, except, the result are averages in this cases.
It is the Quiz Time: SUM & AVERAGE
Sharing and Making a Copy3:47
Since we are learning on an actual sheet, I decided to provide you that sheet for practice. So this lecture will tell you how to share sheets with other and how to use sheets shared by others. Since many students would be accessing this sheet, I have shared as "View Only". You will make a copy of the sheet and use it for learning purpose. All the steps for how to do it, is covered in this lecture.
Count, Counta4:51
Coming back to functions, in this video, we will cover two variations of a function that enables you too count number of elements in a range.
COUNT: This counts only the numeric value in a range
COUNTA: This counts numeric as well as Text elements in a range.
Concatenate, Left, Right and Len9:20
Now we will start learning function that help you to edit strings. In this video, we have four such functions:
CONCATENATE: This is used to join more than one string to form a single string
LEN: This is used to find length or number of characters present in a string
LEFT: This enables you to extract certain characters from left of a string
RIGHT: This enables you to extract certain characters from right of a string
Replace, Substitute, Find and Split7:26
We will learn four functions in this video:
REPLACE: This helps you to replace certain number of characters in a string with a new set of characters or sub string
SUBSTITUTE: This replaces a defined sub-string in a cell to a new sub-string
FIND: This returns the character position of a sub-string in a string
SPLIT: Splits a long string a cell separated by delimiters to multiple strings in separate cells
Let us Recap
Search, Join, Text and Value9:44
Countblank, Sqrt, Floor, Rand and Randbetween7:39
Explore key Google spreadsheet functions countblank, sqrt, floor, rand, and randbetween, with hands-on examples for counting blanks, generating random numbers, and applying range and unique value techniques.
Floor, Ceiling, Subtotal, Count and Countifs7:51
Explore floor and ceiling functions in Google Sheets, learn how to use subtotal, count, and countifs, and apply range-based averages with practical examples.
if10:16
IF will allow us do multiple calculations based on conditions.
And, Or5:47
Not, IsBlank5:48
Explore how not and isblank work together in Google Sheets to test blank versus nonblank cells, using isblank, not, and length checks.
IsEmail, IfError4:36
Learn google spreadsheet logic with isemail to validate emails and iferror to gracefully handle errors, while using blank handling and basic lookup concepts.
Vlookup11:16
Quiz Time
Match and Index7:41
Learn how match and index work together to locate positions in a range. Use index to retrieve values from rows or columns in one- or two-dimensional layouts.
Filter, Sort and Unique10:58
Learn to filter data by multiple conditions, sort results, and extract unique values in Google Sheets using functions to build dashboards.
Date4:21
Master date and time functions in Google Sheets, such as day, month, year, now, and minute second, and apply them to build a mini dashboard from company data.

Basic Mathematical Functions (SUM, PRODUCT, DIVIDE etc)21:28
Learn core Google Sheets functions such as add, sum, product, divide, quotient, and mod, plus the distinction between average and averagea for numeric and non-numeric data.
Operation with Numbers18:52
Explore core number operations in Google Sheets, including abs, large, small, odd/even, floor/ceiling, round, count/counta, max/min, and mastering relative versus absolute cell references.
SUMIF & SUMIFS13:07
COUNTIF & COUNTIFS4:18
Learn how to use countif and countifs in Google Sheets to count cells by single or multiple criteria, ensure equal length ranges, and interpret the results.
AVERGAEIF & AVERAGEIFS6:11
Explore how to use averageif and averageifs, plus maxifs and minifs, to compute conditional averages and extremes with ranges, criteria, and sum or average ranges.
COUNTs and ROUNDs16:45
Text based Functions: Part 112:27
Text based Functions: Part 29:33
Text based Functions: Part 314:34

ROW, COLUMN and ADDRESS12:49
Learn how ROW, COLUMN and ADDRESS work in Google Sheets, including absolute and relative references, locking with dollar signs, and building robust cell references for data analysis.
INDEX & LOOKUP11:35
Explore index and match fundamentals before vlookup, using ranges and zero for exact results, while mastering vlookup and hlookup for vertical and horizontal lookups.
SORT, TRANSPOSE & Others11:53
FILTER15:32
Learn to use filter and array formula in Google Sheets to filter data by multiple criteria, applying and/or conditions, with ranges and curly brackets.
ARRAYFORMULA11:44
Time & Date Functions19:09
Some more useful Functions: Part 114:51
Some more useful Functions: Part 26:00
Explore how to use hyperlink and image functions in Google Sheets to link websites, display images, and manage URLs with labels, hover popups, and sizing options.

Project 1: Invoice14:22
Project 2: Sales Dashboard24:54
Create a sales dashboard in Google Sheets, using a data sheet with dropdowns for lead source, salesperson, and industry, plus monthly counts, revenue, charts, and conditional formatting.
Project 3: Startup Funding Analysis19:44
Project 4: Creating Documents from Data25:08
Create data-driven contract documents from Google Sheets by using VLOOKUP, date formatting, and templates that adapt to buyer, seller, and property details.
Project 5: QR Code7:08
Project 6: Google Forms & Pingpong Scorecard23:51
Learn to build a live ping pong leaderboard by capturing results with Google Forms and compiling them in Google Sheets using array formulas, unique players, and a sortable score dashboard.
Project 7: Client Tracking Dashboard28:12
Build a Google Sheets client tracking dashboard for hotels, with dropdowns for account managers, coordinators, and hotel types; track revenue and automate insights with helper sheets, pivot tables, and charts.
Project 8: Commission Calculation & Payout Management System17:32
Master a profit-based commission payout system in Google Sheets, calculating revenue, cost, and profit percent to assign tiered commissions and track payouts on a dashboard.
Project 9: Create a CRM13:57
Project 10: Student's On-boarding System16:14
Design a student onboarding system in Google Sheets that tracks steps, current and next stages, with checkboxes, color coding, and data formulas like filter and transpose.
Project 11: Formatting Data10:54
Transform a multi-row company dataset into one row per company using Google Sheets formulas such as IF, FILTER, and VLOOKUP, and preserve LinkedIn profiles in the consolidated output.
Project 12: HR Management System25:02
Project 13: Financial Modelling14:43
Project 14: Attendance System14:10
Build a monthly attendance system in Google Sheets with names, IDs, dates, and present, absent or late status using dropdowns or checkboxes. Apply formulas and conditional formatting to highlight status.
Project 15: Multi user Task Management15:30
Explore import range to centralize task management, routing tasks from a master sheet to per person sheets, with status tracking, unique IDs, and indirect and vlookup style lookups across sheets.
Project 16: Gantt Chart10:51
Project 17: Expense Reconciler14:31
Project 18: Personal Finance Planning8:24
Explore a Google Sheets project that models personal finances with assumptions and predictions, forecasting expenses, inflation, and investment returns, aligned with age milestones and retirement planning.

Requirements

You should have a basic understanding of English language as the course uses this language
You should have understanding of day to day maths like addition, subtraction etc.
A thirst to learn new things and improve your personality.

Description

Since I am a full time Google Spreadsheet consultant, with clients all over the world, I know what are the things that are important and should be taught to students.

Google Spreadsheet is on of the most powerful tool developed by Google. It has such a vast use that almost any body in any field, can use it for some thing or the other. If you are a student, you can use it to record your readings of experiment, if you are a data analyst, you can do all analysis and automate it here, if you are a marketing or sales guys, you can have your own mini CRM set up in google sheet. The fact that it is online, adds a huge value as there is no need of having a special software and you can work on your data from any part of the world. Above all, it is free.

Google Sheet uses most of the function and formula used in Excel. So if you have worked on excel, it is a big advantage. However, for this course, prior knowledge of excel is not must, as I will start the course from level zero.

The target of this course is not to make you a spreadsheet genius, or to memorize you all the function, but to enable you to make tools that you can actually use to solve the surrounding problems. To make use of any tool or language, you do not need to memorize each and every feature of it. But all you need to know is what are the possibilities and how to google the answers. This course will focus on the same.

This course will be your starting point to be an automation expert, because automation is the thing in present world, and maximum efficiency of a process is achieved only if it is automated.

Here are some feedback from students, that describes the philosophy of this course:

"the course, is very helpful if you go for job in pvt sector these all things will help you these are very usefull and you can make your work easy by using these formulas in sheet. and save time also so thanks to udemy for this really appreciate you."
"The instructor goes over a lot of detail in great detail and doesn't leave anything out. I appreciate how he stays on topic and doesn't deviate from the lesson ."
"Learning stuff I never knew I could do. This could help me so much"
"I have taken many courses on Spreadsheets, And this one by far is the best I have taken. A++++"
"Very informative. I have learned alot of new things. I understand how dashboards are are made for presentations."

Who this course is for:

If you are a student and you want to add another skill set in your resume, this course is for you. Having relation with data and knowing how to use them, gives you an edge always.
If you are a working professional and and has some messy data and number of unrelated spreadsheets, this course will help you clean those mess and organize the way you work.
If you are a manager, this course will help you to handle your team more efficiently, as you can create spreadsheet and share with your team and ask them to update. You get all data at one place in real time and your own dashboard will give you all that you need.
If yo want to be a freelancer, this is a good place to start with. There is huge demand of google sheet experts and companies need them to fix their internal mess as well as data analysis.

Google Spreadsheet: From Level Zero with 18 Projects (2023)

What you'll learn

Explore related topics

Course content

Introduction to Google Spreadsheet5 lectures • 36min

Function and Formula17 lectures • 2hr 13min

Dashboard 13 lectures • 31min

Dashboard 23 lectures • 25min

Dashboard 35 lectures • 1hr 5min

[New Course] Section 1: Introduction1 lecture • 2min

[New Course] Section 2: Basic Functions9 lectures • 1hr 57min

[New Course] Section 3: Conditional Functions3 lectures • 28min

[New Course] Section 4: Advance Range based Functions8 lectures • 1hr 44min

[New Course] Section 5: Projects18 lectures • 5hr 5min

Requirements

Description

Who this course is for: