Main

Google Sheets Full Course Tutorial (2+ Hours)

Google Sheets Full Course Tutorial (2+ Hours) 2023 Update Get Ad-Free Training by becoming a member today! https://www.youtube.com/channel/UCqyBfm_H9ugGirk1ufYA2YA/join Exercise Files: https://learnitanytime.com/p/exercise-files-google-sheets-full-course-tutorial-2023 Who it's for: Anyone looking to gather, format, and interpret data in a spreadsheet. What it is: Google Sheets is Google’s free, web-based spreadsheet application that mirrors the magic of Microsoft Excel, with some standout features and key differences as well. What you'll learn: First, students will be shown how to navigate the app. Then, they’ll learn the basics like creating and sharing spreadsheets. Next, they’ll learn how to calculate data with functions and formulas, as well as how to filter and sort all their data. Finally, users will get some hands-on time with popular features like Pivot Tables, AutoFill, VLOOKUP, Macros, real-time collaboration, and more. Join Learnit Anytime for ad-free training, exams, certificates, and exclusive content: https://www.learnitanytime.com For Private Group Trainings please visit: https://www.learnit.com/private-group-classes-workshops Manuals: https://bit.ly/30xZb59 Username: manuals Password: password Start 0:00 Introduction 0:09 Accessing Google Sheets 0:52 Navigating in Cells and Menus 3:41 Keyboard Shortcuts 6:08 Editing Cell Contents 8:48 Templates 11:21 Using Explore 13:43 Opening and Saving Sheets 16:58 Using Google Drive with Google Sheets 19:38 Showing and Using the Formula Bar and Gridlines 22:11 Basic Formulas 24:10 Using Autofill to Copy Formulas 27:19 Using Autofill to Create Redundant Data 28:59 Simple Formulas 31:11 Formulas with Absolute References 34:27 Cut, Copy, and Paste 39:21 Inserting Rows and Columns 42:15 Wrapping Text, Rotating, and Aligning Cells 45:00 Cell Formatting 47:48 Hiding Rows and Columns 50:49 Freeze Panes and Full Screen View 54:47 Spell Check and Printing 56:54 Sharing and Emailing Sheets 1:00:07 Publishing to Web 1:02:53 Conclusion 1:04:46 Introduction 1:06:21 List, Format, and Sort 1:06:59 Multi-Column Sort 1:09:26 Filters 1:11:34 Conditional Filters and Filtered Views 1:14:31 Creating Charts 1:18:10 Editing Charts 1:21:01 Removing and Deleting Chart Data 1:24:17 Sparklines 1:26:42 Formatting with Alternating Row Colors 1:29:21 Pivot Tables 1:30:35 Pivot Charts 1:34:48 Named Ranges 1:37:28 IF Functions 1:42:18 Nested Functions 1:45:22 VLOOKUP 1:48:27 SUMIF and AVERAGEIF 1:51:56 SUMIFS 1:55:55 AVERAGEIFS 1:58:31 IFERROR 2:01:02 LEFT and RIGHT 2:03:25 CONCATENATE 2:06:01 Sharing Files 2:08:06 Real-Time Authoring 2:09:50 Google Sheets Add-Ons 2:11:49 Introduction to Google Apps Scripts 2:13:44 Conclusion 2:16:02 #googlesheets #googlesheetstutorial #googleworkspace #googleworkspacetutorial (C) 2023 Learnit, Inc. Any illegal reproduction of this content will result in immediate legal action.

Learnit Training

7 months ago
welcome to learn it training the exercise files for today's course are located in the video description below don't forget to like And subscribe hi everybody Welcome to our Google Sheets part one course now Google Sheets is an amazing spreadsheet application that works in a web browser it's part of the Google Suite of tools along with Gmail and also Google Docs during this course we're going to teach you how to create your own spreadsheets from scratch we're also going to look at fast and easy w
ays to navigate inside the Google Sheets application along with that we'll also show you how easy it is to work with Google drive to save all of your Google sheets online and remember that makes for fast and easy access we're also going to show you how to begin creating your own basic formulas and how to edit those formulas with things like absolute references so join us for this course hi everyone we want to start by how do we get to Google Sheets from any web browser it doesn't matter what web
browser you use but we recommend the newest versions of safari Microsoft Edge Chrome of course Google Chrome and even Mozilla we don't recommend Internet Explorer because it's going away now I'm actually logged into my Google account if you don't have a Google account get one they're free and it only takes a couple of moments up here in the top right hand corner of my Google account you're going to see my access to my Google apps all I need to do to access my Google Sheets is to click on this a
pp button it'll show me a list of all the different again tools and applications that come with my Google account and Google Sheets is one of these options I'm going to scroll down just a little bit and come in and you'll see right here that sheets is one of the applications along with Google Docs Google keep all these great free tools all I need to do to access the spreadsheet application is Click you're going to notice that when I look at my web browser URL bar it has a little bit of a differe
nt URL because I've now switched over to create a new spreadsheet notice in the top left hand corner that it shows me the sheets application is the tool that I'm in and it's asking me if I'd like to either create a new blank spreadsheet based my spreadsheet on one of the Google templates these are pre-made spreadsheets that you can base new spreadsheets on it's like a stamp or you're going to see a list of previously used spreadsheets now in the top left hand corner next to the sheets icon I'm a
lso going to see my main menu Navigator when I click on this it just takes me in so that I can access some of the other applications that are part of my Google account including for example Google slides and Google forms all I need to do to close this is just click away from it and the sidebar closes when I'm ready to open up a new blank spreadsheet which is what we're going to do I'm going to come right here to blank click on the plus sign and a new blank Spreadsheet will open up now some quick
tips when it comes to navigating in your new spreadsheet remember that columns are known by a column letter you can click on any letter and the entire column will get highlighted rows are known by a row number so as I come in and click on a row number I'll see the entire row get selected each box inside the spreadsheet is called a cell and the cell has a name that is indicated by its column letter and row number now you'll also see that in Google Sheets there are drop down menus there's a file
drop down menu an edit drop down menu and the names of these menus help you to know specifically what they do so the edit menu is all about editing content in your spreadsheet in the file menu is about working with the spreadsheet as a whole so go into Google Sheets try creating a new blank spreadsheet and play with navigating in the cells foreign welcome back everybody I'm in a blank Google spreadsheet and I want to just play with some of the things that I can do to customize my experience and
also navigate in cells now the toolbars up at the top are drop down menus and we talked about these earlier but just remember that the name of the particular drop down menu helps you to know what it does so for example the format drop down menu is all about formatting your spreadsheet we'll do that later on when we look at how to format the content inside your cells the tools drop down menu is about spell check auto complete and also notification settings so it's a good idea to spend some time c
licking on these menus and get familiar with what's under them underneath that you're going to have your toolbar now this is something that you can again customize by coming to the far right hand side you'll see that you have the ability to hide those drop down menus just by clicking on the far right arrow those drop down menus will basically turn off and then all you'll see is the toolbar when you click on the button again the drop down arrow it'll populate those drop drop down menus one more t
ime the other thing that you'll see in the top left corner above the drop down menus is the name of your current spreadsheet now when you're ready to actually give this spreadsheet a name all you have to do is click and type in the name of your spreadsheet and notice that the default location where it will save the spreadsheet to is my drive this is on purpose it stands for your Google drive as soon as I hit enter my spreadsheet will be saved in my Google Drive with that name which is great beca
use it means that it's now saved online in Google Drive and it'll be really easy for me to get to it later on now how do I move around in this spreadsheet basically you have a couple of different tools you can click and install with your mouse whatever cell you click in will have a border around it and will be called your active self you can also use your mouse to left drag over multiple cells and create what we call a cell range but you'll still see the first cell you clicked in is the one that
's highlighted now another tool that you'll have over on the right hand side are your scroll bars you can use your mouse wheel to scroll up and down you can also just left drag that again scroll bar up to get to the top of your spreadsheet by clicking and using scroll bars you have fast and easy ways to navigating your spreadsheet in a future lesson we're going to talk about some of the great keyboard shortcuts but again open up a new blank spreadsheet try saving it to your Google Drive and also
practice navigating thank you howdy welcome back I want to show you some of my favorite keyboard shortcuts to use in Google Sheets the great thing about a lot of these shortcuts is they work in a lot of other applications including Microsoft but the great thing is that a few of these keyboard shortcuts will save you years of your life when it comes to navigating in a workbook now if you need a Google sheet spreadsheet to play with go to the practice files that there should be a link for in the
description of this course and open up the file called navigation I want to start by showing you one of my very favorites and it's to get between the top and bottom of this spreadsheet no matter what cell you're clicked in and remember the cellular clicked in is your active cell on your keyboard look for the control key with the home button now these mostly work on a PC if you're using a Mac then I would suggest Googling keyboard shortcuts for Google Sheets for Mac computers but when you type in
control at home you'll see that it takes you to cell A1 so it always takes you to the top cell of whatever sheet you're in now the opposite it is to do control with the End Key now this one will take you to the bottom of the spreadsheet or the last place someone actually entered information but between these two keyboard shortcuts Control Plus n and Control Plus and end you can toggle between the top and bottom of your spreadsheet another one that I really like is the control a or select all ke
yboard shortcut this will select all the cells in your workbook you just hit Ctrl a and it's like a is an apple the entire spreadsheet where there's data will get selected another way to do this same thing without using a keyboard shortcut is to come to where the rows and the column letters meet and you'll see that if you click this is a select all button to select every cell in your spreadsheet another great keyboard shortcut is to go to the top of an area where you want to select everything be
low it where you have typed information and then do control shift down arrow notice this will only select the cells below that have information typed inside of them this can be a big Time Saver because you don't have to select an entire column or row and you don't have to scroll down to select all the content remember if you select something and want to deselect it just click in a Cell outside the selection and the selection will turn off just remember these keyboard shortcuts are to save you ti
me you don't have to use them but again I recommend having a few well-remembered keyboard shortcuts in your toolkit because they will save you a lot of time navigating and selecting content in your Google Sheets so give some of these a try and remember you can use the practice files at any time welcome back I want to look at how to edit content in cells and also how to delete content so first of all how do I just edit something that's in a cell and replace it with something else well the first t
hing you need to do is click in the cell that you want to edit now just a reminder that if you need a practice file in Google sheet so that you can try this out go to the course description click on the link and you'll see all the practice files that go at this course there I'm currently in the file called navigation I've clicked in the cell that I want to replace its contents and then all I'm going to do is type the new information over the top as soon as I hit enter you'll see that what's in t
he cell is entirely replaced now what if I only want to replace part of what's in a cell to either correct it or update it for this I can click in the cell and then if I double click it will give me a blinking cursor I can also go directly to the cell and double click and my cursor will appear inside the cell and I can actually type the correct information when I'm done I can hit enter and the cell's contents will be edited another way to go about out doing this is to actually click in the cell
that I want to edit and then come up and use the formula bar this can be used to either do data entry editing or for formulas and we'll use it when we do formulas later on in the course I click up in the formula bar type in the information I want it to say and then hit enter and we'll see that whatever I've typed or edited in the formula bar will be entered in the cell now another thing that can happen is needing to quickly delete content in cells for this what I want to do is come to the cell w
here I want to delete the content and then select the additional cells a great keyboard shortcut we've already talked about for this is to do Ctrl shift down arrow remember everything that's typed will get selected then all I need to do on my keyboard is hit the delete key every cell that's selected that has contents will again get cleared out so the cells aren't deleted but the contents of those cells are deleted remember you also have the undo button the most important button in Google Sheets
so on your toolbar if you come to the far left hand side you'll see that the undo button is there it points to the left when you're facing your screen and then of course the redo button points to the right I can use the undo button or Ctrl Z to undo anything that I've mistakenly done so just remember that navigating in the spreadsheet and editing content is really just as easy as clicking in a Cell typing in the update that you want to make and then hitting the enter key so that you can move for
ward go ahead and try it out on your own in your own spreadsheet hi everybody another great tool that's available to you in Google Sheets are the templates that Google has created these are a great way to get started if you need a specific type of workbook but don't really know how to begin so to locate the templates I'm inside Google Sheets in an Untitled spreadsheet and remember you can do this on your own as well but I'm going to go up to the file drop down menu like I'm going to open up anot
her new spreadsheet but rather than going in and just opening up a new blank one I'm going to select new and take a look at the additional option here that's called from template Gallery this is one way to access the templates that have been created by the Google team and again a template is like a stamp that you can create your Google sheet from but they've basically created the architecture of the spreadsheet for you a really fun one to try out is the annual budget but notice there are a lot o
f different ones including a wedding planner in this list it's kind of fun to explore and especially if you're new some of these can save you a lot of time all you do to use the template is click on it and you'll see that the template will open up in a new browser tab with the same name as the template file so I picked the annual budget template and it's called annual budget it of course will be saved in my Google Drive folder but if I want to give an additional name I can just click next to the
title and customize the name and hit enter now you'll see that there are directions right here step by step that teach you how to use the particular template this one's telling me that I need to go to row 13 and enter in my own total budget amount so I'm going to just type it over the top of what's already there then it tells me I need to go down and go to the tabs for expenses and income so I'm going to come down to the expenses tab I would enter that information in then I would go to my incom
e Tab and enter that information in and then you'll see that it tells me that I can also delete and rename those tabs but the basic architecture of the spreadsheet is here ready to you go for me so that I don't have to spend the time trying to create that and it really is a great way to explore and try out some different types of Google Sheets without having to go to all the time necessary to do it on your own so try some of the templates out because they'll be a great way for you to practice us
ing Google Sheets and also save yourself some time welcome back have you ever had data that you really want to drill into but you're not sure how to go about it you think I'd love to create a chart with this data or I'd love to be able to Pivot this data I don't know how well Google has created a tool inside the Google Sheets application called explore and it's all about helping you explore your data when you really don't know where to begin now I have currently opened the trip data spreadsheet
it's available in the practice file so go to the link in the description and open it up to locate the explore function you're going to come down to the very bottom right hand corner of your Google Sheets application and it says explore it has a cool little kind of green square with a star inside of it when you click on this it opens up the explore pane it does a lot of different things it allows you to ask questions about your data it even makes suggestions and then it has the opportunity to for
mat create pivot tables keep scrolling down even create charts so how do you use some of these different elements well the first thing you need to do is hover over the thing that you're interested in let's start with a chart I'm going to go ahead and hover over this chart and you can see that in the top right hand corner that it gives me the opportunity to insert the chart or even to view it full size let's start with viewing it full size when I click on this it shows me basically a screenshot o
f what the chart looks like there are also arrows here that will allow me to kind of zoom in and out on it when I'm done all I need to do is go to the top right hand corner and click on the X if I'd like to insert a specific of a chart all I need to do is hover over it come to the top right where you'll see a chart with a plus sign click and the chart will actually be inserted into your data remember you can just left direct the chat chart out of the way because it's like a graphic the sizing ha
ndles on the corners allow you to size it and when you click away it will deselect the chart additional options are going to be where it gives you possible questions if I come right here for example where it's saying this is a suggested question I can click and it will actually give me a pivot table of the answer now remember if you're not familiar with pivot tables this is something we'll talk about in a later course and lesson but it's all about taking large amounts of data and focusing in on
specific facets of this data if I decide I'd like to take this pivot table and put it in again to my current data set I can come in and you'll see that there is a delete option to remove it and then right here I also have an option to actually preview it and then down in the bottom right I can insert it into my spreadsheet and that's what I want to do the unit's saying is this the correct range I'll say create and I'll see my pivot table will be put on a new tab down at the bottom and again this
is showing me an average of information from my sheet data but my actual base data is back on the sheet1 tab so again notice all the options that the Explorer task pane is giving me here including the ability to take my data and format it with alternating row colors along with additional tools that also helps make it easier for me to read into the data so this is definitely something to explore go in open up the trip data practice file and try playing with the explore task pane when you're done
with it all you need to do is click on it and it will close welcome back I'd like to open an existing Google spreadsheet and also learn how to go in and save a spreadsheet now right now I'm in an Untitled spreadsheet and I'd like to open a spreadsheet that's saved to my Google Drive I'm going to come up to the file drop down menu and go to open when you go to open you'll see that there are different locations that you can get to from your device recent files would be any recent Google Sheets yo
u've opened my drive will take you to your Google Drive and this is your online cloud storage that's part of your Google account there's also a shared with me this is where you have had a Google sheet or spreadsheet shared with you by someone that you work with or a friend you also have computers this means the ability to actually locate computers that you can connect to through Google Drive then also the ability to go in and actually upload a file that's saved locally now in our case the the sp
readsheet I want is actually in my Google Drive so I'm going to go back to the my drive tab when I come in all I need to do to locate the one that I want to open is find it in the list of different files that are saved and double click on it this will actually open it up right inside of my Google Sheets application and I'm ready to go now if I want to save it or update it with a different name is I've already shown you I'm going to come up to the top left corner click next to the name that's the
re it'll show me where it's currently saved and I'll type in the new name right over the top and this will save it remember everything saves as you go because Google Drive is online and so is Google Sheets so you actually don't have to worry about saving anything because as you work your files are saved automatically remember it's auto save not instasave so if your internet speeds a little bit slow you might have to refresh the browser window but everything should be saving as you work in the sp
readsheet that means as soon as I select some cells and for example put something in italics it's automatically already saving in fact I can see that and once I know it's been successfully saved it'll tell me that and then it quickly disappears so working with Google Drive and Google Sheets is a great combination because everything is saving as you go and everything defaults to save in your Google drive if you'd like to save it somewhere else notice right here that next to it there is a move opt
ion this would allow me to actually go in and locate a different folder or even create a new folder to save the Google spreadsheet in so try this out go in open up one of your own Google Sheets try saving it with a different name also try maybe putting it into a folder to see how easy it is to work with Google Sheets and Google Drive foreign welcome back I want to take a minute and talk about the main place for files that you use with Google Sheets will be stored it's in your Google Drive Google
Drive is a free cloud storage tool that comes when you sign up for any Google account now from inside any Google Sheets file you can access your Google Drive one of the ways to do that is to go to the top left hand corner to what we call the main sheets home button it looks like the icon that represents Google Sheets when you click on this you're going to see that it takes you back to the getting started screen of Google Sheets where you can create brand new sheets or go into one of the templat
es that comes with your Google Sheets account now from here I'm going to go to the top left hand corner again you'll see three horizontal lines these will take you to the main menu of Google Sheets this is also a way to get to some of the other applications within the Google Suite including Google Docs Google Slides Google forms but at the very bottom we've got the icon representing your Google drive when you click on this it is going to take you into your Google Drive account now a couple of ma
in things about your Google Drive account when you come into it the different locations where you can access files from will be over here on the left hand side my drive represents the different files that you've brought into your Google Drive account computers means any computers that you can access files from and then of course shared would be any files that have been shared with you through Google Drive now for us we're going to focus on my drive now there are a couple of different ways to get
files into your Google Drive account one of them is to go to the new button and you can either upload files or folders or create brand new content you can also drag and drop content from exact for example from your Windows desktop into your Google Drive account we're just going to upload a file to show you how easy it is I'm going to go to file upload I'll navigate on my computer to the file that I want to bring in and again it's a good idea for example if it's a spreadsheet then I'll go ahead
and click on open and you'll see that that file will get added into my Google Drive account and now it's here I have a copy of it online I'll be able to use Google Sheets to update it all I need to do is double click on the file from Google Drive and it will actually launch a new browser tab that will take me into my file in Google Sheets and I'll be able to start working here but when I make changes or update the file it will automatically be saved to my Google Drive it's a really great relatio
nship because I can open the file seamlessly and know that they're saved right in the cloud in my Google Drive hi everybody let's talk about the formula bar in Google Sheets it's made for two things to insert formulas and to do data entry we're going to try it out with data entry and also make sure you know where it's located so to get to the formula bar you're going to go up and right underneath the toolbar at the top you'll see this long white bar directly to the right of the cell name box whe
n you click inside it allows you to again enter data into cells or create formulas now to use it for data entry you're going to click in a Cell then go up and make sure that your cursor is blinking in the formula bar you'll type in the formula bar what you want entered in the cell in the cell then you'll hit enter and you'll see that whatever you put in the formula bar gets entered into the cell I want to show you one more thing I'm going to highlight a couple of cells at the top of my spreadshe
et and go up to the format drop down menu and then come down to merge cells and select merge all this will take cell A1 and merge all of the cells B1 C1 and D1 into one cell and then I can also go up and use the center button to Center that information inside the cell now you can also turn the formula bar off so if you don't see it go to your view drop down menu come to show and make sure that there's a check box next to the formula bar in addition to being able to show and hide the formula bar
notice here you can uncheck to turn it off you can also go to that view drop down menu and turn off the grid lines that represent the different borders between cells and also show and height formulas that we'll talk about a little bit later I want both my formula bar and my grid line showing so I'm going to go back and turn them on now remember we have a practice file for you so go into the practice files open up sales 2-1 and try using the formula bar to do some data entry of your own inside yo
ur Google Sheets files okay we're ready to try out our first Formula now a couple things to remember about formulas you always begin where you want the product or answer to go so you begin at the end I've clicked in my spreadsheet and cell b11 and please remember if you'd like to follow along and try this out on your own go to the practice files open up the file that goes with this exercise which is sales 2-1 and you can try it out now that I've clicked in cell b11 I'm going to hit the equal sig
n now it's interesting to note that as soon as you type equals you're going to see the computer changes Google Sheets changes notice how all the drop down menus are grayed out up at the top and that's because we're in Formula mode Google Sheets is now assuming we're creating a formula and in fact it really tries to help us as we create this formula you're seeing an example of a potential formula right now in cell b11 that Google Sheets thinks we're going to create and this is the formula we're g
oing to create but rather than taking the suggestion and I'm going to have us type it out so we can talk about it we're going to be using a thing called a function or a built-in formula that comes with the Google Sheets application so when you add up cells you need to type in the word sum this is one of the built-in calculations that come with Google Sheets now as soon as I start typing it in Google Sheets tries to be really helpful with this list of potential formulas that I could use if I like
one of these potential formulas from this list all I have to do to use it is click on it and it enters it into the formula I'm creating the next thing you'll see with syntax in the formula is a parenthesis this will enclose the different cells in my Google sheet that I'm adding up in this case it's everything from B7 down to B10 so I'm going to come in and highlight the cells and you'll see that it will again enter the cell names rather than the values this is also important because it allows t
hose values to change but the cells will remain the same therefore I can put different values in the cells and the formula will still calculate now I really need a closing parenthesis but a nice thing you can do with a formula that only has one set of opening and closing parentheses is just to enter and your formula will be complete now let's check because in the cell I see the product or the answer but if I click back on that cell and come up to my formula bar up here then I'll actually see the
formula if I ever want to see the formula I can go to the cell where the formula is located and double click on it the formula will open up but be careful because what you'll see happen if you accidentally click somewhere you can actually accidentally edit your formula so remember click in the cell where you see the answer and check the formula bar for the formula so we're seeing here the syntax of a formula every formula starts with an equal sign if you're using one of the built-in calculation
s you can use some for example to add then you're going to see parentheses your cell range and then a closing parenthesis and voila we have our first Formula so give it a try welcome back I want to show you a fast easy way to copy formulas a lot of people stumble on this function but if you haven't it's going to change your life it's called the autofill command now you'll see that I'm in a spreadsheet called sales 2-2 please feel free to open it up in the practice files and you can try this out
on your own we've completed one simple formula a sum function it's in cell b11 so click and cell b11 then I'm going to take my mouse pointer and I'm going to put it right over the bottom right hand corner now here's the really important thing to get the right autofill you need to make sure that your mouse pointer looks like two black intersecting lines like you're aiming at something it doesn't need to look like a white Arrow it doesn't need to look like a white hand it needs to look like two bl
ack intersecting lines like you're aiming you're going to hold down your left Mouse pointer and drag it over to the right now what it's copying here is the formula but what I want you to see is how the formula has adjusted to its new position so the first Formula if I double click on it I'll see is summing B7 through B10 then I'll come to the next one that was created by the autofill and double click on it it's C7 through C10 the third formula is D7 through d10 and again I can click away from an
y cell and it will turn those formulas off the beauty of the autofill command is that it automatically creates a new formula that's relative to the position of the cells that it's being drugged to and it's a great fast way to copy formulas go into the sales 2-2 practice file and try it out yourself hi we just used the autofill handle in a previous lesson to help copy formulas but I also want you to see how you can use the autofill handle to help you create data entry spreadsheets and what I mean
by that is when you have a spreadsheet it includes information like chronological dates days of the week or months of the year the autofill handle can also help you to do that so what I'm going to do is come into the second sheet of whatever Google Sheets file I'm in because it's blank there's a really great keyboard shortcut that you can use to insert the current date from your computer into a cell in Google Sheets if you're on a PC it's the control key with the semicolon key this will insert
the current date into whatever cell you're working in or you could just go ahead and click I'm going to go up between the columns and widen the cell out so that we can see the date then what I want to do is click in that cell now that same autofill that I used I'm going to come in and again get those two black intersecting lines and I will drag down what Google Sheets does is it sees that date and it copies it for me and creates a chronological list of dates from the date that I entered in it ca
n save a lot of times when you need to create a list of dates another pattern that you can use this for is days of the week so I'm going to come in and type the day of the week this part you do have to type now once you type it in I'm going to go in and again get the AutoFill in the bottom right hand corner and drag to the right the autofill handle will work in all directions so you can also use it horizontally so it will also create a pattern of the days of the week the other thing you can use
it for is months of the year so I'm going to come in and type in a month and then of course I'll go back click on this cell get the autofill handle and drag down and it takes that month and then continues on with the rest of the months of the year so if you're ever creating a Google sheet file that has this redundant type of data in it don't forget that the autofill handle will also help you with these kinds of patterns and save you a lot of time when it comes to typing go into a blank sheet in
a Google Sheets file and just try this out on your own to see all the different things that the autofill can do for you hi everybody I'd like to introduce you to a few of the other simple formulas that are part of the Google platform and if you're wondering if Excel functions are available inside of Google most of them are and they're very similar so it's pretty easy to go between the two platforms let's try out an average function this is another really common formula now remember to find an av
erage you add up all the values and divide by their number to find the middle now you'll see in my spreadsheet that I want to select cells B7 through B10 and find the middle of my expenses I'm going to click in cell B13 and if you'd like to follow along remember you can open these practice files up they're provided this one is called sales 2-3 so I've clicked in the cell where I want my formula to go I'm going to hit an equal sign and start typing in the name of the average function as soon as I
see it in the list I can double click on it now it's already trying to find an average of cell A1 in my case which is incorrect so I'm going to come into the spreadsheet and highlight the correct range of cells which is B7 through B10 now in a function or formula that only has an opening and closing parentheses you don't need the closing parentheses you can just hit enter but notice again that it's even telling me what the answer is going to be so Google Sheets is really trying to help me with
a lot of things to let me know I'm going to have success when I hit enter I see the answer in the cell as we previously did you can now click on the cell with the answer go to the bottom right hand corner get your fill handle and drag it to the right and now I've completed three average functions now another really common type of function is called the maximum or Max function it will find the highest value in a list of numbers so I'm going to go ahead and click in one of my cells where I want th
e answer to go what I'd like to do is find the highest expense that I have so I can use the maximum function to complete this I'm going to hit an equal sign again start typing in the name of the function which is Max I'll see it in the list and I'll select it again all these functions use the same syntax now I need to select my range which is cells B7 through B10 I'll hit enter and I've now completed my maximum function I'll go down to the bottom right hand corner get the fill handle drag it ove
r and I'll see the highest value for each of those groups of cells now the opposite of the max function is called Min and it stands for minimum and it will find the lowest value so I'm now going to click in cell B15 hidden equal sign and type in min you'll see it again in the list of potential functions I'll click on it get the opening parentheses and then select my range B7 through B10 hit enter and now I can go down to the bottom right hand corner of that cell with the completed answer and dra
g it over so we've now done three new types of formulas average Max and Min and you can see how they all use the same exact syntax and how I can use the autofill to help me complete and drag those formulas over now go into the practice file and try this out on your own hi everybody I want to show you an important Concept in Google Sheets and other spreadsheet programs called an absolute or fixed reference in a formula now to do this I've opened up the practice file called sales 2-4 in Google She
ets remember you can do the same thing to follow along and I've come in to look at this spreadsheet we're going to actually create a problem and then fix it to help explain what a relative and an absolute reference inside of a spreadsheet are I've clicked in cell B5 I need to find out if I take each of my sales totals for January February and March and multiply them by this 110 budget increase what it will do to these sales totals it's a really simple multiplication problem so I'm going to click
in cell B5 and I'm going to create my formula I'm going to start with an equal sign click in the first cell and then to multiply I'm going to use an asterisk you don't use the X because it's actually part of the alphabet then I'll click in my second cell which is G2 so B4 asterisk G2 and I'm going to hit enter the formula works great now I'm going to come to the cell where I have my formula total or answer go to the bottom right hand corner get the black cross hair and drag it over to the right
and this is where things start to stop working and we've got to figure out why so let's go ahead and look at the formulas in each of these three cells to see why suddenly when we get to cell C5 the formula has stopped working there's a great tool you can use to view formulas inside the cells of your Google Sheets you're going to go up to the view drop down menu and come down to show then check off formulas I love this because rather than actually seeing the answers you will see the formulas in
cells pretty nice right now if I look at these cells where I have formulas I can see the first one was great but when I come to the next one and click especially if I double click notice that it highlights the cells that are being calculated so the first one in C4 is great but when I come up here and look my G2 has changed to H2 which is an empty cell guess what you get when you multiply something by nothing you get zero remember learning the zero time Stables that's what we're running into the
third formula is even worse because the first part of the formula D4 is great but notice up here I'm clear over now in cell I2 and I need to be fixed in cell G2 this is a perfect scenario to help you understand what a fixed reference is in this formula the G2 needs to stay fixed or absolutely focused on Cell G2 the first part of the formula can remain relative or flexible meaning that it can copy two new locations so to fix this we need to add an absolute or fixed reference in our formula we're
going to do that so I'm going to go ahead and turn my formulas off just by going up to view coming back to show and unchecking formulas they turn off now what's interesting is if we fix the first Formula it fixes all of them so I'm going to go ahead and double click on my first Formula and I can see again the formula in the cell I could also go up to the formula bar I'm going to highlight the G2 and I'm going to hit f4 on my keyboard now if you don't have function keys you can manually type thes
e dollar signs in front of the column and row number F4 though is a great keyboard shortcut especially on PCS to add an absolute or fixed reference in your formula when you see these dollar signs it means that one of the cell references is fixed in place let's see what it does I'm going to hit enter because now I've created or fixed the correct formula I'm going to click back on the cell with the formula go to the bottom right hand corner get the Crosshair and drag to the right and now we're goi
ng to see that the formula is fixed let's view the formulas one more time I'm going to go up to the view drop down come down to show and turn formulas on now I can see again if I widen these cells out just a little bit how each of them is locked in place on the G2 which is exactly what I want but the first part of the formula is relative so this is an example of formulas that have a relative and an absolute or fixed reference for the second portion you will see these a lot inside of Google Sheet
s and other spreadsheet programs like Excel they're very common and important to know about because you will see them in formulas and other analytics tools so remember you can go in and try this out in your own Google Sheets to turn the formulas off I'm going to go back up to view come to show an unchecked formulas and I'll be back to just seeing my answer hi everybody what about when you need to just add new columns or rows to your Google sheet how do you do that well I'm in a new practice file
called weekly sales this one has more data in it so it's a great place to practice inserting rows and columns first of all you just need to get your columns or rows selected so I'm going to come into the Monday sheet of this file and I'm going to select one of my columns now even as I hover over the column I'll see an arrow on the right this will give me options for working with this column and notice that it even gives me options for inserting or also deleting the column now I can also right c
lick if you're on a PC and get this exact same column what's great is it gives you options do you want the new column to go to the left or the right I'm going to pick to the left and notice I get one new blank column and all the data that was previously in this column is moved over to the right remember I can undo this by clicking on the undo button or using Ctrl Z if you're on a PC and that will undo it now what if I want to insert multiple columns in that case highlight the column Letters by l
eft dragging over them remember this will highlight everything in the column below so I've highlighted columns d e and f now if you prefer to use the drop down menus in Google Sheets you can go to the insert ribbon come down and you'll see that there is a columns option Google Sheets knows that I have columns selected and because I have three columns selected it's asking me if I would like to insert three columns to the left or three columns to the right I'm going to go ahead and do three column
s to the right and click now you'll see that I get three new columns and I'd have to scroll over to see them but their smack dab in the middle of my spreadsheet I can see my three new columns again if I wanted to undo that I can go up and click on the undo button and it takes them out now what's interesting is rows work the exact same way so I'm going to come into my spreadsheet and highlight three rows by left dragging and then I can either right click inside of those rows or I can go up to the
insert ribbon tab come in and again Google Sheets knows that I have rows selected this time and it will ask me if I want the new rows to go above or below in this case I'll say below I get three new blank rows everything else gets pushed down and then I could put information in I'm going to click on my undo button again to take those out of the spreadsheet try this in any of your Google spreadsheets especially where you have information just so you can see how it moves the other information in
the columns and rows over as you put new data in by putting in new columns and rows hi everybody I want to work with copy cut and paste inside Google Sheets if you've ever done this in other spreadsheet applications it's very similar in Google Sheets but let's check it out and remember to copy something means to make a duplicate I'm going to come into my spreadsheet and select the cells I want to copy in this case I'm in the weekly sales practice file so remember if you want to follow along get
the practice file and open it up I'm on the Monday sheet and I'm going to start by highlighting cells A4 over to I4 basically the column headings then to copy these I can do the edit drop down menu and select copy I can also do control C on my keyboard or if you right click inside a selection of files you can say copy there as well you'll see a blue dashed line going around the cells that have been selected to be copied now I want to do something a little bit tricky I'm going to paste these new
copied cells into a new sheet so down in the bottom left hand corner of my current Google sheet I'll see the add sheet button I'm going to click on it I get a new blank sheet then I'm going to select the cells where I'd like to paste those cells in I'm going to click then to paste I can right click and say paste I can go to my edit drop down menu and say paste or I can just do control V on my keyboard and it pastes them in now if I go back to the MonDay sheet I'll see that the original cells are
still there now how do I turn off this blue dash line because I could actually continue pasting those same cells in on your keyboard if you hit Escape it'll turn that off now to cut means to move for this I'm going to go to cell A1 and I can either do control X on my keyboard edit drop down menu and cut notice the little scissors or I can right click inside of the selected cell and say cut it looks like the same blue dashed line is when you copy but when you cut you're actually moving now I'm g
oing to go back to my new sheet and I'm going to click in cell A1 then I'm going to right click and say paste or do control V or any of those options that I prefer to paste the information in now if I go back to my other sheet I'll see that it has now moved that information so remember these different ways to copy and paste and cut and paste are up to you pick what's easiest for you I prefer the keyboard shortcuts Ctrl C to copy Ctrl X to cut and Ctrl V or right click paste are my preferred meth
ods you pick what works best for you but just play with this because it's something that you will use every day inside of Google hey everybody let's talk about cell alignment word wrap and also cell rotation now I am in the weekly sales Dash 2 file if you want to try this out in that file I'm going to start by highlighting cells A4 through I4 my column headings and then to do anything to do with cell alignment for example to rotate content in a Cell on the formatting toolbar I'm going to come up
to the right hand side and I'm going to look for the button that allows me to do rotation it has a capital letter A with an arrow pointing to the right when I click on this you'll notice it allows me to tilt the text inside the cells it also adjusts the height of the row as well and there are a lot of different directions that you can go the important thing here is make sure you can still see your text and read it I'm going to change it back to the default now when it comes to horizontal and ve
rtical alignments what you're going to do is come to the horizontal align button that's also on the same right hand side of the toolbar you've got left alignment then you've got Center alignment and you've got right alignment and you can see how it lines the text up according to what horizontal alignment you pick now for vertical alignment you need to make your row a little bit taller so I'm going to go between rows four and five notice how my arrow turns into a double headed Arrow one pointing
up one pointing down I'm going to drag it down a little bit this helps to better represent the default vertical alignment in cells it's at the bottom so I'm going to now come up and go to my vertical align and you'll notice that I can actually Place stuff at the top of the cell at the middle of the cell and at the bottom so you'll see right here that I now have my information at the left middle of my cell now the other thing I need to deal with is wrapping text in site cells normally your text i
s going to expand out into the other cells next to it but in this case maybe I don't want that for text wrap you can also go to the right hand side of the toolbar you'll see that there are lots of different buttons but there's one in the middle that's two bars with an arrow when you click on this it will wrap the text to keep your column width the same so you'll see right here that I can actually pick which wrap I want it to do I've clipped I have wrapped and then I also have the one that's righ
t here which is going to be the middle option so you can see that when I do clipped it actually cuts the text off if I go back and I do overflow then it's back to the default so you can pick which one you want but always remember with text wrapping there are pros and cons now for all these options you can go to the buttons on the right hand side of the the toolbar but you can also go up to the format drop down and you'll see that there are alignment options wrapping options and rotation options
on that same menu so track it out hi everybody I want to add some formatting to my spreadsheet to enhance the data and make it easier to read remember with formatting less is more you don't want your formatting to overtake what's inside your cells now I'm in the practice file called Jan Dash June sales if you'd like to try this out in that file feel free the first step to do formatting is to highlight the cells you'd like to apply the formatting to then I'm going to use the toolbar for this a lo
t of the formatting options are going to be located on the middle right hand side of the toolbar after you select your cells you can do simple formats like bold or put something in italics or italics you can also come in and do strikethroughs or change font color remember that when you do font color it's the capital letter A with the colored rectangle underneath you want to ensure that your font color is still legible in your cells now to do color behind cells you're again going to highlight the
cells and then go up to the button that looks like a paint cam this is called fill color you can see the different colors here and again pick a color that doesn't overtake the content in your cells now for grid lines you're going to need to select the cells that you want to apply the grid lines to go up to the grid lines button or the borders button and you'll notice these aren't grid lines that's what you see between the cells right now these will actually print with your Google sheet if you w
ere to print it but you can see the different grid lines that I can select and to format color and also style over on the right I have border color and under that I also have border style so I can pick different types of again borders for my different cells that I have selected now in addition to all this you also have your font Styles I'm going to click in cell A1 and go up to where it says default these are the fonts that come with Google Sheets again make sure that as you pick different fonts
you pick a font that's legible for font size you want to go right here to the very Middle where it tells you what the current font size of what's in that cell is you have a plus sign to increase font size and then you have a minus sign to decrease it if you just like to type in the font size you want click in the box and then you can just type it in and hit enter and it will again take whatever's in that cell and put it in that font size remember some of the other things we've already talked ab
out including using the merge cells button on the toolbar as well to take cells and merge them together and then also your alignments to adjust the alignment of content and cells and just remember that less is more and you want whatever your formatting is to really enhance the date of your spreadsheet finally what do you do if you add some formatting and you don't like it it's really easy to clear it out all you do is highlight the cells go up to the edit drop down menu and then actually come ov
er to format and go all the way down to clear formatting this takes out the formatting but all the content in your cells is still there hey everybody welcome back I want to show you how to manage sheets inside your workbook so you'll see that workbooks are divided into sheets so that we can stay organized in my current workbook I have five sheets now if I would like to add a new sheet I have the insert sheet button right here on the left hand side when I click each new sheet is called sheet1 all
I need to do to rename a sheet is double click the sheet name will get selected I can type in the new name and hit enter I can also rename sheets by right clicking and coming to rename in the right click menu now this menu is full of great options to help you manage your sheets including the ability to make a copy of a sheet through duplicate you'll see the new sheet will be called copy of and if you create a sheet you don't need you can right click and come in and say delete remember if there'
s anything on that sheet it will also be deleted but good news in Google Sheets this is also something you can undo now to change the order of sheets all you need to do is take the sheet you'd like to reorder and left drag it to its new new location you may also take a sheet and its content and actually move them to an entire new workbook to do this you want to right click on the sheet come up and go to copy to and you can either copy to a new or existing Google Sheets document I'm going to do a
new spreadsheet this will take my current sheet actually copy it into a new blank Google sheet if I come right here to this link I can click on it and it will actually take me to a new browser Tab and open up my new copied sheet that's in a new Google sheet file now the formatting may look different but all the information will be exactly the same if I close the sheet tab this is on it will still have saved that new spreadsheet because everything saves automatically now another thing you can do
is you can also apply tab color to your sheets for this you right click on the sheet go to change color and select a different color if it's a small colored rectangle under each sheet this helps you to recognize which sheet you're on in addition to all these things you can also hide sheets remember this is not a protection it just turns that sheet off so it's not in your way to hide a sheet you're going to right click on it and come in and say hide sheet the sheet turns off and notice right her
e it tells me that my sheets are currently hidden to unhide a sheet I need to go in and unhide it for this I can go up to the view drop down menu come down and it will tell me what sheets are hidden I can select the sheet I'd like to unhide and it will bring it back again this sheets blank but I can now see the sheet so that I can add new content so really your takeaway with sheets is right click and anything you need will be in the right click menu now the same goes with hiding rows and columns
this is a way for you to basically see things closer together to hide columns you're going to go to the column letters select the columns you'd like to hide right click inside of them come into the menu and you'll see an option that will allow you to hide the certain columns that you're trying to turn off now the columns are not gone they're just hit hidden notice there's an arrow between the hidden columns to let me know that they're hidden I can actually come to this arrow and if I drag it ou
t you'll notice that it doesn't turn off the hidden columns what I need to do is highlight the two columns surrounding the area that's hidden right click and then I'll see an option that says unhide rows work the same way you highlight the rows that you'd like to hide right click inside of them and then come up and select hide rows and again I can see how the numbering turns off or it should say changes and then I have these arrows right here to indicate that there's some hidden content there I
can highlight both the row numbers surrounding the area that's hidden right click and say unhide rows so you can hide rows and columns to help make it easier to see different portions of your spreadsheet as always please try this out there's a practice file that you can work with called weekly sales amounts in the practice folder try working with your sheets and also hiding rows and columns hey everybody welcome back I want to talk about a few great ways to adjust the view of your Google Sheets
to make it easier to see what you need to the first one is probably my favorite it's called freezing panes to do this you need to have your workbook open and go up to the view drop down menu and come to freeze now there are two ways to go you can freeze from a point that you have the spreadsheet marked or you can freeze from a column that you have selected now in this case I'm down in row 10. in fact I have cell C10 selected so it's giving me options whether I want to freeze the first row of the
spreadsheet the first two rows are up to row 10 where my cell is currently selected I'm going to select the fourth option up to row 10. I'm going to see a line go underneath row 10 and I'll notice that if I start scrolling down the top 10 rows of this spreadsheet stay locked in place but I can still scroll up to that point to remove the Frozen panes I'm going to go back up to view come down to freeze and just say no rows and it turns turns off I can also click on a column go up to view go to fr
eeze and then I could select for example up to column e because that's the column I have selected now if I come down and start scrolling to the right I'll notice that everything else in the spreadsheet will be scrollable but everything from row e to the left will remain fixed in place to turn it off I'm going to go back up to view freeze and just say no columns and it removes the Frozen paints now I also have a full screen view which is great when you don't want to deal with the drop downs and t
oolbar at the top for this you're going to go up to the view drop down menu and come to full screen you'll notice that all the drop downs and toolbars at the top disappear and it's a nice kind of minimalist way to work in your workbook to get back to the view where you can see the drop down menus hit the Escape key in the top left hand corner of your keyboard and it turns that full screen view off again try this out there's a practice file called weekly sales amounts Dash 2 that you can play wit
h if you need a file to use but try these views out and see how useful they can be for adjusting your workbooks hey everybody I want to go ahead and get this spreadsheet ready to spell check and print so to get to spell check I'm going to go up to the tools drop down menu come to spelling and spell check now you'll see that I can either update or change to the suggested spelling ignore which will skip that word or add it to my dictionary so it will not spell check the next time it comes to that
wording I'm going to go ahead and ignore this one and keep working through the spreadsheet you'll see that it has a hard time sometimes with the names of people but if I continue through eventually I'll see that there are some different words that I can update when you're doing spell check just be careful for example about especially people's names because it doesn't do a very good job with those once you're done you can either will tell you it's finished or you can click on the X to close the s
pell check box now when it comes to printing you can either go file in the drop down menus and down to print at the very bottom you can do control P the keyboard shortcut or up on the toolbar about the third button from the left you'll see the print button it looks like a little tiny printer when you click on this it takes you into your print settings box this will tell you how many pages you have to print notice in the bottom right hand corner there is a zoom in and a zoom out button you can sc
roll down so that you can see each page that's printing now as far as updating how things print by default your workbooks are set to just print the current sheet but if you decided you wanted to print your entire workbook you could do that also you can decide what page size you print on so for example the default is to print letter size paper but you could change that to legal for example if you needed to under that you can decide if the file prints landscape or portrait portrait is usually the
default and then in addition to that I also have the opportunity to adjust my margins Now by default there are three types of margins normal narrow and then white and obviously narrow is going to fit more on the page than wide will you can also do custom margins by coming to the very bottom bottom of the margins menu and you'll see here that it will actually show you what these margins are and then you can actually adjust them by coming to the dashed lines on the side and dragging them in and ou
t to fit more or less on the page now the other thing that I can also do is come in and say for example that I want this to maybe print 75 in size this is called scaling and it allows me to fit more on the page but notice as I do this how my document becomes smaller and smaller because it's trying to fit more this is called scaling another thing I can do is I can come in and just say that I would like to fit to page this will make the entire data set fit on the current page when I print it off a
gain all these options are here to help you successfully print your document the way you want when you're ready you come up to the top right hand corner click on next it will actually load to show you the print dialog box you can click on print and your document will successfully be printed on paper keep in mind that when you do scaling and adjusting of margins what's actually happening inside the scales of your spreadsheet don't change for example the size and the cells but you can print and ad
just what prints on paper from Google Sheets hi everybody what if I want to be able to share my Google Sheets file with someone well obviously I have email but I could also share it through Google Drive so let's look at the first option I'm going to go to the file drop down menu and after I click on file I'll come down to email it's right under share and then I've got email this file this is like sending the file to someone's as an attachment I can also include a copy to myself I'm going to type
in the person's email address it doesn't have to be Gmail by the way it will work for any email platform after I do that I can see the name of the file I can include a message and then the file type in this case I'm going to share it as a PDF which means they'll get a copy of the file in PDF format they can view it they can print it but they can't change it and you'll see this little blue line moving at the top until the file has actually been sent out now another option is to share the file fo
r sharing the file I'm going to come to file and then of course come down to share and I want to share it to others or I can publish it to the web if I share it with others this will use the power of Google Drive I type in the name of the person I want to share it with and notice right here that it will let me know that I am the owner on the right I can decide if the person I'm going to be sharing the file with can edit the file or if they'll be able to view I'm going to give them viewing status
also they will get an email letting them know that I've shared the file with them I can include a message and then I'll click on send this will give this person the ability to actually make edits in the file but I will be the owner of the file and if I come back to file and go back down to share and go into share with others it will show me who the file is currently shared with now the third and final option would be to actually download the file so that I could then have it in a different form
at when I come here to download you'll see that I can download this file as an Excel spreadsheet an open document file Source a PDF an HTML page even a CSV or tsv which is a lot like a text file if I pick for example Microsoft Excel it downloads the file and then I can come in and actually click on the file and it will launch Microsoft Excel if I have Excel installed on my computer and open the file up as it has right here now this is a copy it's no longer the Google Sheets document that I just
had open online it's been downloaded locally to my computer and I would then need to save it locally on my device to be able to use it but all of these different options allow you to have the power to share your Google Sheets in ways that work for you with the people that you also need to be able to use them and view them as always go in and try sharing some of your Google Sheets with co-workers or even other people you work with but always make sure you only share content that's secure hey ever
ybody what if you would like to be able to make your spreadsheet available to a wider audience rather than sharing it with a big group of people what if they could just view it you can do this by basically publishing your Google sheet to the web to do this you need to have your Google sheet open go up to the file drop down menu and go down to share but this time we're going to select publish to web when you do this it actually is going to create a link now you need to decide what part of your Go
ogle sheet you're going to do in this case I'm just going to do the current sheet you'll notice that it will do the entire thing or I can come in and say just the Tuesday sheet I need to decide the type of publishing I'm going to do I want to do a web page then underneath I'll see a link that gets created I can also do embed which is a little bit different I'm going to go ahead and do the link now what I can do with this link is I can actually take it and copy it if I then go up and open up anot
her browser Tab and paste that link I'll actually be able to see my sheet in a view where other people could view it very quickly and easily and see it as you can see here and it's just the one sheet in the top left hand corner it shows me the name of the again Google sheet and also the sheet that's been published this is something that I could provide to other people where they could view it and see it again it's not the same as sharing it or sharing it through email but it is a way for other p
eople to be able to view the sheet quickly when I'm done all I have to do is close the tab and I can also close the link box that it's been creating if I say stop publishing then it will turn off the link so that the link doesn't work anymore it's a great way to make things accessible to a larger group of people again try it out on your own go to file go down to share and say publish to web and see what your Google sheet looks like when you do it hey everybody thank you so much for joining us fo
r this Google Sheets course in this course we've showed you all the basics of Google Sheets from how to use the file drop down menus to create new blank spreadsheets to even how to go to Google Drive where you can store your Google Sheets and also open them up we also spent time navigating in Google Sheets adding basic formulas using the formula bar and then we also spent time actually going into Google Sheets and playing with the toolbar when it comes to formatting and using the tools to help a
ccentuate the different cells inside our spreadsheets we also spent time under the view drop down menu looking at options like freezing panes to help make it easier to view sheets and even your ability to insert rows and columns and if needed go to a columnar row and hide it in addition to that we spent time down on the sheet tabs looking at ways to move or even copy sheets in addition to even changing their sheet color all of these tools help make Google Sheets a very effective spreadsheet appl
ication that works in a web browser we also spent time under the file drop down menu going in and looking at how to share a Google sheet email it to others and even download it in different file formats like a PDF or even Microsoft Excel we also looked at how to print a Google sheet and the different tools that are available to you in the print settings box to help you make sure that what you print fits on paper the way you need it to again we'd love to have you try this out so take advantage of
the practice files and stay tuned for more Great Courses through learnit hi everyone Welcome to our Google Sheets fundamentals course part two this course is for anyone who's looking to take their knowledge of Google Sheets to the next level by learning to manage their sheets with features like sorting filtering inserting charts and even building pivot tables and pivot charts we're also going to learn Advanced functions like vlookup sum if and even average if s we're also going to learn how to
share our Google Sheets with others by using real-time collaboration we'll also look at permissions within Google Sheets so that you can control how others can collaborate with you in them hi everybody let's talk about the list format and sorting in Google Sheets now first of all I have a practice file open it's called trip sort and filter it's in with the practice files so remember go to the course description and look for the link to access any practice files that go with this course now first
of all what is a list format Excel also uses this same format for table Styles and other analytics Tools in Google Sheets a list format is where you first of all provide column headers at the top of each column these help to identify the data below the other important detail for a list format is that there are no blank rows in your data the reason blank rows are bad is they're like hitting a pothole when you're driving a car the analytics can't work so for example the sorting and filtering will
get stuck when you hit a blank row now blank cells are okay if you look at the current file that I'm in I have no entire blank rows and all my columns are the consistently same kind of information so with this list format in my mind let's go ahead and look at how to perform a simple sort in Google Sheets I'm going to come in and actually highlight the range that I'd like to sort by you don't always have to do this but I recommend it especially if your data isn't quite consistent once you get al
l the data highlighted mine's about 200 rows down then what I'm going to do is go back up and go to the data drop down menu this is where your sorting tools are located you'll notice that you have a sort sheet option and a sort range now because I have a range selected which is on purpose it helps to make my sort more specific I'm going to go with this second option you'll come in and you'll notice that I have either the ability to sort the range in column A or I can come in and go down to Advan
ced range sorting options when you really want to control your sort this is what I prefer to do in this box I'm going to come in and tell my range that it has a header row those are the column headers I mentioned earlier and you'll notice that when I do this it helps me to identify what my columns are in this case I'm going to pick last name I can pick my sort order remember there are two A to Z is ascending Z to A is descending and then I'm going to click on sort if I go up and look at my last
name column you're going to see that now it is alphabetized by last name so remember we always want you to try these things out go into Google Sheets try selecting the range and do your own sorting in Google Sheets hi I want to learn how to do a two column sort inside my Google Sheets file right now you'll see that column C my last names are sorted by last name but my problem is if I try to go in and do another sort this first level sort will be gone so how do I do a multiple level sort in Googl
e Sheets again I'm going to come in and highlight the data that I want to sort by this helps Google Sheets to focus on the area that I'll be sorting also remember I have the list format in my data I'm going to come back up to the top just so we can really see how this works once I get back up to the top of my Google sheets I'm going to come back to the data drop down menu and come to sort range because right now I have a range of cells selected not just an entire sheet then I'm going to come dow
n to Advanced range sorting options now from here I can see my first level sort but I want to go ahead and update this so that it has both the sorts I want and it's correct I'm going to check off that my data has a header row I'm going to come in and select the last name column for my first level sort in ascending order A to Z then I want to select another sort column this is add another sort column and then I want to sort by the amount of tickets and actually in this case it's the number of tic
kets sold and 8 is the order as well so there will be a first level sort and then when there's a tie in that first level it will go to the second level now to actually perform this sort I'm going to click on the sort button now what we're going to see is there are last names that are sorted that's my first level sort but then if I come in and notice after there's a tie in the first level sort then into sending order it's going to go and it will do a sort by the tickets column so as I come down I
can see that there are two sort orders going on here to help me further identify an order again my information in my Google Sheets as always we want you to try this out so open up the practice file and try doing a multi-level sort in your own Google sheet hi I want to be able to filter data in my Google Sheets file remember filtering is different than sorting when you sort you reorder the data but all the data is still there when you filter think of going to a public swimming pool we want to ge
t rid of all the stuff we don't want in that water here inside Google Sheets it's the same idea we will be hiding the data that doesn't meet the criteria that we set so how do we perform a filter well first of all make sure you have a file available to you to use I'm in the practice file called trip sort and filter feel free to use this to try out filtering as well now I'm going to come up to the toolbar this time the far right hand side of the Google Sheets toolbar you'll see what looks like a
funnel this is how you can turn on filtering at each column level when I click on this button you're going to see that the area where I have my column headers highlights this is another reason why the list format is so useful inside of Google Sheets now next to each of my columns I see a small set of course horizontal lines that are supposed to look like a filter they kind of do but this is where you go to perform filtering and sorting at the column level I love these let's try out by filtering
for example by the destination column column e I'm going to click on the small funnel and you'll notice that I get a couple of options at the top I can sort each of my columns in either ascending or descending order and the Great Value behind this is that the column header is not included so you'll notice I just did a descending level sort now when it comes to filtering I'm going to click back on the small funnel again and come down you'll notice I can filter based on formats by conditions by va
lues now in my case I have text I see a list of every possible text entry in my destinations column and they're all checked if I come down there's a select all and a clear option if I say clear it turns them all off then I could just check off the item I want to have filter or if there are too many I can start typing in the one I want in the search box it locates it for me I want to make sure there's a check mark next to it and when I click on OK it filters by that particular column now what if
I want to filter by more than one item can I do that Absolutely I'll click back on the funnel come in and I can just check off the additional things I want to filter by I could also make sure there's a sort and once I come in and click on OK you'll see that it indeed is going to filter and allow me to have more than one item here now how do I turn my filter off to turn my filter off I'm just going to go up and click on the funnel this will remove the filter and turn all my data back on so it's u
nhiding it so as always we'd love to have you try this out in your own practice file so go for it hey everybody let's create a filtered View and also a conditional filter inside Google Sheets now remember the list format is a great way to go for this so the first thing I want to do is create a filter that is for a range of dates this is a great place to use a conditional filter I'm clicking inside my Google Sheets file inside the data I'm going to go up to the toolbar to the far right hand side
and turn on the creative filter button again this gives me the ability to filter at the column level inside my list format for this I'm going to come over to the date column and click on the little again filtering button now towards the middle of the drop down menu you'll see that this gives me an option to filter by condition which is what I want now there are three kind of types of conditions that you can use you can filter based on text on dates and on values now my criteria is a date that is
after May 1st so I'm going to select date is after below that I have to actually tell it what the date is it's going to be an exact date and I'm going to come down and enter in a value or formula now just heads up dates are actually values in programs like Excel and Google Sheets so I'm going to type in my date and then I will come down and click on ok now first it may not seem like anything's happened but if I scroll down through the data you'll notice that it's only showing me dates that happ
en after May 1st now this is a filter that I will use a lot what I can do is rather than just continue redoing the filter whenever I need it is I'll save the data currently in this view to do that I'm going to go back up to the toolbar to the filtering button and click on the little arrow next to it this gives me an option to save as a filter view which is what I want to do then I'm going to come over to where I see the name of my new filtered View and what I want to do is click on that and just
rename it I'm going to call it after May 1st you can put spaces in these filtered view names all I need to do to finish renaming is hit enter now we'll see that my filtered view is turned on I'm going to turn all my filters off and the filtered view will also be turned off to do that I'm going to go back up and just click on my funnel again again you'll notice that when I do this it gives me an option to say none under my filtered views or if I just click away I can come in and I'm going to jus
t say none now we'll see that if I scroll back down the filter is still in place so I'm going to go ahead and click anywhere in the data set and click on the funnel again and it turns the filters off and notice that all the data is here now to return to my filtered view I'm going to go back up to the arrow next to my filter in the toolbar and you'll see that any filtered views you've created will show up in a list I'm going to select my filtered View and it turns it back on and I'll notice that
any of the dates that don't fit that condition of being after May 1st turn off and then again to return to the full list without the filtered view I'm going to click on the filtering button say none the filter turns off and the filtered view turns off and at any time you don't want the filtered view anymore you can click back on the arrow and you'll notice there's an option to delete all filter views but remember what we're doing is we're combining a filter with a view so that whenever we need t
o filter by that item again we don't have to go back through all the work we just turn on The View and the view will be there for us so try this out in your own Google sheet hi everybody I want to be able to create a chart inside one of my Google Sheets remember when you create a chart you're visually representing values or data stored inside your Google Sheets file it's a great way to help people understand data quickly and easily now one acronym I like to use when I teach people about creating
charts is the kiss acronym don't be offended by this but keep it simple stupid is the kiss acronym and it basically means keep your charts as simple as possible if someone can't look at your chart and understand it within just a few seconds it's probably too complicated so with that in mind let's make our own chart inside of Google Sheets there is a practice file available for this so remember if you go to the description you can open it up there's a link there this one is called charts 101. no
w the first step is to select the data you'd like to chart I always like to remind people we generally don't include titles and totals when we select our data for a chart so in this spreadsheet I am going to highlight cells A3 down to G7 now notice I'm not including A1 because it's a title and I'm not including Row 8 because it's totals Now to create my chart I'm going to come up to the insert drop down menu and come down to chart now it's automatically going to already assume the type of chart
I want notice the task pane that's opened up over on the right this is called the chart editor now I can change my chart type immediately by coming right to the very first item under the chart setup tab these are the different chart types you have line charts area charts par and Bubbles just keep scrolling down and then specialty charts now generally when you're first starting out the first five categories are going to be the best ones to start with and you'll also see at the top that based on y
our data Google Sheets will make suggestions of charts that might work well with your data if you want to change your chart type come down and pick a different chart type and you'll notice that your chart will update now how do you move this chart around on your sheet because your chart resides on top of the cells right now my chart is partially covering up my data all you do need to do to move your chart is come to the corner of it left drag it it's like a graphic now I always want to make sure
that my chart doesn't overtake my data I can also come to the corners of my chart you'll notice there are small squares on the corners and the sides you can use these to size your chart it's a great idea to make sure that your chart doesn't overtake your data another thing you can do is you can also come in and right click when you right click you'll notice you get a menu that will also help you to adjust your chart so now that we've created our first chart I want you to try it out in your own
file they're really easy to make and again they're a great way to help people understand the data that's inside your Google sheet hey everyone I'm ready to edit this chart so the first thing I need to do is click on my chart so that it's selected when you select your chart it makes Google Sheets know that you're interested in your chart also if you come in and just double click on the inside of the chart you'll notice that the chart editor task pane opens up now again the first side of this is g
oing to allow you to set your chart up by picking chart type coming in and selecting for example whether you want the stack to be at 100 or standard but another important thing that you can also do here is take the data that's in the legend and switch it with the data that's on your category or x-axis so let's just learn a little bit about chart terminologies for a minute I'm going to go ahead and take my chart and drag it over so it's a little bit more front and center and make it a little bit
bigger for this your Legend is of course the area where you understand what your different data bars in the chart or the data series are usually the x-axis is going to be the horizontal axes on the bottom the y-axis or value axes will run along either the left or right side now you'll notice that at the bottom of the setup you can actually come in and switch between what's on your Legend and your x-axis by checking this box off you'll notice that they switch back and forth also there is a area w
here I can come in and I can also decide if I want for example different column labels to be used in my data set when you really want to customize though in the chart editor task pane there is a customize area this is where you can take the different areas of your chart and change the way they look chart Style is the overall look of your chart you'll notice you can do things like make the chart look 3D you can also set a different background area for your chart area you can also come in for exam
ple and set a different color for the border of the chart just so that's easier to recognize you can also come in and change the fonts that that's being used in titles and legends in addition to that you're also going to have an area where you can update the chart axes and include a chart title now I currently have a chart title but for example maybe I'd like to put it in bold and maybe I'd like to change the color to Black to make it stick out more so with each of these areas you go in and you
customize the portion of the chart you'd like to edit another important one to be familiar with is the chart Legend This Again represents what's in your data bars notice here I can actually change where the chart Legend goes right now my Legend is currently on the right I'd like to move it to the bottom when I do this the great thing I guess I moved it to the top we'll move it to the bottom instead the chart adjusts to make room for the Legend So with each of these areas you're able to go in and
further customize the look of your chart you also have an area where you can come in and customize this series these will be the different items they're showing inside your chart and notice you can additionally click on a given area it will become highlighted I've just selected the wages data bars or data series I can come in and actually update the color of those just by selecting them when they're updated in the chart they'll also be updated inside my chart Legend so we'd love to have you try
this out go in and after you've created your try going in and customizing and there is a practice file for this called charts 201 so give it a try hey everybody we want to be able to remove items from our chart and also more easily move them around pretty again easy to do you just have to know where to click I'm in a practice file called charts 301 remember if you go to the description you can find a link and go to all the practice files there I'm going to go ahead and make my chart the focus s
o I'm going to click on it and kind of move it around and size it a little bit bigger so we can see it now first of all how do I remove something from the chart I don't want there for example let's say I don't want to see the wages in my chart well I can't just click on them and hit delete because if I try to click on them which I can do they'll get highlighted but if I hit my delete key I can't directly delete a data series out of my chart but what I can do is remove it so I've clicked on my ch
art so that the chart editor task pane opens up and I'm going to go to the setup tab when I come down I'll see that each of the sets of data bars are represented by S Series to remove a specific series I'm going to come to that series just call wages in this case click on the ellipses at the side and say remove and you'll see what it does is it just removes those data bars out so now I see my chart without the wages I'm going to undo that because now I want to just talk briefly about sizing elem
ents on the chart and removing something you don't want let's say for example I've decided I do not want a legend I can click on the legend you'll see it get selected and then if I hit delete on my keyboard it will remove it if I ever want to bring it back I'd go back over to the Chart editor task pane find the Legends section which right now says None and I just move it back by clicking on the direction or placement of the legend I can also come to the legend and just left drag it wherever I wa
nt it to go on the chart and it will actually allow me to move different elements as well if I'd like to size elements I can click on a specific part of the chart come to it and I can also size it as well so the charts are very adaptable it's just a little bit tricky sometimes when you're trying to click on something and move it in conjunction to the rest of the chart so as always we'd love to have you try this out on your own open up the practice file try turning some things on and off also rem
ove some of the series out of the chart remember the undo button will bring them back as long as you haven't yet gotten rid of all your undo's but try this out so you can see how flexible these charts are to editing hi everyone I want to explore a different type of chart it's an in-cell chart that's great for showing Trends on columns or rows of data it's called a sparkling they're small but they're great when you want to see something that's right in line with the data that it's tracking now sp
arklines are not something you can get by going to the insert drop down menu like we do for normal charts they're actually created via a formula so I've opened up a spreadsheet called try sparklines it's in the practice files I'd love to have you use this as you follow along with me I'm clicking in the cell where I'd like my sparkline to go and then I'm actually going to create a function that will create the sparkline the first step is to start with your equal sign and then type in sparkling no
w of course Google Sheets get smart and gives you the spelled out sparkline I could also be typing this up in my formula bar if I wanted to the next step is to include the range of cells that you'll be trending with your sparkling you can either type the range in or just highlight it and you just want to include the values in this case when you're done you can hit enter you don't need the closing parentheses and you'll see your little in cell chart or sparkline and up in the formula bar we can s
ee the function that created it now if you'd like to get a little bit fancier you can include things like the type of chart sparklines can be column charts and also line charts the default is a line chart as you see here so I'm now going to come in and click in cell f8 and I'd like to create a column sparkling for this one I'm going to start just like I did before by typing the equal sign typing in the name of my function selecting it and then of course highlighting my range which is A8 through
E8 after I do that I want to continue I'm going to hit a comma because I need to put in some options inside of squiggle brackets as I call them I'm going to type in quotes chart type this lets Google Sheets know that I want to specify the type of chart the sparkling will be then I'm going to do a comma and in quotes again I'm going to type in the type of chart I want which is a column chart I need to enclose this with a closing squiggle and then of course my closing bracket and then I'll hit ent
er and we'll see that for this one I have both positive and negatives but it is a column chart if you ever want to get rid of a sparkline just click on it hit delete and it will take it out these are something that are pretty easy to add and also pretty easy to take out and there's more that you can add to them but start by just creating a few on your own and again it's a function so a little bit different way to create a chart try it out hey everybody I want to show you a simple way to help you
read down through rows of data and right now I have a practice file called customer info open feel free to use this to try this out it's called alternating row color another thing to notice about my data is it does include header rows at the top of each column so I know what the data is below and no blank rows but blank rows will still work with this activity all we're going to do is click in any cell inside the data and then go up to the format drop down menu and come down to alternating color
s this will select my data I do have a header row notice how that's checked off if I had a bottom row that was a footer row I could also check it off then I'm going to select from one of the pre-built Styles I could also come in and create my own if I wanted to by clicking here but I can also customize the colors just by going into each one when I'm finished I'll click on done and you'll see that it's highlighted my header row in a darker shade and then I have all alternating row color based on
what I've selected it's just an easy simple way to format your data and also make it visually easier to read down row by row so try it out howdy everybody we're ready to look at one of my very favorite topics in Google Sheets creating pivot tables now why pivot tables if I were to try to take this data set and say that my boss came to me and said I need to know how many tickets were sold at each of the destinations and then at each of the offices how would you go about doing that well you could
sort you could filter you could add some sum functions but it would really be a pain in the neck the great news about a pivot table is it allows you to take a really thick long data set like this one and quickly sort and filter it and also analyze it and calculate it with just a few clicks now if you set up things again pivot tables work best with that list format we mentioned at the very beginning of the course so I've got column headers at the top and no entire blank rows this date is only abo
ut 200 rows down so it's not very big but again pivot tables can be used to analyze really large data sets with that in mind let's make a pivot table I'm going to click anywhere inside the data and then we're going to go up to the insert drop down menu and come down to pivot table it's right below chart now the first step is to pick the range that I'll be pivoting it's already selected my range because I don't have any entire blank rows but this button on the right would allow me to drag over th
e correct range if I needed to also I always suggest the best practice of placing new pivot tables on a new sheet that way you don't accidentally overwrite any of your existing data we're ready to go let's click on the create button once we click on create you'll see that you get a new blank sheet again my original data is still back on sheet 1 and my new pivot table is called pivot table one or two depending on how many you've built now we need to take from our column headers and create our piv
ot table a pivot table is not about all your data it's about focusing on specific pieces of data so I'm going to start by taking the office field and dropping it in the rows area I'm going to bring it under here and click on add another way to do this is to actually click on the part of your pivot table by clicking on the add button and then selecting the field you'd like included again with values I suggest using value Fields so I'm going to pick my totals field and you'll see it's now doing a
sum based on office I'd also like to see where this is intersected by the destination that was sold so I'm going to take destination and drop it under columns now you'll see a lot of blanks when I do this this is important sometimes when you first start using pivot tables people see these blanks and think oh something's wrong but it it isn't this is telling me an important piece of information when something didn't happen now additionally I can also filter my entire pivot table by taking a filte
r down or a field down to this filters area we'll see that the filter will be available to me right here when I click on it I can actually perform a filter and it will filter my entire pivot table in place when I'm done I can turn it off just by coming back clearing it saying select all and it turns it off now to edit my pivot table is also really easy let's say instead of the total field I'd like to see the number of tickets sold I can actually remove the total build by clicking on this X and I
can bring down a different field under my values area now I've seen the number of tickets that were sold at each office for each destination I could also actually remove the destination and we'll see that my pivot table will totally adjust now have any of these changes impacted the data on sheet1 let's check it out I'm going back to my original sheet nothing has happened the beauty of a pivot table is that you can analyze specific Columns of data in your pivot table but your Source data never a
djusts one important thing to remember about pivot tables is that you have to make sure that your Source did on sheet 1 is refresh with your pivot table but the great news about Google Sheets is that they automatically refresh with each other so try out a pivot table of your own inside the practice file it's called customer pivot you'll see how amazing and quick these are to analyze your data all right so we've learned how to make a pivot table now we're ready to make a pivot chart now one thing
I want to mention is in this file that I've opened in Google Sheets it's called customer pivot and chart you can use it as well when you want to make a pivot chart this data is too thick for me to chart this is one of the reasons that pivot charts are so important they allow you to basically focus on specific pieces of data in a big data set and chart those I have a pivot table in this data set on the second sheet so I'm going to click on pivot one and it's going to take me to my pivot table le
t's zoom in on it just a little bit once I've done that what I want you to do is come in and I want to create a pivot chart so I've clicked in my pivot table I'm now going to go up to the insert drop down menu and come to chart just like we would to create a normal chart but because it's in a pivot table it creates a pivot chart now for the most part pivot charts are going to operate a lot like a normal chart you'll notice you can right click on the chart and come in and get lots of options to e
dit the chart but Additionally you also have the chart editor task pane that opens up at the top of the chart editor task pane you can actually change to different types of charts so there are additional types Beyond just a pie chart you have again line charts area charts column charts some of the specialty charts may not work quite as well so just again be sure that you can kiss your chart right you want to make sure that you're keeping it simple so it's easy to understand after you've made the
change to the chart type don't forget that you can go to the customize ribbon to come in for example and update things like the chart border also you can come in and change the chart fonts right and even the background color of your chart so all the same things that you use to edit your chart in a normal chart can be used here but the beauty of this is that we've been able to take something like this focus it with a pivot table and create a pivot chart now the final thing I want you to remember
about the pivot chart is that it's based on the pivot table so let's say for example that I come in and I have a filter on my Pivot chart right now it's showing all items but let's say for example that I clear and only show the first for example five days of data when I do that notice that my Pivot chart also will update based on what's In My pivot table so the pivot chart and the pivot table are always based on each other and when you edit your pivot table your pivot chart will follow soon but
try making a pivot table and then create a chart and see how easy it is to take a very thick data set and really drill into it and create a chart based on that welcome back I want to show you another way to refer to cells other than their cell name now we know that whenever you click in any cell like I just clicked in cell A2 directly to the left of the formula bar in the name box we'll see the cell reference but sometimes I might want to name an individual cell like this one or an entire group
of cells a different name this can be useful when you're trying to do formulas for example or even just select a certain group of cells now right now this group of cells is known by the sheet that they're on which is the expenses sheet and then the range B4 through B7 a named range will really simplify this naming construct with just a name so let's go ahead and create a named range remember there's a practice file for this called name range in the practice files so if you go to the description
of the course you can access those practice files and I suggest it for this now to create a named range I've highlighted my cells in this case B4 through b seven then I'm going to come up to the data drop down and come down to named ranges it's directly above named functions you'll see over on the right hand side that my named ranges task pane opens up I currently don't have any named ranges in this spreadsheet but I'm going to create one and right now you'll see that it's putting in a default
named range one name for my current cells it also shows me the references the sheet name and then of course B4 through B7 when you create a named range you can't put spaces in the name so if you need spaces use hyphens or underscores and you can't use a current cell range so I couldn't for example take this range of cells and call it B1 because that's already a cell reference I'm just going to give it the simple name of January and then I'll click on done I'll see my named range here in the name
d ranges list this pencil next to it would allow me to edit that range but these will save with my workbook and always be here I'd like to continue this process I I'm now going to come in and highlight cells C4 through C7 and come back up and click on add a range in the name ranges task Pane and I bet you can't guess what I'm going to call this yeah this is going to be my February named range I'm just going to keep it February to keep it simple and finally I'm going to do the same thing for D4 t
hrough D7 and add a range there so when I'm done I'll have three named ranges one called Feb one called Jan and one called March I'd love to have you go into your spreadsheet and try this out final thing we want to do is actually make these into a formula rather than using cell ranges and formulas like we've done previous in this course you can use your named range to replace the range of cells that you'd normally use let's try this out I'm going to close my named ranges task Pane and go to cell
B8 where I want to put in my function remember you start with an equal sign type in the function name that you want I'm going to double click on it now I know that I could use cell references here but instead I'd like to use my named range as I start typing it in Google Sheets recognizes that I want to use a named range here notice the icon next to my January make range this is the icon that represents a named range as opposed to a cell reference I'm going to click on it I need to use my closin
g parentheses and hit enter and it finishes the formula for me I can do the same thing in cell C8 and d8 again I hit an equal sign start my formula in this case I don't want to use the named range that it's suggesting so I'll just bypass that then I'll start typing in the name of my named range you'll see it bring up the named range for me double click on it and it finishes it so why is this a good idea sometimes when you're working with groups of people who may not be so familiar with formulas
this can help simplify and help them understand what the formula is doing also when you do formulas that require absolute references these named ranges are always absolute and remember if you need to go back and edit them all you do is go up to the data drop down menu come to named ranges and your named ranges will be in this list one final warning if you accidentally delete one of these named ranges I'm just going to show you what happens so if I come in and click on the trash can next to one o
f my named ranges you'll notice that your formula if you delete it will continue to work but what you're going to see right here is in the formula bar you're going to get a number sign reference error message because the computer is continuing to do it but the actual formula itself cannot recognize the correct range so it will continue to show you the right total but if you need to edit this you'd have to fix your formula so that's one thing to be careful about with named range hey everyone I wa
nt to show you one of my favorite functions it's called an IF function now if functions are very common and they're based on the idea that a formula can have a true or false so you start with what's called a logical test is this greater than this is this equal to this then you have what to do if that's true and then if it's not true you have a false portion so if functions in their simplest form have three different parts The Logical test and the what to do if that's true or what to do if that's
false they're very popular now here in my practice file that's called if functions and remember we'd love to have you try this out so find it with the practice files I need to calculate if my sales reps are going to get a bonus if they've sold over seven million dollars of product they get a bonus but if they haven't they don't get anything but I'd like to put a text message in the formula that will say no bonus so to start I'm clicking in cell O3 and I'm going to create my first IF function so
I type an equal sign and the word if then of course I'm going to double click now the first part of this function is going to be my logical test my logical test is was there sales total that's in N3 greater than or equal to which I'm going to of course type on my keyboard 7 million that's me counting zeros then I'm going to do a comma Now if that's true then I again want to take their sales total and multiply it by five percent that will be their commission rate now what if they don't sell more
than seven million dollars in product they haven't met The Logical test so now we have to say what to do if it's false well I could just put in a zero but it's easier for me to understand no bonus so to enter a text string I'm going to do a quote and then type in the text message which is no space bonus all contained within quotes so you'll see here that I have the name of the function The Logical test is N3 greater than or equal to 7 million then I have what to do if that's true take N3 and mu
ltiply it by five percent if it's false inside quotes because it's a text string I will put no bonus each of the portions of the functions are separated by commas when I'm done I'll hit enter now based on that you're going to see right here that it's already telling me well it looks like this sales person is getting a four hundred thousand dollar bonus now this is a function that you can drag down using the autofill handle which I'm going to do and you'll see again when they meet the true portio
n of the formula they get the bonus when they are under seven million dollars in product then they don't get the bonus and this text messages in a lot of ways easier for me to understand and so again it's a great formula because you can have a true and a false so try it out welcome back I want to show you how to do a nested function now I'm in a practice file called nested functions so please feel free to open it up and use it for this activity in this spreadsheet I'm trying to figure out how to
do my bonus schedule I've started in column e by using an if statement so if we open this up we'll notice that based on their order total if it's greater than 2 000 they'll get a five percent bonus if not no bonus for them we can see it's working fine but I've also noticed right here that the average of all my order totals is 2100. so I'm wondering if I could just use that to calculate whether someone gets a bonus and I want it to just say yes or no a different approach so what I'm going to do
is I'm going to click in cell F1 and I'm going to start by my first function which will be the if statement so we're going to do an equal sign and type the word if and then of course an opening parenthesis now right here I need my logical test but this is where I'm actually going to start getting ready to do my second function so we know that it's based on the order total which is D2 and it needs to be greater than the average of all the ordered totals well I'm going to actually begin another fu
nction I'm going to type the word average notice that it already will give me an opening parenthesis if I'm using those helps that Google Sheets provides now how do I reference this well I'm going to highlight the range of cells that need to be averaged D2 through d10 this does need to be included inside parentheses because it's the innermost function now I need to finish out my IF function so I'm going to type a comma Now if that's true and they are going to get the bonus I'm going to type the
word yes because again it's a text statement so it needs to be included in quotes now if that's false if they didn't order a total over the average I'm going to type the word no so now I'm finishing out my second formula which is the if statement the other thing that's really important is that you have the same number of parentheses going in that you end with that same number of parentheses I have two parentheses going in and I have two parentheses going out when I'm done I'll hit enter now we'l
l notice that based on that I guess this particular person did not make the average so I'm going to go ahead and drag this function down and then we'll just look at it briefly you'll notice that if again I look at the function the two people that met the average total are right here and it says yes for both of them so if we look at the formula we're going to see again if is the first function then I have the beginning of the logical test but then part of The Logical test is the second nested fun
ction section followed by parentheses and then my logical test which is yes for true and no for false so this is the beginnings of what nested functions look like these can get really complicated but they're a really powerful way to do multiple calculations all in one formula howdy I want to show you one of my favorite Google Sheets functions it's called vlookup it stands for vertical lookup now let me explain this function to you it has five primary arguments the goal of vlookup is to allow you
to look a value up out of a range based on something to the left so for example in my practice file which is called vlookup feel free to use it to try this out you're going to see that I have customer numbers in column A what I'd like to do is to be able to come in and type in a customer number here in column M cell M2 and then I'd like it to tell me the yearly cost of tickets from the yearly cost column so the goal is that the vlookup will actually come into the table at the left find customer
number 12 count over nine columns and return exactly what's in the same Row in the ninth column a vertical look up through columns let's try it out I'm going to put my formula in cell N2 feel free to click there and try it out as well in the practice file you always start with an equal sign like we always have and then the word vlookup again notice how Google Sheets always has lots of helps as you go through you'll have an opening parenthesis now the first part of this function is going to be c
alled what's called your search key which is over here in cell M3 so I'm going to click there or an M2 I should say and it enters that my lookup value or my search key will be the customer number that I've typed to the left next I'm going to put in a comma because I now need my range of cells that I'll be looking through so I'm going to come in and highlight all the data in the table at the left it's going to be cells A1 all the way down to j23 now the third portion of my function function is go
ing to be the index column this has to be a positive integer or number so I can't put in I which is the column the yearly cost is in I have to just count over so I can provide a positive number so one two three four five six seven eight nine the value I want returned or the information is in column nine so that will be my column index number now there's still a fourth argument that's very important with vlookup is am I trying to find an exact match or an approximate match in my case it needs to
be an exact match because this is a client number it's not something that will be repeated again I don't have two customers with the number 12. so if it's an exact match you need to enter the word false if it was an approximate match you could actually just type in true or not even put it into the formula but if you need an exact match you must enter the faults very important so again what is my function doing if we look at it well it's coming in it's going to the left hand column of the range a
nd finding my customer number then it's counting over nine columns and returning exactly what's on the same row now could I type in a different customer number like 15 for example and would that work let's check it out here's 15 and if I count over 9 columns the yearly cost is going to be returned by my vlookup these are very popular and very common so try them out because chances are you will run into these hi welcome back in this lesson we're going to combine two functions that we've already u
sed to create some if and average if functions now just as the name implies these functions will only sum something if certain criteria is met or only average something if certain criteria is met I'm in a practice file called sum if and average if feel free to use it so you can follow along I'm going to start with the Sumit function so I'm clicked right here in cell K2 to start this function you do an equal sign and type the word sum if all one word now this particular function has three basic a
rguments the first thing we need is the range of cells that our criteria is based on for me it's the destination notice in column e that I've sorted the destinations it makes it easier to check and see if the function is really working so I want to select all those destinations but not include cell E1 because it's the column heading to do this I love the keyboard shortcut Ctrl shift down arrow notice it selects all my destinations down to the bottom of the spreadsheet but nothing underneath of t
hat now the next thing I need to do is a common I now need my criteria this is Cancun because it's text I need to include it in quotes if it was a value I wouldn't need to do that or a date the third thing I need is my sum range what column am I going to be summing in this case it's everything below H1 so I'm going to click in H2 and do the same keyboard shortcut Ctrl shift down arrow now of course I need to enclose the whole thing in parentheses I'm going to hit enter if I scroll up a little bi
t I'll see here that my total is 29.72 now here's what you can do I can actually come into my spreadsheet and highlight those cells e13 to E30 E21 because that's the range of my Cancun destinations and then we're going to come over and highlight the total column as well for all of Cancun and then just highlight those values if I come down to the bottom right hand corner where Google Sheets does its Auto calculations I'm going to see that indeed the sum of all the Cancun destinations is 29.52 so
this is letting me see that the function worked let's try it with an average this time we want to find the average of the total of tickets sold to Boston very similar formula I'm going to click in cell N2 hit an equal sign and type the word average if and again it does the same thing but this time we're going to be averaging based on criteria the next thing I need is my criteria range again it's going to be based on destination so I'm going to select E2 through e63. hit a comma Now I need my cri
teria in quotes I'll type Boston making sure that I spell it correctly followed by a comma now what am I averaging this time it's actually the same column column h under the total so I'll do Ctrl shift down arrow to get the range of H2 through h63 then I'll hit enter because there's only one parentheses I actually don't need to put the ending one on now I can see the total it's giving me for the average again I'm going to come into the spreadsheet and highlight all my Boston destinations and go
over to include the total column then if I just highlight the Boston destinations here you'll see in the bottom right hand corner it shows me that it's Auto calculating a sum but if I click on that I can also have it do an average now down here it rounds which is 531 but up here in the spreadsheet I can see that if I rounded this it would be 531. so this is a fantastic way to only sum or average based on if certain criteria are met all right let's try out something a little bit trickier this is
a function that is combining sum and if but gives you the ability to have two different criteria it's called sum if s now there is a practice file for this in the practice files folder so click on the link in the description it's called sum if s and average if s I'm going to be on the sum if s sheet for this so I want to come in and click in cell B5 let me describe this spreadsheet to you briefly you're going to see that it's about grocery delivery totals one column are the items that have been
purchased the second one is the amount and the third is going to be the status whether they're delivered in transit or canceled I need to come in and anywhere where toilet people has been delivered I need to Total the amount that's been purchased this is a good example of using a sum IF function because I have two different criteria the item has to be toilet paper and it has to be delivered I'm going to click in cell B5 to create my Summit function I'm going to hit an equal sign and then this is
all one word sum if s now remember that Google Sheets does a great job with giving you suggestions so don't be afraid to use them the first thing it's telling me here is I need a sum range well the range of cells I'm summing are going to be B8 down to B18 now I'm going to hit a comma the next thing I need is my first criteria range this is the item type so I'm going to highlight B8 in this case A8 to 818 and then you're going to see that it's going to ask me for a third thing which is the actua
l criteria in this case it's toilet paper and I have this listed in cell B3 so I'm going to click up there so that's my first collection of criteria now I need to set my second one which is based on the status so again I'm going to highlight column C8 through c18 do a comma and again the criteria for this is whether it's been delivered so I'm going to click in cell in this case B5 and then I need to enclose the entire thing in a parenthesis and I'm going to hit enter now what I see here is that
when the two criteria are met that it's toilet paper and it's been delivered it adds those two amounts up which is 200. now toilet paper is also listed here but notice that in that case it's canceled or it's in transit so therefore it only sums where it equals the two criteria that it's toilet paper and it's been delivered super function hey everybody I want to show you another function that combines the ability to have two criteria before something can be averaged this is called the average if
s function it's very similar to the sum if s but it does an average rather than a sum I'm also in the practice file called sum if and average if and I'm on the second sheet called average if s now what I have in this sheet are a list of products quantities sold and then a sales rep in column C and the actual product name in column B here's my scenario I only want the products to be averaged if Tom was the salesperson and they were apples pretty simple but there is a function that will do all tha
t for me so I'm going to come down to cell C9 and type in equal sign and then begin my function name which is average if s all one word and don't forget the assets really important then the next thing I need to do is get where what I'm averaging in this case it's going to be A5 to A8 that's my average range the next thing I need is my first criteria now this is If the product was Apples so first of all I need to highlight my products which are B2 to B8 then do a comma and then in parentheses I n
eed to type what the criteria is which is Apples the reason it's in quotes is because it's text if it was just a value I wouldn't need to put it in quotes now I need to do my second set of criteria and remember that Google Sheets has all these builts and helps to let you know what portion of the function you're on I'm on my second criteria range now in this case it's the sales reps so I'm going to do C2 to C8 hit a comma and now I'm at the part of the function where I need my second criteria wit
hin that range in this case it's if the sales rep was Tom so there are two criteria is the product apples was Tom the sales rep than average it I'm going to hit enter now we're going to see right here the average based on that is 4.5 let's check it out here I can see that Tom sold apples for a total of five and four if we average that it would come to 4.5 so again it's a simple function but because it can be based on criteria you can allow it to let you only average based on two different items
hey everybody let's talk about a way to make sure that when there might be an error that occurs inside one of your Google Sheets functions or formulas that you can actually control the error message that comes back now we've probably seen this in Google Sheets if you haven't it's common for there to be error messages like you're seeing in the spreadsheet on my screen right now you can also see other ones they usually start with again a hashtag symbol followed by the particular error message that
's being displayed this one is happening because I'm dividing by zero which we know equals zero but again Google Sheets thinks I'm making a mistake this is the practice file called if error and I want to show you how you can control these error messages by actually putting in your own if error when they occur now if error will return the first argument if it is not an error value otherwise it Returns the second one so you're going to see right here that again I'm getting the divide by zero error
message because I'm taking A3 and dividing it by B3 and we know that when we divide something by an empty cell we get zero so hence I get this divide by zero error but instead what I'd like to do is edit this and put in the if error instead so that if this does happen it just shows a zero to do this I'm going to go to my first Formula and actually edit it even though it's working and then drag it down and it will fix my divide by zero error message so I'm going up to the first Formula in the fo
rmula bar and in front of the equal sign I'm going to type if error then I'm going to enclose it in parentheses and again we're going to see right here if it's divided by zero then I'm going to put a comma rather than doing the error message just put in a zero so if there is an error then put in the zero I'll hit enter now the formula works fine so there is no error message involved but I'm going to copy the formula down notice now instead of the again divide by zero error message I just see a z
ero because that might be exactly what I want it to do there are many other use cases for if error this is just a simple example of a way to override those built-in formula error messages with something that you want in this case a zero because that's what it should be hi everyone I want to show you how you can use functions to reshape your data so I'm right now in a practice file called left and right and we're actually going to use both of those functions to pull specific pieces of data out of
a column I'm going to come into column A and insert a new column by right clicking on the left hand side of that and I'm going to call this new column a month now notice that my months are only January through June so they're only one character in the short date format and there are date time functions by the way that do this but I want to show you how you can also use the left function to accomplish a similar thing I'm going to start my function in cell B2 by hitting an equal sign and then the
word left then I'm going to do an opening parenthesis now the first thing I need to do is tell it what cell it's working with in this case it's A2 and I'm going to do a comma and tell it how many characters from the left are returned I only need one because it's the month then I'll hit enter and you'll see that for each month and my auto fill actually comes up by the way if this doesn't happen you can always come in and just use your fill handle to drag it down it's returning the first thing at
the left hand side of each of my cells now what if there's something on the far right for example it's not uncommon to have for example numbers combined with information in cells right here you're seeing my office name column column e but it also actually includes the office number location at the end and I need that so I'm going to insert a new column to the left of column s by right-clicking then I'm just going to call office number and then I'm going to use What's called the write function t
o get the last four characters off the right hand side of a cell so again I start with an equal sign I'm clicked in cell F2 and I'm going to type in the name of the function right I need to start with the cell that I'm going to be collecting the characters from so I click in cell E2 in this case followed by a comma and then the number of characters in from the right hand side of the cell that I will be again returning which is 4. I could do a closing parenthesis but as you know at this point we
don't need it because there's only one set I'll hit enter now again the autofill comes up and if I take its suggestion it's now pulling the last four characters out of each of the cells to the left and putting them into my office number column for me feel free to use these formulas to help you reconfigure data in spreadsheets so you're not spending time having to type it in hi everyone let's look at how you can rework data in your spreadsheets by combining two columns of data together using an a
wesome function called concatenate there's a practice file for this called concatenate and this function can save you a tremendous amount of time here's my goal column B has the first names column C has the last names I want them to be together in one column so the first step is to right click on column header D and insert a new column to the left this column I'm just going to type as full name because that's what I want here in cell D2 I'll create my first concatenation function I'm going to hi
t the equal sign to start my function and then start typing in the word concamate it's a little tricky to spell so make sure you pick the correct function name if you don't know how to spell it the first thing I need to do is select the first cell that I will be bringing together which in this case is cell B2 then I'm going to do a comma Now the thing that's tricky here is I need a space between my first text string and my second the formula does not assume that space so in quotes I'm actually g
oing to put a space to represent that before the first text string and the second text string I need a space between them then I need a comma and my second cell which is C2 now this should all be enclosed in parentheses and I'll hit enter now my auto fill comes on which is mighty convenient if it doesn't remember this is a Formula so if you click on the cell with the formula get your Crosshair and drag down it auto fills it in let's just look at the function again really briefly concatenate then
take the first cell comma if you need a space between the text strings you have to allocate that in quotes and then the second cell and voila it combines them can you do more than one column absolutely great way to save yourself having to retype everything the long way hi everybody I'm in Google Sheets and I want to be able to share a file with someone who also uses Google the great thing about Google Sheets is because all your files saved to Google drive automatically it's really easy to share
them so the file I'm in right now is called practice share feel free to use it to try this out you will need someone that you know that you can try to share the file with to test drive to share the file I'm going to go to the top right hand corner of my Alton Google Sheets and click on the share button the share file box opens up the first thing I need to do is either type in the email address or select from an existing group of people that I've already shared the file with you could also paste
in the email address and I've already shared with this person before so I select their name over on the right I need to select the type of sharing that I'm going to do viewer means the file will be shared in read-only mode commenter means they can leave comments on the file but not actually change it an editor gives them full editing rights I can also include a message and then I'm going to click on send now the file's been shared after a file's been shared it will tell me the access has been u
pdated if I ever need to go back into the file and see what's been shared with all I need to do is go to the top right hand corner click on the share button it'll open up and show me currently that I'm the owner and whoever I've shared it with will be right here in the list and again if I ever need to edit those sharing rights notice I can come right here change the type of sharing that I'm doing or remove access to the sharing try sharing your files through Google Sheets and Google drive it's r
eally easy foreign hi everyone I want you to actually see what real-time collaboration looks like inside Google Sheets this means that you've shared a file with someone and they are also in Google Sheets in the file with you I'm currently in the practice share practice file I would need to have shared this file with someone and have them open it up at the same time they will receive a link in their inbox and they'll see the shared Google Sheets file in that link all they need to do is click on t
he link and Google Sheets will open and the file will open now how do I know that someone else is in this file well I'm going to see that there are two active cells so the person I'm playing right now is here in cell A1 but my co-worker is right here in cell in this case in column D now if this co-worker comes in and starts making changes in my spreadsheet I'll see their changes so for example I'm going to have them make a change down in column or in this case row 15. notice that as that change
happens I see it real time and if for example they add another particular comment I'll see it as they type now do we have to worry about saving things we don't because everything saves as we go the other way that I can know who exactly is in the Google sheet with me is to go to the top right hand corner I'll see my co-worker's name in a small circle or their initial and then I can also see myself as well there's a chat pane where we could come in and add chats to the file that we're working in b
ut everything saves and if I were to close the Google sheep my person that I've shared it with could continue to work in the file and remember anytime you want to stop this real-time collaboration and file sharing you can always go back to the share button and manage the rights that you've provided to the file from this menu but it's a fantastic way to work and it works in all the Google Suite so not only Google Sheets but also some of the other Google applications so try it out hey everybody ha
ve you ever wanted to have additional functionality inside Google Sheets well you can the great thing about Google is it's open source so it means that there are a lot of other applications that you can bring into your Google platforms to really maximize your experience from any Google Sheets file if you go up to the drop down menus and click on extensions you're going to see the add-ons button this is going to allow you to access the add-ons that are available through Google Sheets and this is
a list that continues to grow I'm going to come in and select get add-ons it takes me into the Google workplace marketplace now some of these cost and some of them are free but what you can do is come in and notice under price you can say free of charge if you just want to see free add-ons in addition when you find one that you'd like to try out you click on it there will be an install option this will then ask you to allow you to add it to your Google Sheets experience you install the applicati
on you also have to make sure that you allow it for your correct Google account once it's installed you'll see that it'll be available to you to ever manage add-ons that you've added you can come to the extensions menu and come down to I'll now see the different extensions I just added right here are add-ons but if I need to manage my add-ons I can go to add-ons then manage add-ons and I'll see them in this list if I'd ever like to uninstall something I've added I click on the dots and say unins
tall and it will remove it from my Google Sheets experience but this is an area where you can explore tons of great additional tools that will really make your experience inside Google Sheets more useful and easy feel free to check out the add-ons and again it's a list that continues to grow and change as more tools are created so definitely try it out hi everybody let's say that you want to be able to create your own custom content from Google Sheets or in Google Sheets to do this you need to a
ccess an area called Google Apps scripts now Google app scripts is an addition to your Google Sheets what it does is it provides a rapid application development platform that lets you create your own applications that again integrate with the rest of the Google workspace you can write your code in modern JavaScript and have access to libraries that allow you to get access to Great applications that work within Gmail calendar drive and even Google Sheets now where do you go to access this area yo
u're going to come into extensions in the drop down menus and go to apps script this will open up a separate window again where you can actually create your own custom modules of JavaScript code that could either add customized apps to your Google Sheets application or to other parts of the Google workspace now if you're not quite sure what these look like or what they could do for you I'm going to close the tab that again my app scripts project has opened in and go back to Google Sheets a good
way to just explore possibilities here is to go to extensions again come down to app sheet and you'll see that there are available samples that you can view that have been created by others this will actually take you into the app templates and you can see here that you can create for example a kanban board or a workspace booking app you can see here that they'll give you opportunities to explore the app or even to copy it and make it into your own these are considered a low code no code tool wh
ere you don't really have to know JavaScript but again this is an area where I do recommend learning some of the code because when you start customizing the app you can see that it's going to get a little bit tricky if you don't have knowledge so this is definitely an area that will take a tremendous amount of time to skill up on but it's really exciting that Google's opened their applications and their platform to allow you to bring in custom content so definitely think of it as an opportunity
to upskill and really learn some powerful tools to help you customize your Google Sheets experience hey everyone thank you so much for joining us on our part two of Google Sheets learning experience we've done any everything from sorting and filtering to creating pivot charts and pivot tables we've also explored some Advanced functions like vlookup and if statements and even some if an average if don't forget real-time collaboration inside of Google Sheets and even how you can further extend wha
t Google Sheets can do with add-ons and even Google scripts now as always please like And subscribe to these videos and sign up and attend more of the learn it anytime video courses they're a great way to upskill and expand your workplace opportunity thanks for watching to earn certificates and watch our courses without ads check out learnitanytime.com [Music]

Related articles

Comments