Google Sheets Basics A-Z: Introduction to Spreadsheets (Tutorial for Beginners)

Key Takeaways

In this video, we're going to cover introduction to Google Sheets. So, we'll go over all the basics you need to know to be able to create basic spreadsheets, design some layouts, add some basic formulas, organize your data, basically all the common things you need to know about Google Sheets, whether it's for you personally or for work. So, if you're starting a new spreadsheet in Google Sheets, I highly recommend starting from Google Drive. This way you'll know where your files actually are stored. A lot of times what happens people go to Google Sheets directly, they create a new file and then they have no clue where the file actually is located. So for that reason it's a better idea to always go to your Google Drive. So in this case I'm in Google Drive under this YouTube documents folder. Now, obviously, you can go to new, create your own folders. The way you want to organize your files, it's up to you. But assuming this is the folder where I want my spreadsheet, I'm going to rightclick here, go to Google Sheets, and do a blank spreadsheet. And this way, I know where the spreadsheet is going to leave. And I'll right away I'm actually just going to close this for now. Right away, I'm going to name this spreadsheet. Right here, it says untitled. I'll just click here and name this file. I'll just call this one Google Sheets Basics. And at this point, if I go back to that drive folder and let's just refresh this, you should be able to see that now we have that Google sheet here. So, next time you need to open this, you know exactly where to find this. Now, I'm going to go back to Google Sheets and we're going to set up a spreadsheet here. So, in this lesson, let's assume we want to create a spreadsheet for a cleaning business. So, we want to keep track of different services they do, the date, who's the cleaner, was it paid or not, and things like that. So, the first thing you do is start setting up some columns you think you're going to need. So, we'll do client name. I'm going to hit tab. Tab goes to the next column over. We're going to want to know where exactly this happens, this particular service. So, we'll do location. Next, I'm going to add who the person is who's going to be doing the cleaning or people. So, I'll just do cleaning team. I'll add a column for the type of service. I'm going to want to know when exactly this happened or will happen. So, I'll do date. If you want to keep track of how many hours this took or your estimate for hours, we'll add hours. I'm going to add a column status here. And for status, this is going to be more like completed, is it scheduled, was it canled, something like that. So this will be basically my headings. So a typical line from this would look like this. So let's say we have a client here. I'm going to name this person Tom Williams. And we'll do some location. So I'll do 123 Maple Street. I will have to be careful with locations I do because I had a video that was banned because the locations I entered were real looking locations and they thought it was exposing actual people's data. So again fake location uh we'll have maybe one cleaning person or multiple cleaning people. Now if I have one person that would go like this I just have the name right. I'll do another example on the next line with more than one. So service type I'll do I don't know let's do standard cleaning. Now at this point you can see the standard cleaning didn't actually fit in the service type column. So I need to resize the column. So right on top here we have the columns D and E. So to resize one of them you go right between those two and you get this icon. You have two options here. You can either click and drag to whatever size you want or just double click. Double click will autosize the column to make sure it's big enough to fit the data. Now a date. So I'm going to do something like this. Let's say actually let's do a proper date. So 51 2020 5. Now of course if you are not using US dates yours could be the opposite. So, if you're doing May 1st, you would probably do 15 instead of 51. And then hours, uh, let's say 3 hours, and I'll have complete. So, let's add another line here. So, I'll do Jennifer Lee. Now, in this case, if I have two people, what I'll do, I will basically just comma, space separate them. So, I'll do Mike, comma, space, and then I'll do another person. let's say Jane and of course the rest is just some data. So I'll just quickly enter some data here and we'll continue from here. Now, if you're new to spreadsheets, I would highly encourage you to instead of just watching, open a blank spreadsheet and try to follow what I'm doing. Because a lot of times, if you watch somebody else do things, many things may feel very simple and easy, but because you missed a little bit of detail, you may find yourself in a situation when you try to do it yourself, it doesn't work. Make sure you do this hands-on and if you have any problems, go back and rewatch the part of the video. If it still doesn't work for you, leave a comment, whatever problem you're having, and I'll try to help you. For now, I'm just going to add a couple more lines here. Now, I want to do some formatting for this. So, what we'll do, we'll first convert this to a table. So the way that's going to look like, we're going to just highlight this data. If you're not a mouse person and you prefer to do this with your keyboard, you can click on client name, press and hold shift on your keyboard, and use your arrow keys right and down to select your data. Once this is selected, I'm going to go to format and I'm going to do this convert to table. So that will do this. You can see how it creates some of this design. It gives us a name for this table. It's called table one. I'm going to rename this right away. So, I'm going to click here instead of table one. We're going to rename this to orders or whatever name you feel is appropriate. Now, if you're unhappy with the actual design you get, maybe you don't like the colors and things like that, you can change that by clicking on this little arrow. And here you can see we have header colors. So this top part, what do you want that to look like? We can choose one of these existing colors like so. If you're unhappy with all these colors, you can always go to custom. And here you can choose your own color. So let's say I want a little darker shade of this color like so. I can choose that. Hit okay. And that's the color on top of this. Now, once you convert this to a table, the next thing you would want to do is choose some data types for your columns. This is especially going to be important if you're dealing with dates or numbers and things like that. So, in this case, for example, if I have date here, I would open this drop down here and go to this edit column type and see it's already under date format for me. So, it already figured it out. If it wasn't, you would want to make sure you choose date. This is already selected. I'm happy with that. So, the same thing you would do if you have hours, let's say, you go column type. See, for this one, it didn't choose a type. It's doing none. Now, here I could do it should be like a number type. So, number. So, that's ours. If you have dollar amounts, you would of course do currency type for me. So far so good. Next, what I want to do when I have service types, I would like this to be a drop-down between different available service types. So, if I go to that same arrow here, go to edit column type here, one of the types is called dropdown. So, if I check dropdown, this is going to show on the right and it's going to allow me to set up this dropdown. Now, what it's going to do, it's going to pick up the options you already have from your existing data like this. So, you can see standard cleaning, deep cleaning. If you want to add more, you can click add another item. And we can do move in or move out cleaning. And of course, you can add as many as you like. I can click again. And the same way if you want to remove some, you click on this little trash icon. So for me, I'll do these four. So you can see they're all under gray color. If you prefer different colors, you can assign different colors. So you can open this and say standard cleaning for example should be this color. Deep cleaning is going to be maybe this color and this is going to be maybe that color. You can give some colors if you prefer to do so. I'm going to press done. And now we will have a drop-own choice. See between those four options here standard cleaning, deep cleaning, move out cleaning, moving cleaning. Now, the same type of thing I would do with status. Now, before I do that though, I want to add another line. So, you can see what happens now when I go to the next line here and we add another record. So, I'm going to go here and do Lisa Brown. If I tab, see what happened now because we had a table when we did this convert to table. Now any new line we add right below this will inherit and become a part of that table. So now it will automatically pick up on all the designs including the dropdowns and things like that. So we'll enter some data here. Then here we'll choose you can see the dropown automatically shows up which is great. Now before I go ahead and add a dropdown for status options, let's talk about how to modify our existing dropdown. So what if you have this, you want to change this, you can just click here, click on this edit. That's one way to do this. Or you can simply just click here, go to edit column type, and do drop down. And that will bring you that same sidebar. So you can modify your dropdowns. Now for me this is fine. So now I want to change status. So I'm going to click here edit com type drop down on this too. You can see it picked up complete canceled out of the list. I'm going to add more. I'm going to do complete canceled and scheduled. As you type these different options you definitely want to make sure you don't mistype when the dropown shows up. I want scheduled to be first. So I can go here and take this little icon and drag this up. So scheduled, complete, cancelled. That's the order. I'm going to go ahead and assign some colors. So for cancelled, I do that. For complete, I'll use um green color. Maybe something like this. And for scheduled, we'll use that. Seems fine to me. I'm going to press done. So now we should have another drop down here that's a choice between those three items. So that brings me to this cleaning team. So what if I want this to be drop downs too. Now to be able to do this with drop downs you want to make sure that you have this see comma and space not just a comma. So I have comma space Mike comma space Jane. So with this now if I click on this cleaning team go to that same drop-down type see what it gives me is whatever it sees here in this column. But I don't want Mike and Jane together. I just want different people. So here I'll just modify this list to be Emma Jane and then well we already have Tom Mike here. So I'm going to delete this one. So, we got Emma, Jane, Tom, Mike. Now, obviously, if there's more, you probably want to add those two. Now, for me, this is good enough. I have my list. But if we do this the way it is right now, it will just let us just choose one. What I want to do is choose more than one person. So, this is where I'm going to check this box, allow multiple selections. So, with this, if I done, what's going to happen? see that comma and space separation. Now turn to this little options, Mike and Jane. Just to show you how this works going forward, if we add another client, now we have our cleaning team. If I open this, it will show all those names and we can either choose one. So, for example, I can just do Jane or if I want multiple because I did allow multiple, I should be able to do Jane and Tom like this. And if you want Mike here too, you can add. So, we can do multiple people here. So, that's good. So, I did Jane and Tom. Now, when I was choosing my data types, this one we did, just to remind you, date. So because it's a date in addition to just typing like 51 like that what I could also do if I just double click here it will give me a calendar and then you can choose your date from this list. Now of course here I'll do some number of hours and for this one let's say we're going to do scheduled. Of course if you feel like you need to see more of these you can resize this columns like G. You can expand it. This D, you can expand that too or just double click. Now, at some point, you may decide that you don't like the position of your columns. So, maybe I want this date not here. I want it here or I want it as a first column. How do I do that? So, what do you do? You click on this E, which is the column on top. You'll see this hand that shows up. You click and hold and you can drag this to go someplace else. You'll see it shows this little divider that shows where exactly that column is going to go. So if I let it go right here, it's going to go between location and cleaning. Now assuming I wanted it to be first column, I can click here and hold go all the way left, let it go, and now date is the first column here. Now if you wanted to add a new column maybe between two different columns. So let's say we need to add a new column. We want this new column to go after cleaning team but before service type. So one way to do that if you just rightclick on this top part E. See you get insert column left or insert column right. So I'm going to go insert column left. So that adds a new column. We need to add a name for this column. So let's say this column is just going to be just a mark whether this was paid or not. So we'll just say paid. And in reality, I actually want this column to be last after status. So, I'm going to take this and move it over here. I just wanted you to see how to add a column in the middle if you need to. So, now that paid is the last column. So, I drag it over here. Now, here what I want to do, I just want this to be a simple checkbox whether it's paid or not. So, what I'll do, I'll click on this arrow down again, go to edit column type, and here we have this checkbox as a type. And that will convert this column to little check boxes. So you can just check the box like this. And that would be paid or not paid in case it's not checked. Of course, we can make this column smaller because it doesn't require a lot of space. Now you may want to change the color of those checkboxes. So the way you treat checkboxes as if it was your text. So, for example, if I have this text here and I want this color of the text to be, let's say, red, I would select this, then go on top here to this little text color icon and choose a different color text like so. Now, I didn't really want to change this, so I'm going to go to reset. That's fine. Now, the same thing I could do with this. If I select this right here, I go to that same text color and simply just do let's say this green color, it will convert those checkboxes to be a green colored checkbox. So change the text color, you will get basically your checkbox color. Now if we do add another row of data here you will see it will pick up on that color still green from here which is great. Now let's also talk about how to delete rows. So let's say I did add this row but I don't want it anymore. So you can simply just rightclick on this row number eight and then delete row. And you could delete any other row in the middle. If you wanted to delete line five, right click on line five, delete row, it will delete the row. Now, sometimes you may accidentally do something and change your mind. So, for example, that row I just deleted. If I wanted to have it back, I can use undo, which is this undo button on this top left. You can see right here, you can click on this or control Z. Command Z would be your shortcut to do the same thing. Now, of course, you can keep adding more columns. Maybe you want a new column for notes. Now, obviously, you can have that somewhere in the middle like we did before. Or if you want it after this, you can simply just type the name of the column notes. And because it's the next column, our table will make it a part of it. Now, we have this new column of notes. Of course, you can make this bigger if you are planning to have longer format text here. I'll resize this. Now you can see what it did when I add this new column. It picked up the color of the text from this previous column, which is the checkbox color essentially. So for this one, I'll just change the color. So I'll go here, change this. Reset goes to your default color like this. Or you can just choose your own color, of course. There's a couple of more things I want to talk about as far as the design of your table. So, we went over how to change this background color for headers. Now, you can also go back here and adjust other things like table formatting. So, you can see by default we have this alternating colors. And what that means is that we have basically this white line and then this gray line and then white line, gray line. If you don't like that, you can uncheck that and it's just going to be one color. Now, for me, I would prefer to have that. So I'm going to put that back. Now there's also table grid lines. So if I just check this so you can see what happens. It will basically just add this cell structure for each cell which I will do. So I will add grid lines. Now I can go back here and also have both grid lines and alternating colors like this. Obviously other things we have is this condensed view. Condensed view basically will make this a little smaller so it doesn't take so much space. So if I go here and do condensed view again, you can decide whether you like that or not. It's up to you. For me, I'm going to go back to as it was. And then finally, if all of these are not enough for you, you can go to that show advanced options here. And this will allow you to modify the actual colors that you do. So for example, the header color is one color, but then for alternating, maybe you don't want white and gray. You want different colors. So you can choose your colors here. So you can see all of those are showing up here. You can also apply those right here. So I'm going to do done. This is good enough for me. Now once you have a table, you can do a lot of interesting things with your table. So one of those would be sorting your data. So maybe you want this data sorted alphabetically by client's name or by number of hours or some other column. So it's going to be pretty straightforward. You can simply just click on this arrow dropdown and you can see sort your column. So we can go sort A to Z. And you can see how this column is now sorted alphabetically by name. And because we made a table, it knows the boundaries of this table. So it knows exactly where this starts and when it ends. And it's going to basically move the entire line for that person together with the data. So we can do this by other columns, of course. You can go by hours, sort, and do Z2A, meaning the highest number on top, lowest all the way down. Or maybe you want this sorted by date. So I can go date, sort column, A to Z, meaning earliest date on top, latest all the way down. You can also filter your data. So maybe you don't want to look at complete status right now. You can open the status and go filter column. And right here we have our options. You can see we can uncheck complete and keep cancelled and scheduled. Hit okay. And basically what's going to happen it's going to hide the other rows and just keep the ones that match. So you can see now it goes one and then four and then seven and then eight. So now five six are hidden for example because they used to be complete. Now you'll see the icon will change a little bit. It will show that there's a filter applied here. You can later on open this right here in your filter section. And if you want to just get back all the data, you can just click select all. Hit okay. And that brings all the options back. You can go back and check paid column for example. In this case, because we have the check box, what happens? If it's checked, it's true. If it's not checked, it's false. That's the way you would filter this. So, very simple. You can just filter your data as you see fit. Now, in addition to filtering data, you can also group your data. For you to see how that works, I'm going to add a couple of more rows to this table. Let me add a couple of more data points. Okay. So now we have a couple more lines. So let's say I want all the standard cleanings grouped together, deep cleanings grouped together, something like that. So what you can do, you can click on this arrow and here, well, I'm going to go back to the previous step to column menu because right now we're under filter menu. So right here instead of filter column I can do group by column and I'll go dismiss for now. And you can see what happens here. We get this service type and it gives us this count for each one of these how many we have. So we have three of these and you can see how it took all deep cleanings and it put them all together. Then move out cleanings they all are together. So you can see we get a count of two, count of three here. Now you can see for hours it gives us the average hours here. That's what it did. Now you can always change this. You can click on this dropdown and choose a different function. So you can say I want to sum and this way it will give you the total number of hours for all these combined. For some of them you may not want anything. So for here for example I can do none. Here I could do none. And for some of these, I'll just do none. Right? So you can just keep these for the ones that you actually want. So I did sum, for example, for this one, count for this one. Of course, you can remove it for all of them if you don't like it. But you can see now we have this grouped by service type. Now, at some point, you may want to go back to ungrouped data, the way it was before. And the way you would do that when you create a group, you can see this bar shows up on top and you can see it says a temporary group by service type. And here it gives you the save view button and this little X button. So if I click on this X, you can save this group for later use. For me, I'm going to do don't save for now. And we're back to the way it was before. Now, you could do this by service type. Of course, you can do this by status. Basically, when you do this, you want to make sure you do this by something that repeats. Like for example, in this case, I have like three complete, three scheduled, one cancelled. If I want to have all complete grouped together, scheduled grouped together, we can go back here. Go back to the previous step. Do group by column. And now it's going to just put those together like this. Now, in this one, I'll go ahead again and choose some functions. So, I'll do none here. I'll do none here. For this one, I'll do none. For this one, instead of average, maybe I'll do sum. And for this count, I guess is fine. And for this one, I'll do none. Now, if you don't want to do this every time, obviously, and go change this functions. So, next time you want to just come back and look at this this way, maybe later on, you can always just go ahead and save this view. So, you can see there's this save view button. I'm going to click on this. It's going to ask me for a name for this view. I'm going to call it group by status. That's fine. Hit save. And now we have this view that was saved. Now, if I want to exit this, I can click on that same exit button. Now, we're back to ungrouped territory. Now, later on, if we want to get back to that group that we just have, you can now simply just click on this little table icon on top. And here we'll have that group by status which is the name that we assigned to our view. So if I click on this, it will immediately just go back to that state where we have our grouped columns by status and we have the functions that we chose like so. And then of course later we can just exit out of that view. So now let's add a couple more columns. Let's add another column right here. So, I'm going to right click here and insert a column. And I'll call this rate. And we'll assign some hourly rate for these different services. So, I'm going to go $69.99 here. Maybe we'll do 110 here. Of course, you can keep going. Now, I want this to be more like dollar amounts. So, I'm going to click on this column, go to this column menu, and change the column type. So this is going to be a number type and it should be currency. So I'm going to go currency for this. Now going forward because we have this currency formatting set up. When I go here and enter a rate, see it will automatically pick up on that formatting with dollar sign and decimal points. And of course if you press enter, it sends you to the next line down. If you hit tab, it goes to the next column. Right? So now let's get to writing some formulas. So maybe we did this. Now we would like to create a formula that will take this number of hours and multiply it by rate and get us the total for this job. So we'll just go ahead and add another column for this. I'm going to right click and insert a column. We'll call this one total. And for that we need to take this three multiply by $69.99 and get our total. So to do this we're going to start here. We're going to do equals. And you can see Google Sheets for a lot of basic formulas will probably give you suggestions. A lot of times they could be actually accurate. So right now it's suggesting to do F2 and then this asterisk and G2. Asterisk would be your multiplication sign. So right now F2 is this three G2 is this $69.99. So the way you do that you basically look here. See this the column this the row number. So that makes this F2 and this is G column second row. So that's G2. So to begin a formula you always start with equal sign. Then we're going to do the actual calculation. So it's going to be f_sub_2 meaning this three and then you do the operators. So for the operators you have plus you have minus then you have multiplication which is the asterisk and then you have division which is this forward slash. Now there is this one too and that's exponents. So if you want to take a number to the power of a different number that's the way you would do that. I'm going to go over this in a second separately, but for now I'm just going to go times and then G2 meaning $69.99. Now, if I enter because we're in a table, it will suggest to just apply this formula for every line. And I'm going to say yes, check this box. So, why do we do this with a formula? The reason we do this with a formula, well at least one of the reasons, if I, for example, update one of these numbers, let's say I change this from two to three, you'll see that my calculation will update and go up and get me the new total. And then also when I add more rows, go to the next line. So let's actually add another line here. Now, of course, we're going to pick our cleaning crew. We're going to select our type of cleaning. But one thing I want you to notice is that automatically our new row from that table has that formula in here, it calculates zero here because we don't have anything here. Now, if I go ahead and enter some numbers here, let's say we do like 2 and a half and that's let's say 69.99. Once again, you will see it will autoc calculate for this line and give us the total. Now, I want to zoom out a little bit so you can clearly see what's going on. And if you want to zoom out, you do control plus or command plus depending on the platform you're on. So, plus zoom in, minus zoom out. So, you can see here's my table, here's my rows, I have my data, and I have my formulas now autoc calculating right here. Now, let me quickly go over basics of creating a formula to truly understand how we did this. And for that, first I want to rename this sheet that we're on. See, we have this worksheet. Currently, this called sheet one. You typically want to rename this. So, I'm going to double click and call this one orders, I guess, or let's call this orders data. And I'm doing this to not have the same name as here on top, just to avoid confusion. What I'll do, I'll add another sheet here and double click on this one. I'm going to name this one formulas. And I just quickly want to go over basic formula logic to make sure that makes sense before we keep going on our table. So, as I've mentioned a minute ago, we have plus, minus, multiplication, division, and exponent. So, I'm going to go here. I'll do exponent. We have multiplication with division and then we have plus and minus. So those are the five operators. Now we start a formula by doing equal sign. And then if you have a couple of numbers you want to add. Let's say you want to do 9 + 5, you simply just type 9 + 5. You can see this popup shows up telling you what the total is going to be. 14. And if you want to apply, you just hit enter and you have your 14. Now, if you forget to put equal sign and you just type 9 + 5, none of that is going to happen. So, you want to make sure you start with equals to make sure that that's a formula. Now, you can also go back and modify your formula. So, if we double click on this 14, which is the answer, we're back to the formula. So, you can modify this. Now, if you want to do 9 minus 5, you would do 9 minus 5. Hit enter. That gives you four. If you want to do multiplication that's the multiplication like this 9 * 5 and that's 45. Of course division would have been your division. So you can see 1.8 and then finally we have our exponents which is this. So this is 9 to the^ of 5 meaning 9 * 9 * 9 * 9 * 9. So that's your basic formula logic. So basically in these examples I'm using my spreadsheet as a calculator. Now you can do multiple operations in the same formula. So I could in the same formula do 9 + 5 + another 7 and then keep going min -2 and keep going like this. If I enter it will tell me what the answer is. Now because you can do multiple operations, you need to be aware of order of operations. And the reason that's important is because if you have a formula like this, let's say 8 + 2 * 2, like if you just go left to right, you may expect, well, 8 + 2 is a 10. And then if we do time 2, that's a 20. But you're going to see that this is not going to give you a 20. is going to give you 12 because Excel is going to follow order of operations and order of operations is the same order of operations you learn in school. So basically we start with parenthesis then we have exponents then we have multiplication divisions and then we have plus and minus. Now if I look at this formula, there are no parenthesis. Next is exponents. There are no exponents. Next is multiplication, divisions. So we don't have a division, but we have multiplication. So that's 2 * 2, that's a four. And then 4 + 8, that's your 12. Now, if my intention was to make sure I add first, I want to make sure that this part is in parenthesis. So 8 + 2, that's your 10. and then times two that's your 20 and there you go we got a 20. Now that's your very basic formula logic. Now that being said when I was writing my formula here I didn't do that. So here in the formula see when I double click on this so you can see what the formula was. I didn't type 3 * 69.99. What I did instead I did F2 and G2. So what is the reason behind doing this? So the reason behind doing this, if I go back to this formula sheet to go over this very quickly, if I let's say have a couple of different cities. So let's say we have Chicago and then we have Austin. So assuming you have a warehouse with different items. Let's say you track we have 45 units here and we have 25 units here. Now you may want to calculate total number of units. If I just do this formula the way I was doing the formulas on this tab. If I just do equals 45 times and then the other number well not times actually plus in this case because we want to add them 25 like this. So 45 + 25 that makes it 70. So this is great. It works. It gives me the total. But the problem with this is if I later update these numbers and go to let's say 42, this number doesn't actually update. So it's stuck on that 70 that we did. So now I have to go back and update this 45 to 42 as well. And that's not a good experience when you use a spreadsheet. So instead what we do, we go back to this formula. And to make sure this updates automatically, instead of 42, we reference to the cell where 42 is. And that would be A8. So we type A8 because that's where the number is. And instead of number 25, we do B8. Well, for the same reason, because the number is here. So if I enter, see, that gives me the same 67. Except now if I update this, see my total updates. If I update this, my total updates and I don't have to go back and fix this formula again. And that's the reason why we don't hardcode the number in the formula. We use a reference like A8, B8, etc. Now, if you have more than one item, you'll probably want to keep track of what item these quantities are for. So what we'll do, we'll right click on this and insert a new column to the left. Now when I add a new column to the left, I'm going to name this column item. This 33 moved from A column now to B column. So now it's in B8. And if I go and look at this total formula, double click on this 54. See, it says B8. So Google Sheets was able to automatically fix this for me. So I don't have to go back and fix this just because I add a column. It will fix it for me which is great. And the same for C8. Now that we add some let's say item numbers and this could be just a few or it could be hundreds of item numbers. And for each one we'll enter our quantity in stock. And at this point, you would want to get your total for this item and this item and this item and so on. So, since we have the formula in this first line right here, we want to apply it for all the lines. And to do so, we can click on the cell with a formula, go to this little corner, see this little dot, and just click and drag it. And that applies this for every line. So it's going to go to the next line, apply the same formula, next line, apply the same formula, and so on. It does for every row. Now, when we had a table, I didn't have to do that dragging. And the reason for that is because initially I already had all the data lined up. So here when I started creating a formula here I already had all this existing lines and it figured out oh you're doing a formula in this column so let me just apply it to all and it made a suggestion to us if you remember to apply it to all lines but in the end of the day that's the same thing it does what it does it automatically takes this thing and it drags it for you so you don't have to up until now when I was doing the formulas I would manually type my references, I would do like B8. So I went here and I manually literally entered like B8. So sometimes it will also just suggest things for you as you can see. But if it doesn't and you want to do your formula after equal sign, if you just click on the number, it will type that B8 for you in the cell. And then I can do the plus sign. And then if I click on this, it will type C8. Now be careful though. You don't want to keep clicking accidentally on other cells because it will keep changing the reference. So I want to make sure I click on this which is C8. And when you're done, always press enter to apply the formula. So you can either manually type B8 or you can do the clicking. It's the same thing. Now this also brings me to summing up a whole column. So if we have this whole column of numbers and we want to get the total of these, one way to do that is to go here for example and do equals and then take this and then do plus this plus this plus this which will work but this is not a scalable solution because if we have hundreds of numbers it's going to be difficult to impossible to do this. So the other option is to use a function with a range and the way that works you can see the suggestion that it's doing. So I'm going to go sum open parenthesis and then in parenthesis we have what we call a range. So we have basically a starting point. The first number is D8 and it goes all the way through D11. So you'll have D8 colon and then D11. So basically that colon signifies a range. So we're saying let's grab everything between these two cells. And then we close parenthesis. We hit enter. And that should give us the total. With a setup like this, it wouldn't matter if you have 10 numbers, 20 numbers, 60 numbers. It's still going to be the first number, the last number with a colon, and it's a range. And it works. Now, going forward, if you keep adding to this table and then this formula, see, it didn't happen here automatically. So, I'm going to have to click on this and go to the corner and drag it. Now, that applied this formula for this line, but my total doesn't update. So, my total, if I double click, still doesn't include this 67. So we need to update this to line 12 in order for this to include that number. Now the reason we have to do this is because we never converted this to a table. Had we converted this to a table, it would have happened automatically. The formula would have copied for this row here and for this total would adjust as well. Now to show you that in action, let's go to this orders data and do that in that orders data. When we do that, we'll do that on top of the table rather than below the table. And the reason that's a better idea to put your totals above the table is because if I wanted to add now another line, now I have this problem that I have to create a space. Now you could do that by right-clicking here, insert a new row, and then do that. But to avoid adding rows all the time, you can just put your totals above and don't have to worry about that. That's exactly what we'll do. We'll go back to this orders data. And what I'll do, I'll add some space on top of this. So I'll right click on number one, insert. That adds a row on top. I'm going to rightclick again and insert. I'll add maybe a couple of more. So right now I have a couple more lines on top of this. I'll do control minus or command minus just to zoom out a little bit. And on top here, I would like to get the total for all these numbers. I'll label this grand total. And what we'll do, we'll just add all these numbers down here. So, we'll go equals sum. Now, notice as soon as I did this, it picked up on that column that we had with numbers. I'm going to open parenthesis. In my previous example, if you remember, I did age five colon age 13. So, right now, I'm going to ignore this suggestion and I'm going to do that and then we'll talk about why we should be using what we see instead of H5 colon H13. Let's make sure we type this right. And by the way, if you're new to formulas, if you make mistakes, it's just going to happen pretty often. Don't get discouraged just because you're getting errors with your formulas. Just do it a few more times. The more you do them, the better it gets, the easier it gets with spreadsheets in general. So, I'll do this. Hit enter. And that should give me my total amount. So, you can see 3594. Now if I now add another row to this table. Now you can see that automatically this new row already has this formula which is great. So that worked out very well. The new row has the formula. But what didn't work out is this. So this still does not include this new row because we said go from line five until line 13 and it stops there. So the alternative is to say just grab the column from this table. Now if you remember when I was creating the table I named my table over here orders. If we do orders that refers to the table that's the entire table though not just one column from the table. Now what we can do, we can add a square bracket that will allow you to get to different parts of this table. And one of those parts is these different columns like date, paid, rate, hours, notes. Now total is the column that I want. So I'm going to go total and see it closes the bracket for me. Then I'm going to close parenthesis. So now we're saying get me total column from orders table. If I enter that gets me this. And basically the advantage of this is now going forward if I add another data point here. Now notice as I add all this automatically my grand total updates to include whatever this new line was in this table. So now I don't have to go back and modify my formulas. it's going to know, okay, we have this orders table. In that orders table, we have this total column. Let's grab that information and include that in our total automatically. Now, one last thing I want to go over here. In this example, when I was doing this, when I did equal sum, I did manually end up typing this whole thing. Now, you could also once you open parenthesis, well, if it's suggesting the right thing here, you can just select the suggestion. Obviously, if it doesn't, what you can do, you can just go ahead and highlight this data in this column. And when you highlight it, you can see it will automatically pick up that it's a table. And in that table orders, we have total column. This way, you can avoid typing the entire thing manually. Simply close parenthesis, hit enter, and that's your total for that column. Now, I'll just apply a little bit of formatting. This will make it currency. Now previously we were working in a table so I was formatting the whole column in the table. Now this is not a part of the table. So what I'll do I'll go up here to this one two three box and this is where we can set our currency to individual cells or ranges. So I'll go here do currency. So that's the formatting for our numbers. Now for grand total we'll just give it some different background color. So this is our paint bucket for different background colors. I'm going to select that same color. See from custom is going to basically display the colors that you already chose recently. So I'll go with that. And then for text color, I'll go with white color text like this. Maybe make it bold. Possibly align it center aligned. You can also add some borders. We can select these two. Go here and add some borders to this. And you can see now we have this border line. I'm going to add another row on top of this. Rightclick, insert on line one. It gave us some of this formatting that I didn't actually want. So I'll just go here under format and do clear formatting to get rid of that formatting on top. And then I'll just increase the font size for this a little bit. So I'll select these two together and this is our font size currently 10. I can click plus and we can go to larger format. Now again if you have any other formatting choices maybe you want to make sure this is centered vertically. You can do this. You can increase this line two a little bigger to make this sit like that. So you can decide how you want this centered. So, I'll center it both ways like so. But going forward, what should happen as we keep adding more data, it should automatically apply this formula here. And then this formula will pick up on the table and include our extra rows. So, this covers most of what I wanted to cover in this video. There's a couple of extra things I want to go over. And one of them is, first of all, I'm going to delete this formulas tab. So, I'm going to click on this arrow and we're going to delete this one because I don't want to keep this anymore. Confirm that we want to delete this. At any point, if you wanted to make a copy of one of your tabs, you can click on that same arrow here and do duplicate. And that makes an identical copy of your sheet. You can see we have now two of these. Now, this was automatically renamed to orders 2 as a table name because you can't have more than one table in the same spreadsheet with the same exact name. So, keep that in mind, which is fine. So, I'm going to delete this anyways. Just wanted you to see that you can actually quickly just duplicate your tabs or sheets. But the last thing I want to go over is currently when we have the service types and we have complete, cancelled, scheduled, etc. Right? If we decide to change this list and add an extra item here or remove an item from this list or something like that. What we would need to do is go back here and I'm going to go back to the column menu and we can go to edit column type, click on the drop-down and modify this list, which is fine. That could be what you want. But what I want to show you is how to automatically assign it to another list so that as you update that list, it will update your options here. And for that, what I'll do, I'll just make another tab here. And I'll just call this options. Double click and call this options. And I'm going to just create a table with my options. I'll just do it for this. Well, we'll do it probably for cleaning team in a second, too. But right now, I'll just do this for this status column. So, I'm going to have complete, cancelled, scheduled. Now, keep in mind since I'm planning to create a table out of this, I have to have some column name on top and that's why I left the space. So, I'm going to call this status or you can call it status options. It really doesn't matter. And what I'll do, I'll just select this and I'm going to convert this to a table. So, I'll go format and convert to table. And here, this table is now called table one. It's not a good name. So, I'm going to click here and I'm going to call this status. Let's make sure we type this right. Status options. So, no spaces here. So, I'm going uppercase S, uppercase O. And that's the name of this table. Make this bigger so you can see that table name. Now, what I can do, I can go back to my orders data. And when I'm assigning this status, I can go here and do go back to column menu and do the type to be a drop down exactly like we did before. But here instead of this criteria being drop-down, I can open this and choose drop-down from a range like so. And then it's going to ask me to specify the range. So here I'm going to click on this little box and it's going to ask me to select the range. So I'm going to go to that options tab and I'm going to select this. Now when I select this, see what it gives me. It gives me options exclamation A24. But what I actually want, I want to link to the table called status options. So that's going to be status options. name of the table which is the top part right here and then open a bracket and then the column name is status options with a space. So it's going to be status space options and then close the bracket. So I'm saying let's grab the data from this table this column. Now we do need an equal sign before this. So I'm going to do that. Hit okay. And see when I did that, it again picked up all these options. Now that's what we're linking to here. Of course, you can do your colors as usual. Complete, cancelled, scheduled. And I'm going to hit done. So the point of doing this, so right now we're still going to have our complete, canceled, scheduled, but going forward now, if I go to options and down here, I add another option. So now I'm going to just add reserved as another option. If I go back to that orders data and look at my dropdown, you can see reserved is automatically here. As we update the table, it will update our drop-down choices. Now the same thing we can do with our cleaning team. So I can go ahead and create another table. And once again, I'm going to convert this to a table. So I'm going to select this, go to format, convert to table. I'll make this a little bigger. So instead of table one, I'm going to call this staff. So now we have staff is the name of the table. Member is the name of the column. So keep that in mind. So, we'll go back to our orders data. I'll open this cleaning team column. Click back here. So, we get to our definitions. And under column type, I'm going to do drop down again. And right here, instead of drop-down as we did before, I'm going to open this and do drop down from a range. We'll do equals. We'll do the name of the table which was called staff open square bracket and then the name of the column. Make sure you type this correctly or it's not going to work if you mistype the name of the column or the name of the table. So I'll do this. Now we do want to make sure we allow multiple selections on this. Now you can see I got this error message that this is not a valid range. Now I'm assuming I did mistype one of these things. So if we go back to options and take a look. See this is called staff member not members. So let's fix that. Should be member allow multiple selections. Hit done. So now we should have again that same drop down with our multiple choices. And then if we decide to update that list, we could just go back here, update the list with new staff members. And at this point, what should happen if I go back to my original table and open one of these columns, you'll see that those new staff members will automatically display in this list. And that's a nice dynamic way for you to be able to update some of these dropdowns. If you want to do that, you can just create this little tables and then use them for your drop-down options. Now, before I finish this video, let me also just go over merging and unmerging cells. And for that, what I'll do, I'll just add a big title on top. So, what I'll do, I'll add another row on top here. I'll right click, insert a row. And let's say what I want, I want a giant title on top here that says orders for May or monthly orders or something like that. So what I'll do, I'll just select from here until here, all these cells. And then I'm going to click on this icon here that says merge cells. So I'm going to click on this. It's going to combine these to one giant cell. So now I can type something here and we can click on this. We can redesign this. We can center this. We can center this also vertically. Of course, we can make the text much bigger in size. If you're unhappy with this plus, you can always click here, choose one of these. Or if that's not enough, you can type your own number. So, I can go here and type 70. Hit enter. And that will do that size. I'll just go like 50. That's good enough for me. And of course, you can do some background colors as you see fit and some text colors. Make it bold if you feel that's what you want to do. And that would be how you merge cells. So you want multiple cells to act as a bigger cell. And later on, if you want to go back to your normal separate cells here, you can open this and go unmerge cells. And you're back to your separate cells. I'll go here and just merge it all together. And there you go. That covers your Google Sheets basics. So those would be all the introductory level things you should know. Things you pretty much do daytoday when you set up spreadsheets. You need to track some information. You need to add a few formulas. Let me know what you think I've missed, if anything in this video that I should have covered. And as I've said, if you're new to spreadsheets, open a new spreadsheet, build a spreadsheet along with this video as you go. If there are problems you have or there's something else you want to do, let me know what questions you have or what exactly you need done. I probably already have a video covering that, so I can link you to that video. If not, maybe I'll do a new video for that. That should do it for this video.