Hey there!
I’m Benjamin from Loves Data.
In this video you will learn how to get started
using Google Sheets in our tutorial for beginners.
Google Sheets is a great way to create and
store spreadsheets and because it’s part
of G Suite, all of our spreadsheets are automatically
saved and easy to share.
Google Sheets even integrates with a whole
range of other Google products and third-party
platforms.
Today we’re going to start with the basics.
We will look at how to create a spreadsheet,
formatting, using basic formulas, sorting
and how to share our spreadsheet.
Okay, let’s jump in and get started!
To start, you will need to create a Google
account.
You can then head to Google Drive at ‘drive.google.com’
or directly to Google Sheets at ‘sheets.google.com’.
Personally, I prefer Google Drive, since I
like to see all of my spreadsheets and documents
in a single view.
And that’s what we can see here – I’ve
opened Google Drive.
Now let’s click ‘New’…
And select ‘Google Sheets’…
We’ve now created a new spreadsheet.
We can see it is currently called ‘Untitled
Spreadsheet’, so let’s click this…
And name our spreadsheet ‘My Example Spreadsheet’…
At the top of the window we can see the main
menu at the top…
We then have the spreadsheet…
The spreadsheet can be made up of one or more
individual sheets (or pages).
You can create additional sheets when needed.
For example, you might have all of your raw
data in one sheet and then your formatted
data in its own sheet.
The sheet is made up of rows and columns and
we can edit each cell in the sheet.
For example, we can start entering individual
values in each cell…
However, in most cases you will probably want
to import existing data into Google Sheets.
I’m going to import an example spreadsheet
and if you would like to follow along in your
own account, then you can find a link to download
my example in the description below this video.
Okay, so to import an existing file, let’s
select ‘File’…
And then ‘Import’…
This lets us view files that we’ve previously
uploaded to Google Drive, along with files
that have been shared with me, and recent
files I’ve used.
But we are going to select ‘Upload’…
We can now drag and drop the file, or we can
click the button to select a file.
I’m going to click ‘Select…’
And then I’m going to choose the example
file…
Now that the file has been uploaded, we have
some different options we can choose from.
We can see that by default Google Sheets will
create an entirely new spreadsheet.
And we also have options to use the file in
the spreadsheet we’ve just created.
Let’s select ‘Insert New Sheets’…
We can then leave the other default settings…
And click ‘Import’…
We can now see that a new sheet has been added…
Let’s double click the name of the sheet…
And rename it ‘Members’…
We can see the example spreadsheet includes
some basic details for some sample members.
Our spreadsheet is made up of five different
columns.
These include the name, email address, status
level, revenue and joining date for each member.
We can see that each column is represented
by a letter, so the first column is ‘A’,
then ‘B’ and so on…
Then we have the rows, these include details
for each individual member.
Each row is represented by a number.
We can see the first row is ‘One’, then
‘Two’ and so on…
The row and column can be used in combination
to refer to a specific cell (or range of cells)
in the spreadsheet.
For example, if we just wanted to reference
the first member, this would be cell ‘A2’…
Or if we wanted to reference all of the available
revenue values, this would be ‘D2’ to
‘D7’…
Our example only has seven rows including
the header row, but if you have more rows
in your spreadsheets, then it can be helpful
to freeze the header row, so that it’s always
visible.
To do this we just need to drag the grey bar
on the left so that it is below our header…
Now when we scroll, the header will always
be visible…
We can see some of the values included in
the cells are being cut off.
In this case, you might like to ‘wrap’
the values so that they are visible.
Let’s start by selecting all of the cells
in our sheet.
We can do this by clicking the top left corner…
Now let’s select ‘Format’…
And then ‘Text Wrapping’…
And let’s choose ‘Wrap’…
We can now see that the cells expand to show
all of the information they contain.
Personally, I prefer to adjust the column
widths, so let’s change this back.
Let’s select ‘Format’…
Then ‘Text Wrapping’…
And ‘Overflow’…
Now I’m going to adjust the size of the
columns by clicking the dividing line between
the cells and dragging them so that all of
the information is visible…
Google Sheets will automatically format the
data included in your spreadsheet, but you
can also change the formatting.
Let’s select the first row that contains
the header…
We can now make the text bold…
Let’s select a dark color for the background
of the header…
And let’s change the font color to white…
Now let’s adjust the formatting for some
of the cells in the spreadsheet.
Let’s select the cells in the ‘Revenue’
column…
We can do this by clicking the first cell…
Holding down the ‘shift’ key on our keyboard,
and then clicking the final cell in the range
we would like…
We can then select ‘Format’…
Then ‘Number’…
And then ‘Currency’…
Or we can simply select the currency symbol
in the secondary menu…
We can also remove the decimal places…
Now let’s change the formatting of the dates.
Let’s select the cells in the ‘Joined’
column…
And choose ‘Format’…
Then ‘Number’…
Then ‘More Formats’…
And then ‘More Date and Time Formats’…
We can now choose from additional formatting
options, or even create our own formatting.
I’m going to select the option that shows
the day of the week, followed by the month,
day and year…
Now I can click ‘Apply’…
Okay, now let’s look at conditional formatting.
Conditional formatting can help highlight
particular cells and values in your spreadsheet.
Let’s start by selecting the cells in the
‘Revenue’ column…
Now let’s select ‘Format’…
And ‘Conditional Formatting’…
The conditional formatting panel opens on
the right, and we can use this to control
how the cells will be highlighted.
Let’s choose the ‘Color Scale’ tab…
We can see that cells are automatically highlighted
in green.
The lower the number, the deeper the green.
Let’s modify this, so that we are highlighting
the higher values.
To do this we can either manually adjust the
minimum and maximum value colors…
Or we can simply select the default formatting
option…
And select the ‘White to Green’ color
scale…
We can now see the higher values are highlighted
with a deeper green.
Okay, let’s close the conditional formatting
panel…
Now let’s look at some formulas we can use
in our spreadsheet.
Let’s start by adding all of the values
in the ‘Revenue’ column to create a ‘Total
Revenue’ figure.
To do this, we need to select the cell where
we would like to perform the calculation…
We can double-click this cell to edit it,
or we can use the formula field above the
sheet…
I’m going to double-click the cell…
Then we need to enter the formula.
Let’s type ‘equals’… and then ‘SUM’…
This formula will let us add all of the dollar
values in the ‘Revenue’ cells.
As you type, you will see automatic suggestions
being made by Google Sheets.
These suggestions can be super helpful as
you are working with your spreadsheet.
We can see that it is automatically highlighting
the dollar values in the column.
They’re highlighted with a dashed orange
line.
It is suggesting that we might want to add
all of the values from cells D2 through to
D7.
This is exactly what I want, so I can select
this option to automatically complete the
formula…
And then click ‘Enter’ on my keyboard…
We now have the total revenue value for our
spreadsheet.
Let’s double click this cell again to see
the formula…
I just want to briefly mention that ‘SUM’
is just one function we can use, there are
lots of different ways you can work with the
data in your spreadsheet.
As we can see ‘SUM’ will combine all of
the values that are referenced.
And in this case we are creating a range.
We are saying we want cell D2 to D7.
This is what the colon is doing.
It’s creating a range.
Okay, I’m going to select the cell to the
left and enter ‘Total Revenue’…
And then I’m going to change the background
color for these cells.
I’m doing this just to clearly explain what
the value is for our spreadsheet.
Now let’s add another function below our
total revenue.
Let’s use a function to count the number
or rows in our spreadsheet.
This is useful when you’re working with
larger files.
There are different ways you can do this,
but for this example, I’m going to double
click a cell…
Then I’m going to type ‘equals’…
And then ‘ROWS’…
This will count the number of rows in the
range we enter.
I’m going to enter ‘A2’ to ‘A7’…
And then click ‘enter’ on my keyboard…
We can see there are six rows in our spreadsheet.
Now I’m going to select the cell to the
left…
And tner ‘Number of Members’, so I know
that six refers to the number of members included
in the spreadsheet.
Okay, now let’s do one more simple calculation.
Let’s find the average value for our members.
To do this, I’m going to double click a
cell…
And then type ‘equals’…
Then ‘SUM’…
And I’m going to enter ‘D10’ which is
the total revenue value.
Then I’m going enter ‘forward slash’
for divides…
And then ‘D11’ which is the total number
of members.
I can now click ‘enter’ on my keyboard
and we can see the average revenue for our
members.
But there’s another way we can achieve this.
Instead of referencing the D10 and D11 cells,
we can change the formula.
Let’s delete the current formula…
And now let’s type ‘equals’…
Then ‘AVERAGE’…
And then type ‘D2’, ‘colon’, ‘D7’,
and ‘close bracket’…
This gives us the average for the cells in
the range without relying on the other calculations
we’ve performed.
Now let’s adjust the ‘Name’ column,
so that we have first name and last name in
separate columns.
Let’s start by adding two additional columns
after column A. To do this, let’s hover
over ‘A’…
And select the small arrow on the right…
And choose ‘Insert 1 right’…
And let’s repeat this one more time…
Let’s rename our first column to ‘Full
Name’…
And then name our new columns ‘First Name’…
And ‘Last Name’…
Now let’s double click the ‘B2’ cell…
And we’re going to use a function that will
split people’s names based on the space
between their first name and last name.
To do this let’s type ‘equals’…
Then ‘SPLIT’…
Then ‘open bracket’…
Then ‘A2’…
This is the cell that will be used by the
function.
Then we type ‘comma’…
Then a ‘quote mark’…
Then a ‘space’…
This will be what is used to split the text.
Then another ‘quote mark’…
Then ‘closing bracket’…
We can now see the full name is split using
the space between the first and last name.
We can copy this formula for all of the rows
in our spreadsheet.
Let’s select the ‘B2’ cell and then
we need to copy this cell.
You can do this using the keyboard shortcut
on your computer.
Then we can select the cells where we want
to use this formula.
To do this we click the starting cell, hold
down the ‘shift key’ on our keyboard,
and then click the final cell…
We now have a range selected and we can use
the keyboard shortcut on our computer to paste
the formula into all of these cells at once.
When we paste the formula, we can see that
it is automatically updated based on the row.
This means we don’t need to do any manual
edits.
Now we’re going to use Google Sheets to
help validate the email addresses for our
members.
Let’s start by adding a column to the right
of ‘Email’…
Let’s name the column ‘Valid Email’…
And let’s double click the first cell…
And type ‘equals’…
Then ‘IS EMAIL’…
Then ‘open bracket’… ‘D2’…
And ‘close bracket’…
We can now see that Google Sheets says ‘True’
which tells us this is a valid email address.
Now let’s copy this cell and paste it into
the other cells in the column…
Now we can quickly see any issues are indicated
with ‘False’.
We can then check the email addresses for
these members.
Google Sheets lets us sort and filter the
values contained in our spreadsheet.
Let’s select the column ‘E’…
And then select ‘Data’ from the menu…
Now let’s choose ‘Create a filter’…
We can now see a small filter icon in the
column header.
This lets us quickly narrow down what is displayed
in our spreadsheet.
Let’s click the icon…
And change the selection, so that only ‘False’
is enabled…
Now let’s click ‘Okay’…
We can now see that the spreadsheet is filtered
to only show the cells that contain ‘False’.
This is a great way to focus on certain rows
in your spreadsheet.
You can now click the filter icon again to
change the selection.
Or we can select ‘Data’…
And then ‘Turn off filter’ to view all
of the cells…
Google Sheets includes a range of options
to present and visualize the data included
in your spreadsheet.
A great way to get started is by using the
‘Explore’ feature which automatically
suggests visualization options based on the
cells you have selected.
Let’s select cells in the ‘Status’ column…
And now let’s select the ‘Explore’ option.
You will find this in the bottom right corner
of the window…
We can see different suggestions provided
by Google Sheets.
I’m going to select the bar chart to visualize
the different membership levels and the number
of members at each level…
And I can drag this to my spreadsheet.
Now things can start getting messy quickly
when we add visualizations to our spreadsheet.
So I recommend that you move visualization
to their own sheet.
Let’s select the three vertical dots on
the top right corner of the chart…
And then choose ‘Copy chart’…
You can create new sheets in your spreadsheet
by selecting the plus sign on the bottom left
corner…
For my example, I already have an extra sheet
that I can use, so let’s select this sheet…
You can then use the keyboard shortcut on
your computer to paste the chart…
This chart will continue to be dynamic and
it will reflect any changes that are made
to the other sheet.
Let’s rename this sheet…
And call it ‘Summary’…
And now let’s head back to our ‘Members’
sheet…
We can now select the chart…
And delete it from this sheet.
This means we can use the ‘Summary’ sheet
for all of the visualizations we want to create.
Finally, I want to point out that you can
collaborate with others on your Google Sheets.
Selecting ‘Share’ lets you give additional
people access to your spreadsheet.
This includes view-only access and edit access.
Everything is updated in real-time as people
make changes to your spreadsheet and there’s
also a handy version history if anything goes
wrong.
For more Google Sheets tips, please take a
moment to check out the extra resources in
the description below this video.
So that’s how you can get started using
Google Sheets.
The great thing about Google Sheets is that
all of your spreadsheets are automatically
stored in Google Drive.
This means you can access them from any device,
including Google’s super handy mobile app.
For more Google Sheets tips, check out the
extra resources in the description below this
video.
How are you planning to use Google Sheets?
I’d love to know!
Let me know in the comments below.
And if you found this video helpful please
subscribe, share it with your friends and
hit the like button so I know to make more
videos like this.
See you next time!
