Google Sheets Beginner Tutorial

Key Takeaways

welcome to the google sheets crash course this is a really short course that will get you up to speed on a lot of really important google sheets concepts that you should know before you either start building dashboards or working in google sheets in general so what we want to do first is create a brand new google spreadsheet and to do this what you want to do is go to drive.google.com and when you're logged into your google account and you go to drive.google.com it will take you to a screen like you're seeing here to your own personal google drive and then after you do that click the big button that says new and then click google sheets and this will create a brand new blank google spreadsheet and when you create a brand new spreadsheet as long as you edit the sheet in any way or change the title of the sheet google sheets will automatically save the spreadsheet for you in your google drive and so now that i've edited the title it will now display in my google drive if i click refresh so the first thing that you should know about a google spreadsheet or any spreadsheet is that it has rows and columns the columns go from left to right from a to z and the rows go from top to bottom starting at row one and so on now each square is called a cell and each cell has an address depending on its row and its column and so right now i have selected cell c5 because it is in column c and row five and so that gives it the address c5 now if you want to select a cell you simply click on the cell one time so now i've selected cell a1 by clicking on it one time now you can also select multiple cells by clicking in the middle of the cell that is at the top left of the range you would like to select and then holding your click then dragging your cursor and releasing your click at the last cell in the range that you would like to select or in other words the cell that is at the bottom right of the range that you would like to select and then once you release your click you will have selected the range that you wanted to select and in this case the range that i have selected is a1 through d7 which is notated in google sheets as a1 colon d7 which looks like this and so this is how you notate a range of cells in google sheets and this is really useful because you'll be doing this quite a bit when you use formulas now if you want to navigate through the cells again you can either select them one by one and use the scrolling options to select the row or the column that you need to or you can use the arrow keys on the keyboard to move up down left or right one cell each time you press the arrows on the keyboard so this is also a very useful way of navigating through the cells in google sheets now just like we did with selecting cells we can select entire rows and entire columns in google sheets and so first let's select a single column so if you want to select a column simply click on the letter of the column that you want to select at the top of the sheet and so in this case i'm going to select column d and you can tell that it's selected the entire column and not just the cells within the column because of how dark column d is at the top where as if i had selected only cells in the column column d would be slightly darkened but not like it would be if you selected the entire column now we can do the same thing for rows by simply clicking on the number of the row that you want to select on the left so if i wanted to select row 4 i would click on the 4 at the very left side of the sheet and as you can see not only has it selected all of the cells in that row but it's selected the entire row and if you want you can select multiple columns or multiple rows at the same time just like you can with selecting a range of cells and we'll do this by clicking and dragging our cursor and so if i wanted to select columns d and e i would click on column d at the top hold my click and then drag my cursor right and then release my click and now i've selected both columns d and e at the same time and the same thing goes for rows if i wanted to select rows 2 3 and 4 i would click on row 2 at the left of the sheet then drag my cursor downward as i hold my click and then release my click and now i've selected rows 2 3 and 4. now you can also select cells rows or columns that are not adjacent to each other or that are not right next to each other by holding the control key and then selecting the cells rows or columns that you want to and so now i'm going to hold the ctrl key on the keyboard and select another cell that is not right next to the cell that i originally had selected and so this is how you can select multiple cells and you can also do the same thing with columns or rows if you want you can adjust the width of the columns or the height of the rows by clicking and dragging your cursor in the correct location so for example if you wanted to adjust the width of column e so that it was expanded you would hover your cursor at the top of column e at the very right of column e in between e and f where the double set of arrows appears where the arrows are pointing left and right this means that you're ready to adjust your column width and then you would click your mouse and hold it and then drag your cursor until you've reached the spot where you want your column to be adjusted to and then release your click and now you can see that column e has expanded and you can do the same thing to shrink the columns if you would like simply click your mouse and drag your cursor until you've reached the point where you want your column expanded to then release your click and again you see that column e has now shrunk now you can do the same thing for rows by clicking at the bottom of the row you would like to expand or shrink in between the row that you want to expand and the row below it again where the double set of arrows appears and in this case the arrows are pointing up and down because we're going to expand the row height click your mouse hold the click and drag your cursor until you've reached the point that you want to expand to release your click and now you can see that we've expanded row two now you can do this same thing while you have multiple columns or multiple rows selected which is a really fast and easy way of adjusting the width or height of multiple columns and rows at the same time and so i'm going to select columns c d and e again i'm going to do this by clicking in the middle of the first column holding my click dragging my cursor and then releasing the click and then i'm going to hover my cursor in between any of the columns that i have now selected when you've selected multiple columns and you want to adjust the width of them all at once you can hover between any of those selected columns and like you'll see here once we drag our cursor and release our click it will adjust all of the columns that were selected at the same time to the same width and again you can do the same thing for shrinking the columns and if you want you can do this same thing for rows so now let's go over how to enter text or numbers into the spreadsheet cells and there are multiple ways that you can do this probably the easiest way to enter a value into a spreadsheet cell is to simply click on the cell that you want to enter a value into and then just start typing and so for example i'm going to click on cell a1 one time to select it and then i'm going to start typing the words correct questions and then i'm going to press enter remember to always press enter after editing a spreadsheet cell so that you actually lock in the value that you wanted to enter into the cell another way that you can enter text into a spreadsheet cell is by double clicking on the cell that you want to edit so for example i'm going to double click on cell b1 and as you can see the cursor is now inside of the cell and then i'm going to type total points possible and again remember to press enter now one more way that you can enter a value or text or numbers into a spreadsheet cell is by selecting the cell that you want to edit and then clicking into the formula bar at the top and so in this case i've clicked on cell c1 and i've clicked into the formula bar at the top of the sheet and i'm going to type the word percent and then press enter and so that's three different ways that you can enter a value into a spreadsheet cell you can click on the cell and then begin typing you can double click on the cell or you can click on the cell and then click into the formula bar now it's good to note that if you choose to click on the cell once and then simply begin typing without double clicking or without clicking into the formula bar whatever text that you enter will erase the text before it after you press enter now let's go over a few different ways that you can change the visual formatting of the cells so i'm going to select cells a1 b1 and c1 by clicking holding my click and dragging my cursor and then releasing my click and i've selected all of these cells at the same time so that i can edit all of them at the same time now i'm going to click b to change the font to bold i'm going to select a font size of 14. i'm going to change the background color of the cells by clicking on fill color which looks like the spilling paint bucket going to click dark blue and now i'm going to change the text color by clicking on the big a at the top on the toolbar and then i'm going to change the text color to white now i'm going to center align the text by clicking on the alignment drop down and then clicking center and then last but not least i'm going to wrap the text by clicking on the text wrapping drop down and then clicking the curved arrow which is the symbol for wrap so now that you know how to wrap text i'm going to show you one more trick that will make the text fit inside the cell automatically so what i'm going to do is select cells a1 through c1 and then i'm going to remove the text wrapping by selecting overflow which is the default for cells and so as you can see the words total points possible in cell a1 are too long to actually appear inside the cell and so what you can do instead of wrapping the text is autofitting the column or automatically adjusting the width of the column to fit the longest string of text in that column and so how you do this is you hover your cursor in the same place that you would to adjust the column like normal but instead of clicking and dragging you simply double click your mouse and google sheets will automatically adjust the column again to fit the longest or widest string of text in that column and you can absolutely do this for multiple columns at once so what i'm going to do to demonstrate this is select columns a through c and then shrink them down to a small size so that you can see how far each of them expands when i use autofit and so now i'm going to hover my cursor between any of the selected columns until the double arrows appear and then double click and as you can see it's automatically adjusted the columns based on the widest string of text in each column and so in this case the text in column a is much wider than the strings of text in columns b and c okay so now let's enter some numbers and formulas into the cells so what we're going to do here is calculate the percentage of different tests that were taken based on the correct number of questions or in other words the total points earned and the total points possible so first let's enter some numbers into column a to represent varying numbers of questions earned on the same test so i'm going to enter 7 6 5 and all the way down to 1. now let's do the same for total points possible but this is going to represent different people having taken the same test which has the same number of questions for each person so the total points possible is going to be the same in each row so i'm going to type 10 and then press enter in cell b2 and now i'm simply going to copy and paste this number into the cells below it and so here's a quick lesson on copying and pasting when all that you're copying and pasting is either text or numbers later on i'll show you what happens when you copy and paste formulas so i've selected cell b2 and then i'm going to press ctrl or i'm going to hold ctrl on the keyboard and then press c and then you'll see that dotted lines have now appeared around the cell which indicates that it's been copied and then i'm going to select cell b3 and then hold the ctrl key on the keyboard and then press v to paste and now you can see i've duplicated the number 10 into another cell and if you would like you can do this more rapidly by copying the cell that you want to copy and then repeatedly pasting it into the location that you would like it to be and again when you're copying and pasting normal values like text or numbers and it's not a formula it will replicate exactly what you had in the cell that you copied from including formatting so now let's enter our first formula by calculating the percentage of one of the tests so select cell c2 by clicking on it one time and then begin your formula by typing an equal sign every formula begins with an equals sign now type a2 to refer to cell a2 and then type a forward slash which in google sheets means divide then type b2 to refer to cell b2 and so we're telling google sheets to divide cell a2 by cell b2 and then press enter and you can see that our answer is 0.7 which is 70 so i'm going to show you another way of changing the cell format and in this case we're going to make this 0.7 appear as 70 so on the top toolbar menu where it says one two three click the more formats menu and then click percent and so now you can see that our answer is showing as 70 percent rather than 0.7 and these two numbers are the same exact number but they're just showing in a different format now so now let's see what happens when we copy and paste a cell that has a formula in it so select cell c2 then hold ctrl on the keyboard and press c to copy then select cell c3 hold ctrl on the keyboard and press v to paste and you'll see that our formula has now been transferred into cell c3 but the special thing is that google sheets has automatically changed the cell references so that it's calculating for row three instead of row two so unless we specify otherwise google sheets will automatically change the row reference when we copy and paste a formula into a different row and it will automatically change the column reference if we copy and paste a formula into a different column and in this case this is what we want and so if we continue copying and pasting this formula down into the cells below it you'll see that each time the references have changed in our formula a8 divided by b8 a7 divided by b7 and so on now before we move on i want to show you a different way of copying formulas down a column so rather than copying and pasting our formulas what we can do is use what's called autofill and so what we want to do is take your cursor and hover it over the small blue box that's at the bottom right of the selected cell that has the formula in it and when you do this you'll see a plus sign or a cross hairs appear and when this appears click your mouse and hold the click drag your cursor downwards and release the click and google sheets will have used the autofill function to automatically copy the formulas into the cells below and again just like when we were copying and pasting you'll see that the references have automatically changed something else that i want to show you which isn't relevant to this example but it's really good to know is that autofill can also be used to fill formulas to the right and not just down and so if you select the cell that has the formula in it that you want to fill to the right hover your cursor over the small blue box which is called the fill handle click and hold your mouse drag your cursor to the right and then release your click and now again these numbers that we've just created are not relevant to this example but what you can see is that google sheets automatically adjusted the column references each time the formula was copied or filled to the right and so here we have b5 divided by c5 and then c5 divided by d5 and then d5 divided by e5 and so if you want to quickly copy formulas down a column or to the right in a row you can use the fill handle for both of these things or again you can copy and paste so since i don't want these formulas that i've just copied into columns d e f and g i'm going to delete them so if you want to delete something that's inside of a cell in google sheets simply select that cell and then press delete on the keyboard and google sheets will clear that cell now you can also select a range of cells and then press delete and google sheets will delete everything that's inside of the cells in that selected range alright so we went over how cell references change when you copy formulas from one place to another but sometimes you'll want a cell reference to stay the same when you copy and paste a formula and so i'm going to show you how to do this but before we get into that let's make a couple of quick changes to our sheet so first of all let's freeze the first row so that when we scroll the first row will always stay on top and how we're going to do this is click view on the top toolbar then hover your cursor over freeze and then click one row and so now as we scroll row one will always stay frozen or in other words will always stay on top and if you want you can also freeze columns in the same way by clicking view hovering over freeze and then clicking one column two columns or a specified amount and just to show you i'll click one column and so as you can see as we scroll right the first column always stays at the left and our top row is still frozen as well but in this case i only want the top row frozen and not the first column and so i'm going to unfreeze the column that we just froze by clicking view freeze and then no columns now i'm going to show you how to add rows and columns to your spreadsheet in google sheets you can add rows and columns anywhere that you want and in this case we're going to add a new row above row one and so how we're going to do this is right click on row one and then click insert one above so now i've just added a new row at the very top of my sheet and you can do this same thing for columns simply right click on the column where you want to add a new column and then choose between insert one left or insert one right i'm going to choose insert one left and so now i've just added a new column on the very left side of my sheet and i've just created this column as an example and i don't actually want it there so i'm going to delete it but before i do this i just wanted to show you something which is that google sheets has automatically adjusted the cell references in your formula just like it did when we were copying and pasting formulas into different cells and so after adding the new column google sheets adjusted the cell references to say b3 divided by c3 but you'll see that this will change after we delete column a which we can do by right clicking on column a and then clicking delete column and now you'll see that google sheets has changed the cell references back to what they originally were before we added the column which is dividing cell a3 by cell b3 all right so now i'm going to show you how to lock the cell references in your formulas so that they don't change when you copy and paste your formulas so first we're going to delete everything from cell b3 to cell c9 and then we're going to enter the number 10 into cell b1 because we're going to refer to cell b1 with each one of our formulas that we copy and paste and we're going to set it up so that we can copy and paste our formula and that the cell reference b1 will not change every time that we copy and paste so let's set up our new formula by clicking on cell c3 and then typing an equal sign then type a3 to refer to cell a3 then a forward slash which again means divide and then type b1 but the important thing that we need to do here is we need to add a dollar sign before the number one to lock the reference to row one and so we're going to press enter and we have the same answer that we did before but the thing is that now we can copy our formula down or use autofill to fill the formula down and we get the same answers that we did before but without needing the number 10 in each row of column b because in each formula we are referring to cell b1 and it did not change each time that we copied and pasted the formula because we put a dollar sign before the one now this same thing applies to columns if you want to copy and paste your formulas from left to right and you do not want your cell reference to change then you would put a dollar sign before the letter which represents your column reference this is the difference between relative references and absolute references relative references will change when formulas are copied and pasted into other cells but absolute references which contain a dollar sign before the row the column or both will not change when formulas are copied and pasted into other cells in this particular example we've done a division problem by using the forward slash but i also want to show you the different symbols or operators that you can use in google sheets to do math problems with and so again we've used a forward slash to designate division but if you wanted to do multiplication you would use an asterisk if you wanted to do an addition problem you would use a plus sign and if you wanted to do a subtraction problem you would use the minus sign okay so now that you know how to copy your formulas and paste them into the cells below i'm going to show you two really easy ways that you can extend your formulas all the way to the bottom of the sheet so if you're using individual formulas where there's one in each row like we are here and if you wanted to use the fill handle to click and drag and copy the formula all the way to the bottom it might take a really long time depending on how long your sheet is and so here's a really easy way that you can copy that formula all the way to the bottom very quickly so select the cell that has the formula which you want to copy which in this case is cell c9 then hold shift and control on the keyboard at the same time and then press the down arrow key and this has highlighted all of the cells all the way to the bottom of the sheet in column c now hold ctrl on the keyboard and then press d which stands for down and google sheets has automatically filled the formula all the way to the bottom of the sheet and so as you enter new data into column a the formulas are already present and ready to calculate the percentage for each new entry and you can do this same thing if you want to copy your formulas all the way to the right of the sheet by holding ctrl and pressing r for right so control d means down which will fill your formulas down to the bottom of the sheet and ctrl r which means right will fill your formulas all the way to the right of the sheet now there's one more really cool method that i want to show you that will allow you to extend your formula all the way to the bottom of the sheet and with this method you'll only need to use one formula to fill a whole column with calculations so i'm going to delete all of the calculations in column c except for the very first one in row three and what i'm going to do is add a function that's called array formula and so right after the equal sign i'm going to type array formula and then type a parenthesis and then press enter and now the key thing that we have to do when we're using array formula is that we actually have to include a range of cells now again we want b1 to stay the same for each formula and so we're going to keep that the same but for the a3 reference we're going to add a colon and then type a so instead of designating cell a3 we're designating column a starting at row three so i'm going to press enter and as you can see now we've calculated the percentage for the entire column by only using one formula and we did this by using array formula an array formula is a very very useful function that can be used for a lot of different things and one of the things is extending formulas so now i'm going to show you a few more formulas that are very very common to use in google sheets one of the most simple formulas that you can use is what's called a cell reference this is where we are simply referring to another cell and displaying the contents of that cell with a formula so how we do this is click on an empty cell such as cell e2 then type an equal sign and type c 2 and then press enter and so now you see the word percent has appeared which is in cell c2 and so this is a simple cell reference but now let's refer to a cell from a different tab and so if you want to create a new tab in google sheets bring your cursor to the bottom of the sheet to the plus sign and then click add sheet and so google sheets has just added a new blank sheet and so if we want to refer to cell c1 from the sheet one tab what we'll do is type an equal sign and then type sheet 1 and then put an exclamation point and then type c 2. this is how we refer to a cell from a different sheet in google sheets we type the name of the tab and then put an exclamation point and then type the cell reference and now i'm going to press enter and the word percent has appeared in cell a1 on the tab sheet 2. now i want to show you something special that happens when the name of the tab has a space in it so what i'm going to do is double click on sheet 1 to edit the sheet name and then i'm going to add a space between sheet and 1 then i'm going to press enter and now you can see that the formula has changed and so this shows us that google sheets will automatically adjust the reference when you change the name of the sheet but what i really want to show you is that google sheets has added an apostrophe at the beginning and at the end of the tab name or the sheet name in our reference and so this is really important to know that when you're referring to a tab that has a space in its name you need to add an apostrophe at the beginning and at the end of the sheet name or in other words the tab name and if you want to transfer the formatting from one cell to another there's a really easy way to do this as well so i'm going to click on sheet 1 again and i'm going to click on cell c2 because i want to copy the formatting from this cell to another one now i'm going to click on the symbol that looks like a paint roller which is the paint format feature and then i'm going to click on cell e2 and then google sheets automatically copied the formatting from cell c2 to cell e2 so now we're back to our formula in cell e2 that refers to cell c2 but what if you want to refer to more than one cell at a time let's see what happens when you input a cell range into the formula bar and so i'm going to add colon c so that my formula now says equals c2 colon c and as you can see this didn't do anything like we hoped for now google sheets used to say array formula not found when you would do this but now it just doesn't do anything and the problem is in either case that you need to add a ray formula and so if we wanted to refer to a range of cells or a column what we need to do is add a ray formula right after the equal sign and then type a parenthesis and press enter and now we're referring to an entire column with one formula and so whenever you're trying to refer to a range of cells google sheets will expect you to add the function array formula if you want you can also move cells rows or columns in google sheets and i'll show you a couple different ways to do this so the first way that you can move a cell is by clicking on the cell that you want to move and then hover your cursor towards the top of the cell until the hand appears click your mouse and hold the click and drag your cursor until you reach the place where you want to move the cell to and then release the click and when you do this google sheets will move that cell and everything inside of it whether it's text or a formula to the new location and will keep everything as it was in the old location now even though the formula doesn't change when you move a cell if you move a different cell or a different column that is attached to that formula then the formula will change and so let me show you what i mean so let's take a look at our formula here in cell c3 which refers to column a and so if i move column a which is just the same as moving a cell i select the column and then i wait for the hand to appear i click i hold my click and i drag if i move this column all the way over to column z so now you'll see that your formula refers to column z instead of column a so when you move a cell that has a formula in it the formula won't change but when you move a cell that a formula refers to that formula will change so before i move on to show you another way of moving cells columns or rows let's do a little bit of a review and so when you select a cell there are three different places that you can hover your cursor for three different effects the first is in the middle of the cell where you'll see the pointer and this is where you click to select a cell or where you click and hold and drag to select multiple cells so click in the middle of the cell to select it and look for the pointer if you're trying to use autofill then hover your cursor over the bottom right of the cell until the plus sign or the crosshairs appear if you're trying to move the cell hover your cursor towards the top of the cell until the hand appears so the middle pointer is for selecting the bottom right with the crosshairs is for autofill and the top of the cell with the hand is for moving so now there's another way that you can move cells rows or columns and that's by using cut which is control x and so i can do the same thing that i did when i moved the cell by selecting it holding control on the keyboard and then pressing x selecting the location that i want to move the cell into then hold ctrl and press v for paste and so cutting and pasting has the same effect that moving does and again you can do this same thing for rows and columns simply select the column that you want to move or to cut hold ctrl and then press x select the column that you want to move the column to hold ctrl and press v to paste another thing that you can do which is really useful is duplicating tabs now i've already showed you how to create a new tab by clicking the plus sign but if you want you can duplicate an existing sheet by right clicking on that sheet and then clicking duplicate and google sheets will create an exact duplicate of that tab so now i'm going to delete the sheet that i just created by right clicking on it and then clicking delete and then clicking ok by the way feel free to leave a comment below if you have any questions for me or if you just like the video and want to say hi and if you want to learn how to analyze data and build dashboards in google sheets go to spreadsheetclass.com data so now i'm going to show you one last formula which is the if function this is a very very common function to use in spreadsheets creating an if then statement which is to say telling google sheets if something meets a certain criteria than to perform a certain function or to display a certain value and then if not to perform another function or display another value so what we're going to do is click on cell c2 and then type the word passing and then add a question mark then click on cell b2 then click on paint format and then click on cell c2 so that we have the same formatting as we did before now click on cell c3 and type equals if parentheses b3 greater than equal sign 0.6 comma quotation mark type the word yes put another quotation mark type a comma then type a quotation mark type the word no and then type a quotation mark and so we're telling google sheets if b3 is greater or equal to 0.6 then display the word yes if not display the word no so in other words if the grade is passing display the word yes if not display the word no so i'm going to press enter and for this cell it's telling us that yes the grade is passing because it's greater than or equal to 0.6 which is 60 percent now i'm going to use autofill to fill the formula down and as you can see it's made the same calculation for each row the second grade is passing but all of these grades are not passing because they're below 60 percent and then when we come again to another grade that's 60 or more the formula displays yes that the grade is passing