hello everyone my name is joe and i'm going to be facilitating this course for you today today we're going to be talking about google sheets now google sheets is a spreadsheet program included as part of a free web-based software within the office suite offered by google i'm really excited to be here with you today and today we're going to go over an introduction into google sheets to access google sheets the first thing we're going to want to do is open it up via a web browser now i like to use google chrome because well google products work best with google products so i'm going to click on google chrome and what you'll notice is is that i'm already signed into my google account so that's the first thing you're going to want to do is you're going to want to sign into your google account using your google credentials you'll notice i have my gmail and my first name and last name there now right next to it is the app launcher it's also known as the grid or the waffle so you can call it whatever you like and when i click it i see menu where i can access all the different google applications and if i click on sheets it's going to bring me to the start page here now the start page is really nice because i can access all of my recent files i've been working on i can access some of google sheets templates i can search for files or even start a blank file now another way to quickly access a new spreadsheet if you know you're not going to be using a template is to open up another tab here within your google chrome and type sheets dot new and press enter and when you do that it'll open up an untitled spreadsheet so take a moment sign into google and when we come back we'll talk about the interface of google sheets now that we have a new sheet opened let's talk about the interface of google sheets now google sheets utilizes these little drop down menus and if you simply click on one of the corresponding words here like my file menu it'll show me everything that's within my files menu if i just simply move over it'll show me everything in edit my view commands insert format and so on and so forth now right underneath it we actually have access to a quick access toolbar and these will be some of the commands that are the most popular in google sheets now other things we can do when we're in an untitled spreadsheet is we can name our untitled spreadsheet which we'll talk about doing a little later we can create comments and open up the common history share this spreadsheet which we'll talk about later take a look who signed into the account which it's us so perfect if we wanted to go back to that google sheet startup page we can just simply click on this and it'll bring us back to the sheets home if i click on it you'll notice here we are where we can review all of our recent files and the templates but i'm going to keep it open to a new spreadsheet and i'll just exit out of this other one as well so take a moment get familiar with some of these drop down commands and when we come back we'll talk about being able to actually search these menus if you're brand new to using google sheets no worries there's some help here when it comes to taking a look at all of the different menu options now at first it could look a little overwhelming but no worries because if you go over to the help section you'll notice we can search the menus now searching the menus is great when you don't really know exactly where things are within those menus for example if i'm new to sheets and i type in just my name here i'm just going to type joe into the cell maybe what i want to do is i want to bold this but right now you'll notice that it's not bold so i'm going to click on that cell and let's say i can't find where bold is where is that command i can't find it if i go over to my help and i search the menus for bold i just type in hold it'll pull up that command and when i click on it it'll actually bold the word if i wanted to underline and i don't know where that is i can go to help again and what you'll notice is there is a little bit of a keyboard shortcut when it comes to searching the menus which is all backslash so if i just simply use my oh backslash it'll pull up my search menu and i can type in underline and there we go not only can you see that it's going to underline it but it also gives you the keyboard shortcut to underline it so if you're like me and you enjoy those keyboard shortcuts you can quickly learn them through these search menus there we go i'm gonna go to help underline select it and i see now that it's underline my word so take a moment try out that help menu there and when we come back we'll talk about selecting and navigating ourselves within our google sheets and sheets there's many ways to navigate and select cells we can either use our mouse to click around so you'll notice i'm in a1 i know i'm in a1 because of the blue border around it and if i wanted to go to cell c3 i can just click using my mouse or f2 or b7 i can use the mouse to click around to navigate the different cells another way to do this is to use our arrow keys on the keyboard if i use my right arrow i go to the right if i use down i go down left i go left and the up arrow goes up now i can also use other keys besides the arrow keys like my tab key which is next to my q on my keyboard if i press the tab it goes over to the right and if i use shift tab it goes to the left if i press my enter key or the return key it goes down and if i hold down my shift key shift and enter goes up now let's say we had some data here i'm going to input some data but we'll talk about this in a moment so i'm going to put in some very simple data like name i'm going to press tab to go over date tab to go over and we'll say salary now i can press my enter key or i can go down with my down arrow it's really up to you so i'm going to use my enter key here and what it does is it automatically puts me under a2 which is nice because they know that i'm going to start on the next row and start writing some stuff like my name date of higher and then my salary will make it a huge salary there we go millions of dollars so now that i have this a great way to select cells is to either click and drag you'll notice by clicking and dragging i'm able to highlight these cells i can also use my control key and my shift key and then my arrow keys to quickly select the cells into the next empty space so i just simply used my control shift to the right and ctrl shift down to quickly select this so take a moment practice and when we come back we will learn some more google sheets tricks now you may have noticed in the previous section i was able to enter and put in some data into this sheet now the way i did that was pretty simple i just simply clicked on a cell and i started typing and that's it that's how we can enter data into the spreadsheet so for instance if i wanted to start over here i'm just going to simply select either by clicking and dragging right or remember we can select by control shift to the right or down and then i can press delete on my keyboard to delete all that data i can start over by just starting in a1 i'm going to type in name i'm just going to type in and then press tab to go over one and i'll type in again date of higher tab to go over one and salary now let's say for instance that i didn't want it to just say salary maybe i wanted it to say pay per hour let's do that if you ever want to edit the data all you have to do is click into the data now you'll notice that once you click into a cell you'll see right over here we have our formula bar and it will actually show you the true value of a cell now you might be saying joe what does that mean exactly well let's say that i enter in data here i do this i enter into one perfect and then i'm going to do the same thing i'm going to enter in another one as well but in a different way now what you're going to notice is that both of these ones are very similar i'm going to use my little help bar that we learned about before to make sure that's not underlined so now you'll notice we have two ones in here they both look the same but they're really different and i know they're different only by taking a look at the true value in the formula bar if i click into this one it just has a simple one there but if i click into this one it's not actually just one it is a calculation one plus zero so if you simply want to see what the true value is behind a cell you can click into it and if you ever want to edit that cell you can either backspace by clicking on the backspace on your keyboard and re-entering into the cell or if i just simply wanted to edit this i can come up here to the formula bar and edit it any way that i want and i'm going to change this hey her hour or i can also put let's just do hourly rate there we go so that's how we can edit data and also enter in data or even delete if they have to so take a moment practice this on your own and we'll have some fun when you come back google sheets has a great way of analyzing data and i want to show you a unique tool that google sheets has where we can ask questions about our data and quickly visualize and analyze our data so the first thing we're going to do is we're going to create some false data together so let's do this if you still have name date of higher and hourly rate we're going to select all of this and press delete on our keyboards and what i want to do is i just simply want to put in some fake data here i'm going to put in let's do the sales person we can do week one week two week three all the way to week four and what we're going to do is put in some fake data here and then analyze it quickly using a unique tool called explore so i'm going to put in some fake names here i'll put in my name i'll put in my good friend trevin's name my good friend liz i think that's good all three of us that's perfect all right so now we're gonna just put in some false numbers for week one two three and four for each of us and i'm gonna make some simplistic ones nothing too crazy so if you're following along you can copy those or put in your own data if you like now you'll notice that my e 3 still has that underline i'm just going to use the keyboard shortcut control u for underline now ctrl b for bold ctrl u for underline and ctrl i for italicize so those are just a keyboard shortcuts that you can use to quickly underline bold and italicize so now that we have our data here if we quickly wanted to analyze this data we could do so by actually clicking the button for explore now if you're not familiar with it no worries at all if you look at the bottom right hand corner you'll notice that we have our explore button right there looks like a little star and if you click on it what it's going to do is it's going to open up explore now i really like explore because what it can do is take a look at our data and quickly answer questions about our data it can show us some different formattings for our data and it quickly analyzes and creates visuals about our data which is great so for instance maybe what i want to know is the sales person with the minimum in week three and if i click on that it'll tell me that the answer is joe in week three i have the minimum amount of money that i brought into the company and i can always change that maybe i want to see week two and once again it's me it looks like i'm gonna be the bottom dog for each of these weeks maybe i want to see who has the maximum and i can see the top sales person by week two is liz if i wanted to take out look at week four once again it's liz now it'll tell us the answer it'll tell us what the question is and if we wanted to we can see the formula and it'll tell us what it is and we can also rate this answer now the next thing we can do is take a look at related questions like count of the sales person we can take a look at previous answers and if we wanted to simply exit out of the answers and go back to our explorer we can click on the back arrow another great thing that it offers us is formatting and if you don't know what formatting is it simply means it's going to change the appearance of the cells now i can alternate the colors and if i click on this blue color you'll notice it makes this a nice dark blue and then it bans the rose so that we can access our data and visually see it a lot better you can choose between different colors here now another thing we can do is insert quick analysis charts like this bar chart right here or even a pie graph and maybe i want to take a look at this one now to insert this there's two ways to do it i can either come over here and click on the insert chart or if i just wanted to simply view the full size of this chart i can click there and see what it looks like once i'm done i can click insert chart and i'll actually insert it in or i can just click right here and there we go so as you can see the explore can be very beneficial when asking questions about our data formatting the appearance of our data or even inserting visualizations of our data take a moment and explore let us talk about how we can utilize calculations within our spreadsheet to quickly get total numbers for our total sales amounts now the first thing i want to do is i'm going to just simply delete this graph by clicking on it now i know it's selected because of the different sizing squares here and i can just simply click delete on my keyboard once it's selected and there we go i'll also exit out of my explore by going over and clicking on the x so what i may want to do here is add another header under f1 and i'm going to call this one total sales because i just want to simply see how much we made total in a combination from week one to week four now what you may notice is it's going to keep that same formatting that we discovered in our explore before so if you did format your cells or change the appearance of them it will follow along for the rest of them as well so let's start off by creating a calculation and i'm just going to zoom in by utilizing my zoom button right over here right now i'm at a hundred percent i'm gonna click the drop down and i'm gonna go over to let's do 150. that looks good so to start off any calculation within a spreadsheet what we're going to have to do is use the universal sign of mathematics which is the equal sign i'm going to click on equals and there we go and now i can start off by saying i want to take this number which is 1200 so i'll type in 1200 and i want to add that so i'm going to use the plus key to 1300 so i'm going to type 1300 plus 1400 plus 1500 now once i'm done with my calculation i can simply press enter on my keyboard and it will give me that total calculation which is awesome so we can do some great simple calculations within spreadsheets so if i wanted to quickly see how much is 500 multiplied now when we do multiplication we're going to use an asterisk i'll do 500 times 500 you'll notice that not only is it telling me what it is exactly up here but if i press enter it will then input that value into the cell so what i want you to do at this moment is fill in the rest of these total sales i'll do that as well and when we come back we'll keep going on with some great google sheets so while we were gone what we did was we filled in the rest of these total sales and once again to simply do a simple calculation we're going to click the universal sign of mathematics which is our equal sign and then we'll start to add using the plus sign now we've done some great work here and it would be a waste if we didn't save it but the good thing is is that we don't have to worry about saving within google sheets because google sheets will automatically save this for us now you'll see the document status if i click on it all changes have been saved to drive now this is really important to know because the minute that you open up a spreadsheet whether it be google sheets google slides or google docs the minute you start making changes within one of these files it automatically saves to your google drive if you don't know what google drive is stay tuned because we're going to talk about that in a second so as i said before we don't have to worry about saving this document because it automatically saves to our google drive but we may want to rename this document and to do that all we simply have to do is come over here to the untitled spreadsheet click into it and start to name it now once it names it you'll see that it says that it's going to put it in my drive so let's name it together now you can name it whatever you like i'm gonna personally name this my google sheets practice fun and that's it then i can click away and now this file is forever known as my google sheets practice fun i'll even tell you the last time it was edited was three minutes ago and let's say i want to get rid of this 250 000. i can just simply click delete you'll notice that right here it is now saying that it was saved to the drive so what exactly is google drive well let's move our attention from google sheets for a second and talk about google drive so to access my google drive i'm going to once again open up a new tab in my web browser go over to my grid waffle or app launcher whatever you like to call it and i'm going to click on drive it looks exactly like this it uses the colors of sheets slides and docks and it looks like a little triangle if you click into it it will bring you to this online storage system i want you to think of this google drive as your own personal filing cabinet system that follows you around magically wherever you go as long as you have computer access and internet access you will be able to access all of your files in your google drive now what you'll see here is if i scroll down you'll notice that i have all of my files right here and i also see that if i wanted to see the last time i modified a file or if i wanted to quickly access file i can access that google sheets practice fun right here so i'm just going to click on my drop down and i want to see the last time it was modified and i'll put it in that order which is nice or we can also put it in a name order from a to z or we can reverse the sort direction from z to a whatever we like if i scroll down i'll see now that i have that google sheets for fun and i created it just now last summer was modified 949 and you'll see it is 952 here so google drive is a great way to access your files from any device from anywhere around the world all right so take a look at your google drive you might find some stuff that you didn't even know was there take a look at it and when we come back we'll get right back into google sheets in our previous section we created a calculation by going into our cell and typing in equals the universal sign of mathematics and then entering each entry manually with a plus sign now you might be saying to yourself joe that's great but it took us a long time to do that and i agree with you it did take a long time so i want to show you something else something known as a formula now a formula simply takes the cell value whatever's in the cell and it adds it to whatever's in the next cell and the next cell and the next cell so it doesn't actually matter because let's say for instance i messed up let's say on week four it wasn't 1500 it was actually sixteen hundred well even though i'm going to change this what you'll notice is now this calculation is wrong because remember i originally had the 1500 here so now i have to go in and manually change this to 1600 and that might take us a long time if we made multiple mistakes instead what i want to show you is how we can use a formula so that we can have this automated and never have to worry about it again so to do that the first thing we're going to want to do is let's delete what's in the cell now remember to delete anything in a cell you're first going to select it and then press delete on your keyboard now the same thing with calculations applies to formulas we're going to start off any calculations or formulas with the universal sign of mathematics which is our equal sign now that i have this instead of actually typing out the manual number for week one which is 1200 i'm not going to do that i'm going to actually select the cell b2 i'm going to select it by clicking on it you'll notice when i select it not only does it tell me that i have selected b2 it tells me what the value is inside of b2 which is our 1200 number after that i'm going to add a plus sign i'm going to click on the second cell which is c2 and i'm going to keep doing this i'm going to add and you'll notice that every single time i add a new cell it keeps a running total of how much we're up to and right now all three of these is equal to 3900 and i'm going to add one more plus sign and select e2 so now it's simply saying whatever's in this cell plus whatever is in this one plus whatever is in this one and whatever's in this one is equal to 5500 now let's say i made that same mistake it wasn't 1600 i was right the first time it was 15. watch what happens when i change the value of this cell from 16 to 15. remember our total 5500 i'm going to update week 4 to 15 and it reflects that cell 5400 because instead of manually inputting the number it is actually whatever is in the actual cells so take a moment do that on your own for the rest of these and when we come back we'll talk about some more great tips and tricks now that we're a little more familiar with formulas and calculations you may be saying to yourself well joe these are great but it's still taking me a lot of time to do these and you would be right because think about it when we did this formula here we had to click on e2 plus c2 plus d2 and it took us quite a lot of time to do so the next thing i want to talk to you about is something called a function now a function is a pre-existing formula that was built by google and it will help us to speed up those calculations and formulas now you can find some of these formulas by going right over to our insert go to function and you'll see all the functions that were pre-existing and built by google now if you're doing a math function you can hover over math and you'll see all the different functions for math and if you're not sure what to use you can always just simply hover over and it'll tell you exactly what it does like for instance our sum function what it does is it sums up a series of numbers or cells now that's something that can actually work for us because that's what we want to do we want to sum up week one through week four so i can use this function and i'm going to show you how to do that the first thing we should do is just select these three formulas and press delete on your keyboard and let's start off in f2 now the same thing applies whether it's a calculation a formula or a function you're always going to use the universal sign of mathematics which is our equal sign and boom there it is so the next thing we're going to do is choose the actual function we're going to use now remember we can use the function by going over to the insert and then inserting a function that way or if we already know what the function is we can start to type out the name of the function and in this case we know it is sum so i'm going to start typing out sum s u now once i do that you can use the tab key to open up the parentheses so it's going to go equals the name of the function and then you're going to press the tab key to open up that parenthesis now you could hold down the shift key and press nine for an open parenthesis but the tab key makes it so much simpler now you'll notice right away that the minute i click on that tab key it opens up a little bit of a blueprint for us to follow if we've never used functions before not only that gives you an example of a function it tells you about the function and it tells you about the first entry of this function value 1 and tells you what it is so it tells me here that the first number is going to be the first number arranged to add together so that's perfect i know that that is right here b2 now that we have this there's a couple things we can do we can either use a comma and select the second value a comma and select the third value and a comma and select the fourth value but you may be saying to yourself oh isn't that pretty much the same as a formula you just did equals b2 plus c2 plus d2 and you're right so instead of doing it this way which you're more than welcome to what you can also do is you can select all of the things you want to sum by clicking and dragging and what you'll notice now is instead of it being an entry on its own it now says that we're selecting from b2 all the way through this little colon here is going to be all the way through e2 okay so we're selecting the sum we want to see the sum of d2 all the way through to e2 once we're done we can press enter and there we go it now tells us how much it was and we know 5400 is the correct answer so now we see in our formula bar this is equal to the sum of b2 through e2 so take a moment fill out the rest of these and practice on your own and when we come back we'll get into saving some time with something known as autofill even though functions save us a lot of time we still have to do a function for each of these cells and if we had more data here which we're going to add in a second it could be quite tedious to do so instead i'm going to show you a quick way that we can use the first function or formula and copy the pattern all the way down so the first thing i want you to do is if you did fill out the total sales we're going to make sure that they are deleted out so i'm going to delete all the total sales out we're going to add some more people here i'm going to add my friend sammy my friend ben my friend damon and my friend delete so now we're going to just add some more numbers and i'm just going to copy that same pattern where we use the one number and then add increments of 100. all right i just want to say here that damon's my ceo so i'm going to make sure that he makes the most out of all of us in this one so now i have all of my data here which is perfect and i want to do the same exact thing what i want to do is i want to create the total sales amount using a function so to do that we now know how to we're going to start off with our equal sign we're going to type in the function which we're using the sum tab on our keyboard to open up the parentheses and we're going to select exactly what we want to sum together which is week one through week four and press enter on our keyboard now we can do this for all of them but it's going to take us a moment so instead i want to show you a great little trick when it comes to using the same pattern within all the functions or formulas so you'll notice that we are doing the sum of b2 through e2 and you may have noticed right here there is this square at the bottom right hand corner of the cell that's actually known as our auto fill handle now autofill is a great tool within our google sheets that helps us to follow a pattern now what do i mean by pattern so as i said before this is equal to the sum of b2 through e2 and if i wanted to use the autofill it would follow that pattern and it would say okay i see what he's doing he's adding from week one to week four and they'll do the same thing if we use the autofill and follow the pattern except for trevin's numbers from week one to week four and so on and so forth so that's what we're going to do we're going to auto fill down now to autofill it's very simple you're going to take your cursor and hover right over this square you'll notice your cursor changes from the arrow point to a black-haired cursor and all you have to do is click and drag all the way down and you will notice in a moment it will autofill all of these cells if we take a look at it it actually followed that pattern now it's b3 through e3 b4 through e4 the sum of b5 through e5 and so on and so forth so it saw what the pattern was and applied it to all of them now another thing we could have done when it comes to autofill and i'm just going to use ctrl z ctrl z will simply undo my last move is if you hover over instead of clicking and dragging you could give it a double click and as long as there's data to the left or to the right it will autofill to that next data stopping at the blank cell so you don't have to click and drag you could have just double clicked now remember you can only do this if there's data to the left or to the right so take a moment practice autofill on your own and when we come back we'll talk about relative reference versus our absolute reference as you can see autofill can save us a lot of time so let's keep going with this let's say i want to add another column here and i'm going to call this one bonus so i'm going to click into g1 type in bonus and everyone's going to get a bonus everyone's been doing really well in our sales lately so we're going to give everyone a 20 bonus now i want to put the bonus rate somewhere and let's do the bonus rate right down here in the a10 i'm going to put bonus rate and i'll put that bonus rate as as i said 20 since everyone's doing well we'll do 20 now what i want to do is i want to see how much their bonus is going to be their bonus is going to be 20 of their total sales and to do this all we have to do is create a formula so the formula is going to be this we're going to say equals we're going to take their total sales i'll just simply click on the cell and we're going to multiply it remember using the asterisk by 20 and when i press enter it will give me their bonus amount which is 1080. now i don't want to keep going with this right because we don't want to sit here all day and have to do this but we know a cool trick now auto fill so let's auto fill by double clicking and look at that wait a second it didn't work let's take a look at why remember if i want to see the true value of a cell i'm going to look into the formula bar so let's take a look at g3's formula so i see it's f3 okay so that's good f3 multiplied by b11 e 11. wait a second there's nothing in b11 and if i keep going down it looks like f4 b12 f5 b13 f6 b14 and so on and so forth so i see what happened now now when we use autofill what it does is it uses relative reference it's relative to the one before it which is how it follows the pattern so here it worked for us because it was b2 through e2 and then they did b3 through e3 and so on and so forth and that worked in our favor but unfortunately it doesn't work in our favor now because what we need to do is we have to do all of these f's f2 3 4 5 6 and 7 times b10 and we don't want b10 to be changed to b11 or b12 or b13 like it did here so how do we actually stop that from happening well to do that we're going to use a technique called absolute reference so let us try this again we're going to select all of these and just press delete on our keyboard and we're going to write out our formula once again so the first thing i'm going to start off with is my equal sign and i want to do the total sales so i'm going to click on it multiplied by b10 but the thing is we don't want b10 to ever move we want it to stay there and that's why we're going to use absolute reference we absolutely don't want b10 to move that helps me remember that i want to use absolute reference because i absolutely don't want it to move now since i don't want b10 to move we're actually going to have to tie it down with anchors and these anchors are a familiar icon which is the dollar symbol now i like the dollar symbol because it kind of looks like handcuffs right if i use the dollar symbol here it looks like handcuffs they're handcuffing v10 down so that it can't move that's to stay there forever now to do that we're gonna have to put a handcuff in front of the b so a dollar sign in front of the b and we're going to have to put a dollar sign in front of the 10 because what we want to do is we want to make sure that it doesn't move from column b and we want to make sure it doesn't move from row 10. so it stays here forever now if you never remember things like putting a dollar sign somewhere i'm 100 with you so i'm going to show you a keyboard shortcut to absolute reference anything you can use the keyboard shortcut f4 on your keyboard now if you're using a laptop you may need to hold down the fn key which is usually next to the windows key on your keyboard but if not if you're using just a normal external keyboard you can press f4 all the way at the top you'll see the f keys f1 through f10 or 12. so to use that i'm going to click anywhere next to the b10 whether i'm in front of it at the end of it and between it somewhere now my keyboard i'm going to press f4 and look at that it will absolutely reference it so if you're not good at remembering where to put those you can always just use the keyboard shortcut now that i have this absolute reference here i'm going to press enter gives me that number again 1080. and this time let us autofill again and look at that it worked and let's take a look at why f2 b10 f3 that b10 is now tied down it didn't move f4 b10 and so on and so forth so sometimes we do need to use absolute reference versus relative reference take a moment try this out on your own and when we come back we will get into the second part of our training welcome back so now we're going to be talking about how we can actually insert columns and rows into our spreadsheet so let's say for instance that i want to make a notes section to the left of the sales person column a well to do that we simply have to go over to the insert menu right up here give that a click and you'll notice we have buttons to insert rows above or below or insert columns left and right so let's say that i want to insert a column to the left of a i'm going to first select column a by clicking on the actual column and then i can go to insert and i can insert a column to the left or the right of a and i'll click column left and you'll see that it inserts one column but what happens if i wanted to insert more than one column do i really have to keep clicking insert and column to the left well you could i'm going to show you a faster way so i'm going to click undo real quick to insert more than one column at a time you're going to select that amount of columns so let's say i wanted to enter 2 i'm going to click a and drag b and then go to insert you will now see instead of inserting one column it's going to insert 2 to the left or to the right and if i selected three and i go to insert it'll insert three and so on and so forth so let us insert two columns i'm going to click on a and b go to insert and i'm going to do two columns to the left and this is where i can put some notes and the date of the note and i could say something like joe called and sick for half of week one and that helps because now i know why joe's numbers are so low i'm just going to make this a little bigger by clicking between a and b columns and dragging it and there we go and i'll say that i entered this in today august 2020. let's say i wanted to add another sales person here to do that i can insert a row above or below wherever i'm selected so let's say i wanted to go right next to ben's row so i'm going to select ben's round and go to insert you will now say that i can insert a row above or below the same concept applies to rows if i select two rows and i go to insert it'll enter two rows and if i select three or four so on and so forth they'll insert that amount so if i want to just simply insert below been i can go to insert a row below and there it goes and i'm just going to press ctrl z to undo so what i want you to do is take a moment enter in that data enter a column or a row and then when we come back we'll get into some more fun now we have the spreadsheet looking a little weird because ever since i inserted the new columns for the notes and the date i noticed that my bonus rate is just hanging out right here so what i may want to do is i may want to move the bonus rate to somewhere else within my spreadsheet now i can do this by copying it and moving it by using copy and paste or i can actually just move it by clicking and dragging it i'm going to show you both ways so for instance the first thing i'm going to do is i'm going to change my zoom to 125 so i can see everything in my spreadsheet i'm then going to decide where i want to put the bonus rate most likely i want to put it over here so there's two ways i can do this i can select it and use the copy and then paste functions if i wanted to and i can do that by going to my edit cut copy or paste and you'll see the keyboard shortcuts ctrl x to cut i like to think of the x as scissors ctrl c to copy c for copy and then ctrl v to paste and the v looks like the tip of a glue bottle so paste those are just little things that help me to memorize these keyboard shortcuts so i'm going to use the cut which will actually remove it from this section and put it wherever i paste it if i was to copy it it would just simply create a copy of this content and then i can paste it somewhere but since i don't want to make a copy i just want to move it i can use cut and then select where i want to cut it and i'm going to go back to edit and paste and there goes it's now over in the a and b columns now there could have been another way to do this and if i hit control z i'll show you to simply move content from one cell to another you're going to highlight it you'll notice that i get this little hand icon so the minute that you hover over the border you will change it from a pointer to a hand once you see that hand you can click and grab you'll notice the hand looks like it's actually grabbing it and you can move it around wherever you want now if you move it on top of cells that have content it'll tell you that you're going to replace it there see it replaced sami's data so instead i'm just going to hover highlight click and hold and drag it over to where i want it so two different ways to do that you can either move data by clicking selecting it and then moving it or using copy paste and cut take a moment try it out on your own and then we'll come right back so you may remember that awesome tool called autofill and we used autofill for our total sales and it just helped us to quickly copy that pattern in the formula and we also used it in our bonus column as well to copy that formula we used relative reference here while in the bonus we used absolute reference so i want to actually show you a little bit more of the features when it comes to autofill autofill doesn't just help us to copy patterns between formulas and functions but it also helps us to complete a series of values so what do i mean by that well let's say that i type in here one and then i press enter and i do two i want to follow that pattern all the way down to 10. well to do that i would select the one and the two and that's actually a pattern because 1 and if we increase it by 1 it's equal to 2. and if we follow that pattern and increase it by 1 that would be 3 and then 4 and 5 and so on and so forth so if i want to i'm going to use the auto fill the same exact way hover over my square until the cursor changes and i'm going to click and drag and look at that it completed the pattern increments of one now i could do this for the next one too i can go to twos i can click four highlight both of them and use autofill now you wanna be sure to make sure to select both because if you don't do that let's say i did one and then i did two but i forgot to select the two and i autofill it's just going to autofill once all the way down so it's important for you to make sure you select one and two just to show auto fill what the pattern is we can do it for three as well three to six are increments of three and there we go now not only that but we can have some more fun with this it doesn't only fill in a series of values for numeric values but also for things like the week monday if i want to go down i can autofill look at that i can also use the abbreviated form of the week one i can do months abbreviated form for months and there we go it worked now let's say for instance that we want to do week one two three and four we could have just done this week one and now that it has the pattern there we go so you can use this to really save time with that said take a moment try it on your own and then we'll come back for more fun another great feature within google sheets is being able to align our text within the cell i want to show you what that looks like so the first thing i want to do is i'm going to take all this data and i'm going to delete it out and let's say for instance that what i want to do is i actually want to align this text into the middle so what do i mean by that what you may have noticed is that whenever we put a text field in it goes over to the left and whenever we put a numeric value in it goes to the right so if i put in my name it stays on the left and if i put in a number it goes to the right well if i wanted both of these to align in the middle or maybe both of them to be to the left i would have to select a cell go over to my format and here's where we can align the text merge the cells wrap the text or even change the rotation of the text so let's talk about aligning first the first thing i notice is that we have three options we can align it to the left the center or the right and then going vertically we can align it towards the top the middle or the bottom so i'm going to align this in the center and you'll see that now they both move to the center if i wanted to align it by going back to the left align it to the left and to the right now if i want it in the center again but this time i also want it towards the top of the cell we can go to format top now it doesn't look like much at the moment but if we did enlarge these rows you would see that now it's on the top and if i go to format and i align it towards the bottom it will be on the bottom and for the middle will be in the middle now another thing we can do besides just aligning it in the middle or to the left or to the right or in the center is we can choose to rotate the text right now there's no text rotation so it's by default but if we wanted to we can tilt it up as a little tilt we can tilt it down we can do things like stack vertically or even rotate up or if we want to turn it around we can rotate down and last but not least we can choose a 90 degree angle and really customize how we want this to look this is more for appearance when you're doing different data sets now the last thing we can do here is let's say i'm just going to delete this example that we have joe called in sec now you notice that this was a long sentence so i had to really make this big because if i didn't it spilled and it cut off into b if i wanted to i can choose that format and actually do text wrapping so it wraps the text around right now it's an overflow so it's spilling over into the b column and we're cutting it off so instead i'm going to use wrap you'll see that it will cut it off to where it's big right there if i wanted to put in column next to the four it would cut off by the four however big the column is sick it would cut into the sick but i think this looks good just like that and once again this is preference and just the way that you want your appearance of the sheet to look i didn't mind it like this so i'm going to keep it back in this order but just know that you can do that so what i want you to do is take a moment now and try it out for yourself and then come back and we'll have some more fun now back in an earlier section we actually used the explore button right down here and we were able to change the way that this appeared so it kind of put in a nice little appearance so it wasn't just pure black on white now we can make more changes to the appearance and format our sheet to just make it look a little more vibrant so for instance if i wanted to change the colors of my headers here what i can do is i can select all of my headers and you'll notice on the quick access toolbar we have something called fill color we can actually fill the background color of the cells and design it any way we like i'm going to use my favorite color which is blue i actually like this blue here not only that but we could have bold or italicized or even underlined or changed the text color maybe i want it to be red maybe i wanted to just stay automatic black we can use keyboard shortcuts like control u to underline control b to bold back and forth maybe for each of these i want it to be ordered so i can use my borders and add a border that looks pretty good and we can change the appearance of this now not only do we have to change this manually but if we go into our format menu we can also select a theme so i'm going to click on format theme and it's going to open up the standard current theme which is standard and we can choose different ones like a simple light you'll notice that clicking on them will show you a preview of what it will look like it will change not only the text it'll change the color all the different font styles the sizes of the font and i'll click on all these different ones just take a look at which one i like the most if i don't like any of them i can always click on customize and choose maybe i want the font to be ariel maybe i want the text color to just be a nice black if i have any charts i want the chart backgrounds to be just white choose different accent colors like blue accent number two would be darker blue accent three maybe a green and we can choose all these different colors pick the ones that we like the most and if we have hyperlinks we'll make them red once we're done we can simply click done and we're good to go we can exit this out and this is a great way that we can format and change the appearance of our charts our tables and just our normal data take a moment try it out on your own pick some of your favorite colors we've created a pretty great spreadsheet here but we may not always have the time to do so instead we can use pre-existing spreadsheets that were created from google and those are known as templates so to get to the templates i'm going to go over to sheets home and if you remember in the startup page we had access to these templates right here now if you wanted to see more templates you can always go into the template gallery and that's what i'll do i'll click into template gallery you'll see they have a bunch of templates like to-do list templates annual budget monthly budget some calendar templates as well work related templates project management and even education we're going to keep things simple let's do a simple to-do list so it's going to load up the to-do list and look at this we have check marks we have a date and the task and if i simply select the check mark it will cross off the task so maybe i want to add a task for today and i will put something like there we go once i'm done with this task i can click the check mark and we're good to go so take a moment check out one of those templates and try it out we don't only need to use one sheet all the time sometimes we may need more sheets now i'm referring to the sheets right down here you'll notice we have one sheet called the to do sheet now there's many things we can do here like maybe for instance we want to take this sheet and delete it or maybe we want to make a duplicate or copy it somewhere else to a new spreadsheet or an existing one rename it change the color of it or even protect it let's say that we want to make sure that we have a to-do list for every month so instead of this being called to do i'm going to click the drop down and i'm going to rename it i'm going to call this one january to-do list now the next thing i may want to do is create another one for next month and instead of creating a whole nother sheet especially since this was a template created by google i can simply click duplicate it will make a copy of that sheet now once we start doing this we can do a couple of them so maybe i'm going to cross out some of these tasks and deselect them just so that we have a clear sheet here and i'm going to call this one i'm going to rename it i'll call this one february to do list we can keep doing that just making those duplicates if we wanted to we can also hide some of the sheets so for instance maybe i'm done with january and i don't need to see it anymore i can always click the drop down and hide the sheet i'll say use the view menu to unhide sheets so it gives you a little tip there if you're wondering hey where's my sheet how do i get it back you can also just click on all the sheets and select januaries it'll bring it back as well or you could have went into the view and you could have just simply seen all your hidden sheets another thing we can do is change the color of these so maybe i want this one to be a nice blue and i want this one to be a nice red and we can color code take a moment try this out on your own and then we'll come back for some more fun stuff let us head back over to the original file that we were working with to do so i'm just going to go back to the sheets home and i'll see some of the things i was working on today which is our google sheets practice fun i'll open that up now that i have this what i want to do is i simply want to maybe freeze the pains now you may be asking well why would i want to freeze pains and what is that well let's say that we have a lot of data here going down so i'm just gonna play around with this i'm gonna move this a little bit right up here and i'm gonna delete these two columns and we'll delete column a and b by right clicking delete columns what i'm going to do is just take this data right here and copy it and then paste it over and over again just so it makes it look like we have a lot of data now the reason why i would use freeze panes is because sometimes when we're looking at our data we may do this we may start to scroll and look and we'll be like wait a second what are these numbers for oh yeah week three what are these numbers oh yeah total sales and that can happen a lot when you're using massive amounts of data so a little trick is to actually view this with freeze panes now since we're talking about viewing it a certain way we're going to go into the view menu and the first option is our freeze option now you can freeze no rows and no columns one row and one column two and two or up to the current row so the up to the current row and up to the current column matters where we are in the spreadsheet for instance if i'm in d column 9 when i go to view freeze it will say column d row 9. so if i wanted to just simply freeze this top row of headers what i would do is i would click anywhere below it and go to view freeze and i'm just going to freeze the first row and what that will do is freeze all of this but it will allow me to move here so now when i start to scroll down my headers scroll with me and then i'll always know that this number is a bonus this is the sales week four three two and one and we're good to go so take a moment and try this out for yourself another thing i may want to do at this moment is maybe change some of these values from just a normal value to a currency because we are talking about a dollar amount to do that you may notice right over here there's this dollar sign and that will format our text to a currency so i'm going to click it now when we're dealing with massive amounts of data that have just full numbers here for the amount we don't necessarily need the change so what you can do is come here and increase or decrease the decimal places i'm going to do that i'm going to decrease both one two now our data looks really nice so take a moment and try this out for yourself when we left off we talked about freezing pains and it was really helpful especially when i had massive amounts of data and i wanted to keep my headers from moving with the entire sheet now if you want that to stop happening we just have to simply go back to view freeze and select no rows if we had columns frozen we would go to freeze and no columns and everything will go back to normal now what we're going to talk about next is how we can not freeze columns and rows but how we can actually hide them because maybe there's certain data in here that our managers don't need or maybe we don't need to see it for instance maybe i don't want people to know the 20 bonus rate so what i can do is i can hide these two columns now to hide a column you can either right click on the column and get all of these different menu options or we can click on the drop down and get that same menu now you'll notice i do have an option to hide the column and that's exactly what i'm going to do i'm going to click the drop down and hide that column click the drop down and hide the column now it's not going to mess with my formula because we haven't deleted anything we've just simply hidden it and if we want to get it back you'll now notice that we do have these arrows going back and forth which simply means that something's being hidden so that's a sure way to know when something's hidden to unhide it you're going to click and boom it brings it right back so to hide drop down hide drop down and hide and if you want to bring it back just click on the left or right arrow and it will bring you right back take a moment and try it out on your own so far we've created a great spreadsheet but sometimes before we send it out we like to just double check that everything's looking okay well let's say for instance i was rushing through creating this and i spelt week four wrong maybe i forgot an e well i'm not going to look through every single cell to see if i spelled things correctly instead what i can do is i can use a tool called spell checker now to access that we're going to go over to the tools menu and go to spelling you will see we have an option for spell check i'm going to give that a click when i do that it's going to search through the entire google sheet and look it said change whack too weak now i can either change that or ignore it or maybe i meant to say wek i want to add that to my dictionary but since i know that i did mean to say week i'm going to want to be sure to change it to week and click change it's going to fix the spelling error and then i may just search all the sheets just in case and we're good to go no spelling suggestions and our document looks great if you're looking at the spreadsheet and you decide that well you're not utilizing the tools at the moment and you just want to look fully at the spreadsheet there's actually a view where you can use the full screen view to just see the spreadsheet and none of the commands now this is great if you send this off to someone and they're not looking to edit this sheet but just simply view it to do that we're going to head over to the view menu right up top here and we're going to go over to full screen so view full screen and what you'll notice is it hides everything up to the formula bar and then it tells us that the controls have been hidden if we want to show the controls we just simply press escape on our keyboard and it'll bring it back so now that we have this great worksheet here we may want to print it so to print it there's a couple ways we can do that we can either come right over here to the quick access toolbar and select print it also gives you the keyboard shortcut or you can click into the file menu and all the way at the bottom click on print now once you do that it's going to open you up into the print settings it'll tell you how many pages total you have you can see which sheet you're currently printing and if you had multiple sheets you can select which ones you want you can choose the paper size the orientation landscape or portrait the scale maybe you want to fit it to page fit it to width normal you can select different margins some formatting options like showing the grid lines showing some notes and also you can add some headers and footers like the page number the workbook title the sheet name maybe i don't want the sheet name on there because it only says sheet1 so i'll remove it and there you go you can choose exactly what you want now once you're done setting this up you can just simply click next and then i'll open up your computer's print settings and you can print it so take a minute check it out i'll see you soon besides just printing this we can also download this google sheet in different ways to download it we're going to go over to our file menu we're going to go to download now what you'll notice is we have different formats we can download by like microsoft excel or even a pdf or a csv so you can use these different formats especially if you work with different companies that don't always utilize google sheets maybe they use microsoft excel and you still have to send this information to them so take a moment and check it out another great feature of google sheets is that we can take this sheet and publish it to the web now to do that it's very simple we're going to simply come over to our file menu again and go over to publish to the web and give that a click now once you click on that option it's going to open up a dialog box and it's going to give you two options a link to the entire document or we can actually embed this in some html code we can also access the publish content settings automatically republish when changes are made which is nice so you don't have to worry about uploading it every single time there's a change and you can also click on learn more if you wanted to learn a little bit more about being able to use stock sheets and slides and embed them in html code or even just put the link into the system and there we go we've gone over how we can publish to the web we can print our documents download it as things we can even share this document but how do we email it as an attachment well to do that we're gonna find that button once again in our file menu you'll see the option to email as an attachment i'm gonna give that a click now when i do that it's going to ask me if i want to send a copy to myself and you can either check mark that on or off then you can choose who you want to send it to for instance maybe i want to send it to my work email and then we can put the subject now the subject will automatically put the file name in there but you can always change that now we have a couple of options when we do an email as an attachment we can actually send a little message and say hey you know here's a little detail about this worksheet but the biggest thing is that we can change the formatting of the way the sheet will be sent we can attach it as a pdf and if we click the drop down we can also attach it as an open office spreadsheet or even a microsoft excel file and you can choose whichever one you like i always go with microsoft excel because when we're dealing with spreadsheets like this most of the time people want to edit or take a look at different tools within the spreadsheet i'll send it off send myself a copy just so that i have it send a little message and then send it take a moment take a look at those settings and i'll see you back i had a lot of fun talking about google sheets with you and i hope you got a lot of good tips and tricks out of this course as always stay safe and healthy and i'll see you next time thanks for watching don't forget we also offer live classes and office applications professional development and private training visit learned.com for more details please remember to like and subscribe and let us know your thoughts in the comments thank you for choosing learn it [Music]