Google Sheets For Teams I: Collaborate Better With Your Teammates | Al Chen | Skillshare
Search

Playback Speed


1.0x


  • 0.5x
  • 0.75x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 1.75x
  • 2x

Google Sheets For Teams I: Collaborate Better With Your Teammates

teacher avatar Al Chen, Excel Trainer & Coda Evangelist

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

    • 1.

      Introduction

      2:35

    • 2.

      What makes Google Sheets unique vs. Excel?

      4:31

    • 3.

      Best ways to share your Google Sheet with your company

      5:59

    • 4.

      Be blazing fast with keyboard shortcuts

      7:03

    • 5.

      Analyze data better with filtering and sorting

      7:28

    • 6.

      Always get correct cell references in your formulas

      5:57

    • 7.

      Make VLOOKUP more dynamic for your teammates

      5:36

    • 8.

      Top formulas for cleaning your data

      8:12

    • 9.

      Use Google Sheets' AI to clean data for you

      8:12

    • 10.

      Final Thoughts + Class Project

      1:05

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

203

Students

3

Projects

About This Class

Learn the basics of Google Sheets and how to best use it with your team

Most Google Sheets classes teach you formulas and functions, but this class teaches you how to better collaborate with your team in Google Sheets. You'll still learn the basics of Google Sheets and how to use popular formulas like VLOOKUP. This class is meant for those who need to use a shared Google Sheet with their team. Are you an advanced Google Sheets user? Take the advanced Google Sheets For Teams II class!

What you'll learn

  • What makes Google Sheets unique vs. Excel? 
  • Setting up permissions correctly to share a Google Sheet with your teammates
  • Making VLOOKUP more dynamic for your team
  • Using Google Sheets' artificial intelligence to clean your data for you
  • and more!

Why you should take this class

You've been shared Google Sheets at work but may not feel confident in editing the Google Sheet or doing some task your manager asks you to do. Through my career, I've seen many Google Sheets used by teams and I cover some of the most common scenarios for making your team's Google Sheets better. You'll walk away feeling more confident in how to best use and share your Google Sheets with your team!

Google Sheet class file

You'll be using a Google Sheet file I've created for this class. Make a copy of the file here.

Who is this class for?

  • Google Sheet beginners
  • Teams using Google Sheets to share data
  • Prerequisites: You have opened and edited a Google Sheet before

Questions?

Other classes to get better at spreadsheets

Meet Your Teacher

Teacher Profile Image

Al Chen

Excel Trainer & Coda Evangelist

Teacher

I have been an Excel power user for 10+ years. I started learning Excel when I was a financial analyst at Google. 50,000+ students have taken my online Excel classes and I have facilitated in-person workshops to over 5,000 MBA students around the the U.S. 

I founded KeyCuts, an Excel training and consulting company to Fortune 500 companies. If it isn't clear I'm addicted to Excel, perhaps my podcast about Excel and data analysis (Dear Analyst) will convince you :). 

Outside of Excel and spreadsheets, I work full-time at Coda, an all-in-one doc for teams. If you would like to read my full journey with spreadsheets and Coda, check out this blog post.

Feel free connect with me on LinkedIn and ask me any questions about Excel, ... See full profile

Level: Beginner

Class Ratings

Expectations Met?
    Exceeded!
  • 0%
  • Yes
  • 0%
  • Somewhat
  • 0%
  • Not really
  • 0%

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

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.