The Beginner's Guide to Google Sheets - Online Spreadsheets

In this tutorial we're going to learn about Google Sheets. And Google Sheets of course is part of the Google Drive online suite of tools that is somewhat similar to Microsoft Office, but online. And we're going to focus, like I say, in this video just on Google Sheets. If you're interested in learning more about Google Drive, please watch my other videos on Google Drive. And I also have a video on Google Slides, so watch those other videos as well as this one. As it says here, you can use Google Sheets to create powerful spreadsheets, complex spreadsheets if you need them to be complex. And the thing about these spreadsheets is these are online by nature. And as it says here, with Google Sheets you can create, edit, collaborate, which I think is one of the keys of the importance of Google Sheets wherever you are and for free. So those are really some of the key elements and benefits that Google Sheets has going for it. And I am just on google.com slash sheets slash about. And I could click this button to go to Google Sheets, but honestly I never access Google Sheets this way. Rather, I'm already in Google in some form, usually google.com, but it could be any of the other Google apps or tools. And then I just click here in the upper right on the app symbol, and then I go to Google Drive. So for me, this is how I access Google Sheets. I go to Google Drive first because this has everything, all of my documents, everything that I've been working on. That tends to be where I start the process of creating a Google Sheet. And to create that sheet, I can just click here on new, and then go down and choose Google Sheets. That would give me a blank Google Sheet to work with, or I could put my mouse here on the arrow and go to from a template. And this gives me a whole bunch of templates that I could choose from to use in Google Sheets. Here's a to-do list, an annual budget, monthly budget, calendars, wedding planners, annual business budgets, education, attendance sheets, grade books, all sorts of different things that are pre-made and ready to go. You just click on one that you're interested in, and then it opens up and you can go in and edit it, put in your own budget information, etc. And you can use this spreadsheet online, and it looks beautiful. It's pre-made. A lot of times these templates are just what you need. I'm going to X out of that though, because more often than not, when I use Google Sheets, it's because I want to create my own spreadsheet. Now before I create my own, I do want to point out that it's also possible to file upload a spreadsheet. So if you use Excel, or if you use Apple's Numbers to do spreadsheets, you can click File Upload, and it will take you to your computer, and you can identify the pre-existing spreadsheet that you'd like to pull into Google Sheets and use. So here's one. It says Detailed List. I'm just going to double click on that, and notice that it's uploading that spreadsheet into my Google Sheets account, and there it is. And I can click on it here, or I could click on it there, whichever way. But look what it does. It opens up a preview, basically, of this document. Okay, remember this is an Excel spreadsheet that I've uploaded, and Google Drive is trying to preserve it as an Excel spreadsheet, unless I give it permission to open with Google Sheets. And there are some other options as well, but I'm just going to click Open with Google Sheets, and now what it's doing is it's creating a copy of my Detailed List spreadsheet. And you can see it's really not that detailed, but that's okay. And so now, if I back out of this document, you'll notice that now I actually have two documents called Detailed List. One that is preserved in Excel format, just as it was when I uploaded it, and then a second one that's a copy of the first, but that is part of the Google ecosystem. It's ready to be edited, shared, etc. in Google Drive. And so for me, when I upload an Excel spreadsheet like this, and then I open it in Google Sheets, I typically end up deleting the original. Now, that may not be a good idea for you, but that's just what I do, because if I know I'm not going to need that Excel spreadsheet anymore, why have two copies? I just delete the one that's from Excel, and then I start using the one that's native, really, now to Google Sheets. And I can double click on that at any time, and go in and make changes, and go from there. So that's really important, to know how to upload pre-existing sheets, or sheets that maybe somebody sends to you. You're going to find yourself doing that a lot if you're switching to Google Sheets. Okay, so now let's look at how to create one from scratch. So I click on New, I go to Sheets, and I can click to open up a new blank spreadsheet. And honestly, if you're familiar with Microsoft Excel, especially the old version of Microsoft Excel from 2003 or before, you should feel pretty much at home here. You'll notice that we have columns across the top of the spreadsheet, A through P, but of course it continues. If I browse to the right, you can see that it keeps going, and it goes all the way to Z, and it can go even further than that. And then we also have numbers, 1 through 36, but again, it actually goes much, much further into the tens of thousands if needed. So we've got this sheet made up of columns, made up of rows, and the intersection of the column and the row is what creates a cell. And each of these cells in Google Sheets can hold a huge amount of information. And that information could be a number, let's say 600. I just typed 600 in the cell, and I tap Return or Enter on the keyboard, and it puts that information into that particular cell. Now, 600 is easy to fit in that cell, but I could put 7 million, you know, whatever I want to put into there. And notice that the number that I put in is actually bigger than the cell itself appears to be. But when I tap Enter or Return, it still put that number inside the cell. It looks like it's spilling over into column C and cell C2, but that's not actually the case. I could still type in anything I want in cell C2, hit Enter, and the first number is still intact. So that's important to know. Each of these cells can hold a pretty big amount of information, whether it be text, whether it be numbers, whether it be a formula. Okay, the next thing you need to be aware of, just as a spreadsheet basic, is the idea of a range. If I click and drag, I can highlight more than one cell, and the word for more than one cell that's together is called a range. So anytime you click and drag to select more than one cell, what you're doing is you're selecting a range of cells. Down here in the lower left, we also have Sheets. This is Sheet 1, and I can click on the arrow next to Sheet 1 to do things like duplicate the sheet or copy it. I could rename the sheet. I could call this 2018 Budget or whatever I want to call it, just by renaming the sheet. I could change the color of the sheet. Now it doesn't change the actual sheet color, but it just gives you a color code down here at the bottom. Okay, you can also protect the sheet. So there's some nice things that you can do down there. But what if you just want a second sheet? You can click here where it says Add Sheet, and now I have Sheet 2. Now all of the sheets that you make, added up, form a workbook. Okay, so this entire document that I have here with Sheet 1 and Sheet 2 together, that forms a workbook. Okay, so I know that's a quick review that many of you probably already know about, but it's important to know the vocabulary. We have a workbook that's made up of two worksheets, and each worksheet is made up of ranges, yes, but for the most part, the basic elements are columns, rows, and cells. Now across the top of Google Sheets, we have some of the most common tools and features and options that you're going to need as you create sheets in Google Sheets, and as you use those sheets. And we'll learn some of these as we progress through this tutorial, but you could also figure out a lot of these on your own. You also have a Help menu here at the top, and you can do a search. Just type in what you want to do, and it will give you help that you need. Above these commonly used tools and options, we have a menu system, a file menu, edit, view, insert, format, data, tools, add-ons, which are pretty exciting. You can get add-ons that power up your Google Sheets experience, give you superpowers, basically, that you can use in Google Sheets. In this case, I've added only one, Split Names, that divides first and last names into separate columns, but there are lots of other add-ons that are great for Google Sheets that you should look into. For now, though, I just want to create a basic sheet. So I need to clean this up a little bit. I'm going to click and drag to select what I want to change. So you click and drag to select the cells that you want to affect. In this case, I just want to clear them out. I tap Delete, and it cleared them out. Okay, so now that it's cleared out, I'd like to zoom in a little so you can see this a little better. So I'm just tapping Command-Plus or Control-Plus to make it bigger so that you can see the screen a little bit better and closer up. Okay, so first thing I'd like to do is put in a little bit of a title here for my Google Sheet, and I'm going to call this Investments. So I just type that in. That's going to be the title that you see in the spreadsheet itself. Now, I should also officially title the spreadsheet, and I can do that by clicking up here where it says Untitled Spreadsheet, and I'll type in the title. Next, I'm going to go down to cell A2, and I'll click on the cell, and I'll type in Dollars Invested. Okay, so that's going to be my first column of data. Now, I could click to move on to my second column. That's absolutely acceptable to do that, but the downside to that is I had to take my hands off the keyboard to use the mouse to click here to enter information into B2. And so a faster way is when you're done typing in cell A2, for example, just tap Tab, and Tab will move you to the right. So in cell B2, I'll type in Investment Name. Okay, so what kind of stock did I invest in, or what mutual fund, or CD, or whatever it might be. Now, I'll tab over, and I'll type in Current Value, and I can just keep tabbing over and adding more and more information if I wanted to. But for the sake of this tutorial, I think that's good enough. That's enough information to track. Okay, so now I need to start entering some actual information here. So I'm going to click on A3. Let's pretend that last month I invested $100. I'll just type in 100 and tap Enter or Return on the keyboard. And notice what happens. Tab moves you to the right. Enter moves you down. Okay, so that's a little shortcut that will help you to enter data quickly in Google Sheets. And this is very similar to Excel. Now, incidentally, if you hold Shift and tap Enter or Return, look, it moves you up. Similarly with Tab, Tab moves you to the right. Shift Tab moves you to the left. Okay, so I'm going to tap Enter or Return to put in some other hypothetical investment amounts. Okay, so these are dollar amounts of investments. And I'll stop there for the sake of time. Next, I can go over here to Investment Name, and I'll type in Google Stock, let's say MidCap Mutual Fund, and a CD, and a 401k. And then, of course, I could go in and put in the current total value of those funds. So I'll just put in some hypothetical data. So give me a minute to do that, and then we'll resume the tutorial. Okay, so my spreadsheet's coming along fairly well here. But there are a few things that are bugging me, and I'm guessing that they're bugging you as well. The first is that my makeshift title here is in the upper left corner. It really would look nicer, I think, if it was spread out over the entire range of data that's listed here. So to do that, all I have to do is click on Investments and drag so that it encompasses the area that I would like to center the word Investments over. And then I can go up here to the top. And you'll notice, because I zoomed in, I've lost some of my buttons. They didn't fit anymore because I zoomed in so much. So watch out for that. But if it does happen, look what you can do. You can just go to More to get the rest of those options. So here's the one that I need. It's called Merge Cells. And I can just click on Merge Cells. You may have to click on it twice, and then click away. And look what it did. It broke down the walls between these cells, and it's merged those cells into one. And now I could go up here to the Frequently Used Tools bar, and I could click where it has Alignment, and I could choose to center that text. Okay, so that looks a lot nicer. Now, while I'm at it, I may as well make it bold. I could change its color if I wanted to italicize it. There are lots of different font options that I could use here. Another thing that's bugging me a bit is that my text does not truly fit in the space provided. Now, that's really not a problem. But if you want to make sure that your spreadsheet is highly readable, what you would do is you would go up here to the column letters, put your mouse on the line between the two columns in question, and then just click and drag to make it wider so that you can fit the information inside the box. Now, just like in Excel, there are a few tricks that we could talk about here. You can double-click between the two, and it will automatically resize it to the perfect width, whether that's wider or narrower. So that's a trick that you could use. You can also click and drag over multiple column letters, and then put your mouse between any two. It doesn't matter which two. Click and drag, and it will automatically make all three columns, in this case, the exact same width. So that's a nice little trick that you can use as well. But in most cases, I just double-click between each one to get the perfect width for each column. Okay, a few other things that are bugging me a little bit. I wish these numbers would come in as dollars, and it is possible to do that. I just click on the column letter. It highlights the entire column, and then I can go up here and say, Format as Currency, and any numbers in that column now look like currency, look like dollars. And I can do the same here. Notice that there are options to format as a percent, and there's many other options that you have here. Date and time, scientific, plain text, just a whole bunch of different number format options for you to choose from. Now, it's important for you to know that you can also do pretty much all of the typical Excel formulas and functions inside Google Sheets. So if you're used to using Excel and doing things like equals sum, okay, this is a sum formula. And as I type it, it gives me hints to help me to complete it successfully. But basically, I can just click and drag to select a range that I want added up, and it gives me the total there. And of course, I could have also just typed in equals sum, left parenthesis, and just typed in a description of the range that I want added up. So in this case, it would be A2 through, and through is indicated with a colon sign, A6, and then hit enter. Now, I should have put a right parenthesis, but I didn't. And I did that on purpose to show you that even though you're supposed to put in that right parenthesis, you don't really have to. Just tap enter, and Google Sheets puts that in there for you. So there's the sum of that column. I could do the same thing here. I could start the process over and type in equals sum, but there's a wonderful tool inside of Google Sheets, and it's called the Autofill Handle. At least, that's what it's called in Excel. And it works similarly in Google Sheets. So I can just click on that little handle, that little box in the lower right corner of the cell, and that, of course, only appears when you click on the cell. But anyway, click on that blue box in the corner and drag, and look what it does. It copies whatever is in this cell and pastes it into the adjoining cells. So what is in this cell? Well, it's a formula. This formula. So by dragging that over, there is now a formula here, and notice what it's doing. It's trying to add up B2 through B6, and this one is trying to add up C2 through C6. Now, this one works. It makes sense. But this one does not, so I'm going to delete that one. So try out the other formulas that you may be familiar with. Things like average, things like max, things like min, and there's so many more. But if you're used to using Excel, just have some faith that it's going to work inside of Google Sheets, and typically it does. You can use those same formulas and functions that you're used to using. Now, another powerful thing about Excel is having the ability to sort your data so that you can organize it the way you want to organize it. Well, guess what? You can still do that in Google Sheets. If we go up here to Data, here we have a Data menu where I can sort the sheet by column B. Now, why B? Because that's what I had selected before I went up to Data, and I can sort it alphabetically A to Z or Z to A. Also, if you're used to filtering, notice what you can do. You can click and drag to highlight the column titles that you've put in, and you can say, I would like to filter, and that's up here in Data. You can turn on the filter. You can also just go here to More and turn on the filter, and what that does is it gives you an additional button here that you can use. So, let's say I want to filter out everything except for CDs. So, I just uncheck everything but CDs, and for you to really see this, I need to zoom back out. So, you can see only CD is checked, and I'll click OK, and now everything else is hidden. I only see CDs. In this case, there's only one. So, I want to put everything back, so I'll click Select All, click OK, and so filtering and sorting, you can still do those inside of Google Sheets. They work slightly differently, but you can still get the job done. If you need help with it, click here where it says Help, search for sorting or filtering, and it will give you some additional tips. Now, there are some other things that you should be aware of with Google Sheets. For example, you can do things like conditional formatting. Just by clicking and dragging to select a range, you could right-click and choose Conditional Formatting here at the bottom, or you could go up here to Format, Conditional Formatting, and you could give your data a color scale. OK, so what it's doing here is it's coloring the data depending on how high the number is, how low the number is, and you can put in a midpoint if you would like. What's the midpoint color? What's the max value color and the min point color? So, there's some nice things you can do with conditional formatting, similar to what you can do in Excel, but not quite as many options. And so, basically, what I'm saying here is that Google Sheets is powerful enough that you can do most of the common things that you might want to do in Excel, but now you can do those in Google Sheets. Now, some of you long-time Microsoft Excel users are probably thinking, yeah, but it doesn't have my favorite obscure option that I must have, and it seems limited compared to Excel. And I'll be honest with you, it is. Google Sheets is missing some of the most powerful options that Excel has, but they may not be very commonly needed. They may be somewhat obscure. But what I would say to that is that's okay. Google Sheets has some advantages that make it still worth using and worth being aware of, you know, even if it doesn't have every bell and whistle that's in Microsoft Excel. So, let me point out three advantages to using Google Sheets over Microsoft Excel. Now, I love Excel. I'm not saying to stop using Excel. I'm just saying that there are a few advantages to Google Sheets that you need to be aware of. And one, probably the most commonly used, is this option here, to share. Now, you can do some of this also with Excel. I'm not saying you can't, but the share options in Google are really great. So, I can click share. I can enter the names of people or their email addresses that I would like to share this spreadsheet with, and they could help me edit it. Now, they'll be able to edit it because this shows a pencil. The pencil represents edit. But if I don't want them to be able to edit, I can just change it to can comment or to can view. So, it's up to you, but I'm going to leave it at edit. And then I could put in people's names or email addresses. I could add a note to them and then click send, and they would be able to edit this document. I could also click advanced and notice that right now, it's set to be private, only I can access, plus invited people. Okay, and as they accept that, they'll be listed as other people that can edit. If I want to, I can click change and make it completely public on the web. Anyone that finds this spreadsheet could go in and without any sign-in or anything, could post information in my spreadsheet. You may not want to do that. Another option though, that's really great is this one. Anyone with the link would be able to edit it, no sign-in required, or just share with specific people. Now, notice I said anyone would be able to edit, but in order for that to happen, I would have to go down here where it says can view and I'd have to change it to can edit. So you have to watch out for that. If I mean for anyone with the link to be able to edit it, I need to make sure that's marked, but I need to also make sure that this says can edit and not just can view. And then I could click save. Now, anyone with the link, well, here's the link. So I could then right click on that, copy the link, and either email it to people or post it on a website or whatever I want to do, anyone that clicks that link would be able to edit in this case. Okay, so those advanced options are often pretty important when you're sharing a sheet. So that's one thing that makes Google Sheets, I think, a powerful tool to use. Instead of emailing sheets back and forth as attachments, why not just post it in the cloud, in Google Drive, share it with a few people, and any one of those people can go to the sheet, make changes, and everybody sees the changes. There's one document. Instead of sending attachments back and forth and having 20 different versions of a particular document, this, in many cases, is a lot better. Another advantage to Google Sheets, and another thing that actually kind of makes up some of the deficit between the tools that you have in Excel and you have in Google Sheets, is this, add-ons. If you go to add-ons, get add-ons, you can power up your version of Google Sheets. Okay, there's insert and delete cells. There's AutoCrat. Now, I'm not familiar with all of these things. Remove duplicates, Google Analytics. But you could add in these superpowers or options into your Google Sheets account and be able to do a lot of the same things that you can do in Excel, but now you're doing them in Google Sheets with these add-ons. So to get the add-on, you just put your mouse on it and then click free. And most of them seem to be free. You have to choose the account that you want it to work with, give it permission, allow, in this case. And look, now, in add-ons, I now have two add-ons that I can utilize. So that's a second advantage to Google Sheets. The third is very powerful, but probably won't come up too often as you're using Google Sheets. And what that is, is because Google Sheets live in the cloud, they're online, they can access online information and pull that information into your sheet. So for example, let's say I would like in my investment sheet to get an overall picture of the market, how it's doing, et cetera, or a particular stock. Let's say I'm invested in a certain stock and I'd like to track its progress. Look what I can do. I can type in equals Google and look, it recognizes Google Translate and Google Finance. I'm going to go with Google Finance in this case. When I click on Google Finance, it teaches me how to pull information about a stock into my sheet. Okay, so let's say I'm interested in buying a stock in IBM. I could type in equals Google Finance, left parentheses, and then in quotes, I'm going to put in IBM comma. Next, I put the attribute in quotes. So price comma, and then put in date and in parentheses, the actual date comma. And this is the start date that you're tracking. And then date and the end date that you're tracking. How about 2018? And then comma and the word daily in quotes. Okay, so there's my example. Equals Google Finance, left parentheses, in quotes, IBM. But I could put Coke in there. I could put Google in there, but you need to put the stock ticker, not the actual name of the company. In this case, it's one and the same. And then you put in the date and the word daily. Now watch what happens. When I hit enter, it loads up that information of the daily value of the stock. And I can go back and see how that has gone up, how it's gone down, what the history of this stock is from day to day. And that's like three years worth of data that I can track. So that's something that's quite difficult to do in Microsoft Excel, but you can do that in Google Sheets. And it's still kind of hard, but at least it's doable. So I hope that this tutorial has helped you see the possibilities for transitioning to Google Sheets if you'd like to transition to it, or for those that want to keep using Microsoft Excel, but use Google Sheets from time to time as a way to share spreadsheets with colleagues or coworkers. I think it's important to know about the options that we have here in Google Sheets. And I hope that this has been a worthwhile video for you. So thanks for watching. And please consider following me on my social media websites like Pinterest, Twitter, and Facebook. And please do subscribe to my YouTube channel for more videos about technology for teachers and students. And watch for a new video at least every Monday.