Transcripts
1. Introduction: My name is Al Chen and
I'm an Excel power user. But this class is all
about Google Sheets. Why am I here doing a class about Google Sheets
instead of Excel? Well, when I first
started working in finance at Google and Tilton, sudden Google Sheets
was still in beta. It had, it had only come
out a year earlier, and it was hot trash. It was absolute garbage. Even people internally at
Google Preferred using Excel over our own
Google Sheets product. The only thing we
will she was good at was real-time collaboration
and all the other features. We're nowhere near as good. Excel, fast-forward 15 years. And Google Sheets has just as many features as Excel plus some
additional goodies. Knots in Excel. I continue to use
Google Sheets today because of its online
collaboration features, which have gotten even
better over time. And that's what this
class is all about. This class is an introduction
to Google Sheets. More specifically,
you'll learn some of the key features
and techniques for collaborating better
with your team. Most Google Sheets classes teach you about formulas
and pivot tables. But most people use Google Sheets because
they need to have a shared spreadsheet with their teams in the office or on the other
side of the world. We'll talk about some
features that make the blue sheets
standout from Excel. The best ways to share it and give permissions to
your colleagues. How to make formulas
like VLookup more user-friendly for your
teammates and more. I really wanted to
teach this class to show the true power
of Google Sheets. And that's getting a shared
understanding about a model. A report or analysis with Google Sheets is real-time
collaboration features. You'll walk away
from this class with a deeper understanding of
Google Sheets, features, how Google Sheets stacks
up against Excel, and how to work collaboratively
with your teammates. This class is geared towards
beginners and Google Sheets. Who wants to learn
some of the core features that help make teams be more productive
at work in Google Sheets. After taking this class, I hope you'll think
about Google Sheets, not as a competitor to excel, but as a tool meant for
team collaboration. I encourage you to post your questions in the
discussion forum and screenshots of your class
project in the project section. Now let's get started
with lesson one.
2. What makes Google Sheets unique vs. Excel?: In lesson one, we're going
to talk about some things that make Google Sheets
unique, verse Excel. You're probably already familiar with some of the features. These features where
like your work is always saving when
you're in Google Sheets. Your Google Sheets are always linked to your Google account. And there's some other
things like pivot tables that are automatically
refreshed, more formulas. But we're going to
talk more about all these little
things that I love about Google Sheets that make it more unique compared to sell. Their work is
always saving since everything is in the Cloud,
it's in your browser. Number two is that it's linked
to your Google account. So it makes real-time
collaboration really simple when you have people
shared on this Google sheet. And finally, pivot tables
automatically refresh. There's are some more
formulas and Google Sheets and a few other things. So here's some of the
big differences that I see in Google Sheets. Some other quick
things I like about Google Sheets versus
Excel is the ability to mimic Excel with offline
mode in Google Sheets. The first thing you
have to do is go to your Google Drive. Here's my Google Drive. And I click on Settings. And then make sure that this offline checkbox is
checked off right here. And then when you go
back to Google Sheets, you can go to file and then make available
offline right here. And now the document
can be edited. The Gucci can be
edited when you're on a flight or when you don't
have good internet connection. So it's kinda like using
Google Sheets as if it were a standalone Excel file. Another quick thing
I like about google Sheets is themes or themes. If you go to Format
and then themes, you'll see all
different kinds of themes you can use
in Google Sheets. Before we do that, we have
some data right here. We have a chart off
of this data and a really small
PivotTable right here. And if I go to Format
and then go to theme, I can actually select
various themes for my dataset by just clicking
on these different pictures. And I guess makes your
data look a little nicer versus having
to individually edit the font and the background
colors of your charts. This makes it
really easy to pick a look and style that you like. And I like the style is a
little more than Excel. We have themes and
Google Sheets. I also liked the way
that the print dialog looks in Google
Sheets versus Excel. If I go to File and
then go to print, allow these settings
are in Excel as well, but they're just really nicely laid out in this interface. In Google Sheets, you can
play with the grid lines, you can play with headers
and footers really easily. There is no separate sub menus. As you'll see in the Google in Excel when you're using
the print dialog. Some other cool things include using hyperlinks
in Google sheets. Let's say I want this word ESPN or the name ESPN
to be hyperlinked. Espn.com. I can literally just
press Command K on my keyboard and then
paste this Lincoln ESPN. And what's cool is that when
I hover over this link, you can see information about the website since again,
everything is online. There's some cool formulas in Google Sheets to like
this image formula, which is audit
available in Excel. And using this URL plus
the I'll reference and C5, which is just another
URL right here. I can actually create QR
codes of various websites. So if I drag this down, I can create these QR codes wouldn't scan goal
automatically to these upsides. So if you want them
quickly and make a QR code for some landing page, you can quickly do that with this formula right here
using the image formula, which can be dragged
down to Craig, Craig QR codes were
many different URLs. Finally, in Google Sheets, you also have this
thing known as chips. And if you have email addresses
and your contacts list, you can actually convert them
into these people chips. So I can go to insert and
then convert to people chip. And these email
addresses, sometimes they convert to the actual
names of the people. But when you hover over them, you get more information
about who that person is based on your
conversations with them. And if they're in your
contacts list and you can see what companies they worked
for and all that stuff. It just makes your contacts list a look a little
more interesting. Versus having just an
email address with a name.
3. Best ways to share your Google Sheet with your company: In lesson two, we're going to learn about the best ways to share your Google sheets with your teammates in your company. There are a variety of
sharing permissions within Google Sheets as tool. And so we're going
to talk about all the different ways you just think about sharing
and giving permissions to your colleagues
to get access to your Google Sheets so that people that should have
access have access. And those who shouldn't
have access can never see are three different
types of permissions, editors, commenters,
and viewers. When I click on Share
and Google Sheets, if I want to share with
one of my colleagues, say, I can give them access as a
viewer where they can only view the Google sheet
but not make any edits. I give them a common taxes
which allows them to comment, comment on cells and
charts and stuff. Or I can give them a full editor access which allows
them to actually change the values inside the cells. Now, when I click the gear icon, there are additional
permissions for editors, commenters,
and viewers. So after I click on Share and click on this
little gear icon, you get some additional
settings about what the people can do that have
access to your Google Sheet. Editors can change
permissions and share. This allows people you share
this Google sheets with to allow them to share with
there friends and colleagues. The second option, viewers
and commenters can see the option to
download printed copy. Generally, if I'm working
with them, my company, I have these two
turned on because I want people to be
able to quickly share my Google sheet unless it's sensitive information like its payroll information or sales information or
customer information. But for the most part,
internal Google Sheets, I typically have these
two settings turned on. If you want to share
to certain people, you can also share them to just people within your
company or to the public. If I go over here to figure one. If you look at this option here, if you are working
with Google Sheets or Google Docs within
your accompany, you may see these
three options when you have the share
dialogue like this. Right here, I only have
restricted because I'm not working within a
company with this blue shape, but you may see restricted your company name in this case, my company name is cota,
anyone with the link. And you can basically
have people within your company or anyone in the public can see
the Google Sheet by clicking on that dropdown.
In Google Sheets. Well now when you share
with your company, Let's say I share with
my company coda here. You can also similarly give
people within your company similar access as viewer,
commentary or editor. So unlike sharing individually emails right here, one-by-one, when you give your
company access, you can also tell Google Sheets that anyone in your
company can either view, comment, or edit
your Google Sheet. With access in your company. When you have access
to your company, you can click the
gear icon to get people to search
for Google Sheet, which is in figure three. Now there's another,
this is what happens when you
click on this little gear icon right here. You'll see this ability
to let people in coda or your company name
can search for this file. Sometimes you may want
people to be able to find your Google Sheets just from searching on Google Drive. You will generally
turn this setting on within your company context. I generally turn this on so that might go She's are discoverable. But again, if you're Google
Sheets or sensitive with payroll information or HR data, you may not want
to turn that on. Go ahead and experiment with playing with these
different permissions. Sharing individually
one at a time, changing it to anyone restricted
or anyone with the link. Or if you're in a
company context, see if you can share
with your company. Try opening Google Sheet and Incognito Mode to test out
the different permissions. One final thing about
sharing is you can also publish your spreadsheet as if he were publishing
like a website. You can go to File, Share, Publish to Web. This walks you through a
dialogue of how you can publish a public URL
that anyone can access. And it's kind of like a
public resource that allows people anywhere on Google to search for this Google sheet. Typically speaking,
you won't publish to the web if you're working
with in your company because you want to
keep your Google Sheets pretty confidential. But sometimes this is
good for publishing public templates that you
may want people to access. The difference
between publishing a spreadsheet and sharing a
link with anyone on the web. Remember, you can share
a Google Sheet and say, anyone with a link can view this means anyone the
Internet can view this link. I'm going to change this
back to restricted. If you do that. It's the same thing as
publishing to the web. Except that when you
publish the web, people can't automatically
see updates in real time. When you make updates
to your Google sheets. They have to refresh the page to see the Google Sheet updates. But if you're sharing with
anyone with the link, anytime we make updates
in the Google Sheet, they will automatically see
those real-time updates. So just a small difference
there if we decide to publish your google Sheets. An example of a public
Google sheet that I released is this template. It's a template for splitting
costs with your friends, which I published on my blog. Anyone can view this Google
Sheet and find it online, and they can copy it
to use for themselves. So this is an example
where you might want to share a Google Sheet to anyone on the
internet to find. But generally speaking, when you're working
within your company, you won't be polishing
your spreadsheet to the web or making the Google sheet available to anyone with the link
to view, comment or edit.
4. Be blazing fast with keyboard shortcuts: Lesson three. This is one of my favorite
lessons in this class because I love keyboard shortcuts
coming from the world of Excel. And people always
think Google Sheets can't be used because he
can't use keyboard shortcuts. I'm here to tell you
that's completely wrong. You can use all kinds of keyboard shortcuts
in Google Sheets. And we're going
to talk all about my favorite shortcuts
they use in Google Sheets to move around faster and be more productive. Come a long, long way in
terms of being able to use keyboard shortcuts in Google
sheets just like you can use keyboard shortcuts in Excel. The first thing you
have to do is go to your System Preferences
in your actual Mac. And if you go look for your
keyboard settings right here, the main thing I want you
to turn on is this use F1, F2 keys as standard
function keys. Just make sure that
it's checked off. And this will allow you to use the function keys on your keyboard to be
faster in Google Sheets. Now the first thing
we're gonna do here is use the F2 function key. This isn't a top left
of your keyboard. And we want to add the state
to each city name here. Now, today Google Sheets, you might be double-clicking
here and then saying like that, Massachusetts. But instead, if you
just press the F2 key, that setting turned
on right here. But like I mentioned
in your Mac settings, you can now just easily press F2 and then enter
into that cell to start typing in stuff
for, for that cell. And this also applies to
formulas to, down here. We want the formula
in B21 to include this third three ran out only includes the two
threes are here, 18 plus bt plus benign. We want to include plus b. Now, instead of
using the F2 key, you can also just
press the Enter key or the Enter key lets you get inside a cell really
quickly as well. This is really useful when
you want to edit formulas. I can press Enter and then plus, and then just use my
cursor to go to B20. And now I have
fixed my formula to include all these threes. So F2 and enter are ways to get inside the cell to
start editing formulas. Now a big, big keyboard shortcut that more people do not
use in Google Sheets is using Control plus Option or the Alt key to access the menu items up here
in Google Sheets, the option key is kind of like that little key at the bottom
left of your keyboard. And it may look, MA, also say Alt key
for some people. But let's say I want to
access the File menu. I can press Control
Option F, as in Frank. And it automatically
opens up this menu. And notice that when
I open up this menu, it looks more like Excel, where certain letters are
underlined within the menu. If I want to go to the
download menu here, I can just press the
letter D and then I can also go to the
letter P for PDF. And it will
automatically move me to that saved print PDF option. And these are all
the letters that correspond to accessing
all these different menus. So one thing I you typically
use a lot is the Edit menu. So I'll press Control Option E. And it brings up this
menu where I can press D for delete, undo that. So there's many
different ways you can use all these
different menus just like you can use the
ribbon in Excel. Control Option or Alt key. Now, let's try using some of these keyboard shortcuts within this little dataset right here. Let's first use the control or the command key to move around
the data a little faster. This is something
you'll probably be used to wear if you're
using an Excel, I can press the
command key and the bottom-left to my
keyboard and just move around this whole sheet or this whole dataset
really quickly. Let's go back to where I was. And that's one keyboard
shortcut I think is really useful in terms of
moving around a big dataset. Now let's insert a
blank new row after row 41 using Control
plus Option plus I. So let's go to 41. And I'm going to press
Control option I. Then I'm going to
press R for row. And I'm going to say
insert one row above. I can just press
the R key again. And I get a blank
new row. After 41. I can actually let me
go back here and do Edinburgh 40 to
control option I, Our for rows and then one
more are for row above, and I can insert
a blank new row. Now let's say I want
to delete this row. I just said to her,
don't want to keep my dataset connected. I can now go to Control
Option E for edit. And notice how the word delete has the letter D underlined. I can press D there. Then I can press D again. Notice how says row
42 is the letter D. I can press D again, and it just removes
that empty row from my, from my dataset. Finally, another keyboard
shortcut I really, really like in Google Sheets is just removing
all the formatting. This is a simple
shortcut which is Command Plus the backslash key, which is like in the top
right of your keyboard. And notice how these
cells right here, these headers are like this blue background
and bolded it. I'm going to select these cells. I want to make these
no formatting. I'm just going to select
this and then press Command. Backslash automatically removes all formatting
from yourselves. So this is really useful
when you have a bunch of cells that are formatted
and really crazily, and you just want to quickly
remove all that formatting to get back to like
plain, plain text. And finally, smart fill is another really useful
shortcut that I like to use. It's not really a
keyboard shortcut, but it suggests
foremost for use, I kinda consider,
consider it a shortcut. Let's go up here and
look at this table. I have these names, these main characters from these various Netflix TV shows. And if I start typing in the first name of
each character, let's say start typing in Joe, then Marty, and then Spencer. Notice how Google
Sheets automatically, this is their AI
coming into play. It says, I automatically see
that you're trying to put in the first name for a bunch
of people in this cell. We think we know the
formula for you. You can click on
the checkbox and it automatically finds
the firstName for you. But more importantly, a rights
in the formula that you need to extract that
firstName from that cell. It's not going to be perfect
because some of these, you can see only have
one FirstName, LastName. And it gives you an error here, but it's a really quick way
if you have simple cells, simple first names and
last name to quickly pull out the
FirstName without you having to figure
out the formula to write left and find
and all that stuff.
5. Analyze data better with filtering and sorting: Lesson four isn't a really
important lesson for those of you who are
working with a lot of data. We're going to talk
about how to better analyze your data
through filtering and sorting using Google Sheets is inbuilt filtering
and sorting menus. We're going to be working
with a small dataset related to happiness levels across
different countries. And I'm going to
help you explore ways you can filter and sort this data to make it do what you wanted
to do for your team. And we're going to
sort the studies by various different columns and filtered by different columns. And analyze which
cities are great to live in based on a bunch of properties like sunshine hours, obesity levels,
happiness levels. These are all different metrics you can get about
these cities to help you figure out what
study you really want to move to or live in long term. The first thing I
wanna do is sort this list by sunshine hours. I want to find the city
with the most number of sunshine hours. I think it's per year. And the key thing about
sorting Google Sheets as you have to select
the DataFirst like this, I'm pressing shift command
right arrow down arrow. And then I'm going to
go to data sort range. Instead of clicking
these first two options, I'm going to click on
advanced sort range, advanced range of options. I'm going to say my
data has a header row, and now my headers show up
here in this drop-down. And I want to say
sunshine hours. Z2 is in descending order. It's sorted. And now you can see that
the first city, well, the first of the Geneva, but there are some
missing data there. But the first city with
the most number of sunshine hours as Cairo,
followed by Tel-Aviv. And let's say I want to sort by sunshine hours and then by life expectancy in
descending order. I can just go back to my
select my data here again. I can go back to my sort range,
advanced sorting options. And it has header row. So I want to say sort by
sunshine hours, Z to a, and then also sort by, I believe it was life
expectancy sort. Now my theta has been sorted by sunshine hours and then by the
life expectancy in column. This is pretty similar to
the advanced sort menu in Excel. Now let's start filtering
data filter list to only the cities, Amsterdam, Vienna, and Berlin. This is also in the Data menu. I can go to Data,
create a filter, and you don't have to select
the data for it right here. Excel google Sheets
automatically selects the whole range
of data for a right here. And I can just click
on the drop-down, this filter icon here, then just clear all. And then click on Amsterdam, Vienna, and then Berlin. And then when I hit OK,
those cities will show up in my filtered list. Now I'm going to undo that
filter by pressing Command Z. And I'm gonna go to step four, which is filter the list
of shows cities with a happiness level above a 7. And that's going to be in
this column right here. Now there's many different
ways you can filter data and not just by
clicking on specific values. You can also say filter by
condition or filter by values. I want to filter by
condition this case and say the hopes Here, filter by condition and say that the data has to be
greater than a seven. Since I want to show
the cities with a happiness level of greater
than a seven hit, Okay? And these are all the cities where there's a
happiness a little above a seven in
case I want to go to the city with the highest
happiness levels. Now, we're going to work on something now called
filter views, which is a really powerful
feature for teams who wanted to quickly share filters with their teams and colleagues. So let's go to Data and
then go to Filter Views. And we're going to create a
new filter view like this. And you get some, you
get a little bit of this black border around here. Don't worry about that. Now the next thing
you do is we're just going to
create a filter for the top ten cities and we're going to call it top ten cities. So up here you're going
to see this name here, call this, changes
to top ten cities. That's the name of
our filter view. And we're going to show the top ten cities based
on the rank column. So click on this link. And you can just say
filter by condition here, where the value is less
than or equal to ten. Since we want to show only cities that rank
one through ten. It okay. I know this isn't, this isn't ranked right now. We could also sort this as well. I believe Let's go
to sort a to Z. And now we can see
one through ten here. Now, after we've finished
doing this filter, I want you to click on
the X and you kinda go back to your original
Google Sheets mode. Now we're going to create
another filter view called live past 80. Let's go back to
data filter view. Create new filter view. We get that black border again and this time
we're going to call this filter live past 80. The whole point of this
filter is to figure out which cities Have show people that live
past 80 years old, which is gonna be this
life expectancy column. So I'm going to select this data and click on what I think
I actually have to. Let's actually change
this back to here. And now we can, okay, I just want to make sure
that filter on this pass AT. And let me just fix
this one really quick. Let's create this
all over again. Filter view, Creek,
Filter View. There we go. Alright, let's rename
this to live past 80 V2. And here we have the drop-down
here for life expectancy. I'm gonna change this
to filter by condition. I want this to be greater than 80 because I want to
show all the cities have greater than 80 years old
life expectancy hit Okay. Then I can just
click out of that. Now the key thing
here is, let's say, I don't want to
spend time filtering for the top ten cities
are live pass AT. And I've saved those filter
views in my Google Sheet. Now, any one of my
teammates can now access those filter views simply by
going to data. Filter view. Top ten cities. And your girl, she will automatically
filter and sort to show that original
filter view you saved so that your
teammates can easily access all these filter views
without having to manually go through
each one on their own. Let's go back to
show the live past 80 filter view. We'd call it V2. And now you can see
all the cities where the life expectancy is
greater than 80 years old. A really powerful tool, again, creating filter views for seeing your data really
quickly without having to go into the
filters themselves.
6. Always get correct cell references in your formulas: Lesson five is how to set up
cell references correctly in your Google Sheets using absolute and relative
cell references. If you're building a model or ports and you have all kinds of variables and numbers stored around as inputs and
your Google Sheet. Knowing how to properly referenced these
cells is important so that your model looks correct to the
rest of your team. So we'll talk more about
this in this lesson. We have a list of salary
calculations here. We have the people here, their hourly wage,
number of hours they worked per week and
then their final salary. And if I look inside these
cells by pressing F2, I can see that's simply the rate times the number of hours
to get their total salary. Now how do I get the tax
rate in this column, assuming a tax rate of
10% here in this cell. So you would think it's simply just salary times tax rate. If you hit Enter, you'll get the correct tax where that
first-person now Fano. But if I drag this
down this quickly is not correct because if I look inside these cells
again by pressing F2, the salary references correctly going to this cell right here. But the tax rate
column or cell is moving when I drag this cell
down, this formula down. So I have to figure out a way
to lock in that tax rate. So it doesn't move when I'm
dragging my formulas around. Now, the way to do this manually is you put in a
dollar sign before the column and the
row number hit Enter. And if I drag this down
now this will be correct. These are all the
proper tax rates because now if I go
inside these cells, it's not moving the tax rate
cell when I'm dragging down. Now the faster way to do this by using keyboard
shortcuts, again, which I really
love is going tax, salary times the tax rate. While you're still in
the edit formula mode, you can press the F4 key. It automatically adds those
dollar signs for you. If I press the F4
key a few times, you can see how
it cycles through all the different
combinations of locking in the column and
column and row. Just the row, just the
column where nothing. I'm just going to lock in both the column and row right there. And then I can negotiate that will even suggest
to auto-fill here, I can hit the
checkbox and my cell. My formulas are
properly created with the absolute reference of I7 and with this relative
reference of F11. So epsilon relative
references is really important
concept to grasp it and Google Sheets and it ended
and in Excel for that matter. Let's try to fill
out this table below a little more
complicated scenario. And we want to create
one formula in cell D26 using absolute
and relative references. So that when we drag this
formula to the right, and when we drag it down, it properly calculates
the formula. If we take a look at this table, we have starting sales for
these various products. We also have along the
columns the variance from the starting sale amount that we want to calculate
in these cells. So for instance,
this cell will be 1 thousand times
one plus this 5%. Because this is 5%
above starting sales. For this cell, this would be 1 thousand times one plus 4.5. This is $45 above
that starting points. So you can see how we want to do this for every single cell, but it would take
way too much time to manually write out these
formulas one-by-one. And unfortunately, if I drag this simple formula to
the right and down, it's not going to give
me the correct results. As you can see here, these are really wildly crazy numbers. And that's because we're
not using relative, absolute and relative
references correctly. If I think back to this problem, what I really want to tell Excel to do or Google Sheets to do is to lock in the
column reference here so that when I
drag it to the right, this form to the right, the C does not change, only the 26 role change. So I just want to add a dollar
sign there to that C2H6. Now with a D24, I want the row to be
locked or not the column. So that when I drag this formula to the
right and to the down, I only want the column
to move around, but I want the row to stay fixed on that percentage
column, row 24. So I'm going to put a
dollar sign right there. I'm currently using an
absolute fixed reference of column C for this,
the starting amount. But the 26, this rho
can turn of 2728. And for this reference, I want the 24 to be locked because I don't
want this column, this row to move
around in my formula. But the d, this column
can move around as I'm dragging to the
right and to the bottom. If I drag this now to the right, Let's drag it all
the way to the right here and drag it down. You can see this
automatically calculates the proper number based on the absolute and
relative references that I've put in cell D 26. It's a little more
complicated scenario using absolute and
relative references. But when you're building
a model with your team, you want to quickly
build out these tables. And if you have data that's
being fixed in the cells, you want to make sure that
the cell reference is not move too much or at all when you are dragging your formula
around your Google Sheet.
7. Make VLOOKUP more dynamic for your teammates: Lesson six is all
about how to make a div lookup more dynamic
for your teammates. You're probably already
familiar with how to use the VLookup formula. But this is more
about how to make this more user-friendly for your teammates so that people
can understand how to use your model using different ways of using the VLookup formula. Tv shows and movies here with various attributes
about these TV shows. And let's use the VLookup
formula is a pull in some information
about these shows and to also make it more
dynamic for our team. So let's do it really simple. If we look for a VLookup
formula to get the Netflix released date for the
show, love is blind. The Netflix released
date is right here. And I'm going to start
writing E equals V lookup, the parentheses and
the search key. That's what I want
to look for in my list of Netflix shows. I'm gonna put double-quotes. It's important with
DoubleClick because we have names with
spaces in them. You have to put the double
quote and then love is blind. And that was a
double quote comma, the range is it Alyssa
data I want to search for? In the search for it. When I pull back information, I'm going to select all
these cells right here. Batna of H3O and then comma, the index is simply
the column number in my table that I
want to pull back. So I can literally just count
the number of columns here. Column one is title, column two is main character, 345 as Netflix or
at least eight. I can put the number five there, comma and is sorted. I normally just call
this, put this as 0. Or if you put false by
put 0 to be fast, right? Parentheses. And that's my VLookup
formula to find the Netflix release date for
the show, love is blind. You can see there it's
February 13th, 2020. Now let's do something
more interesting with the VLookup formula so
that we can make this more dynamic for our teammates who are using this Google Sheet. I wanted to create a
VLookup formula in cell G9, so that when my teammate
or colleague enters as shown name in this cell, the viewership score
will automatically change depending on what my colleague rights
inside that show named cell. So let's write equals VLookup. Instead of manually typing
and love is blind here, I can use a cell reference. I'm just going to
select cell C9 as my cell reference
because that's the cell my teammates or colleague
that are going to enter in. Then I can click on press comma and do the
same thing I did before. Select my whole table of
Netflix shows and movies. Netflix release date
again is column 123455 and then
comma 0 or false. I can put false here as well. Right now it says an error. But let's start pulling
in and writing and some show names and
this cell as if I were, if you're giving this to
your colleague to fill out, let's say I type in bad land. You can see how the viewership score
automatically changes because the cell reference for what we're looking for is in C9, which is what my
teammates are going to be using when they are
entering information. The platform. It also changes
the viewership score. And we can also
type in what moral, just make sure it
works, it works. July 21st has 17, and that matches up with
that cell right here. Now let's take this one step further and create
a formula that lets my teammates not only
enter in the show name, but also put in the
column number that the, they want to look at four. That's for our list of TV shows. Maybe they want to look
at the main character. Maybe they want to look
at the viewership score. Maybe they want to
look at the type. Let's put that here. This formula is going
to be equals VLookup. My search key is gonna be the show name, which is cell C3. The range is going
to stay the same, which is this list
of shows right here. Now instead of putting
an index column, which is Netflix release date, I'm going to put a
reference here to see 14. And that's gonna be the
column that represents what my teammates are putting in to pull back
information they want. And then put 0 here. This is my final
VLookup formula. It's gonna be an error for now. But let's say my shown
name is uncooked. Still going to be an error. Let's say I want to pull back the main character from uncork, that's going to be column two. I can put the number two here. And now you can see
that this pulls back the proper main
character, Elijah. Let's say I wanted to pull in the Netflix with these state that's gonna be call them five. Or let's say I wanna pull
in the viewership score, which is column sudden. You can see how this formula
automatically pulls in these two cells to figure out
what to do the VLookup on. So it's a more
dynamic way to pull information back from
your list of data. I just realized I made a small mistake here with the
viewership score. This should actually
be column sudden. Not call them five. So I can now properly
now referencing the proper column when I type in and show name here to get
the viewership score. So this is a way you can make your Google
Sheets more dynamic with the VLookup formula and make it easier to use
for your teammates.
8. Top formulas for cleaning your data: Lesson seven is all about
the top formulas for cleaning up and
transforming your data. A lot of times the only
thing you're doing Google sheets sometimes
is copy and pasting from a CSV into Google Sheets and you have to
clean up the data because there's a
bunch of misspellings, or you have to get the
firstname out of the full name. And we're going to talk
about all these formulas to help you clean your data
faster so you can start doing your analysis quicker as well In this lesson for cleaning your data from those can
automatically be created for you using the smart fill
function in Google Sheets. We've talked a little
bit about this in lesson three with
keyboard shortcuts. In this, in this
lesson we talked about how you can quickly
get the first name by just start typing
in the firstName of people here,
Marty and Spencer. And notice how Google
Sheets automatically comes up with formulas,
suggestions for me. And this gives me these
formulas left to find, et cetera very easily. But it's important
to understand how these formulas work on
the backend in case you have to do some more custom were advanced cleaning up your data. So we're gonna do that here by first using the left function to get the word TV out from
this column right here. And the way I can do
this is by this writing, literally the left
word, left parentheses. The string is going to be just this cell right here
that contains a type. The second number, second option is the
number of characters. I only want the
first two letters from the left of the cell. So I'm just gonna
run number two. Our parentheses. And Google Sheets
automatically gives me the statistical auto-fill
and that's going to click on Okay, checkbox. It works for most of
the options we get TV, but for movie you can
see how only pulls in the first two letters of
movie, which is okay. I just want to show the
example of how you can use the left function. Now let's try the opposite
of left, which is right. If i equals right, I want to pull in just
the last four letters or characters from this
cell to get the word show. And most of my cells, I
can write equals right? And then click on this cell for, because I want four
characters from that cell. Again, press checkbox
to get that auto-fill. And you can see for most
cells properly it gets the show name or show
word but not the movie. Because again, I just wanted
to show the example here. Number three is use the find function to find
the position of the space, the main character's name. In. To put this into column age, what that means
is I want to find the number space right there, or the space right there. I want to find what
position within this whole name does
that space exists. For Joe Exotic, for instance, it will be positioned,
this has 1234, would be the space of that, like count of that
space character. Why we want to do this will become clear in the next steps. But for now, let's start writing
the formula equals find. The search for this is the
space I want to search for. When you want to search for
a space and Google Sheets, It's symbolized by pressing the double quote, double quote. That's it. Just an empty like
double-quotes and have, make sure you have
space in the middle. The text of search is simply the main character column comma. And then starting
at, I can actually, this is an optional thing, so I don't need to
put a comma here. I can just backspace that. This is basically my
formula right here. Find the space in the main
character column, hit Enter. And for most cells
is able to find a space because
some of these names don't have first and last name. But the space and especially
James is in position eight and named Joe
Exotic here again, it's in position four,
so this works correctly. Now this is going to be the
hardest formula we're going to use for cleaning
up our data is we want to use the MID
function to get the characters
lastName into column. I want to get the word exotic bird James into the
last name column here. And we're going to use the find, the MID function to do that. Mid is the function name. The string is always going to be our main calling me question, which is the main character
column starting at. Now this is where that space in name column comes into play. We already know that we got
the space from this column. We can actually reference
that right here. Then the last thing is the number of characters
you want to pull it back from this cell starting
after the space. So normally speaking, I just put a really big number
here, like 100. And let's just recap. What we're doing here
is we're looking inside the main
character column. We're starting to add the
position which is space, which we pulled from
column H within this cell, we're pulling a 100
characters from that cell. If I hit Enter, I
hit the checkbox. And you can quickly
see for most cells, it pulls back the LastName from that cell except for
people that have first names. One small little error
going to fix is that there's an extra space there between exotic and
the end of the cell. So what I normally do here
is I just put an extra plus one here to increase
that space by position, by one spot because we want to start where the word
letter E starts. Hit Enter. And I'm going to drag this down. And now you can see
that this is removing that extra space in
that mid function. Again, all these functions
that we just wrote, wrote here can be pretty
much figured out when you do this like autofill thing we've talked about
in lesson three. But it's important to understand how to use these formulas for more advanced data cleaning, which will do a lot
in Google Sheets. Now a quick way to do
this without using mid and find and all
these other functions is using the split function to
get the first and last names and the columns j
and k as one of my favorite functions
and Google Sheets, you can write equals split. This basically splits
a cell by some type of character or thing
that you provide. So if I write split, let's take a look at what it
requires split. First thing is the text,
I'm going to click on that. The delimiter. That's the thing that separates the
first and last name. In this case, it's simple space. I can just put double-quotes
space, typical. Remember how we symbolize a space by putting in to double-quotes with
a space in-between. Then these are two
optional parameters. I'm not going to include them. Like I said, my parentheses
hit Enter, and there you go. That was a much
faster way of getting the first and last name
in these two cells. And if I just drag these down, you'll see that I get
the first and last name for most characters because some characters only have
one name right here. And notice how if I add
a dash in step six, put a dash in between a few, the first and last names. And let's see what happens.
Instead of putting a spacer, I'm going to put a dash
space in-between here. I'm kinda to a dash and
change this to a dash. Notice how our formula
is changing now, it doesn't properly pull
in the last name anymore. I just puts in one
full name here. That's because we're
our delimiter changed. Often now change this
delimiter to be a dash. And Google Sheets knows that. Okay, I want to split the word Joe Exotic by the dash
that I see in the cell. And that's because I've changed that right here to a
dash instead of a space. If I drag that down
to a few cells, we'll see how that fixes some
of these these names here. So these are some
really useful formulas for cleaning up your data.
9. Use Google Sheets' AI to clean data for you: In this final lesson in
this Google Sheets class, we're going to talk about
how to use Google Sheets is own artificial intelligence
to clean the data up for us. Why? Use really
complicated formulas to clean up your
data when you can have the AI do it for us without us having
to write out the formulas. So we're going to explore
some of these builds in AIP tours and Google sheets that can help you clever data. In like a second or less. You have data that's
being entered by your colleagues
or your teammates. And in the year column, people accidentally put the
letter S for a year, 2000s. Or they might put a dash
for multiple years. If you're spelling the
word New York City, you might spell it multiple different ways or abbreviations. And then for dollar amounts, you might say no data
or to point to mill. And the problem is that
this is not standardized. You want all this
information to be standardized in one way. And the formulas
we talked about in this class do help with
cleaning up this data. But there's some times it's gonna be a lot of manual work. But luckily we have
Google Sheets as artificial intelligence to
help us clean data for us. But it's not gonna be
perfect, but it definitely helps us get there
part of the way. If I scroll down here and I
look at my dataset, I have, you can quickly start seeing
some issues here in terms of there's too many extra spaces
in this IMDB title id. Some of these titles also are
weirdly have spaces here. And in the country
column I have usa here, but there's USAA,
which is not correct. I have the N A error here. So this is a dataset that looks like it needs
to be cleaned up and we have to use formulas
to clean things up. We can also rely on
Google Sheets as AI to help us clean up
a lot of this data. So the way you can use
this is by going to data, data cleanup and then
clean up the suggestions. Let's take a look right here. If I go to data, data cleanup, you get the little
magic wand here and click on cleanup
suggestions. Google Sheets
automatically scans the entire Google
Sheet and tells you, Hey, here are some cells we think have issues that
we can help you fix. So if I hover over
these options, this first one, it
says Trim whitespace. Google Sheets already
knows that there's an extra one space and B27, there's three extra spaces here. It's really hard to
see with a naked eye because you don't see
those extra spaces. But if you press F2 or Enter, you can see how your cursor
is one space above there, and then three
spaces ahead there. These are small things that
you can't detect unless you use the AI or use
the trim function. But we're going to rely on
the AI to help us out here. The American beauty has a bunch of spaces
after the word beauty. That's why there's all
these little red marks. And then if I look at
these options right here, there's spaces in the directors
name's Stanley Kubrick. So I'm just going, I can either ignore or trim all. I'm going to just say
trim all because I want Google Sheets
to fix all this for me without me having to
write any formulas, Tylenol. And then close that out. And this fixed, again, Google Sheets is ai is
not perfect, perfect, but it's gonna help
you fix most of your problems right
from the get-go. I can also remove duplicates
by going to data. Data will clean up,
Remove Duplicates. Down here I can see I have
Sarah and a volta West. This is also
repeated right here. And I want to remove
that duplicate. And imagine if I had
like thousands of rows, I don't want to manually
delete rows one-by-one. I think just go to data, data cleanup, remove duplicates. And it's going to analyze
all my data right here. Looking at this whole
thing, roof duplicates. One to pick a row found. 13 rows remain. This is pretty
similar to the way Excel's remove duplicates
option works as well. The final kind of Ai here
we can use is go to data. They did clean up and then
go to Trim whitespace. It says no cells like the
cells have whitespace trimmed. That's because when I first
did the data cleanup, cleanup suggestions right here, I already removed all of my trailing whitespaces
and leaving whitespaces. So if you do that, chances are you
won't have to use the Trim whitespace
option in it, it cleanup. We got all of the Google
Sheets AI fixes down ready. Now here's a more
manual fixes we have to make using formulas and other
options in Google Sheets. For this column,
we want to replace the USAA with just the word USA, abbreviation USA,
because the USA is, it might have just been
like a data entry error. Someone accidentally
added an extra a. And the way we do this is using the Find and Replace
feature in Google Sheets. So I can just
select these cells, then go to edit,
find, and replace. And I'm going to
tell Google Sheets, I want to find us say a
and replace that with USA. And maybe I only want
to apply that bigger. I want to make sure it matches the lowercase
and uppercase. But for now, I'm just
going to say replace all. Notice how it says
two instances of USA now has been
changed the USA. So now if I want to
use this data for analysis like in a pivot
table or something else. It's much more standardized. And I don't have to worry about like these USAA options here. The last one is replacing
the n slash a column. Here in the rating
column with a blank. Maybe there is no rating
for these movies, so I don't want it
to show n sachet. I just wanted to
be a blank cell. So how do I do that
in Google Sheets? Again, using the final
replace thing off feature, I'm gonna go to go to find, sorry, Edit, Find and Replace. And I'm looking for hashtag
en slash a, replaced with. Now you might be tempted to put double-quotes space
double quote, like we have shown
in previous lessons. But actually if you
just leave this blank, you literally replace
and sachet with another thing which
has a blank cell here. So that's how you can
replace your errors with or anything
with a blank cell. So I'm just going
to say Replace All. And notice how those NAs have replaced with Gucci
Google Sheets. This has nothing blank. But now replace that. And these three here, these you can
actually just change by removing the decimal
place right here. And same thing with this one. Just to make the decimal places. Because it looks like this
also needs to be replaced. We need to replace the periods with a comma here it looks like. So I could do another
Find, Replace. And I can say find the period
replaced with a comma. I think this will hope looks like it didn't
really change that. I think you have to do some more custom final replace here to, there we go. So now that this change
this into numbers, and I can actually go this
to change the format number, number and remove decimal
places as necessary. And that's how you
can quickly fix her data using the
AI in Google sheets. But then as you're
looking through data, you'll see these one-off fixes
you'll have to do manually using the final replace or using formulas like we
discussed in lesson seven.
10. Final Thoughts + Class Project: Thank you so much for
taking this class. I really hope you will do the class project that I
included in the class file. And the class project is giving you a quick
high-level overview. What it involves is you're gonna be looking at a dataset of the top selling Amazon
books from 2009 to 2022. I'm going to ask you a bunch
of questions and give you a bunch of tasks to do on this dataset based on
the skills that you gain from taking the
lessons in this class. So if you haven't watched
all the lessons, please do. You're gonna be asked
questions related to these skills you'll
learn in these lessons. And a few other questions and
tasks that I didn't cover, which you'll have to
Google on your own to figure out my thing. After you do this project, you will become an expert in how to set up your
Google Sheets files for your teammates and
be able to better collaborate with your
teammates on a shared dataset.