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

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
Access the newly added content and many new projects from section six (new course, section one) or continue with old content before it is deleted in 1–2 months.
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
Discover core text and value functions in Google Spreadsheet, including search, find, join, and trim, plus converting text to numbers and formatting dates to handle real data cleanly.
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
Master vlookup in google spreadsheet to look up a value, select the range, and return the correct column while handling not found or blank results, using restaurant data as examples.
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.

HR Tracker: Part 19:40
Build an HR tracker dashboard in Google Sheets by creating a new spreadsheet, establishing data validation for team members and departments, and organizing monthly salary, bonus, and leave data.
HR Tracker: Part 25:10
HR Tracker: Part 39:56
Build a basic Google Sheets dashboard for a company, using if statements with ranges to categorize data by departments, compute bonuses and averages, and visualize with charts.

Sales Dashboard: Part 18:32
Build a dynamic sales dashboard in Google Sheets that tracks multi-channel inventory, pricing per channel, and daily sales, refunds, and returns across Amazon, eBay, and retail channels.
Sales Dashboard: Part 214:16
Explore how a sales dashboard tracks inventory in a google sheets, using data validation and list ranges to reflect add, sell, refund, return, and discard actions.
Sales Dashboard: Part 316:28
Build a dynamic sales dashboard in google sheets that computes item price, tax, and delivery, using formulas like match and is number, with data from amazon or ebay.
Sales Dashboard: Part 49:05
Sales Dashboard: Part 516:55

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
Explore sumif and sumifs for conditional totals in dashboards, mastering ranges, criteria, and dynamic sum ranges (entire columns) while handling multiple conditions and relative references.
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
Learn count blank and count unique, including blank cell nuances, then master rounding functions (round, round up, round down, round between) plus rand, rand between, and rank for data analysis.
Text based Functions: Part 112:27
Explore text based functions in Google Spreadsheet, including lower, upper, proper, len, left, right, trim, and concat or concatenate, to clean data, extract codes, and build full names and IDs.
Text based Functions: Part 29:33
Text based Functions: Part 314:34
Master text based functions in Google Sheets, including search versus find, starting at positions, and handling value errors; trim, mid, and right extract dates and build multi-line strings with car/concatenate.

IF17:24
Learn how the if function in google sheets creates condition-based outputs using true/false logic, and, or, and nested ifs, with len examples and iferror for clean dashboards.
Checking Value Type: Part 17:22
Master testing values with Google Sheets is functions such as is blank, is email, is url, and is error; learn to handle spaces, validate formats, and use with if statements.
Checking Value Type: Part 23:27
Explore how is formula, is date, is text, and is number checks reveal value types in Google Sheets.

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
Explore sort and sortn to arrange data by a chosen column using full-column ranges, with ascending or descending order and top-n results, plus transpose, unique, join, and cross-sheet references.
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
learn how array formula consolidates lookups with vlookup to auto populate designations from names, while managing errors and range expansion in google sheets.
Time & Date Functions19:09
Some more useful Functions: Part 114:51
Explore Google Finance functions in Google Sheets, including tickers, price data, currency conversion, and importing data from CSV, HTML, and ranges for real-time cross-sheet updates.
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
Explore startup funding data in a Google spreadsheet, computing total funding, segment insights, and headquarters counts while building a filter-driven dashboard.
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
Learn to create a poster in Google Sheets by listing phones with images and URLs, then generate QR codes via the Google Charts API for each URL.
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
Build a practical HR management system in Google Sheets to track employee details, departments, salary history, current salary, leaves, and payouts.
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: