This content provides a practical guide to essential Google Sheets features, covering basic formatting, data cleaning, cell referencing, lookup functions, text manipulation, logical operations, conditional aggregation, and automation with macros, explaining their utility for real-world data management and analysis.
Mind Map
Click to expand
Click to explore the full interactive mind map • Zoom, pan, and navigate
Hi everyone, welcome back to the
channel. Today we are going to learn
some of the most powerful features in
Google Sheet. Everything from basic
formatting to advanced formulas like
VLOOKUP and macro. Instead of just
showing you how they work, I will
explain why each feature is important so
you will know exactly when to use in
real life. Let's dive in.
While working on documents, especially
spreadsheets, basic setup and formatting
is important because clean and well
formatted data is easy to read and it
prevents mistakes later when you'll
analyze or even share with your friends.
So now let's try basic formatting in
practice. Let's look at this table. So
before to start working with a tables,
especially with a big data set, you have
to remove all duplicated data. To do so,
you may highlight your whole data by
pressing Ctrl A or command A on your
keyboard. Go to data, data, clean up,
remove duplicates.
Another step, if you need a more space
for specific columns or for the rows,
you may go between two columns like A
and B, resize it
or between second and third rows to give
a more space.
And one more. So if you'd like to apply
the styling, you may again highlight
your whole table content, go to format
colors, and then choose the one style to
apply coloring options on your table.
And the one more step, it's about
phrasing the rows and columns. For
example, my table has a lot of rows. If
I will scroll it down, I cannot see all
time in the header. It's important to
see the header to understand what's data
given for us in that row or cell. So to
do so we have to again highlight this
given header of the table. Go to view
freeze select here one row. Now if I
will scroll down anytime I could see the
first header in my table. The same you
can try with the columns. For example I
would like to freeze the first and
second columns. Go to the view freeze
two columns. Now if you will scroll to
the left or to the right every time you
may see A and B. The best way to sort a
table in a proper way is to insert the
filters. Highlight the header filter and
then for example let's say I would like
to sort my table based on items. Let's
go to item. Select here sort in
alphabetical order.
Let's learn those cell references in
Google Sheets or in Excel. We have a
three types of cell references which are
relative, absolute and mixed cell
references. When you write a formulas in
Google Sheets, the way cells are
referenced affects how the formula
behaves when you copy to the other
cells. Let's start from relative
references. By default in Google sheet
on Excel, cells are in relative
reference. Relative reference means it
adjusts automatically when you copy the
formula to other cells. Let's see an
example. We have a different items in
the table. The units, the unit cost. So
my task is to calculate the total price.
To do so, I have to put equal sign take
the address of units multiplied by unit cost.
cost.
to do the same steps for the rest of the
items. I have to copy down the formula.
By double clicking the output, you can
see how relative references work.
Now we will try to look at how absolute
references work. An absolute reference
does not change when you copy the
formula. Let's say this is the year 2025
pencil price 100 instead of previous
prices. And then I have to take first
only pencils
filter. Let me take only pencils.
Let's imagine that for every pencil the
price should be same 100 ting units are
same only price has changed. Let me
calculate once again total
equal to let me take the address of unit
multiplied by price. Enter. and let's
copy down.
So I got a dash symbol. Sometimes your
Google sheet may return zero. So why in
the previous example it was okay but not
in the second example. So if you double
click the output you could see the
formula multiplies E29 which is the unit
address multiplied by K29 which is empty
cell. That's why we didn't get the
proper result. when you'll copy down the
formula, you will also copy down the
cells. So therefore, we have to change
the address K25 into absolute one by
putting dollar symbols before K and
before 25. It means you are phrasing the
given cell by column and row. Let's copy
down formula one more time.
Now we got the correct answer.
This is the way how absolute references work.
work.
Another function that we have to learn
it's about VLOOKUP and HL lookup. When
you have a large data set manually
searching for information is impossible.
V look up and hook up finds the data
instantly. Let's look at the small table
about students. We have the ID, name,
city and birth date. The birth date
column is empty. So our task is to fill
out D column considering another sheet
where we have students ID and birth date.
date.
Copying and pasting the birth dates it's
not a solution since the ID in this
table and on another table are
different. It means the order of ID
numbers has been mixed. The solution is
VLOOKUP since we have to look for
information vertically. So we have to
put the equal sign type the function
VLOOKUP. So VLOOKUP accepts four
arguments. The first argument is search
key. the search key it's the same data
between your two tables this table and
this table so as you could see ID is the
same data so as a first argument I'm
going to give the address of first
student's ID number
then it says range so where I have to
look for this ID so the range is a and b
columns comma and then it says index
okay Google sheet with the help of v
look up already found the same that I'm
looking for and then we have to give
what we need to get by this ID. So we
need to get the birth date which is B
column but you cannot indicate the
column B instead we have to put the
indexes index it means the order of the
column for example ID it's the A column
column number one B it's the column
number two which means index number two
comma and then we have to specify false
false it means one ID one birth date enter
enter
so I got the result now so let's check
is that correct one or not. So the ID
This is here
and the result is here. So if you'll
change data from the second shade for
example, let's say it's not it has to be
not 18th, it should be 17.
The first might table already updated
because these two are already
interconnected with each other. So to
apply the same vocal function for the
rest of students, we have to just copy
down. Another function that we have to
learn is about concatenate. Concatenate
is used to combine different data from
different columns into one. In our
example, we have a full name, surname,
name, father's name. So my task is to
combine these three together. As usual,
we have to put equal sign. Then take the
address of surname with the amperand
symbol which is shift 7 name the same
symbol and father's name. So now the
last step that I have to fix it is to
put space between surname and name and
name and father's name. To give a space
I should open quote symbols put space
between these two and don't forget to
put amperand. It has to be between each
of the objects.
In order to convert the text into
uppercase format, we should use a
function upper, select the address,
press the enter or vice versa, lower,
And the last task here is how to convert
the names and father's name into short
name format. Equal sign. Let's take the
address of surname
and percent and then we will learn new
function now which is left B. Left B it
means you're going to take the letters
from the left beginning side. So left B
takes two arguments. First one is a
string. String means the address of
name. So I will give the address of Timan,
Timan,
and then it says number of bytes which
is how many letters do you need to
return? I need only first letter from
the left side. Let's press the enter. So
I got the correct result. So I should
And the last step it is to give a dot
Let's learn the logical functions which
are if and or. These functions help you
automate decisions, analyze data faster,
or even organize text without doing it
manually. For example, we are going to
consider the table about students and
their scores. Students is going to pass
the test if his or her score is greater
than or equal to 75. So we will use the
function if if takes three arguments.
First one's logical expression which is
score has to be greater than or equal to 75,
75,
and then a value if it is true. If it is
true, we're going to return pass. So
since it's the string, it has to be
between quote marks. Once again, quot
symbol value if it is false. Fail.
Great. Another example. We have now the
same students scores for the test.
However, instead of one test, we have
two tests. The same in order to pass
both of the test results should be
greater than or equal to 75. Let's again
try if function. So we'll start
completing the first test result
However, if can accept only one logical
expression since we have a two we should
True value it is pass.
Okay, one more step. Uh this is the same
as in the previous example. However, we
have a three test results the same. If
since we have a three, we can
automatically put the end greater than
or equal to 75.
Another one.
Okay, let me autofill.
So the question is for you.
The second student his test results
three of them are greater than 75.
Why the students result was fail? Why
not pass? Try to think. Another function
is about sum if count if average if. In
our previous lectures you learned how
does sum work max mean. So today we will
add one condition. If means you have a
one condition. If it says sum if s it
means multiple conditions. So we have a
table about products. We can see the
categories. We can see the sales income
date and so on and so forth. So my task
is to calculate what was the amount of
sales income for all bananas products.
So we don't need to sit and take only
bananas with a filters. Instead we have
a function sum if
sum if takes three arguments. First one
is a range range where we have a
criteria. Since banana is a column B as
a range I will highlight this column. If
your table is a big one in order to
highlight the whole column you can press
the keyboard control shift down comma
and then my criteria is banana. So
created you can specify between quot
symbols like this or
you may give the address of the cell
like this comma and then it says sum
range. This is the numbers that we have
Here we are the same steps for average
if but please don't forget count if
accepts only two arguments that was in
your previous lectures. So the last
feature that we have to learn today it's
about macros. If you do the same steps
again and again macros saves your time
by automating repetitive action. For
example let's imagine you were a sales
manager. You have to control the data
every month. I have a for August, for
September, for October and every month I
should do the same steps like coloring
the table, sorting the table data,
counting some data. So to save my time,
I will run the macro. Let's go to
extensions, macro, record macro. The
first step it is to highlight my whole
table, go to format, coloring, and pick
the one styling color. So we did it in
the beginning of our lecture. Second
step, let's say I would like to sort my
table by car name. So filter first.
Let's go to car name sort. Third my task
it is to create a drop-down to count the
cars by the um for example F body type.
How many hatchbacks, how many sedans. So
let's insert first drop down.
So I'm going to choose here drop down by
the range. At range let's apply F column.
column. Okay.
So, let's select one body type
So, I have a 13 sit down, hback 31 and
So now I should repeat the same steps on September.
September.
So I'm going to go to extensions macros
task number one. It says you should
As a result, you can see macro
automatically styled my table. Second,
it automatically sorted my table. Before
it was another car brands. Here we have
another car brands and also it counted
the data.
Mhm. And let's try for October last time.
time. [Music]
Perfect. So, we finished our script.
This is the way how a macro works. So,
try to using the macro to save your time
Click on any text or timestamp to jump to that moment in the video
Share:
Most transcripts ready in under 5 seconds
One-Click Copy125+ LanguagesSearch ContentJump to Timestamps
Paste YouTube URL
Enter any YouTube video link to get the full transcript
Transcript Extraction Form
Most transcripts ready in under 5 seconds
Get Our Chrome Extension
Get transcripts instantly without leaving YouTube. Install our Chrome extension for one-click access to any video's transcript directly on the watch page.
