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!