Google Sheets 2022 Google Workspace Spreadsheet Help Tips

1. 0 Sheets Course Introduction: Google Sheets is one of the productivity applications included within the Google workspace. My name is Lawrence. I'm going to be your instructor for this course. And I'm going to be walking you through all of the amazing things that you can do with Google Sheets as well as I am a Google Developer Expert, I'm gonna be happy to share my knowledge of Google sheets with you. This course. Google Sheets is a spreadsheet app which has powerful and easy to use. It's also got a familiar layouts so you can get started quickly. In the upcoming lessons, we're gonna be showing you how you can get started with Google Sheets. You can start using sheets, how you can collaborate and share the benefits of a web-based workspace, sharing sheets with others, Saving your sheets data best practices in order to recover the different versions of the content within the sheets, sheets compared to Excel google Sheets with other file formats, how you can create, export and import data into sheets, editing, updating content, different tips and helpful ideas for using Google Sheets, I can update self formats, apply filters for different views, protecting sheet data, ranges and sheets, set-up named ranges, inserting into your Google Sheets, different types of content, charts, images, as well as using the comments and notes features. Some of the new features such as Slicer, exploring the different sheet data options, how you can use the Tools menu and the tools options add-ons and become more productive with Google Sheets, functions provide you a powerful way to interact with the data of sheets. We're gonna be covering the most commonly used functions, the math functions, the operator function, how you can select cells, relative and absolute cell content, as well as helpful sheet functions using the date text functions, filter functions, info functions, lookup functions, math functions, and a whole lot more. I know you're excited to get started. Let's get started using Google Sheets. If you have any questions or comments, I'm always happy to hear from you. 2. 1 Getting Started with Google Sheets: One of the nice things about google Sheets is that you can access it from the browser anywhere on any device. It's all you have to do is login to your Google account. I've already logged into my Google account. One of these as ways to connect two sheets to see where the sheets are that you're creating is going over to your Drive who are logged in. If you go to the Google homepage, you can click the Google ops icon in the top right-hand corner and select Drive. Or you can directly go to drive.google.com forward slash drive can see all of your drive content within this location in order to create a new doc, there are several ways to do that. One of the easiest is to go over to the drive, select the new button in the top left-hand corner, and then from the drop-down, select Google Sheets. So it's going to open up a brand new Google sheet, ready to go and ready to start adding in your content, working with the sheet content. First thing when you do create those sheets, give your sheet a name, can be done with simply by clicking the default name in the top left-hand quarter where you're going to see the untitled. And then you can give it a meaningful name that's gonna show up within your drive. And I'm going to call it test one. The nice thing about google Sheets is that it automatically saves it to the DR. Next time you go back to your Google Drive, you're going to see the sheet is created. If you do have a folder open and if you create the sheet, then that sheet will then be created within the folder. One of the benefits of using Sheets is, as well as using drive is that it's really easy to please the sheets in certain folders within your drive, you can create sheets and access sheets directly within your browser in order to update and add cells and add content to the cells, you simply select the cell. And once you select the cell, you're going to see that it's got this blue highlight. And from there, you can add content into the content of your spreadsheet. You can also resize it. So zooming it in and I am going to make it bigger so that it's easier to see. What you'll notice is that the way that the cells are laid out, the columns, the columns will be laid out starting with a, b, c, d, e. And the rulers will be numerically incrementing from one onwards. So that's how you can identify the cell that you're working out and also select the range that you're working out. So this is going to be important when you are selecting that content within formulas and other formats while you're using your sheets. If you've got content within B for that, you can go down starting with the column B. And then the content here is gonna be what you're gonna see. If you have a value that you want to access in before, then you'd be able to identify that particular piece of content. I'm going to add in some more content into the page. This one now is going to be within the seven. So that's gonna be the cross where we can select the content from. In order to update the content, you just select the cell. You can either update the content directly within the cell or you can go to the top bar. We're also, we're gonna have the formulas later on in the lessons. And from here, you can select the content and make updates to it. And even if we accidentally close the browser tab, we have it saved here within the Google Drive. And then when we reopen it, it's going to automatically see it's going to have automatically saved the content into the drive. And that's one of the really nice things about google Sheets is the benefit that it does allow you to easily save it within the browser. You can also resize it and change the zoom size of your browser window. And this will also affect the content of the sheet. There's two ways, and this way we're actually updating and we're increasing the size of the tab menu, as opposed to increasing just the sheet content within the Zoom menu that we had tried earlier. There are several other ways that you can create sheets. And one of the ways is if you have a sheet open, if you want to create another sheet, you can select under file. And you can either make a new comp, make a copy of the current sheet, or you can select New and selecting a sheet from there. When you are selecting a sheet, you can also select a sheet from the template gallery. What the template gallery is of preset templates that you can use in order to easily set up your sheet contents. So if you did want to do something similar to what the Google Finance Investment is, you can select that. And that will open up your sheet already preformatted within that template format that we were just looking at. This is a quick and easy way that you can access your sheet content. Another way to open up sheets, and within the browser navigation bar, you can type in sheets dot new. Once you press Enter, that's going to also open up a brand new spreadsheet which is ready to start using. And this is just a brand new spreadsheet. So every time you go to sheets dot new, it's going to create a brand new spreadsheet. So another way to open up sheets is to go into your Google apps. From the Google ops dropped down once you've logged into your Google account. You can go directly to sheets. Sometimes you'd have to scroll down a little bit because there are quite a few products within Google. So go down and scroll down to sheets. And once you select sheets, this is going to open up the mean sheets. So under docs.google.com, forward slash spreadsheets, and this is where you can select all of the sheets that you've recently used. It's going to default search it, just buy sheets, creating a brand new spreadsheet. So you can create a new spreadsheet by selecting the blank tab so that once again opens up a new spreadsheet. And also within the docs.google.com forward slash spreadsheets, you can select from the various templates within the template gallery of the sheets. And from here once again, you can select the various templates that are available that you can use in order to quickly format your sheets content. There's always an ever expanding group of templates. If you are using a company workspace account, then your administrator of the workspace is able to add in default templates directly within the gallery so that you can easily select them for your work environment. Now also, depending on the settings of your organization and your workspace setup. This is also where if you are within a workspace, you can add and save a template. This is also dependent on your administrator and how they've set up your Google workspace. If you've just started working with sheets, there's always help. If you do have any questions, they have a really good help menu. That's gonna be the last tab in the navigation at the top of your spreadsheet when you open it up. So they do have a tab for help. So you open that up and this is going to have a pop-up window where you can search the help for various options. So let's say you want to do a formula and it will give you the most commonly asked questions about how to add formulas, functions, formula texts, add formulas and functions and so on. It already pre-populate whenever you're searching within the help, There's also training. So if you select that, that's going to bring you to the support dot Google website. And this is where you can get more in-depth training and information about how to use your spreadsheet also under Help. So any updates. So this is going to give you a listing of content and what's been updated with sheets. You can also provide feedback about your sheets, so help improve it or report any abuse. There's a privacy policy here, Terms of Service, the function list. So these are all the pre-defined functions. And we're gonna be looking at these in more depth later on in the lessons of the course. And then lastly, under Help, There's also the keyboard shortcuts. These are great once you start becoming more familiar with how Sheets works. And then you can really easily select and navigate with your sheets content by using the keyboard shortcuts. There's also the typical ones that you probably already familiar with, where we've got the cut, the paste, copy, select all. These are typical ones that are commonly used within most applications. As you can see, there are a lot of different shortcuts. And of course with shortcuts, it does take some time to get familiar and comfortable with using shortcuts. And we are going to be covering this as well in the upcoming lessons. 3. 2 Sheets Collaboration Sharing: Hey, let's add some more content into our sheet and then we're going to be sharing the contents of the sheet to others within the cells. When you select the cells, if you have a cell such as a one, you can drag it and this will automatically populate using the smart population that Google Sheets has. So it automatically try to populate the content. If there's no clear pattern, then what it will do is when you drag it, it will just duplicate the content as is. This gives us some content to work with. I'll also update this. And if we set it to something like three, there's actually going to be a pattern that it's going to assume that the one above it is going to be to the one below it is going to be 456. So that way you can create your own type of pattern and have Google automatically generate the pattern within the cells. And this is a really nice way that you can easily populate some content into your sheets. And we're gonna be showing you more ways that you can use this format where you're populating content in the upcoming lessons. So that way, we've got a nice range of content within our testing sheet. We've populated with content from cells a to D, and that's within the columns A2, d, and then the cells numerically one to ten. What happens if we want to share this content? There's a number of ways to share the content. And depending on how you want to share the content, if you want others to be able to view the content, let's say you want to provide a web URL for others to see the content. That you can do that by going under the File tab at the top, going all the way down to where we've got the share option. Within the Share tab, you've got two options you can share with others. We're going to look at that. And there's also published to the web. So we can click Publish to Web. You're gonna get the pop-up window that's gonna allow you to make your content visible, publishing it to the web so that you can link to it and others can embed your content within their document. We've got the link option here so we can share either the entire document or we can select the sheets. And this is where when we're selecting the sheets, we can rename the sheets. And that will allow us to, if we have a lot of sheets, allow us to pick out the ones that we wanted to use if we want to just share a particular sheet. And in this case that's what I want to do. I'm sharing the data from Sheet1 and then we've got how we want to link it. So either a webpage, a comma separated values. So CSV file, tab separated values, TSV file or a document, a PDF document. If we select document and we hit publish, publish, and it'll give us little pop-up here that are we sure we want to publish the selection. And once we hit Okay, It's going to present us with a URL that allows us to publish that URL, to share that URL with others, they're gonna get a download link to download the PDF onto their computer. So make it usually easy to share it within your Gmail, within your Facebook or within Twitter. You can share that document really easily and allow others to access the content of the document within the document, if you open up the PDF and this is the PDF link that I've shared. So this provides you a PDF version of the contents of the spreadsheet. It's really simple and the content, the columns, the data is all there. So all of the available data on the sheet is gonna get shared. What happens if we wanted to share it as a CSV? So the same idea. So once we do the sharing as a CSV, we can copy the web URL. In this case, instead of the content being output as a PDF, output is going to be CSV. And we're gonna get the same thing where we get a CSV file that we could download and open up on our computers at all. So you can really easily share it within the email, Facebook or Twitter. The really nice option that I usually generally like to use is the webpage. What the webpage does is this gives us once again a link that we can share. We can share it within their e-mail, Facebook and Twitter. But this time, whenever anyone goes to the link, they're gonna see the data of our spreadsheet so they can't actually interact with the data. This can be presented as a webpage using this URL. So anyone with the URL can see the sheet content. And if I do go in and if I make any updates to the context, let's say a delete a column once it saves. And if I go out and if I refresh, so next time the person is reloading the link, they're going to have that new updated version of the content that I have in the spreadsheet. So this is one way that you can share the spreadsheet. In addition to sharing, when you do publish to the web, you have some publishing options. So you can do whenever you don't want to publish it anymore or you don't want to share anymore, you can select, stopped publishing bill unpublished your web URL. So now if anyone tries to go to that web URL that you had published, they're going to get a message like this that the document is not published. Once again, if you do want to share it, then you can either select entire document, select the Publish. There's also options here where you can publish content. We're sharing it as the entire document or just the sheet itself. And if we want to publish it again, just hit the Publish button and go through the same process. So that will get once again to get activated. It is the same URL. So if you choose to Unpublish it and then he went to republish it again. There's also at the bottom when we are within the pop-up where we've got the automatically republish when changes are made. So if you were to uncheck that, that's going to keep the document as is. That will allow you to work with the contents within your spreadsheet and not have it updated within the published format. There's also an embed option. So this is an unframed embedded option that you can use within HTML code to embed the contents of the spreadsheet in an iframe. So go ahead and try it out and create a link to publish your content to the web. I'm going to go ahead and stop publishing this. Within the share options. We do have the share with others. What this will do is this will allow you to share your document so others can actually open up the sheet document and access it. You can select who you want to share the content with within the odd people and groups. So you can either either people or groups. It's going to give you a listing of the people that have access to it. So currently I'm the only one and on the ulnar, and it gives you the indication. You can also set different permission levels, such as viewer or editor. So you can select another user that's within your address book, or you can start typing their email address and that will automatically populate their name. If they do have a Google account, you can select in the right-hand side for these user or this group of users that what permissions are gonna have. So either they're gonna be a viewer that only allows them to view the document. They're going to have to go to the same web URL as you've got your sheet document at and be able to view it as a commenter so they can view and then the Additionally they can make comments within the commenting option within the sheets, which I'm going to show you later on in the lessons. And then lastly, there's also editor. Editor gives them the full permissions to edit the content, update it, delete content from the sheet. That's the one that you want to give if you trust the person and of course, if you want them to have the full permissions to be able to edit it, select the permissions appropriately for the set of users. You also have an option to send a notification to that person. So if you do share the document, it will notify the person or the group of people that this document is now shared with them, send them a link within their e-mail. And you also have an option to add in a custom message to let them know more about the share of the content. So this will give you a summary of what you're doing and the different shares. So what we've got with editors and what editors can do and viewers and common tourists can do with the document content. So once you're ready, you're able to just click the Share. And now a default, by default, an email has gone out to the person that I'm sharing with. There's two ways to access that share screen within the spreadsheet. So you can go in the top right-hand corner or you can go under the File and select the Share, share with others. Now that we've shared it with one person, if I hover over the Share button in the top right-hand corner, it's going to let me know that I've shared with one person. If I select that share, I got to see the other person that I've shared the content with, the permissions that I've given them. There's another option down here at the bottom, and this is who you can provide the link to. So right now the link is restricted, only people added. It can open the link. And the link is gonna be actually the same length that we've got here within our URL when we're opening up the spreadsheet. And the difference with spreadsheets is this part here where we've got the pasta d slash. And this is gonna be a unique identifier for every spreadsheet is going to have a different set of characters here for the identifier of the sheet. And that's how the web URLs can distinguish the different sheet content that you can copy. Or you can just take it from your web browser URL and share that link to the people that you've just shared the content width. If they are not within the shared people and groups, they're not gonna be able to access the content. If you do want to make anyone have access, you do have an option down here under the gut link where it's got right now, by default it's restricted. But if you select that, you can also select anyone with the link. So anyone with the link can view the content. You can also set this to commenter or editor as needed so you can update these permissions as needed. You can also always go back to restricted and that way that will restrict who can access the content. There's always just one owner to the content. And if you are the owner, you are able to switch permissions and allow others to become the owner of that document. So you've got the full control and there's only one owner per document within Google Drive. Once you're happy with the share settings and if you have shared it, if you ever need the link, you can really easily collect, click that, and copy the link for the document as needed. There's also another way to share it. And that's going into the drive and you've got within the drive permissions. With the Drive. You can also select the user and you can have the same options as we just did within the spreadsheet, where we can click the icon in the top right here for sharing. And that will give us the share of pop-up. And that's the same share pop-up that we saw within the spreadsheet. Alternatively as well, you can right-click it and select the Share option. And that will once again give you that same share pop-up where you can share the file and select who has access to it. And if you ever do want to remove someone from having access to your shared document. As the owner, you have the full control. You're able to either remove the person. And there's also an option if you are the owner to transfer the ownership to others and you can do that within the drive. Or you could change their permission level In viewer common term to an editor. That is one of the nice things about google Sheets is that you're really easily able to collaborate and work together on documents and share that content as needed. 4. 3 Saving your Sheet v2: As we create more documents, becomes harder and harder to find the documents that we're working with or that we want to work with. So there's a number of tricks and one of the ways is to organize it within the drive. Within the drive you can add in folders and you can move content within the various folders. So if you want to create a folder, give it a name of test. And then that way you can move the document into that folder and it's still going to remain the same. Permissions are still going to remain the same. The link to the document is still going to remain the same, but within the folder structure of your drive, it's gonna be different. You can also select under recent if it's document that you've been recently working with and as well within the sheets. Of course, you can search it and search it by the name and also customize the search. So if you are looking for spreadsheets in particular, you can select the type, select spreadsheet. And from there you can set up the different parameters in order to do the search, to find the document that you want to work with. And this is where naming the document is really important so that you can easily find and identify the document that you wanted to work with. And also naming sheets is also very important. So you can create multiple sheets. And if you want to change the name of the sheet, you've got options down here at the bottom where you can select the sheet, double-click it, and give it a new name. So make sure the name is meaningful and that it's going to actually represent the content of that sheet so that others, if they are viewing it, they can easily understand what is caught contained within this spreadsheet. There's also a few options as well, and these are similar within the drive where you can select the sheet and if you right-click it, you can set and add a star option. You can add starred to it so that we'll add in a star. And if that also allows you to easier find documents, so you can select under the starred within the drive and that will give you a starred contents that are currently within your drive. So this is one way that you can highlight important document. It also within the drive, you can select and get the show file location. So that's going to give you the location of that file within the drive. If you have multiple folders deep, you're gonna be able to identify where that file is located within the I for information. And the top right-hand side within the Drive. This will give you more information about the file. It'll give you a brief icon there for the details of the file. It's also got the activity of the file. So what has happened? Who's interacted with it? Different edits that have happened on the file itself. There's also a brief on who has access to it, the file types. So this is a Google Sheets size location, who the owner is. All of the details that you need in a quick glance, you can also get this information directly from the sheets itself and under the File tab at the top, you can go down to the bottom where it says details. That will give you the basic details of where the file is. So I'll give you the location, the owner when it was modified, when it was created. In addition, you can do the quick menu icons here at the top. If you want to add a star to the file, you can select the star or you could unselect the star. You can also select to move the file. And if you do select the Move that will have the pop-up window, it allowing you to select the file and then move it. So navigating to your drive and move it to another location on your drive. You can select Move here. Once you've navigated to where you want to move the file, you can also quickly create new folders with a new folder icon. And this can provide you a way that you can add in the file if you're creating a folder on the fly, when you've got this menu open and drop the file within the brand new folder. Now that's created on the drive. So if I go back to the My Drive, there's the test ten and there's the file sitting within that location. There's also the document status. This will give you information a bit with the current document is and whether the document is ready for offline use. You can also set this document to edit it directly on your computer when you open up the browser, if you do have the offline set, in order to do that, you can select the folder drive, and within the full Drive, you can select available offline. So when you do make it available offline and you go back to here, this will allow you to access the document using your Chrome browser to that URL. Even if you're offline, you don't need an Internet connection. And then when you do establish an Internet connection, it can sync the content backup to the DR, also within the drive as well as within the ducts. You can really easily rename your document. If you want to give it a new name, you can do it within the drive. And correspondingly, it's a lot easier to do it within the sheet when you do have it open. So if you wanted to rename it to test new, then you could easily rename it to test mu by simply clicking on it, sending the offline access. That's also under the File menu where you can remove the offline access. Now the document will no longer be available offline. And then you can make it available offline within the file menu, just as you did within the drive menu. And another nice, really nice feature with Google Sheets is that you also do have the different versions. You can see the content that's been changed. You've got information about who made the changes. You've got an autosave. So it's automatically saving the different versions and when the versions are being made, there's also information about who made those updates. You can always restore to that version. You can click to see the version, the version content you can restore to that version. You can give the version a name so that, that way it gets stirred into the versions. So if it's a major change that you might want to revert to at some point, then give it a name. You can also make a copy of that version and copy that into a separate spreadsheet. This is great for when you are collaborating, that you can easily see who's made changes and what the changes are to the document. This gives you a whole lot more details than you did see within the drive. When we click on the drive and we got the details that we've got the activity here. So this is a more detailed view within the versions to be able to see the detailed activity that's happened, the changes that have been made to your spreadsheet in order to access your documents offline, you do need to have the extension within Chrome, and that's available under the Chrome extensions. You can also search for the Google Docs offline extension, ASC that this one has a lot of users and use it and added into Chrome. So that will give you the opportunity to set your documents to be shared offline and make them available offline. And you do need to open them within the URL using your Chrome browser. 5. 4 Sheets vs Excel comparison: It's gonna be a quick list of top features when we're comparing sheets to excel. When if you are coming from a Microsoft Excel environment and you are in YouTube, Google Sheets, this good pride you a quick overview of some of the functionality that you can expect and sheets that is gonna be similar to what you had within Microsoft Excel. One of them is the collaboration in real time. With Google Sheets, you do have the ability to share your content. I've actually shared it to my other Google account. I've provided full edit permissions. Once those permissions are provided, and only once those permissions are provided and you are logged into your other account, which I am at this point, I'm able to access the document. It tells me that I've logged into my other account. And this is what makes collaboration in a real-time really easy with Google Sheets. And I can see the collaborators are currently available. So if I flip over to the other top, I can see that that other account is the collaborator. So it will automatically assign colors and let you know who else is viewing and interacting with the content at that same time, I can also see where that person is. So right now, I see that the original account with aqua color is sitting on a one and I'm sitting on E1. If I go over to the other account, I see that the cell that is selected is in fact a one. I can select multiple cells. And that will indicate to the others that are collaborating who's interacting with that content. And then you can also see which cells that other person is interacting with as that is gonna be highlighted within your version as well to share and have access to the sheet. Then you can share that easily with just the click of the button and then selecting their user account. The other nice thing about google Sheets is that it automatically saves to drive much as what you'd expect with Microsoft Excel where it be sharing using SharePoint or OneDrive. When you go to the document status, which is this cloud here at the top, you can see that this document that all changes saved to drive. This is also where you can find that this document, if we've set it up for offline use that we've enabled or disabled it. This will give you a quick overview of when it's being saved. And you can also see in the version history the different saves that are automatically done to drive. And then you can within the document, go to any one of those versions and open them up. That's another nice feature about sheets that's consistent with what Excel has, is where you can imagine manage the version history. So several ways to access that. And one of them is to go down to the File tab. And then under version history, you can see the version history or you can use the shortcuts in order to do so. There's another option, just as you would expect with Excel, where you can add formulas and use the autocomplete and suggestions. Google Sheets has this as well. So they do have a Formula tab here. So if you had several cells and if you wanted to get a total from those cells, you can select the cells and add in a formula. And this can be done using the equal sign, then the keyword that you want to use. So in this case I want to apply a sum. So it'll asks me to select the first value. I can either enter in a value or select it from the cells of the spreadsheet. And in order to select it from the cells of the spreadsheet, it will automatically populate that in. And then I can add in a second value. And then once I've completed the formula, then I can close off the brackets and I can press Enter, and that's going to automatically populate that content. We also see that we've got the auto-fill, the suggested auto-fill. And this is a feature within sheets that will try to anticipate what you want in the following cells. And here it's suggesting that I might want to add this autofill formula into the remaining cells. If I, as a continuation of the sum that I've already got here, it's suggesting that I use the sum and I can see the formula that it's making the suggestion, It's suggesting E5 plus e six. And that would be resulting that sum. So I can apply it and click Yes. And I can see that it's automatically suggesting to take the two rows values and then on the third row provide the sum. And it's done that for all of the remaining cells. And this was automatically suggested. It does have a really smart tool feature. Is similar, similar to what you get with the Formula AutoComplete and suggestions. You also have an option to record macros, and that's under extensions. If you select extensions macro, you can select to record a macro. And right now what it's doing is it is recording the macro. The macro is a series of steps that you're using. Right now. It's gonna be recording all of the actions that I'm taking within the spreadsheet and then select those and bold those values. I can save that as a macro and also provide an optional shortcut for that macro. So let's go ahead and we'll save that macro. We can also select to edit the script. And that will open up the Google Apps Script Editor. And it will provide details about what we did within the macro. We also have an option under the extensions and macros. We're going to have that test macro now set. If I ever wanted to run the macro and the script does need authorization to continue. Let's do a quick authorization for the macro to run. And I am gonna be running through this in more detail later on. If I was to erase this and run the test macro, it will automatically run through the same steps that I did within the macro. This is also something that you probably are familiar with from Excel and using VB script. Whereas in this case, we're gonna be using Google Script to accomplish the set of automating that set of actions that we just recorded. There's also an option to create filters and that's just under data. So just as you would have for Excel, you can select and create filters. And you do have an option for filters for everyone or filters for me. And what filters will do is they're going to give you a quick view of the data and the selection of the data within the filter. And I'll give you a quick pop-up tip here for more information about the filters. And we see what the filter is by selecting the COG wheel on the right-hand side within the filter just to the right of the range, we can re-select the different range. And as you can see that currently the range is going to be from F2 to H12. So in order to make a selection after kick, hit the cogwheel to update the range. Instance. If I want to update the filter, I can rename it. I can update the range, duplicate and delete it. And I'm going to be providing more details about filters. But for now, if you do need to access that filter again, you can select under data and filter views, and that's where you're going to have the filter that you just created. We can create pivot tables manually or automatically. And that's just under Insert where you can select a pivot table which can be inserted into a new sheet or into the existing sheet. So I'm gonna go ahead and create it as a new sheet. And then this is the setup for the pivot table that we're gonna run through in the upcoming lessons. You can also create charts manually or automatically. That's under Insert Chart. And it will automatically try to generate the chart. And it gives you the chart editor so that you can customize the chart as needed. And also the new kid insert the chart within your document where it's needed and where you want to apply that chart. This is all really made easy using the Google Sheets. Most of what you do commonly with an Excel is also available within Google Sheets. And the common functionality that you've grown to expect with the spreadsheet applications is all going to be available within Google Sheets. We're gonna be covering that in the upcoming lessons. 6. 5 Create and Import Files to Sheets: This lesson is gonna be management of their Google sheet file, how you can rename it, move it, download it, download options. Also how it relates to working with XLS. How you can download and open up Excel files and also upload them back into Google Docs and then use your Google Sheet. Use your Google Sheets in order to open and update an Excel file just as you would with a Google Sheet. All of that and a whole lot more is coming up in this lesson. In order to access Google sheets that you've created, you can go to your Google Drive and going by, you can search by the name. Or if you know the location of where the file is located on your drive, you can open it from there as well. For the Google Sheet itself. Once you have it open, you can find out where it's located by going down to the details of the file and it'll tell you the location of where the file is located. So the main folder that it's sitting in, this case it's gonna be sitting within test ten, and that's gonna be the file location of where the current file is located. Once you have the Google sheet open, you can also select at the top move, and that will give you an option to move the file from the current location to another location on your Google Drive. Right now it's sitting within the test ten folder. If I want it to move it back to the root folder, I can simply select to go back to the drive. And then once I want to, once I locate the folder that I want to save it into, I can select the move here. I can also create a brand new folder in the left-hand side under the New Folder icon. Let's move the folder and the file over to the main route of the drive. When I am on the drive, I have an option to add a star. And then that way, those can be a way that I can search for files and specify that I want to look at all the files that I do have currently start, and that will provide me a directory of files that I only have starred dot I can see. In addition, you can select the file and you can add a star to it. So I had to start. That way. It will add a star and allow us to locate it whenever we're searching under the start. There's also an option when we've got the file open that we can select and add a star to it. And that will as well place it with under the Start directory so that we can easier locate the files. The most important files that we're actively working on. There's also sorting where we can select Recent files and that will list out all of the recent files that we've been working on, providing us another way to easily access the files. Once we've located the file that we want to open up on our drive, we can select to open the file, and that will open the file to the latest version that we've just been working on. And once we've got the file open, we have several options to manage the actual file itself. Under the File tab, we have an option to create a brand new spreadsheet. We can also create the spreadsheet from a template. We can open another file that's located on her drive. From here we can select to open a file either from our drive. And it gives us a brief preview of that file. We can select files that are shared with us. We can select files within the Start folder, and this is where it allows us to lot easier locate those files. There's also recent and then we also have an option to upload a file. We can drag a file from our computer and add it and upload it. So first off, let's go ahead and we're going to download this existing file and download it as we've got several options here, so we can download it as a Microsoft x L file. So that will give us the XLSX extension. There's open docs PDF of Y as a webpage, comma separated file or tab-separated file values. We select whichever one we want to work with when we download it. So whichever format suits us best in this case, let's go ahead and we're going to download it as a Microsoft Excel file. That will kick off an initiate the download. And we'll see that this gets automatically downloaded into our Downloads directory on our local computer. So now this file is sitting with the same name that we used for the Google spreadsheet. This is gonna be sitting on our computer. And from there we can open it up directly in our computer. We have an option to select Open. Now because it's an Excel file, it's going to automatically tried to default to open it up under if we do have Microsoft Excel installed, this is the file opened as an XLSX file using OpenOffice strictly on my computer. So we can make some updates to it and make some updates to some of the content. Save it back. And now let's upload it back up to Google Sheets. And I'm gonna save it as an XLS file and then just hit Save, keep the current format. And now going into Google Drive. I'm going to take the two files. So this was the original one, the XLSX file. And then this is the new one that we just created. I'm going to upload them both to my computer. And then we're going to open them up with Google Sheets. Now with Google Sheets, you don't actually need to convert them into a Sheets Doc, you can work with the XLS format. So the typical Excel format, what happens when we try to open it? We can just double-click to open it. Notice the icon is going to be different than the Google Sheets. Where the Google Sheets has the Google Sheets icon and the XLSX files and the XML files are gonna have an X. As you open it up. It's going to give you a tour, a quick tour for the first time to give you information about how to edit XLS files directly on Google Sheets so you don't have to convert it. And notice that this is the extension that the sum Microsoft Excel format file. So we can make up dates to the file content and have it saved as the XLS file, as the Excel file. Let's open up the XLS file and it does take a few moments. Now on the drive we actually have all three versions and the name is actually going to be the same except for the XLS files. Those are gonna have that XLS extension. So indicating that they are Excel files and that you could be editing them as such. So the name is slightly different, but the main name of the files is going to be the same. So we've got all three versions. So we've got the original one that we had. So this was just the test new in Google Sheets. We've got the XLSX files, so it's the newer version of Excel. We can upload dot and we making some updates. This was exactly a direct copy of what we had within our Google Sheets. And then we have our Excel file. And this is the one that we actually edited an updated locally on our computer where we added those three items within the cells and then we uploaded this version. When you are working with XLS, It's always good practice to have the main file that you are working with to avoid any type of confusion. If you do choose to work with the XLS files, then ensure that you are marking the list as the main file that you want to work with. Once you have it within Google Sheets, this is going to give you the exact same options as you would have with sheets, except it just saves it as an XLS file. That's the only real difference between them, is that they extension is going to remain the same. And you can also open it and edit it as such and do all of the collaboration that you have. Typically with Google sheets. There are some other options when you are downloading. So there's the open document format, there's a PDF, the PDF file. You won't be able to edit that within Excel. There's also webpages. There's a comma separated. That's good if you're dumping values and there's tab separated values. If you do want to rename the files that you're working with. You can also do that under file, or you can just double-click the name and type in the new name that you want to use. So there are some options where you can rename it and it's going to default opening up within the input area of your sheet where we've got the name. You can also move the file. So just as we moved with the shortcut up at the top, we can also select to move the file. And then it'll just ask us where we want to move the file into. So it opens up a pop-up with r dr. And then we can navigate here. We can create new folders just as we did up here at the top with the quick menu options, we can remove the file so we can move it into trash. What that's gonna do is that's going to temporarily delete the file from the active folder. And if it stays within trash for more than 30 days, then that file will actually be permanently deleted. If you do want to take it out of trashy can do that as well. So let's go to the home screen and go back into r dr. And we're gonna notice that those files are actually gone. That file is actually gone. It has been moved into trash. So let's restore it from trash. So files that are in trash there for 30 days and then they automatically get removed permanently. If you open up trash, you're going to see the files that are trashed. You can always empty the trash so that will permanently delete the files. If you want to restore a file, then you can select the file that you want to restore. And you'd have an option to either restore or delete forever. If you restore it, it's going to automatically go back to where it was located. This one automatically went back to where it was sitting before. I'm gonna go ahead and close the XLS and Excel files and remove those. And when you hit Remove, it's gonna move them into trash. And again, the same 30 days applies that you can either remove them or restore them if you need to. Let's open up the file that we were originally working with. This was our testing file. And under the File menu, we can also make a copy of the file. So this is automatically going to try to take the name. It's going to make copy of. This is important because this is a good way to avoid confusion on which is the actual file that you want to be using. So it is a good idea if it is a copy of whatever reason you want to make a copy that just note that within the name to avoid confusion with the name of the octave, one that you want to work on. You can also select to share it with the same people, copy the comments and so on. Then also gives you the location where it's creating the copy of the file. Now I've created the copy of the file. There are some other options where we can import. Importing. What that will do is that allows us to import files from our drive. There's also shared recent and upload. If we want to upload and import an uploaded file from our computer, let's upload one of those XLS files that we just created earlier in this lesson. I'm going to select the XLS version and open. So what it's doing is it's uploading it. Once we're uploading it, we have a choice where we can create a new spreadsheet that will update them. We can insert new sheets. We can replace the existing spreadsheet and then there's a few other options there. Or we can just select to import the data that's contained because this is an XLS file, so it's a spreadsheet file so we can import the data. We're going to insert new sheets and we're going to import the data from that sheet. And now we see that we've got a new sheet that's been created and using this name of Sheet1 and they've bracketed around one so that we have a way to distinguish what our original content was as opposed to what our new content is that we've just imported from the sheet. An odd shortcut to drive. You can do this as well when you click the file within Drive. And you can select to add a shortcut. Shortcuts linked to the original, allowing it to appear in more than one location. So you can do the shortcut here, and it'll create a shortcut and ask you where you're creating the shortcuts, which part of the drive you are creating the shortcut. There's the shortcut dotted has created to the test file and suddenly use the same name. And this is just a shortcut with the shortcuts that's going to have this arrow pointing up icon. And also this is gonna be the same thing that if you create the shortcut from directly within the file and allow skew where you're creating the shortcut. And let's create another shortcut that will give us two shortcuts to the same file. They're both gonna look the same. And we can take these and we can move them around. So that way we can have shortcuts sitting in other areas of our of our drive and then we can access those. You can also have different shortcut options where you can rename it, you can add it to start. You can see the details you can make a copy, can remove it. You can also move the shortcut. The shortcuts, once they are created, are going to operate just as you would with any other files. If you select Settings, this is the settings for the spreadsheet. So it'll give you the locale and it will affect formatting details such as functions, dates and currency. And in addition, that time zone, this is going to affect all time-related functions. Generally, you're not gonna see much difference. But if you are working with dates and currencies, then make sure that you are setting the proper settings for the spreadsheet in order to accommodate those. There's also a calculation. How the calculation works. This is effects on how today rand random between our updated. So whenever onchange we can update it every hour and every minute as well. So onchange, there's iterative calculation determines whether formulas with circular references are resolved by iteration calculation. There's more information about this. So generally, you can just leave that off. But if you do need this option, then this is where you can find it in the settings. Once you've adjusted your settings, you can hit Save Settings. And that's going to save the settings for this spreadsheet with the new updated settings. This is all available within file we've already covered to share. You can share it directly as an email file, and E can email all of the collaborators as well. You can publish it to the web. So there's a lot of options under file. This is mainly dealing with management of the document. All the different options that you have for that management. And here's some quick tips to customize the way that Google Drive handles your Google Sheets. So if you go into the drive and up at the top right-hand corner under the Settings cog, you can select that and select Settings. And within the main settings under the General tab, you've got an option here to replace with shortcuts. So don't replace with shortcuts. So options for replacing items with shortcuts. There's also convert uploads. So if you want to convert files to Google Docs editor format, you can select that or unselect that. There's also the offline option for Google Doc Sheets, slides on the device. And then there's suggestions. So a suggested files and shared with me. There's also the densities, so the view, so this is the view that we're looking at within the drive. You've got some options to update. How it's manages the uploaded files as well. Best practices in general is that if you are uploading multiple files and to avoid any confusion, keep the octave one within the Google Drive and then you can download the inactive or the different versions onto your computer if you need to. So best practices also to rename it or to move it. So if you are making separate items, then you could rename it as such. So this is your main. Then just go ahead and add the words mean or bracketed main to that. So it's up to you how you want to manage that. But in this way, you can easily tell which one is the main file that you're working on. So I just usually just do like convene or an active in order to indicate that this is the active one in the name of the file. If it's not needed in Google to avoids over having too many different files. This is just a copy. So go ahead and remove it to keep your drive fairly clean and only with the active ones that you are interacting with. Also to avoid confusion on the files because the shortcuts are gonna share the same file names. Unless you absolutely need shortcuts. It's suggested that you don't use the shortcuts and if you do have shortcuts, keep them. And you can also rename them as such. And again, it's all about trying to avoid any confusion with the main active file does the one that you're going to be working on, especially when you're collaborating. So this helps others understand better, which is the file that you want to have as the current version of the file, the main version of the file. Those are just some quick tips for best practices when working with Google Drive. Also, when you are uploading the file and if it's an XLS file, if you do need it within the XLS, then you can keep it as XLS. Otherwise, you can convert it into a Google format by making a copy of it. This XLS file, if I want to make a copy of it, this is going to automatically try to convert it into Google Sheets. Or we can say that as Google Sheets. And again, it will make a separate copy of it as a Google Sheets copy of the file. The original XLS file will always remain, but it will also have a brand new copy of the version of the Google Sheets. So this one was the one that was just created. And again now we've got two with the same name. And actually we've got three with the same name because also including the XLS agreed upon, which way is the best method to handle multiple files so that everyone knows which is the active file to work on. 7. 6 Editing Google Sheets: It edit the contents of your cells in your spreadsheet. Well, this lesson's going to show you some examples of what you can do under the Edit. That's all the typical stuff that you'd expect with edit. And then also Find and Replace how that works. How you can add notes to the cells, how you can actually rename the cells, how you can apply formulas. So there's whole bunch of good stuff coming up in this lesson. And I'll be giving you some really cool examples of what they, what you can do with the edit functionality within your Google Sheet. Go ahead and open up your Google Sheets. So right now I just have some dummy data within the sheet and I'm gonna be showing you how you can update and manipulate the contents of your sheet. So the mean tab at the top here under Edit. So you've got the typical auctions that you'd expect. The undo, redo, cut, copy, paste and paste special. Paste special does is it gives you more options when you are pasting. So if you just want the values or the format or formula, then you can select these as what you're pasting on top of the contents of the Google Spreadsheets. So contents of those cells, you also have an option to delete. You can select what you want to delete. And then of course there's the find and replace. So these are all using the common shortcuts that you're probably already familiar with if you've used any of the other spreadsheet formats, as well as most document interactions are gonna have the similar type formats for the shortcuts that we've just outlined here. The Find and Replace gives you a pop-up window. So if you want to find a certain value and what you want it to replace it with. And it gives you a selection of where you want to search. You can search in a sheet, you can search in a specific range, or you can search across the entire spreadsheet. So all of the sheets. If you do select a specific range, then you can specify the range of the selected data. Or you can go out and make a selection of the data. It'll give you that pop-up and gives you an opportunity to set the data range. And this is the data range here that's been selected and highlighted in the background. So this is going from Sheet one that we're making the selection. So A12, D3, so including all of the content within that range. Also under the find and replace, we can match the case. So making a case-sensitive match entire cell contents search using regular expressions. This is a really interesting one because this allows us to use regular expressions. There's more information on regular expressions, the help on Google, and I'll give you some examples of regular expressions. Regular expressions are a sequence of characters that specifies a specific search pattern. So you can really customize the search pattern that you're looking for using the regular expressions. You can also search within formulas within the sheets. So you can make and really customize selecting them. You can just do a find, you can do a replace, and you can also do a replace all the difference between replace and replace all replaceable only replace the first instance of it. And if you do a find, it will find the first occurrence of it. And here we just did the Replace of it. So we see in the background here under column B, it's replaced by last name with the character that we're adding here to replace it with. So we're just including the one at the end. If we do a replace all that's going to replace on all the occurrences that are within the range. And then we can also update this. So this pop-up window isn't gonna go away. Once we've done the Replace All, we can continue to do replaces finds and replaces, customize the characters that we're looking for and also what we're replacing it with and we can make updates as needed. So once you're done with the window, you can either exit or hit Done. And that will complete the Find and Replace option. That's all available under the Edit. There's also ways to interact with the cell content directly by selecting the cell content. And if you right-click it or on a Mac, if you do a control, click, it'll pop up the pop-up window with the typical commands that we've just seen. So that's the copy cut paste, paste special. And we also have the delete, delete column, delete cells that will select all of the cells that we've currently selected and delete those. We can also insert cells, insert row above, insert column above, Insert Cells, and then how the cells are gonna behave, how they're gonna be shifting, accommodate the inserted content. There's also where we can show the edit history. What that does is this gives us information on that selected content and what their edit history is. That again, down here at the bottom. The edit history is only available if it's a single cell. So you can't select a full range and get Edit history. The edit history is per cell Ollie. There's also some options here where you can insert a comment, insert a note, and there's also some special actions that you can do with the cell where we can have conditional formatting data validation, which we're gonna talk about. Little bit later on, get a link to the cell defined named range and protected range. So we're gonna be covering that in a little bit more detail later on as well. If you want to insert rows, you can select the rows or the columns. The main selection area at the side. And from here, if you were to right-click or control-click, you're gonna get the option to insert above or insert below. Depending on how many cells you selected, you're going to be able to send, select that entire block. So now that I've selected three rows of content, I have the option to insert three rows above, Insert three ropes below. We have an option to delete all the rows to clear the contents of the rows. We can also hide and resize the rows. Once we've selected that full range, then we can insert or we can delete as needed. If we do an insert, what it's gonna do is it's going to insert three brand new empty rules that we've just inserted below. Then we can select that content if needed. If we want to copy it, we can go and we can paste that block of content once again into the cells. If we select a block of cells that already is content and we press paste, that's going to overwrite all of the selected cells in the range. If we're not selecting a similar range and we tried to do a paste for this case. Let's select all of this and do a copy. And we're only going to select this as where we're pasting. And what's gonna happen is even though we only selected the starting point, it's going to take the starting top right-hand cell and overwrite all of the existing cells with the copied content. So keep that in mind that when you are selecting a range, it's going to copy that entire selected range. With the paste, the paste special. If you want the values only, you're gonna get something like this where it's going to be posting the values only. There actually wasn't any formatting, but you can select the cells and you can apply some styling to those cells. And then when we make a selection of those and do a copy, Let's do a paste special and do the values only. So it's going to remove that formatting that we just added in. And if we do the format only, it's going to apply that format only to the paste. That means that we can select Existing Content and do a format only. And now we'll just apply that format to the existing content. So notice it didn't overwrite the values. It just kept the formatting that we were using with the selected content. In addition for the paste special. So we can do conditional formatting only and data validation only. So this could be a little bit more about that later on, There's also transposed, which allows you to paste that content as transposed. Where the rows become columns and the columns become rows of data. So that will give you this type of output. When we've got the selection, Let's select that one more time and we'll select a, do a copy and then go back down. We've got some empty space starting at row number 24, and we're gonna do a transpose. So it will provide that selected cell data across all of the available content all the way up to the last available column. So it uses the same value and it transposes the row values into the column of values. And you can see that a little bit better when you are just using the one cell. But before I had multiple row is selected. And the result was that it would loop through and it would paste all of the rules of content. And then it would start again, paste the next set of Rosa content until they ran out of space for posting it within the current available number of columns. Also on Paste Special, so you can do Column Width only if you had a specific column width that you had selected. Just adjust the column width on these. So make coffee. And then let's do Paste Special and Column Width only. What happened is it copied the column width from a and B and copied it onto C and D starting at C. And we also had caught column be copied. So it allows us to essentially copy those widths to the new columns. Also, there's all except borders. If you do have borders added into. Your contents. We can select that and typically it will copy the borders. But if we don't want the borders, we just want everything but the borders that we can select it that way. So if we do a paste there, notice it does keep the borders. Let's do a paste and do all except borders. And in this case it pasted everything that we had except for the borders. So there are some options to how you can paste and what the results of those pastes are gonna be. Just as we saw when we want to insert number of columns or rows. We can select it and we can insert the rows. We can do the same thing for columns. So if we've copied and we want to insert columns, once we select, so right now I've copied two columns. So I have an option to insert two columns to the left or two columns to the right. I can also delete the selected range. I can clear the contents, I can hide the columns. I can also resize the columns. If I resize the columns, it will give me two options. I can either set the column width by pixels or it can allow it to fit the data and fitting the data will resize it to the largest data, the widest cell data. And it'll use that as the width for the other cells so that everything matches with the widest cell data that you can see the entire contents. So if we did have within a cell whole bunch of values, we can't see those values unless we can click on the cell. But if we do a resize column and fit data, that's going to make the column with really large so that we can see the, all of the data within the one view. You can also resize columns at the top where you can drag and drop the column widths. If we want to select all of these columns, and we want them all to be matching with whatever. Once I've selected, it's going to apply that same width across all of them. If I wanted to resize these selected columns. And I resize them smaller, it's gonna get applied to all of the columns. If you want to select columns, you can hold the Shift down and select a full block of columns. If I want to select ones that are not next to the ones that are already selected. I can do that with I can do that by hitting the column and hold it while I'm holding Command down on my keyboard. And that allows me to select individual columns. And now I've got all of these as the selected range. So if I make any adjustments or resizes, that's gonna get applied to all of the selected values. Also, when I make a selection of those, It's gonna apply that to all of the available columns. If I do a resize of the column to fit to the data, it will automatically resize them accordingly. The same goes for the rows as well. So I can select multiple rows and all of the selected rows if I drag and if I re-size the row size, that will be reflected across all of the available rows. I can always undo that and redo actions by using either the keyboard shortcuts are going under the Edit menu to undo and redo. If we select an individual cell, we already saw that we can show the edit history that gives us what we've done and all of the history that has been associated with that cell of data. We can also delete it. We can add in a comment. A comment is going to look like this, where it adds the comment into that particular cell. And once the comment is added, you're going to see it within the comments. If you open up the comments, it'll show you the comments that are for the entire document. And if you want to see comments for this particular cell, you can either hover over it or you can select it and it'll highlight it within the comments area. And that gives others an opportunity to reply it to comments. And also marked the common size resolved within the discussion area. Just going to mark this as resolved. Once you do, it will disappear and it's no longer going to highlight it as an active comment. There's also, you can insert a note. The notes are going to be showing up, has a black triangle in the top corner of the cell. If you hover over them, you can see the notes that are listed for those cells. And then you can always select that and update odd to the note. If you want to get rid of it, then you can select it. And you've got an option now to delete notes. So once there are some notes, then you have an option to delete the notes. If you delete the notes, Let's go to remove it, the notes from the cell of that. If you want to add in new notes, then you can right-click again or Control click and start typing in new notes. This is only for the cell itself. So others that are using it or if you're using it and you want to make specific note on the value that's contained within the cell, then you can do it this way where you can add it as a note into the cell. If we want to use formulas, I'll give you a quick example and there's a lot you can do with formulas. We're gonna be covering it in more detail later on. So let's say I want to add column E and column F. I could go over to G. This is where I want to have the result. And in the top bar that's empty here just to the right of fx, you just start by hitting an equal sign. And then you need to use and select the range that you want to apply this formula to. Then once you hit Enter, that's going to run the formula. So we didn't actually say what we wanted to do with E and F. So there's no formula that has been, or there's no action that's been associated with this formula. When you select and you start typing, you're going to see within the drop-down the formula. So if I wanted to add those together, then I would type in the, what I wanted to do with the formulas. So in this case I want to do a sum. It's going to ask for some parameters. I can. I'm going to need two values, at least in order to formulate a sum. So the first value is coming from E, and then add that to gather the second value from F. I can also hardcoded values. So if I wanted to add 100 to both of those values, I could do that as well. Close the rounded brackets and this is going to give me the result of the formula. If I want to apply the same formula to the cells below, I can always drag. And whenever I select the cell, I get a blue box and I've got a square here in the bottom right-hand corner. If I select and hold the click button down, that allows me to drag and apply the same formula to the other cells that I've just dragged it to. When I select the cell, if it does have a formula, it's going to show the formula in the top bar. If not, it's just going to show the contents. On this case it's a string contents that are sitting within the cell. I can also have, I also have indicators of what cell I'm on. Right now it's D eight. So column D E, row number eight. You see that here within the name box, I can apply a new name to this and hit Enter. So now when I go on top of that particular cell, now it's gonna be referred to as new name. You can provide specific names for each of those, and then you can use the named blocks within the formulas as well. I'll show you a little bit more about that later on. 8. 7 Sheet Content Options: In this lesson, I'm gonna be showing you how you can freeze rows, freeze columns. What that means is that they're always going to stay froze, frozen. So if you have a lot of data that you're going through, this is a good way to see the headings of those columns. Also how you can highlight rows and columns and what your options are when you select them. So you can either hide them or you could show them. There's some resizing options as well. So fit to the data. They're gonna be going over some of those. And then also most importantly, how we can sort contents that are contained within a particular column and as well how the spreadsheet will behave with the sorting action. So in this case, if we want to sort the entire sheet according to what we've got here within the column G under the header numbers, we can do that as well. So go there, either going into the data when we select a range and that deselecting the short sort range. And then using the data as a header and selecting via the header. This also gives us an option to do sorting and managing the sorting of the spreadsheet. So all of this in a whole lot more is coming up in this lesson. We're gonna look at different options for viewing content. Now a lot of times when you do have a role in starting in a new row, you might have the headings in the top row. I'm just going to add in some headings there in the top. So this can be the last name, this is the topic and maybe this is just the full name. And ID. Sometimes you might want to make those bold so that they do stand out differently. And if you've got a whole bunch of content that you are scrolling down, it's also useful in many cases to be able to see what the headings are of those columns. Especially if you have a lot of data that you're sorting down, there is a way to freeze the rows. You can select one rule, you could select multiple rows depending on how many rooms have headings you have. I'm just going to be selecting the top row. So selecting the one in the left-hand side of the rows. And then I'm going to right-click thought. And then going down to the bottom where we've got the view More Actions. I've got the top option is to freeze up to one row. And what freezing the one row now does is as I scroll down to row 48, I can still see the heading information here at the top. So this is a really useful way to see that content. If I want to unfreeze it, I can select it and going back down to the bottom, I can unfreeze the row. If I had multiple rows of content, I could select, I could select several rows and group the rows, freeze the one rule. So if I group the rows, now I'm not going to have the option to freeze more than one row. So even if I have selected them, if I go down to the options, I only have the option to freeze up to one role. So it'll always just tie to freeze that one row. I do have more options when I go under the View tab at the top here where I can freeze, no role is I can freeze one row, two rows. I can also select the columns to freeze. If I want to freeze two rows, that will allow me to freeze the two rows. And I don't have to actually select them. I can just go under the View and do the freeze. If I don't want to or want to remove the freeze, I can select the no role is and that will allow me to just have no rows that are frozen. So that will allow me to remove the freeze. If I want to freeze row six, I can freeze up to row six. So that will freeze everything to row six. Again, if I want to remove it, I can unfreeze the rows. If I want to freeze the first two rows, I can select the row that I want to freeze up to and select freeze up to row two. So that will allow me to freeze multiple roles or as many rows as I needed, with all of the rows at the top being frozen up to whatever the selection row that I've made. Also under View, I can update the view or I can remove it, the formula bar. If you're not using formula within your spreadsheets, you can remove that from the view that will give you a little bit more space. Also the grid lines. So removing the grid lines between the cells. Usually obviously because it's a spreadsheet, we do prefer to keep the grid lines in and then of course the formulas are optional. You can also show the formulas or hide the formula bar. So let me add in some formulas here. Here, we'll just do a sum of the value of e plus 100. That's our formulas. And also, when you do create a formula, it will have the suggestion for autofill. So this is an example of where it's making a suggestion of auto-filling the formula all the way down. So you can either accept it with the checkmark or reject the suggestion. And then that way it won't auto-fill the formula. By selecting it. You're gonna see that it's showing all of the formulas that we're using. That's because the view is set to show the formulas. If we don't want to show the formulas, we only want to show the results that we can uncheck that. And now we're back to seeing the results, which is the default view under the show. There's also the protected ranges. If we do have certain ranges that we want to set as protected ranges, where we're selecting some content, right-clicking it, and then selecting it as a protected range. You can enter in a description set the permissions who has options to edit that content, who also show different warnings when they're editing that range. This gives you the list of the users that currently are able to edit. So if I want to remove another user from being able to edit that range, that I can do that as well. And that will lock the range from that editor that other user being able to edit. So under View, if I go to show, I can show and I can see the protected ranges on the screen. And the protected range that actually is going to be for myself. Let's set permissions. So I'm gonna remove, we're just going to show a warning when anyone's editing this range. So again, going back to show, and let's show the protected ranges. And the way that the protected ranges will be identified within the sheet is this strikethrough pattern that this indicates that this is a protected range. And if I do try to edit it, it's going to give me a heads up warning that I'm trying to edit a part of the range that has been set as a protected range. So this is a way that you can set content. Also let others know that certain content should not be changed. If you are using protected ranges. It is also a good idea to set the view for the protected ranges. By default, it's not going to be set. But of course, if you do try to edit them, you're going to still get that same warning message that you are editing of protected range. So they actually work independently even though you're not gonna be able to see them, but you're gonna be able to have the results of that protected range, regardless of whether you can see which part of the spreadsheet as protected or not. We've already covered the freeze, so we're able to also freeze columns. If we wanted to freeze the first two columns, we can do that as well where that's freezing the first two columns and unfreezing. So going back under View, there's also an option to group. What group will do is if you don't have anything selected, it'll ask you to group the row, group the column, and select what you want to group. And then you can also right-click it. And under the View more column actions, you can group the selected columns. Because this is a protected range. I'm gonna get that warning message. When I group them together. Then that also gives me this icon where I can hide and I can show them from the view because now they're grouped together. I'm actually going to get rid of the protected rain. And there's the protected range. Let's remove the protected range and also remove the who can edit the specific range. That's gonna remove out the protected range. And now I'm not gonna get that error message when I'm grouping column B and C together and hiding and showing them what grouping does is it just allows us to keep those particular columns within the same grouping. There's a few other items that I wanted to cover before we conclude. There is also, whenever we go view more cell actions, you can randomize the range. So what this will do is this will update the order of the items in the range. I'm going to just create a quick set of data from one to ten. We'll select that data, go under the View more cell actions, and we're going to randomize it. So what randomized did it basically the selected Arrange at randomize the items in the selected range and reorder them. And if you select this again, you can again do the randomizing. And that will randomize the items of that selected range. To undo it, you can just do a Edit, undo or Control Z. And that will allow you to undo it and bring it back to the original order that you wanted it within. There's also under the View more cell auctions. There's define named range. If you want to have it as a named range, then you could reference it as needed. And we're gonna look at that a little bit more later on. That when, why we'd want to name a range so that we could reference it within other parts of our Google spreadsheet. If you have grouped columns and you want to remove the grouping, you can right-click. And this will give you an option where you can clocks column group, remove the group, expand the column groups, collapse all group column groups. And then you can move it using the plus or minus button to the right. Let's open that up and actually going to move the button. So just moved it over slightly. You can also move it back if you want. You can open up that and you can remove the grouping of that selected range. So those are some of the options that you have in order to view the content and as well, once you've selected, Arrange some of the options that you have to interact with the range content. We're going to look at the data validation and conditional formatting in the upcoming lessons. There's also the sort options. So let's select the range that we want to interact with. Go down to view more cell auctions and do the sort range. When we select the short range at selects the range that we want to sort from. The data has a header in the row. This case because we've selected it within the mid part of the rows, we're not selecting the top row, so we don't need to check that off. They'll give you an example of that as well. Checking that off the sort by column. So column G, and this is the column that has been selected. So we can select it and sort it either ascending or descending. So if currently it's going to be ascending, so let's sort it descending. You can also add another sort column. So if we want to sort also an additional column, so let's add in a sort the second column that we want and we don't actually have the selection of that. And I'll show you how you can select those later on. Let's go ahead and do the sort. And now when we look at it, what happened is that we sorted that selected range. If we want to, we can sort the entire column. And if you go over the column, there's gonna be a drop-down arrow and selecting that will give you the same options where you could copy, cut, paste, paste special, and you can insert columns, you can delete the entire column, you can clear the contents, you can hide the column, you can resize it. And then in the middle there you've got the sort options from Sort a to Z, so that's ascending and descending. And then there's also the same options that we saw before, where we've got a few more column actions. And this gives you the ability to freeze up to column G. So it'll freeze everything up to column G. You can group the column, convert it to links, remove the link, open up the links, get links in the range. And then you can also randomize that selected range, defined the named range. So there's a lot of options there. Let's go ahead and we're gonna go with the sort. And what the sort does is it automatically sorted it. But notice that it did keep the header because it was smart enough to know that this is the header. So it kept the header and even though it's sorted it from the top down, it kept the header. And even we had sorted and selected that entire range, selecting the column that you want to sort. And then going up at the top, you've got an option under data where you can sort by column G, a to Z, or column G ascending or descending. And then in addition, you can sort the range. Right now we've only got the range, but if we want to sort the entire sheet, column G or the selected range, we can do the sort. And this will actually sort it. And notice what's happened here is that we ended up sorting the headers. So we definitely don't want to do that. We want to keep the headers as is and sort everything according to what we've got within the selected column. So let's go back to it. Let's select multiple columns within the range. And then we'll do the applying the sort where we're sorting the A12Z. What we want to do is we want to sort the entire selection. So we've selected all of the cells we want to sort by and under date data. So we're not going to short the sort the sheet because this will sort everything including the headers. We want to go to Sort range and then select the advanced range sorting options. And here we've got the option for data has header row. We want to make sure that we're not assorting by the header row. And once we do select that, we have an option using the headers in order to select what we want to sort by. If we want to sort that column G, which is identified with the header of numbers. And then now we can do it either ascending or descending. So let's select it descending and do the sort that actually sorted all of the content, but kept the headers in tact. And that's one way that you can make sure that you're not moving your headers when you're doing the sort, select the columns that you want to use within the range that you want to use. And those columns are the ones that are gonna be affected by the sort. And then you have the option once you select the headers, to use those headers, the sort order for the rest of the sheet contents. And another way to do that is to select the top row and just do a frieze of the first roll. And this way, whenever you do the sort, it's not going to be sorting the rows, it's just going to keep them as frozen. And this will give you that option that you can simply select and do the sort. So that's another way that you can make sure that your headers don't get sorted. And this is another way that you can tell your spreadsheet how to behave when doing the sorting. 9. 8 Format Cells in Sheets: This lesson is gonna be all about formatting of cells and all the different really cool things you can do with the formatting where you can angle contents of the cells. You can update the different formats. So different number four months, you could add in decimal places. You can select fonts, different sizes of those fonts, bold. You can set the fill colors as needed, and there's also borders. So the different borders that colors how to adjust the type of border. There's merging of the cells if you've selected multiple cells. And then also there's under the More options as horizontal alignment, which aligns it either to the left, right, or center. There's a vertical alignment. So top, middle, or bottom, there's how to handle the text wrapping. There's text rotation. So this allows us to tilt up till down and also customize the angle of the tilt. And these are just really neat ways that we can manage some of the content and how the content is being displayed within the spreadsheet. So that's all coming up in this lesson. In addition, we are going to be looking at Format and conditional formatting and how we can apply conditional formatting to cells that are matching with their content. The conditions that we're applying and looking at for those cells within the range. This lesson we're gonna be looking at how we can format cells. So once you select a cell or you can select a range of cells, you have options for formatting the contents of those cells. You can go up to the format tab at the top. And from there you can select the number. So if it is a number that different number types, you can also select numbers as plain text. So let's go ahead and we're going to add in some numbers. And also once with some decimal places as well that we have some variety of numbers that we can work with. And then going down to the cell, I can drag it and add additional cells with additional content. This way, we've got some cells that have number formats. So let's select both of those cells that have the number format school over to Format. And we've got an option to do it as plain text. If we update it as plain text, we're no longer gonna get the sum at the bottom. So these are now considered text numbers. And if we drag them, it's not gonna be able to distinguish what the next cell value is because now they're gonna be treated as text. There is a disadvantage if you do have numbers, do treat them as numbers and make sure that they are formatted as numbers in order for them to be able to behave as numbers. So we've got the different numbers, so automatically it will detect the number. So when we do select a number, we can select the format of those numbers. Going over to the format. When we do treat it as a number, we see that we did add in the dot 0 is 0. So we have an option to update the number of decimal places that we have within the formatting of the numbers. We do also have an option, a quick option here within the quick menu, where we can decrease the decimal places and we can also increase the decimal places. There's also a drop-down for number formats. And then there's a few quick number formats such as percentage that we can add in. We can also update it and set it to dollar amounts. So currency amounts. And then again, we can go back and we can decrease and increase the number of decimal places that are available within these cells. So let's go back in where you just going to report them back to the numbers. Now that they're back as numbers. And you can always tell that they aren't numbers. When you hover, when you go over, you make the selection, it's going to automatically do a sum of those values. So that way we can always be sure that they are numbers. Let's go down and select more number formats. So we've got the currency rounded. So what this does is this rounds the currency. If we were to select that and do the rounding of the currency, it will just round it up so no decimal places for the currency. Let's go back and update those again. So we can also have it as a date and a time, or we could set it as a custom currency. So I just selected the custom currency by default, we're using the US dollar, but there's all the different worlds currencies that we can use. If we wanted to change it as chinese Yen, we can apply that. And that's going to automatically apply and add in the currency symbol as well into the values. There's also formatting it as a date. When it is formatted as a date. Let's center it in a number. And I'm going to select to format that as a date. It'll automatically try to make a date out of the contents. In this case, it gave me dates in 5514, so let's move it back down to 2010. And now we've got a date format. Once it's within a date format, if you double-click it, you're gonna get a calendar popping up. And that way you can navigate and select the specific dates and increase them and decrease them as needed. If you do a drag down, it will automatically increment the days by one. You can change the format of the date format. You can also include the time, so update it to the time format. It can be date and time formatted. And there's also a duration that you can set it. So this is gonna be the number of milliseconds that it's going to be applying that duration to. Also under the format. Let's switch it back to the date format. Under also formatting, we can apply different boldness, italicize, do a strikethrough of the content. And we can also update the text color. So there's the custom colors that are for the theme. We can add in additional color using the color picker, adjusting the hue and the hex value. So that way we can select and create a custom color. And there's also the default colors that we can use. We can select any one of those default colors. Those are also available when we go to Format under text. So there's the bold italic underline and strike through. And there's also fought size. So this font sizes as well available within the quick menu where you can select and you can adjust various font sizes to those. Selecting the different colors. That's all from the quick menu in the color picker. If you do want to reset them back to their original, you can clear the format by going to the format and clear formatting or doing the shortcut, the keyboard shortcut in order to clear the formatting. And this will automatically revert all of the formatting back to the original formatting. There's also alignment so you can align left, center, and right, and there's also top, middle, and bottom. So let's do a center align. It'll make the cells slightly larger height. Now let's do an alignment to the middle. So do select what you want to do the alignment and then select it as middle, so that will automatically middle, center it. There's also the wrapping. So you can do an overflow where you can cut off the overflow. You can rob where you can clip the overflow. So let's update and add some overflow content into the cells. This one is overflowing with school to the format and where we can wrap it. So it automatically wrap to the next line. And we can also clip it, which is the default. And let's go back to the wrapping and the overflow. That's how you can manage if you've got content that's overflowing. And sometimes it's a good idea to make sure that you're overflowing according to how you want to output it. And if, especially if you've got a lot of content in here that could potentially be needing to have the customized rapping. There's also a rotation. The rotation what that does in the format, you're able to tilt it up. You can also tilt it down. You can stack vertically and you can rotate up stacking vertically. So what happened now is that this tax content got stacked vertically instead of horizontally. There's also where we can rotate up. This way, you can get this type of effect where the content is rotated up. There's also the rotating down. So that will just rotate it down to the next 90 degree. You can also set a custom angle with the customer angles. There's several different predefined custom angles that you can set the text stat and make that selection and update the contents of that cell without customized format. There's also for the font size. Font size usually, typically it's a lot easier to just do it on the quick menu where you can select the size. You can also enter in a custom size if you don't see it within the drop-down. And in order to take the custom size, just press Enter and that will set that value at the Custom Size. And then you can also get the predefined sizes. You can also do alternating colors. This is a nice way to be able to better read the sheet content, where when you select alternating colors, what range you want to apply the alternating colors to, the different styles for header and footer. And then there's also the different color themes that you can do. This just makes the content a lot easier. You can customize how you want the header and the color one, color two. So these are just gonna be alternating colors. You can add in custom styles. You can customize the way that these styles are going to look. You can also update the header to match the style that you want to use. There's a lot of really good options here. So typically what you'll do is you'll do a lighter and darker shade so that it is easier to read and then doesn't ruin the flow of the colors. You can also remove the alternating colors by selecting it and doing it under the format as well. Then at anytime you can just select everything and you do a quick clearing of the formatting. And that will remove all of the formatting that we've actually added in. There's also an option to merge cells. So you can select the range of cells that you want to merge. And you can do a merge of those cells. Once you've selected the range that you want to merge under the Format, select Merge cells. So you can merge all of the cells. You can merge them vertically or you can merge them horizontally. If you merge them all, that's going to just create one big cell of content. And it leaves us with the one test six cell. I'm just going to revert it back. So it's automatically gonna take that top cell content and overwrite that other cell content. Let's also see what else we can do for merge, where we merge vertically. So only preserve the top leftmost values. So if we merge any way, it's going to only serve that top-left value. So let's revert it back and then do one more format are going to emerge vertically. So again, it's only going to keep the top leftmost value. If we merge, it's going to be removing those, some of those values that might've been there. You've gotta be really careful when you are doing the merge that you don't want to be inadvertently overriding some of your content that you need within your spreadsheet. So these are just some of the ways that you can update the way the content is viewed. There's also what you can do as well. So you can have options for borders. This is as vowel available within the quick menu, selecting the borders. You can have all borders. You can have inner borders, horizontal borders. These are the horizontal borders, outer borders. You can remove all of the borders. You also have options for customizing the color of the borders. This is a good way if you want some content really to stand out. There's also the border style that you can adjust. And if you do the drop-down, you've got the merge here as well within the quick menu. You also have the texts rotation that we just looked at. So that's texts rotation. So you also have that within the quick wrapping, you have the vertical alignment, you have the horizontal alignment, the text wrapping, all under the More option. In addition, you also have a way to change the font. If you want to use a specific font, you can select from your recent fonts, from your system fonts, and then set those as the fonts that you want to use. And then of course there's also the font size. So you can adjust that and that's going to adjust the way the fonts are being output. Once again, if you want to get rid of some of this, you select the range of cells, go over to the format. You can clear the formatting, or you can select the specific formatting and adjusted individually as needed. In this case, I'm just going to remove the alternating colors that just removed the alternating colors from the content. There's also, if you go under Format and select theme, this will give you a theme for the formatting. So you can set the specific theme for the formatting and this will make some adjustments to the formatting. So in this case, what it's doing is it's updating some of the font styles. Also, if you have some colors that will update those colors as well, according to what the theme is. You can fully customize this theme as well. So selecting the Fought theme. And there's a whole bunch of options down here on the right-hand side with the colors. The text color is the oxidant colors. There's a lot you can do with the themes. And as well when you're customizing the theme. This is a customized theme which is available under the Format option. There's also a conditional formatting. Conditional formatting is formatting depending on a condition. So we've seen all of the formatting which is automatically done to the cell contents. If we select conditional formatting, it's going to open up the format rules. And here once again, we need to select the range that we want to apply the formatting for. And then the format rules that we want to use, there is a drop-down for the Format Cells if so, to give you a bunch of options here. So if it's empty, not empty contains text. Text does not contain starts with date. So this is the date value if the format is as a date. So this is date is before, after. Then for numbers we've got greater than, less than. So let's do the greater than. And we're going to apply this. If the value of the cell contents is greater than seven, then the formatting what we want to apply, it's just got a default there so we can make it bold, italic. We can update the text color, and also even the filth filled color as well. Let's update the text color. Then once we've set this as a rule and we hit Done, That's gonna be applied to the cell contents. If we want to apply that to other selected ranges, we can do the conditional formatting and select the range that we want this to apply to. We can also select Formatting, Conditional Formatting that we've already set under the conditional under format. And if we go to conditional formatting, it will open up the existing rules. So we can, from here we can delete it or we can edit the existing rule. So if we want to update the range and add another orange here, Let's select the range from the cells directly. And even with the pop-up, were able to select the range. So now we're not only going to be looking at E4 to E12, we're gonna be looking at F12, F21, and hit, okay. Now this format is being applied to it. Let's update this to a value of three, where it's doing this. And we can also apply another, a second rule. So applying it to that same format. And if it's greater than five, Let's customize what we want for the output. They've got some options here for his defaults. You can select this as a default. Let's customize some of this. Setting it to orange, purple and hit done. And taking this rule. So it's purple if it's greater than five, but this rule is already in place. So if we want it to be purple and we want to be read that we need to update the order of the rules. And you can do this simply by dragging and dropping the order. Whenever you're hovering over it, you see you've got the four dots. So this gives you an option where you can drag and update it. And whatever rule is at the top is the one that's going to over, see the ones that are below it. So if cell is not empty, it's gonna get a green color. And if it's a value of five or greater than five, then it would be purple. But because this rule is the top rule, there all have values. This rule gets applied to all of them. So this type of rule, if we're applying to all of them, we want to keep it at the bottom because we want the values that are greater than five to be purple, we need to have that rule at the top. The second rule where the value is greater than three comes into effect on the remaining ones that weren't true for this rule. And then the last one, we can also add additional rules as needed as well. So that provides us ways that we can apply conditional formatting depending on what the contents of the cell are. So that means that if we were to update these values within the cells at any time, it would also change the and updating the formatting of the contents of the cell. 10. 9 Google Sheets Filters: So we're gonna be looking at filters and the exercise in this lesson is going to be removing out empty rows of content. So we've got a few filters that we're gonna be applying. And I'm gonna be showing you all the different options that you have for filters. So right now we do have a filtered view. To remove the filter, we can just hit the X that's gonna go back to the previous view. And if we had some empty rows of content that we want to get to get out of our content. Using filters is a great way to select that content and then do the removal of that content just by using the filters. Now that we've got the content, we want to select all four of those columns. And I want to remove the empty rows of content. So going over to data, we're going to create a new filter view. And that filters are automatically applied to the first row. So I can select any one of these because we just want to remove out all of the empty rows. So I'm selecting that and I'm using filter by condition. I'm selecting the ones that are empty. So if they are empty, it's going to return back all of the empty rows of content, which is 369. And now I can select those rows. I can delete the selected rows. I can remove the filter and my contents now have removed the blank line that was within the content that I have within the spreadsheet. I'm gonna be showing you that in a whole lot more in this lesson. I've got a spreadsheet with some blank lines within the rows of the data. So what we wanna do is we want to filter out that content. And what we want to be able to create a brand new filter with the data content. So going over to the datatype, select the Create a filter. You can also see the filter views. And from here you can create a new filter view as well. We're gonna be creating a brand new filter. So let's select the filter. And the filter automatically gets created on the range of cells that we had selected. So I had rose 111213 selected. And once we get the filter, once we set up the filter to create the new filter, we're gonna see that we've got these drop-down menus here in the top cell and the top role of each one of the columns. And here we can select how we want to filter the content. Going back into the tools and data, going back into data, let's go ahead and we're going to remove the, remove the filter. And we're going to select the filter. We're going to create the filter on all of the existing data. When you do create a filter, anyone with access to your spreadsheet will be able to see the filter as well. And anyone with permission to edit will be able to edit and change the filter. Let's create a filter. Select all of the columns that we wanted to use. Then under the data, create a filter. Once we've selected all of the columns of data where are automatically in the first roll, we're gonna get the filter options. This is where we can apply the filter conditions that range. So we click the filter. We have an option to sort, sort, ascending or descending or sort by color, filter by color. So if we had different colors, filter by condition, we can enter in a condition that we want to filter by. Let's do that where we're checking to see if the texts contains my last name for the filter. If the pop-up window disappears, then you can always go back and select the filter and the first row. And this will give you the pop-up window once again, how you want to sort it. So if we do select the sort that will sort the content, It's not going to affect the header, it'll affect all of the contents underneath. Effectively that actually did remove the space rows. Let's undo that. And the same thing if we sort ascending or if we sort descending. They're both gonna remove out all of the spaces. We don't have any colors on a jet, so we can't do any filtering by colors. Let's try filtering by the condition. Will check to see if it is empty. And apply the filter. And the filter we're gonna be applying is gonna select all the contents. Let's go ahead and we clear the filter and hit Okay. So now we're only going to be seeing the cells that are empty because that's the filter that's been applied. If we want to see the ones that are not empty, we can apply that filter. And now we're only going to see the ones that are not empty that are matching that condition. Let's add some text color to the cells and will update the filter for colors. Let's update the filter. And we're going to filter by color. So we have an option to filter by text color. Let's filter by only the text color of red. Filter by text color of green. Remove that filter by none. So that will show everything once again. We still have the filter for the condition. If we remove that, we're gonna go back to seeing all of the available contents. We can also filter by the background color. Let's say we've got some content that's highlighted. And we want to filter by color and by the fill color of yellow, then we can select the content that is only of yellow. You can apply multiple filters. So we've got a sort by, we can do a sort by color as well, by the text color that will sort the contents by the text color. That will sort it. All of the contents from a to Z that are within the filtered contents that are being returned back. Let's also apply multiple filters will provide filter by condition. So we want to make sure that the text contains and the value that is contained by the text. So we want to make sure that that is contained within the filter. Hit Okay. Now the text has to contain spikiness. The inside can return back anything that doesn't contain that. Let's filter it by values. Here we can clear out all of the values. So that's could remove out all of the filter values. We can select all of the available filters. If we want to search ones that contains specific values, we can do that within the search. And we've seen now it's returning the matching ones that have the value of two. We can search for a particular value and select only those values to filter the content by. We can clear all and then type into the search the one that we want to select. Click on the ones we want to select within the filtered content and hit Okay, and that's gonna return back only the ones that are gonna be matching within the filter. If we do want to remove the filter, we can always go to Data and hit the remove the filter and it'll bring our view back to what it previously was, back to the normal view. We can also create saved, delete, and even share a filter view. I'm just reverting back with the spacing. And we're going over to the data. And I got to remove the filter that we were just working with. Then going back to data, we're going to create a new filter view. This new filter view, you can give it a name. This can be test to filter. We can select the range for the cells that are gonna be contained within this filter. If I wanted to select everything, I can select from 81. So from the first cell that we want to work with, and that's actually going to be A2, because the first row is going to be where we've got the headers and then going all the way back down to D15. That's the view that we wanted to apply. We can select the options, which is the COG on the right-hand side, which allow us to rename. And that's what the tab as well here that we did within the input field. We can update the range, and we've already done that by clicking on it. We can duplicate the filter and we can delete the filter. There's also more options with the filter. Once within the filter view, I'm gonna make this a little bit smaller so we can see the view. And there's an option here to either show the menus or hide the menus. You can use the shortcut for that as well. This is just more space that as I'm doing the recordings, so it's a little bit easier to see the content. We're returning back. We've got the filter views. The filters are on each one of the first items within the column. For the data, we've got the range surrounded with a green border. And now we can select the filter. We can apply the different filters. So we had the one for the text color. So this will return back only the ones that match the right text color. There is actually an error here because we did want to apply the filter to the contents and we want to have the first row as the header. So by default within the filter, the first row is the header. Let's update the range. And this way we're able to apply the filters to the contents and not to the headers that we have within the first roll of the content. Now we're filtering by color, by text color. Let's go back to none for the filter. And under the filter conditions, we can check to see if this column the contents contains. Let's check for it to contain the word test. And then apply the filter for that so we make sure that there is some content there. If we want to remove out filtered content, let's select all of the rows that are empty. If it is empty, we want to return back all of the rows that are empty. And so if we want to remove out the rows that are empty, now we're only showing the roles that were empty. So it doesn't matter if we have 15 records or if we have 5 thousand records, it's going to return back all of the empty ones. So we can select these and I can delete the selected role is. And now I can go back to, back to the filter views and go back to none. And the result is gonna be that we've removed at all of the empty rows using the filter. That's one of the ways that you can use and make use of the filter in a powerful way that you can select the content that you want. And then there's, there's content that you don't want. You can select the Delete to remove it. And that's one way that you could remove the empty rows. And if you ever want to go back to view the filter view, you can see the filter view here. And we see that we don't have any empty rows because we did do the delete on those empty rows. Another way to close the filter view is just to click the X, and that will also close the filter view. The filter view is saved automatically. We can also, if we wanted to delete the filter, we can go to the filter views. We can select the filter. And then under the cogwheel we can delete the filter. And now if we go into data and back to the filter views, that filter is going to be gone. If you want to create a new one, we have an option to do so if we want to duplicate the filter view and if we wanted to make some adjustments to the filter view. So in this case I'm going to duplicate that existing filter view. We can rename it, we can re-select a brand new range. And then whenever we want to flip back to that view, it's going to automatically be saved within our filter views that we have. If we want to share a filter view, we can go under the data, select the Filter view that we want to share. And that's gonna be available within the URL. Once we copy this URL and if we share it to somebody else that has the same web, the access to the Google Sheet. They're also going to be able to see the filter. And this is at the end here is gonna be where it's indicating the filter view. If you get rid of that last part, the last part of the URL is where it indicates the filter view. So when we refresh it, and we don't have that within the web URL. We're not gonna see the filtered view. But if we do have the filtered view, and that's gonna be part of the web URL. So now if we went over and if we shared that view, it's automatically going to go into the filtered view because of this last part of the web URL. So that's how you could share your filter views. What filters do is they help you analyze a set of data within a spreadsheet. With filters, you can share a specific view with others. You can also sort data within that filter. Fir filters. We saw that we can apply multiple filters. We also had options where we could name the filters, update the name, update the filters. I make coffee is of that filter with the same rules. In order to be able to better share that set of data. You can see the filters that have been applied. Whenever you go into the first top row, you can select that and that's where you're gonna see what filters are applied. So these particular columns don't have any filters applied yet. And you can apply different filters on the different columns as needed. I'm going to go back to none and hit Okay. I'm going to filter it by the text color of black. Here we won't have an option. So if I had an option of red text, I can apply another filter here to filter it by text color of red. So we can apply multiple filters to the different columns of data as needed. Go ahead and try it out to become more familiar with what you can do with filters. 11. 10 Protected Sheets and Ranges: This lesson we're gonna be covering how you can hide sheets, unhide sheets, and also protected, and how you can protect sheets and ranges within Google spreadsheets. If you don't want people to change content in your spreadsheet, then you can add protection to it so that you can protect a particular sheet or a specific range within the sheet. So let's go ahead and we're going to create a copy of the sheet. And then we're going to protect this one that will prevent users from being able to update the content within the protected sheet or range. So going under the data tab at the top, there is an option where you can select the protected sheets and ranges. So they make a selection of that. In order to protect a range, click the range to protect. So if for instance you want it to protect the name, this range of names, you select it within the view area and then click the Add a sheet or range. You can enter in a description. And this will protect the range. It'll give you the protected range down here. So it will be protecting B2 to be 11. Once you've selected the range and you've given it a name, you can set the permissions. And that will allow you to restrict who can edit the range. You can also set to show a warning. When editing this range. You can add editors as well. It'll give you the list of the current users who can edit the document. From here. You can unselect them if you don't want them to have the permission to edit that range, you can't uncheck the current user. So you can't restrict yourself with the current account from editing the range. You can also select the warnings when this is being edited. And once you hit done, that will change the values. And now if anyone tries to make an edit, it's going to pop up this message that says that we're trying to edit content that is within a protected range. You can hit cancel. And that will revert the cell back to the original. Or you could hit Okay and accept the changes on that range. In order to protect the sheet, you select the data protected range. That's going to open up the pop-up window. Where once again you can select either a range or you can select a sheet. It gives you the option of the two sheets that I currently have within the spreadsheet, I'm going to select the protected sheet. You can also accept certain cells. So if you want to keep certain cells within the sheet editable, then you can select those cells in order to select the range. Select the range finder, then highlight the range within the spreadsheet. You can have multiple ranges, so you can add additional ranges that can be edited within the sheet. Once you've completed the ranges and added the ranges that can be edited in the sheet. Just hit Okay. And from there you can set the permissions. And once again, we get the option to set a warning or restrict the edit to particular users. And we can customize this as well with the users. And we can also add additional users if needed. Once we've completed this pop-up window where the range editing permissions we hit Done. And that's going to make the changes to that protected range. If you do want to open that and if we want to make any updates to it, you can select the protective arranges. And then from here, you can select those ones that you've protected. If you want to change the permissions, you can also restrict. So I'm going to remove my other account and then a login and I'll show you that it is now protected. And I'll also update this other one here where I'm selecting and I'll remove out that other account completely from there. Now going into the other top or I've logged into my second account, Let's see what happens if I try to make some updates. It tells me this error message that there was a problem. You're trying to edit a protected cell or object, please contact the spreadsheet owner to remove the protection. You're not actually able to make any changes to it. So even if you select it, if you try to make any changes or edits to it, you're going to get that pop-up window. So that's what the others are going to be able to see if they tried to edit content that you're protecting. Now there's another option to set the protection on the sheet so you can select it from the sheet tab at the bottom. And there's a dropdown here. With a pop-up menu where you can select the protect the sheet. And this will open up that same window by default going right to the sheet with the sheets selected. And then from here you can enter in a description are just going to call it pro two. And we'll set the permissions on the entire sheet and protect that sheet as well from others trying to access it. So if the other account tries to use it, they're going to once again, that get the pop-up window. That's saying that they're not allowed to make any edits to that content. That's how you can protect a range or a sheet. And the person that can do the protection is the one that owns the spreadsheet. If you can edit a spreadsheet, you can decide who can edit the ranges and sheets, but you can't take permissions away from the owner. Permissions should be set by the owner of the spreadsheet. But the editor as well has an option to set the permissions. If we were to select the sheet, if we wanted to remove it, we can select the protect sheets and range, and we can select the Delete to the right of the name. That will allow us to remove the protections on the sheet. Now going into the second account, I can add in protections, but I can't protect the sheet from the owner. So if I added it in to protect it, I call it pro five, set the permissions. I'm not able to actually remove the owner because this account is just an editor and it's not the owner. So the permissions can be set, but they can only be set and they always have to include the owner. The owner can never be removed from protection from editing their own content. So let's go back and I'll delete that and go back to the main account. So changing the tab back to the main account. You can also hide sheets from view. So going down to the sheet and selecting the drop-down, you can select the Hide sheet. So now the sheet is not gonna be hidden. And all editors as a spreadsheet can view and unhide the sheet. So once the other person comes in, they're not going to see the sheets down here at the bottom. But if they do want to view the sheet, and the same thing for the main account. If you go to View and hidden sheets, then you can show the hidden sheet. Because this sheet was actually hidden by the owner. They can select View and then show the sheet once again, and then the other account as well. We'll be able to see the sheet. They can select the sheet and hide it. But if they don't have the if this is a protected sheet, they're not gonna be able to hide it from the owner. The same as editing it. They're not gonna be able to hide it. Because Sheet1 isn't protected. They are able to hide it. If the owner goes in and they go under view, I'll just refresh that and then go under view. We can see the hidden sheet and then we can show that hidden sheet. So that way you can hide and show sheets as needed. And also do the protection and the protected ranges as needed. Within Google Sheets. 12. 11 Sheet Options: In this lesson, we're gonna be looking at the various sheet options. So going into your Google spreadsheet and down at the bottom you are, you've got the sheets. There's a quick pop-up menu that will allow you to provide some options that you can do with the sheet. We did see them in the last lesson, or you can hide and protect the sheet here as well. If you want to delete a sheet entirely, you can select and you can delete the sheet. So I'm gonna go ahead and delete the sheet that we created in the last lesson. There's also duplicate the sheet. This is a quick and easy way to duplicate it. You can also rename the sheet. So by default it will say copy of whatever the original sheet name was. If we wanted to create a brand new sheet, I can always rename it to a different sheet name. There's also a copy to this allows you to actually copy the spreadsheet to an entire new spreadsheet or into an existing spreadsheet. So the new spreadsheet will create a new spreadsheet. And that will create a brand new spreadsheet with the data from the sheet. So now if I go into My Drive, I've got a brand new spreadsheet that's just called untitled spreadsheet. Once I open that up, that's gonna be the contents of sheet to sheet two is still gonna be named sheet two. And it's gonna have the same content that we had within the main sheets. So this is creating a brand new sheet. If I wanted to take the sheet and if I wanted to copy it into an existing spreadsheet, selecting that will give me a drop-down of all of the sheets on my drive. So I can also go to the recent where I can select that new sheet that we just created and open it. And now that Sheet1 is also going to be copied into the new spreadsheet that was just created. And also noticed the name of the sheet is going to be copy of Sheet1 because we're making a copy a sheet one in the second spreadsheet. So you can now select that and to rename that. And if you want to be called Sheet one, then you can do it that way. That's one way that you can copy sheets and it's a quick and easy way to copy content from one sheet into another. You can also select the change color. So that gives you a way to select and add colors to the sheet. Another way to distinguish the various sheets. If you wanted to have a color scheme within the spreadsheet, this is the way you can add more colors into the sheets. Just using the sheet names, you can provide different groupings to the contents that are contained within the sheet at anytime you can select and you can even add custom colors. And the custom colors allows you to select either the hex or through the hue, the various different colors that are available. So you can customize that and have the color that you want for the sheet to represent the sheet. There's also the Protect Sheet and Height sheet, and there's also the move right and left. What this will do is this will move the sheet over to the right. And now we don't have another option to move it to the right because we've already moved as far right as it will go. We can also move it to the left, and that will move it back to the left side. You can as well select it, hold your mouse down and drag it and drop it in order to create a new sheet. There's also a plus sign here at the bottom where you can select and that will create an automatically a brand new sheet and sequencing it with the next number in order. So it would start by default as Sheet1, that sheet two and then three. So if you created a brand new sheet, that would be sheet for by default. There's also an all sheets options, so that will provide a drop-down list of all of the sheets. If you do have a lot of sheets here at the bottom, this is one way that you can list out all of the sheets and easily switch from one sheet to another. Also notice that the color-coding is in place. So if the sheet does have color added to it, you're going to get this dot with the color of the sheet. And if you do have a lot of sheets, that this is a quick and easy way that you can identify the sheets and select them within this dropdown type menu. This is some quick options for working with the sheet. Within the quick menu of the sheets. 13. 12 Named Ranges: This lesson we're gonna be setting the named ranges. So under the data tab at the top, select named ranges, that's going to pop up the named ranges tab in the right-hand side. And from here you can add a range. And then you can use named ranges within formulas. And once they're ready to use. So let's create a range and I'll just call this values, will have some random numbers and then we'll attach a formula to these numbers. So let's add the named range. I'll just call it numbers. We'll select the range. So select on the sheet tab here, and then from there, highlight the range that you want to use. We can use the Select Data and from there highlight the range that we want to use or you can type it in manually. So right now it's using Sheet1. And that explanation mark means that these are the columns that we wanted to add. Once we've selected the range, we can just hit Okay, and then done, and that's going to add the named range into the values. Now if we want to, we can select that named range. I'm gonna do a formula in column F by doing the equal sign, and this will just be a sum. The sum is going to be the range that we want to use. So once we start typing it, we're gonna get a drop-down of the different named ranges. So there's the numbers range that we selected. You can hit the Tab or you can just click it in order to accept That's the range that you want to use. And then close off the brackets. It's going to highlight in yellow the numbers range and provide the sum of all of those values within the numbers range. If we go back to the range, we can always edit it. So if we want to remove out certain values from that range, that we can update that. And then that's also going to update the value within the formula because it's always gonna be using that selected range. So as this range changes, then that will also affect the different formulas. This is what you can use a named range for, and you could apply multiple named ranges as needed. And then call to those, reference those within the formulas. There's also in the top left-hand corner here. Once we select the named range, you can also see that this is a named range because it will tell us the value there so you don't have to open it there anymore. Once you select the named range, that you can select it and see the different named ranges. So right now it's named numbers. If we do want to change it, and I'll just change it to vowels. That's also going to change some selecting it and typing in a new name for it. Once I press enter. Now it's gonna be known as VALS for the name box. You can also select the shortcut in order to update the name of the box. And also notice that within the formula it's automatically going to update the new, the new name. So now this block of numbers is going to be known as vowels. You can also select the range that you want to use. And by default it will have the column and the row values. You can enter. You can select that. I'm going to call it vowels. And now if I want to create another formula and use some by starting to type, I'm going to get both of the named ranges that I have. So this is the second one, and it will indicate what the range is on sheet one, E16 to eat 11 vowels is going to be Sheet1, E2 to E11. I'm going to select the second one. Then use that within the formula. Now it's going to be referencing that second set of values. You can also select it within the formula, and it will list out the sheet that has been selected. It will list out the range that has been selected that's being used. This is a way to provide more meaning to the various ranges that you're going to have within your spreadsheet and then be able to select them within the formulas as needed. 14. 13 View options Sheets: In this lesson, we're gonna be looking at some more review options. So most of the view options are available under review. So selecting view you do have the option to show, so you can hide the formula bar, that's the bar here just below the quick menu, selecting that you can hide it and unhide it. So depending on how you're using your spreadsheets and depending on how much space you want within the spreadsheet. So if you go to hide it, and then they also have an option to hide the grid lines. So we can select and we can hide the grid lines. And that will provide the sheet with the grid lines. You can add in the grid lines once again, under the show, There's also the formulas. If you select, that will show the various formulas for the cells. In this case, we did have the two formulas that we created last time. So they're providing the formulas and not the output of the formulas. If you do want to see formulas that are being used within the spreadsheet, this is one way that you can do that. There's also under the View you can show the various protected ranges. So in this case, I don't have any protected ranges. But if I did add in a protected range, which we did see earlier how we can do that. I'm going to just call it a test and set permissions. And then done. Now, when I do the view of the protected ranges and hover over the protected range, it's going to show the protected range that there is within the p. I can once again show the formula bar and show the grid lines was commonly we are familiar with the grid lines within the spreadsheet. There's also other ways that you can get more view screen area if you're not using the quick menu, you can hide the quick menu. So you can hide the menu. You can either use the shortcut, the keyboard shortcut to hide it, or you can press the chevron here and hit up. And that's going to provide you more space and hide that short menu. You can also unhide the menu by collecting down or the keyboard shortcut, and that will unhide the menu tabs. So again, providing you more options with what you want to view on your screen. There's also a side panel on the left-hand side. So this is a side panel that you can select and you can hide or show the side panel. The side panel allows you to connect to other workspace apps and you can also add additional add-ons here. This is a quick way that you can select and integrate and move to the other Workspace. Applications. For instance, if you want to add to keep, you can make notes without having to close your spreadsheet. And you can interact with the other Google workspace ops. In order to close it, you can always hide the side panel. And that's going to provide once again more space for the viewing area of your spreadsheet content. There's also a zoom option. With the zoom option does is this is actually different from the zoom of the browser. This will zoom in only on the content of the spreadsheet. Whereas if you go to the browser Zoom, I currently do have it at 200. So I can make it smaller, but the zoom on the content itself is still going to be 200. So that will make the content larger and keep the tabbed menus the same. If you update the browser, it's going to update all of the tabbed menus as well and zoom that in. So there is a difference between the zoom of the sheet content under view and also the zoom of the browser. There's also an option for fullscreen. Fullscreen just does that where it opens it up on full-screen. So that gets rid of all of the menu options. And you can select those and view them once again by pressing Escape on your keyboard. If you are hiding the formula bar and if you are hiding the grid lines. And then if you do go to the full screen view, that's gonna give you the most optimal space within the spreadsheet. And then once again, if you do want to have the View menu back, you can either use the shortcut or you can press Escape on your keyboard. And that will bring you back to the previous state with the top menu bars. 15. 14 Insert Options Sheet: In this lesson, we're gonna be covering the options under the Insert tab. If you do want to insert cells, you can insert cells by shifting to the right or to shift down. So you can do this by highlighting the cell that you wanted to insert, where you want to insert, and then selecting under the Insert. Right now it inserted this blank cell, shifting the cells over to the right. So in order to undo it, you can always select under Edit Undo. And that will bring the cells back to the raw how it originally was. And then once again, if you do want to insert cells, you can insert individual cells within your spreadsheet under the Insert, There's also an option to insert rows. Selecting Insert Row is so currently I have Roll 11 selected within the spreadsheet. I can insert a row above that's going to shift Roll 11 down to 12 and insert a blank row above it. I'm going to undo that. And there's also an insert below. If you want to insert more than one row, you have to select as many rows as you wanted to insert. And the way to select multiple rows is you can hold the Shift key down on your keyboard and then select the number of rows that you want to insert. And then under Insert. Now if you go to role is, you're going to see that you can insert five rows above or five rows below. So the number of rows that you do want to insert, you'd have to select them within the sheet. And also that the cells and the columns are now going to be great out. I can also insert a brand new sheet from here using the keyboard shortcut. Or I can select it as a sheet. That's going to just insert a brand new sheet. So the same option that I had down here at the bottom left, where I add a sheet and it will automatically select the next number. So I have added in sheet number 34, although I've deleted them. So that's where it's going to five. If I delete this one, the next sheet that it's going to insert is going to be sheet number six. So it will keep incrementing sequentially. And even if the sheets are removed, it will take whatever the next number was that the sheets that have been inserted and part of this particular spreadsheet. Also, if you are selecting columns and you want to insert multiple columns, you can highlight the columns. Once you've highlighted the columns, the same way as with the role is, you can now insert three columns to the left or to the right of the selection. If you answered it to the left, it will move the content over. In this case, I do have a protected range. So it's going to indicate that there was a protected range. And because I'm inserting, it's changed the protected range. So let's go ahead and we'll undo that. And if we do want to remove out the protective ranges that we saw before, we can select the protected ranges, and then we can select and delete those protected ranges. I'm going to go ahead and remove the protected range so that we won't get the error message anymore. Also under the insert. We can insert a people chip. What a people chip is, that this is gonna be the email of the users that are selected. In this case, we throw an error because this isn't formatted to receive the people chip. The best option is usually for a blank cell and then that's where you can do the insert and you select the people chip and it will show you the people that currently have access to the document. So I've got the two accounts here and I can insert them using either the people chip or I can select it and type in the odd symbol. And that's going to have the same pop-up window as the people chip and allow me to select the people that are currently have access to the spreadsheet. And it will insert it according to the user's name within the contact list. And if you hover over it, it's going to do the users card where you can open and get additional details for that user. So it's not gonna insert the email address, but it will use the name. And if you hover over it, That's where you can see the email address that's being used. Just like any other content within the spreadsheet, you can select those and delete those. There's also a checkbox. So in some cases when you are creating spreadsheet content, you might want to use a checkbox. So the checkboxes just so up as checkboxes. And when the users click them, they can either uncheck them or check them. Notice that within the formula it will have a Boolean value, so that's either true or false. If it's checked, that means that the value is gonna be true. If it's unchecked, it's gonna be false. And you can select the cell to see what the value is. If you select on top of the checkbox, then it's going to automatically toggle checkbox. If you do want to see just the value, you have to click anywhere on the cell, but not on the checkbox itself because check-in clicking on the checkbox will automatically toggled the content. There's also a link option with the link option does, is it allows you to add in some text. Then you can link to other parts of your content. You can search or paste a link in here. So let's go ahead and we're going to search for a link. So I've got a ten. That's gonna be the cell over here. I'm going to select it. And now that is going to link to the ten of the sheet, which is gonna be the cell with the contents of a nine. It's not looking for the contents of the cell, but it's looking for the a nine value. The value is how it's reading the sheet. You can also copy the link where you can edit the link. Or you could remove the link just by selecting the cell with the link. Let's go ahead and we're going to edit the link. We can also select the range of cells that we wanted to link to. So let's do that where we're going to select a range and we'll select Sheet number two. And this is gonna be the range that that link is now going to link to. Now whenever we click it, it's going to take us to that range. So this is an easy way that if you have a lot of content within your spreadsheets, that you can switch from one sheet to another and gets selected cells and content and be able to easily navigate to it. So that's where you can use the links. If you want to remove it or update it, then you can just select the cell. And this is where you can copy, edit, or remove the link. And it will also indicate what the ranges that it's linked to. So here we see that it's sheet to with explanation mark in the range that it's selected. And then the value of it is gonna be test1. Let's go ahead and insert a function. The functions are gonna be the same as what we saw with the formula bar. So if we do want to produce a sum, we can select function sum. And there it will automatically populate the formula bar with the sum function. And from there we can select the various values and continue to build out our formula and provide the value for the formula. We cannot any point select and select from all of the available formulas. And it gives you a nice drop-down list of all of those formulas which we are going to be looking at later on in the lessons. So these are just some more options to make your spreadsheet content more interactive with insert. 16. 15 Insert Charts and Images into Sheets: Under the Insert option, you can also do Charts selecting it is going to open up the chart window and selecting and allowing us to chart out content. By default, we've got a setup and we can also customize the chart type that can see from the drop-down menu, there's various different default chart types that we can select from. Most commonly you're going to be using column, but there's also other different types of charts where you can do area. You can do line charts, you can do combo charts as well, bar and pie charts. Let's select the pie chart. Now we need to select the data range that we want to apply this chart too. Let's go ahead and we're going to select the range of data. We're going to add a range. I'm going to move the chart down so that we can see the data from our spreadsheet. The range that I'm going to be using for the data range is going to be the column E values. So we're going to select those values. Google Sheets file as well, try to suggest ranges. You can select the suggested ranges as well. And those will automatically populate the content column as I'll show you what if there are any errors. And in this case it says that the column two must be numeric, is throwing an error there. So let's get rid of the column where we didn't have the numeric values. And this will produce a chart because right now we're just using the values of a0. So it's representing the content within the pie chart. So if you were to update any one of these numbers, that will also update the chart and the way that the chart looks, the chart is going to be floating. So you can re-size it by the corners and you can click on it and you can move it around within the spreadsheet and place it to location wherever you want to place it within the spreadsheet. This charts can automatically generate. If you want to edit the data from the chart, you can select the chart itself. And as you hover over it, you're going to see three dots in the top right-hand corner. Select those dots and that allows you to edit the chart. You can also delete the chart. You can download the chart as either a PNG or a PDF file. If you were to download it as a PDF, That's going to automatically download as a PDF. And then of course you can open that up. And this is going to just provide the chart image as a PDF. Again, going back to the three dots in the top right-hand side, those are options to download the chart. You can publish the chart. Publishing the chart will provide you a URL that you can share. You can also use the quick links for Gmail, Facebook or Twitter of the chart itself. You can embed the chart into HTML code. And also you can update that to be interactive or you can set it as an image. So let's go ahead and we're going to publish the chart. And I'm just making it smaller. This is gonna be the URL automatically by pressing it, it's going to copy that chart data. And I'm gonna paste that into the web URL, allowing us to interact and select that chart details. Now if I go back into the chart data, if I update it and go back out to the chart and refresh it, it's going to update with the new values for the chart. Within the spreadsheet itself, the values have already updated and it'll just refresh it. And sometimes you do need to wait a few seconds and then refresh it. And this is now going to give you the new updated values of the chart. Make this bigger back again to 200% and go back to insert. You could also insert a pivot table. The pivot table you can insert into a new sheet or existing sheet. So we'll select the new sheet that we're going to insert it into, selecting the data from the pivot table. So let's go ahead and want to select the data range. Here it's providing some suggestions for the range. So you can either use the suggestions or you can select the columns than rows, that data that you want to use. I'm going to go ahead and select that and then create the pivot table. Here it's got suggestions so we can add in rows of content. And then the column could just be the values. Then the row is, can be the lost. This will create the pivot table as a new spreadsheet with the datas that we had within those columns. You can insert as a separate or a new sheet or audit into the existing sheet. Usually, I do suggest to create it in a new sheet. But it depending again how you're formatting it, going back to the chart, you can also publish the chart and then you can also copy the chart. And you can move the chart into its own sheet. Suggests as we saw with the pivot table, we can create a new sheet with it. This is just creating the chart in a new sheet and then that wouldn't you do that? You do have the options to edit the chart, published the chart, download the chart and delete the chart. So those are the options that you see within the main menu bar of the spreadsheet. When you move over to the chart, you can also edit the chart here. This allows you to customize the chart and what it's being, how it's being output. You can once again copy the chart. Then go back to the main sheet. And from here you could paste the chart. So doing a pace, we'll paste that chart back in here. And once again allow you to have the options to move it around as well as to edit it. So it's gonna be actually a separate chart now. So if we do this chart, it's not going to affect the main chart because this is a copy of that chart. And it's using the data the same way that we were within the original chart, but there are actually independent of each other. And if you do ever want to remove the chart, you can just delete here or you can delete the chart. So either way, this will delete that entire chart from the view. Also for the pivot table to delete it. If it's in a new spreadsheet, it just acts as any other sheet. To delete it. You can select delete from there. There's also images, so you can insert an image into the cell or insert image over cells. So if we've got a cell selected and we insert an image into the cell, we have several options to get images. We can either upload it from our computer, we can use the camera. We could insert it by URL so we could paste the URL. We can select from photos that we've got within our account. We can also select from the Google Drive. If we do have any images on the Google Drive, we can select the list. Then we have an option here for a Google image search. If we wanted to add in images and if we select for Google Sheets logo, this is going to show us the Google Sheets logos. From here we can select the image that we wanted to insert. And these are gonna be web images. I'm going to close that tab there. So these are gonna be web images. So also be careful as you're adding them in to make sure that you have the proper permissions to use content. So this is the same as going out to the search engine and then finding the image that you want to use. So you got to be careful with the images that you are using. Makes sure that you do have the proper permissions to insert them and use them within your document. And then once you do, you can just go ahead and insert that inserted the image into the cell. You can copy it by dragging it and copying it. If we make the cell bigger, Let's make the image bigger. If we want to insert the image across multiple cells, we can insert across multiple cells. So we'll select that same Google Sheets logo and select the image that we want to insert. And this is going to insert it across multiple cells that will keep it as a floating item where we can drag it and we can cover it across multiple cells. And basically hovering on top of the sheet content. Whereas these were directly within the cell. If we can copy them, it's going to copy the cell content. And this is over on top so much all with the charts. The image is, they're going to work the same way where you can replace the image. You can delete the image, you can assign script to it. You can reset the size. You can use alt texts and you can put the image into selected cell. If the image is in the selected cell, this is just gonna be regular cell content. It's going to act as any other content within the cell. Lastly, there's an option for drawing. Drawing will open up the drawing window within the Google workspace. And from here you can draw content. You can also do word art as well and also add in images. There's various options that you can do where you can download the drawing into various formats. You can also use the word art. You can paste content. These are just some view options where you can show the ruler, showed the guides. You can also do the snap too as well. I'm going to make this slightly smaller so we can see the entire window for the word art. So opening up the drawing and word art here you could enter in the word art. So the word that you want to use, press Enter when you're ready, and that's gonna load the word art in so you can move this around within the image, also under auctions now. So now we can download it if we want to download it as a PDF. So that's going to download the current contents of the drawing application. We can also copy, paste, duplicate. We can rotate it, we can flip it horizontally, flip it vertically. So that just flips the current content. We've got some options here to snap it to the grid guidelines as well. We've got the undo redo. We can zoom in, show the ruler that guides here as well. If we wanted to have in a line. So we've got some options for lines or you could do line arrow, elbow connector, curve connector, curve, polyline, and there's also scribble. Scribble is sometimes useful if you want to do a signature. You can select the scribble. You can also update the line width. If you are making a signature, you can customize this to be different widths as needed. There's also different types of lines that you can use. So you can use the dotted, the dashed, and different patterns for the lines. There's shapes that you can insert, such various shapes that are available. There's arrows that you can insert. These are all going to get inserted into the one image is call-outs that you can use. And these are just useful to illustrate some of the content that you wanted to use. There's also equations. There's text. If you want to insert text, you could insert texts by selecting and clicking. And then typing the text that you want to insert. And then that text then you can select, you can update, select it, update the size of it. There's more options with the texts. If you wanted to underline bold, italic, you can highlight it. You can select the alignment of the text. There's also the paragraph spacing if you want to bulleted lists, numbered lists. So all of these are available. You can also select the font style. So these are the same fonts. So you're going to have on your computer and it allows you to write text into the image. And then there's also an option too, images. So you can select the image just as we saw before, where we can go to a Google search and search for an image that we want to use and select that image. And we can bring the image into our image that's being created within the sheet. So just as we saw before, we can now we can download it if we want. Once we've created the image that we want to use, we can save and close. And that's going to add the image as a floating image into the spreadsheet. And then we can re-size it as needed. And we've got the same options where you can edit it. We can delete it, we can assign a script to it or alt text to it. So this can be moved across multiple cells just as we saw with the chart and the other images that we brought into the sheet content under the insert and the chart PivotTable image and drawing. There's a lot of different functionality here that you can make use of in order to add more visuals into your spreadsheet content. 17. 16 Comments and Notes Insert: Lesson we're gonna be looking at inserting notes and comments that's available under the insert. And you also have a keyboard shortcut for these to insert a comment or a note. Comment is used to provide additional information on the contents of the sheet. So you can select the comment. You can also comment to specific other users by starting with the at symbol. And that's gonna select the other users that you have within your address book. And also selecting other users that might be assigned to have access to the document. You can also assign a particular comment to that user, add in some text for the comment. This is going now going to be directed to this user. And now I can assign to that particular user the comment. Once the, if you want to see what comments have been assigned, you can hover over the cell. You'll see a yellow cornered colored corner that you can select. And then you can see the comment and content information. And you can see that it's been assigned to that user. You can also edit that, delete it or you can link to the comment. There's also an option to checkoff that will mark the content has done in hide it from the discussion. Also, if you go into the comment history now, that comment is now gonna be sitting within the comment history. So under that particular cell. So if we do select it, we can see where it's located. So it's within Sheet1, G6 cell. If you select the comment, it will automatically go to the cell that had the comment and highlight it. And from here, you can have the same actions where you can reply to the comment. You can mark it as done or you could delete it. You can also select under the comments tab within the window that you want to look at comments that are only for you. Comments that are open, comments that have been resolved. And you can also look at the comments on particular sheets. If you have multiple sheets and multiple comments, this is a way to sort through the comments. You can also scroll down and see the different comments that have been added. There's also different ways that you can set the notifications for the document. So if you select the e-mail notifications under comments, so you're gonna get notifications for all. You're gonna get notifications only for comments that are addressed to you. And then if you select none, you won't get an e-mail notifications for comments that are added into the spreadsheet. There's also an option here to add new comments. So just as we saw under the Insert for the comment, we can use the shortcut or we can use this comments icon. And that's another way that we can add in a comment into the cell by right-clicking it. We also have an option to add in a comment. And this is going to also show up in the comments, just as we previously saw the comments being set. I'm going to assign this comment to this user. And if you assign to a user that doesn't have access, it's going to ask you to allow access to that user. So let's update this and we'll set it to this user. So that was an error there and it was able to catch that error. So I'm able to assign that comment to that user. And it actually is able to detect that this is the same e-mail address that I'm logged in us using this content. I can also just do an assigned to me and just do an assignment. Now this content is gonna be assigned to myself. If I do a search, I can look at the content that's only been assigned to me. Comments. By adding comments, they allow you to collaborate with others on the spreadsheet. And the cells that do have the commented areas are gonna get highlighted with the bottom, with the top orange. And then when you hover over the cells, so even if the cell has some content, that comment is still gonna be there until we take some type of action on the comment. If it's something that we want to mark as done, that will remove the highlight in the top or top corner, but the comment is still gonna be visible if we look at comments that have been resolved. So that's gonna be a resolved common tour. If we look at all the comments, you can also reopen a comment. So by going to comments and we can see the ones that have been resolved, I can select under the More Options. I can once again delete it or it can reopen this comment. And that will print the comment back into the spreadsheet. If you will do accidentally close the comment, then you always have an option to reopen it. You can also reassign the comment that is clicking on the comment. And we can click the comment where we reply to the comment. When we reply, we type in the comment. And this allows us to reassign it either by using the art or the plus. That's going to pop up the window for the other users and allow us to assign it to the person that we wanted to use. Once we press reply, that's going to add in that new user and assign it to that user. Under the More Options we can edit, assigning it to multiple users by adding in the art to multiple users and saving it. So now it's been assigned to users. Also under Insert we can add in notes. So notes can be into cells. And similar to what we saw with the comment, if there is a note on the cell, when we hover over the cell, that note is going to pop up. The notes are gonna be indicated with a icon of the cell. We can select it and under the right-clicking of the cell, we can delete the notes from there. If you can add in the notes and then you can also delete it by right-clicking as well, or by selecting insert in the top tabs at the top. 18. 17 Slicers in Sheets Filter: This lesson we're gonna be looking at slicers. So that's a new feature under Data. And under the data tab, you've got an option for slicer. What slicers do is the lowest another way to filter content and make it more interactive. So if we do create a chart, and let's go ahead and we're going to create a chart from the data that I've selected. And then within the chart, we're going to update an update the slicer. I'm going to make this into a pie chart. Let's edit the chart and update the chart type. Setting it as a pie chart. We can close it. Now. We want, we want to do is we want to enter in and add in a slicer for the data. So let's add in the slicer and select the data that we want to use for the slicer. The data is going to be the same data range though we had just used for the chart. And that's also one of the suggested ranges. Let's hit Okay, on that. And we can close down the slicer. And slicers just like the charts. They go across multiple cells. So we can drag it and we can move it. Once we select the slicer. This is gonna give us an option to filter. We can apply different filter conditions. And we can also select all of the values that we want to filter. We can clear the values and select the values individually that we want to use once we hit Okay, that's also going to update the chart content. Right now we're only going to be using the selected data that we've selected into the chart. And this allows us another way to create our content and make it a lot more interactive. So we can also select and just filter by various conditions so we make sure that it's not empty. This is also updating the view that we're seeing, just as we saw with the filters right now, by applying the slice, we've sliced out the selected content values that we're using in this by double-clicking the slice, this is where I can re-select different values. We can customize the data in the slice with the title, and I'll call it sliced chart. Here we can set different font types, font sizes, font styles, backgrounds to customize the way that the slice icon looks like. And then under the data we can also update the slice content. So here we're still selecting the sheet data from E2 to eat 11. We can always update that range if we do need to buy the slice by double-clicking it. And that's going to open up the Slicer window so we can apply that and update it as needed. We also have various columns that we can select within the slice. And right now we're only just using the one column, so that's the only option that we have there. So now going back down to the slice, we've updated the font there. We can clear all of the values, and then we can select the values that we want to use within the slice. Once we've made a selection of the various rows of data, we hit Okay, That's going to adjust what the view area is. And the view area also is going to be adjusting the chart that we're outputting. So now we've selected these rows to be represented within the chart, values, the slicer. And if you want to get rid of it, you can just press the Delete key or the backspace on your keyboard, or from the three dots on the slicer. You can edit the slicer, copy the slice surge can delete the slicer. You can set the current filters as a default. There's even more information if you want to find out more about the slicer available under the Learn More. So let's go ahead and we're going to delete the slicer. And once we delete the slicer, that's going to remove the slice content and bring our chart back to the way that it was originally using all of the selected data without the slice. 19. 18 Sheet Data Options: A look at some more options that we have under data. So selecting the data tab at the top menu, we've got the column starts selecting, that will provide us the stats on the columns, the count, the distribution. And this gives us a quick kind of chart of the values that we have within the sheet data. We've got the frequencies. So the values that are most frequently used, so we have six being used twice in the selection of that column. We've got the various values that are being used. We've got the total rows, the empty cells. So this is the sheet in general. We've got the value when it's summed the average, the medium, the minimum value, the maximum value. So these are stats and information that you can use to get a quick overview of what's contained. You can also select other rows and ignore certain rows. So we can select rows that we want to ignore from the stats. We can always go back to 0 if we want to add in and just have 0 rows, we can increment them by one. So if we want to remove out certain rows and starting at row number seven, so excluding all the rows from one to six, we could ignore all of the rows before that and only see the stats on those cells and values. We can inspect additional columns by moving to the next column. It will have the column heading here. So in this case we've got lost. Then the next one is going to be code. And the next one is going to be name. It's going to give us again that information, a quick chart and a distribution. We've got all of the values. And then for the sheet as a whole, this is just some information that you could have a quick glance about your current sheet and particularly the column contents. There's also the data validation. Data validation does is that it will check the cell range. So let's do a data validation on the contents column E and hit Okay, the criteria that we want to use, we want to list from the range number. Then we want to set it that it's between. We can also do a not between less than, equal, less than or equal to, greater than, equal to. So there's a number of options there. So we're only going to return back numbers that are between 310. We can show a warning or we could just simply reject the input. So we'll just leave it at show a warning. If the data isn't gonna be valid and this is the valid data within the criteria. We can also show the validation help text. So let's do that. And this is what the criteria is in a summary sentence. This is good for if you do want others to be able to see the criteria that you're using, then you can show the validation help text. So let's go ahead and we're gonna save this validation. Now when we go over that selected range, we can see that this is invalid because the input must be between 310. This is how you can have various criteria that should be met within these values. If you want to remove it, you can select the range and under the data, select the data validation that will pop up the same range and the number in the criteria. And we can just simply remove the validation from there and that's going to remove the validation part. This is a helpful way of making sure and ensuring the data is going to be within the valid data that you're expecting within that content. There's also a data cleanup. So there's cleanup suggestions. There's removing of duplicates and there's trimming of whitespace. We enable the suggestion pop-ups. If there are cleanup suggestions that come up when we're adding in data, it will provide that and provide those cleanups suggestions. And this is just a smart option that you have when you are working with your data. Within the data cleanup. In this case, we do have two rows that have the same values. So let's select that and do the data cleanup. And we're going to remove the duplicates. So this is the rows and columns that were selected. So if we want to select a specific range, we can select specific column that we want to analyze. So in this case we want to analyze just column E for duplicates. And it found the one dude duplicate. So then it's found and removed the duplicate. So ten unique rows remain. So it removed the duplicate value where we had six, and it just removed at that value. So it removed out even the other columns because they weren't duplicate. One we're selecting the particular column. We're want to remove out the duplicates. It will remove out duplicates to the selected column and then reflect that across all of the data rows. Also under the data caught clean up, we have the Trim whitespace option. We can select the content, and this is in case we have some extra whitespace. In some whitespace, we'll select all of the content. And then under the data cleanup, trim the whitespace. So it found the two cells that I just added, the whitespace. And it's trim the whitespace from these cells. So now when I click them, there's no more whitespace in front or afterwards. It's kept the whitespace in the middle. So even if we have multiple whitespaces, we do the data cleanup, trim the whitespace. It will remove out the multiple spaces that I had within the text and clean it up so that there's only the one space. So if you have multiple whitespaces or spaces afterwards the text or before the text, it will clean up that content in the cell. We're also able to split text to columns. So let's select that options and selecting the column that we want to apply this to. We can select the separator. And right now it's detecting it automatically. What it did is it automatically detected the space as the separator and separating out the firstName and lastName into separate columns. This is a nice way that you can have data that gets separated out if you want to separate out the data, as long as you have a good pattern within the data, it should automatically be able to detect it. You can also look for specific splitters such as a comma semicolon period space. Or you can use a custom splitter and specify what you want to split it by. In this case, if I wanted to split it by certain characters, that will allow me to split it with those characters. And what it does is when it does split the content, it will actually remove out the characters. So it's removed out the RE and split that content into a separate column. Then once you press enter, it will complete the split of that content. So those are some more options that you have under the datatype. 20. 19 Sheets Tool Options: This lesson we're gonna be looking under the Tools option. Within the tools, we can create a form. If we create a brand new form, this is going to create a form that's going to allow us to add form responses. And we can update that form so we can add different questions of the form. And this is where the form data is going to be submitted. So this creates a Google form that we can submit content into the spreadsheet. And if we want to XX, want to select the form, we can delete the forum, we can duplicate it, we can copy it, rename it, changed the color, and then we've got the same sheet options as well for this, if we want to access the form content, you can go under the tools and from here you could manage forum. You can go to edit the form, go to the live form, send Form, embed form in a webpage shows summary of responses and unlinked form editing the form will bring you to the form editor where you can update the questions. You can add in more questions. So this is the form in Google Forms. And updating those questions. It will use whatever the question name is, whatever the question title is as the heading for the sheet data. Then once again, going back to the tools, you can go to the live form. This is the form that you can share and others can access the form so they can fill it out and they can submit content. If they do submit content into the form, you're going to see the timestamp and the option of the form so that you get the data that was submitted. Again going to manage, you can send the form. So this is the form URL where you could send it via email to others. You can also collect emails. You can use a direct link to the form or you could get the embed HTML. So that allows you to embed the code as HTML. You can also send it via Facebook or Twitter so that others can interact with the form content, also under the managed form. So if you want to embed it into a webpage that will provide you the embed content directly to that pop-up window where you can embed the form contents into a webpage and HTML. You can also see a summary of the responses. This is again going over to the forum interface, where we've got the responses. From those responses, you can see the questions, the individual responses. And then also you can view the spreadsheet that this form is connected to. And within the options of the form, you can get e-mail notifications when there's new responses. You can also unlink the forum, download the responses, print all of the sponsors, delete all of the responses. If you do deep delete the responses, That's going to delete the responses within the form but not within the spreadsheet. And those thoughts responses will still be set within the sheet. You've got the typical form where you can customize the theme, you can preview and then you can send it. So you've got the typical form options as this is just a regular form within Google Sheets. Let's go ahead and we're going to delete that form. In order to delete it, you have to unlink the form first. So go ahead and unlink the form. And then that way we can delete the form sheet from our spreadsheet. The form will still exist within the Google Drive, but now it's not gonna be linked anymore to the spreadsheet. Unl have an option if you wanted to re-link it or link it to a new sheet. Also under Tools, we have a spellcheck and a personal dictionary. So the spellcheck will just go through the words and make suggestions where you can ignore, change, or add to your personal dictionary. There's also an autocomplete. Autocomplete is a really useful function that usually I keep on and enables auto-complete within the sheet cell. There's also formulas, suggestions, and formula corrections. So these are just useful ways to, when you are interacting with the, with the Google sheet that you have all of these suggestions with the autocomplete. And this is a smart autocomplete, which I usually find that these suggestions are very useful in what I'm trying to do, this notification rules. So when you click the notification rules, you can notify the your account when any changes are made or whenever the user submits a form, you can also notify and when you set the notification so it can be a Daily Digest or it can be e-mailed right away. These are also useful if you want to monitor and you've shared the Google Sheet with others and you want to monitor interactions on the sheet, including form submissions. There's also an accessibility option. You can turn on the screen reader support. You can also turn on the magnifier support. You do have accessibility settings within the tools options of your Google Sheet. 21. 20 Sheets Addons: It can be exploring add-ons and extensions. So under the Extensions tab, we have an option for add-ons where we can get add-ons for sheets. We can manage the add-ons that we've already added. Get add-ons. We'll open up that marketplace that have all of that different add-ons for sheets. So there's a lot of really useful add-ons here. So you're welcome to check those out. They do have a star rating, as well as how many have been downloaded. These add-ons can be used in order to enhance the functionality. You can also search for specific add-ons. There's also support settings and you can launch it in a separate window, also under the extensions taught. So if you do add add-ons and from there you can manage the various add-ons that you've added onto the spreadsheet. There's also macros. Macros allow you to record certain actions within the spreadsheet and then you could replay those. So in order to record a macro, it will start doing the recording of the macro. We auto filled the range. Let's save this macro now. So this is the test macro. We can set a specific shortcut to run this. I'm gonna go ahead and just save that. At anytime. Now that we've created the macro, we can go under Extensions macros and we can manage the macros as well as we can run the test macro. What the test macro does is it's going to auto populate that same content that we just provided. This is gonna be running within scripts. So we have to accept permissions for our account to run the script content in order to produce the functionality. Once we've accepted permissions that we can run the test macro. And what that did is it still populated, auto populated those same cells. So let's remove out that content that the macro added in under extensions. And we'll run the test macro. And now it's gonna repopulate the data. The macros are created within Google Apps Script. So also under extensions, if you select App Script, it will show you the App Script Editor and it will include the macro that we just created. So this is the script version of what we're doing within the macro. So from here we can actually edit what the macro does and update it. So it's bound to the current document. This is gonna be a script that we can access update. We can also apply additional Apps, Script functions, and run those in order to interact with the content of our sheet. So that's all under extensions. If we go to the up sheet, we can create an app and we can also see sample ops and we can learn how to create apps. So let's go ahead and we're gonna see some of the sample ops that we have under the op sheet so we can copy it. So this is simple inventory Kanban board. So there's a lot of really cool things here that we can use as templates. And it's created an app sheet within the Google account for that Kanban board that I selected. This is just another option that you have under OP sheet. You can create your own apps as well. This is all under the Extensions App sheet. And if you want to learn more about App sheet, you can select the learn how to create apps. The website will provide you additional documentation about how you can create apps using your spreadsheet data as a source for the data. Again, you could select from the pre-built op templates using the spreadsheet data as a source for data to interact with that content. As long with Apps Script, there's a whole lot you can do with Apps Script similar to JavaScript. It runs in the Cloud directly within the Google servers. And you can access your app script using the script.google.com, creating scripts that can connect to your spreadsheet and use the data from your spreadsheet in your application. 22. 21 Explore Sheets: This lesson we're gonna be highlighting a really neat feature within sheets, and that's the explorer. This is open at the bottom right-hand side of your spreadsheet. You can use the shortcut in order to open the explorer. And what the Explorer does is it allows you to essentially check the data and a number of different ways. So there's some smart ways to ask about your data and interact with your data. So the bottom five code by values. So if you want to pull that information out, it basically allows you to really filter through and check the data. There's different options here within the questions. List of names. So if we did like a list of names, it would try to suggest what we want to look out for the names. And in this case, it was able to identify that column B had the various names because from the heading name. So it provided the answer for the names. And a smart way to answer the questions also provides you the data set that it's using in order to provide the answer. You can edit that too specific to be more specific form the data and the columns. There's also some options here where we've got the code with highest values. So that will provide the code with the highest values. We can also update the formatting, so it's got some neat formatting suggestions to make the spreadsheet content more readable. Also, once again, you can edit the alternating colors and for the data that you want to alternate, there's an analysis of the data that's been provided, provides you a table format. You can select the Insert Chart, and that will automatically insert the chart that we're reviewing within the explorer. This is a quick way that you might want to generate some charts that represent the data within your spreadsheet. So it's another smart option that you have for interacting with the content. That was the analysis where we can enter in the chart. It provides you some details about that. There's also a pie charts so you can enter in that you can also view at full size. So this gives you some data that's available within the pie chart. There's also the bar chart. Once again, it's trying to represent the data from the spreadsheet, creating various chart formats that you can use and then insert into your spreadsheet as needed. So once you have the data there, It's always worthwhile to check out the Explorer and see what different ways to present the data that there are. And then once it does create, it is like any other chart where you could edit it. You could update it, you could download it. You can move it to its own sheet. You've got the same features that you have within any other chart that you would've created within your spreadsheet. 23. 0 Functions Section: Undulations on making it this far into the course. This section is gonna be showing you and demonstrating you some of the examples of how you can work with functions. Functions provide a powerful way within Google Sheets to organize, interact with the data and the content that you have within your spreadsheets. I'm gonna be showing some amazing examples of some of the functions that you can use in order to become more productive with Google Sheets. Some of the functions that we are going to be looking at is going to be count returns, a statistical count of the number of cells in the dataset. There's also the date converts a value into a date month D. We're also going to be looking at find, and it allows us to find text if to apply condition index to look up the content of a cell, to look up and looks through the rows and columns for a key and returns that there's also match which turns a relative position of an item, maximum, minimum. Now, how you can use round some today, VLOOKUP and a whole lot more. Let's get started looking at some of the functions within Google Sheets. 24. 1 Sheets Formula Simple Operators: This lesson we're gonna be creating a sample invoice using the mathematical operators, where we're taking a value of cost and quantity, creating a subtotal from the two cells, the values of those cells. And then we're also applying that same formula to the following rows of content to create the totals for the subtotal, we're also calculating the tax on each line where we're taking the subtotal amount, multiplying it by 0.13 to create the 13% tax on items. And down at the bottom, we're calculating all of the subtotals together, the sum of the subtotals we're adding in shipping, as well as calculating the sum of all of the rows of tax and creating a final total for the invoice. And this is all done with the mathematical operators as formulas within the spreadsheet. And as you adjust the values for the quantity that's going to get reflected within the tax and the subtotal and the total. So all of the numbers will adjust. You can also update the cost and that will as well be reflected within the calculated totals that are gonna be changing depending on what the value that you've entered into, the cost and quantity is going to be using Google formulas. You can do simple math operators of created a sample invoice that you might have. And we're gonna do math operators in order to calculate the tax, the subtotals and totals for this receipt with simplest formulas is to use the math operators. The math operators can be created simply by selecting the cell and they're going into the formula bar and doing an equal sign within the equal sign and do rounded brackets. And then enter in the math that we want to apply. Once we hit Enter, it's going to complete the calculation. Although the formulas, we'll start with the equal sign for multiplication, we're gonna be using the asterisk which will complete this calculation that's contained within the rounded brackets. You can also use values from the cells which we are gonna be using in this example. For instance, if we want to create the subtotal where we've got the cost and then the quantity. That means that we're taking the cost and we're multiplying it by the quantity in order to create the subtotal. So let's create the formula for that, where we start with the equal sign and set the rounded brackets, then select the cells that we want to use. So in this case B, to which it will now highlight an orange we want to add, or we want to multiply by the quantity which is contained within C2. Once we hit Enter, it will provide us the value. Also Google Sheets will provide the suggested autofill. If we hit the checkbox or enter, it will complete the autofill on those additional following cells. Now if we go in and if we update the quantity, it will also update the subtotals within column E. We can also update these and format it to be as currency format it and then also in addition, update the cost and set the format for those to be currency. We also want to add in the tax of 13%, we create the formula for the tox. So starting with the equal sign, the rounded brackets, we're gonna select the value from B2. Multiply that by the value from C2. That's where we can get the subtotal. I'm going to add in another bracket because I want to get the total of b2 times s2 it, Let's multiply that by 0.13 in order to get the percentage value. That will give us the percentage of 13% off of that total. And we can drag it down in order to apply that same formula for those cells that are incrementing. So instead of b2 and c2, it will use B3 and C3 and still multiply it by the 13%. This gives us a running total of the tax value that we're calculating. And if we do make any updates to the quantity, it will be reflected within the tax and the subtotal. We could have also taken the subtotal amount and just multiply that by 13, so that would produce the same result. So we can take the value from column E and multiply it by 0.13. This actually should be E2 that produces the same values and they're still going to adjust the same way as we just saw. If we do update those quantities, it's going to update the subtotal and that's also gonna be reflected in the tax. Let's get the totals that we have for the subtotals and the tax. I'm going to move these over just one column and then take the subtotal. So the subtotal is gonna be sum of all of these values. Instead of doing value E two plus y3 plus E4 plus E5, E6, and E7, we can do a sum of the entire range, which is going to be a lot easier. And that's where we can use the some keyword in order to accomplish that. And then summarizing and creating the sum for the entire range. So it's already being suggested here within the formula bar. In order to accept the suggestion, we just hit enter. And because we want this form, if we do add in additional content in the following rows, we want it to go all the way down to number seven. So let's adjust this to be E7. That will create a subtotal for all of these values that we have here. We want to also create a value for the tax. So again, we can create a sum for that of all of the numbers in the cells. We need to select the range. Now we can use, we can use the mouse in order to select the range that we want to apply this to. And then at close off with the other rounded bracket. And once we hit Enter, That's going to create the total for the tax. It does have a suggested autofill. So in this case, the formula that's trying to suggest is not going to be correct because we actually want to add the subtotal, the tax in order to create the total. So let's press the X here to reject the suggestion. For here, we're gonna be doing a sum of the top values. There's also another way to do the sum. And under more, if you select the functions icon, you can select the sum that way and that will automatically add that into the formula bar. And then you can select the range, press Enter, and that will summarize all of these as the total I've added in an additional row here. So if we do want shipping, we can create a value for the shipping. And here we can enter in a number for shipping. So if we wanted $15 for shipping, it's also update this format to be currency. And that amount for shipping now gets added in the total because it's within the range of E8 to E11. If we were to update the shipping costs, that would also still be reflected within the total. Now you can bold the total or make it larger so it stands out more. We've successfully created a dynamic invoice that can change if we change any of the values here of the quantities or even the costs that we're charging per item, it will automatically calculate the tax, the subtotals, then the final total that's owed on this particular invoice. 25. 2 Relative vs Absolute Cells: This lesson we're gonna be using an absolute cell value. Whereas before we created the formulas with the rows relative to the other rows. In this case, we're using the B2, C2. And then in the next row it was B3, C3. The next one was B4, C4. But we wanted to keep the tax rate fixed to this absolute cell value of 9% or whatever percent we want to use for the tax. And we could do that by applying the dollar sign for the column and then also the dollar sign for the role. And now, when we drag down the formula, it's not going to change that absolute value, but it will still change the relative values to the cells where it's getting the cost and the quantity from. Show you how to do that in this lesson, coming up, looking at the differences between the relative and the absolute references. In the previous lesson, we looked at how we can use a value from the cells. And then as we dragged it down, that value updated, let's create a value for the tax rate. Tax rate will be 13%. And that was what our tax rate that we wanted to charge. So typically we saw with the subtotal where we can take the value of B to multiply it by C2. That created the total. We can do the autofill for this total, but now we want to calculate the tax rate. The tax rate will be always the same. It's not gonna be changing as we saw with these relative values. We wanted as an absolute value from the cell be nine. Set that up and we're gonna take the value of t0 and then multiply it by the value of B9. And that's gonna give us the current tax. If we drag it down, this doesn't calculate properly because the next row is gonna be automatically incrementing because it's tying to take our relative reference where it's using the value for b ten instead of the B9. So we can, of course set it as a hard-coded number, but we want to be able to adjust the tax rate and have that reflected within the column D totals. So the way to do that is that we can use the value that we have within B9 as an absolute reference to that cell so that we're not actually changing it. And this is where we can use the dollar sign that will make the absolute reference to that particular cell. So if we update this formula and use the dollar sign, $9 sign B in order to indicate the B9 cell, it's going to keep that as the absolute value. So as we drag it down, that value of 13% will stay as the absolute value in all of the rows of content. Then we can apply the subtotal as needed. So now let's adjust the subtotal to reflect the values multiplied by the tax and get the total for the cost quantity plus the tax for the subtotal. So we have to make an adjustment to our formula. We're using b2 and c2. Let's update. Instead of going to E2, we're going to paste in the bracketed be two times C2 and multiply it by B9. We can drag that down. The subtotal now will be the value of b2 times C2 plus the value of d, and that will be D2. The subtotals now will reflect the value including the tax according to the values of the quantity and the cost. And update all of these and set the format to be currency. Now to separate out to get the total and move these over slightly. In order to get the total. This is gonna be the reflection of the values of the sum of column E. So let's add that in to the formula, sum of column E, and it's only going to be E2 to E7. For the total sum. Also update this to be a currency. We've also, the tax will also be a currency. I'm going to get rid of the 13% up here because now that we've got a changing tax value, so if we were to change this to 11% of the tax, then that would also be reflected within the tax and the subtotal. Let's calculate the sum of the tax. So this is all of the contents of column D. So starting at D2 and going all the way to D seven. Now in order to pull out the subtotal, we can do a subtraction of the tax from the total and do it backwards. And that will pull out those values. So let's create that where we're going to use the value of e ten and subtract the value of E9 from there. And that will give us the value before the tax. And the subtotal is going to be including the tax. So we can also indicate that within the values there, Let's make the total bigger so it stands out more. The subtotal we can even make smaller. Probably won't need the subtotal. But again, this is just another way to do those calculations are also if you want to get rid of the contents of these, we can apply some conditional formatting to those. Under the Format Conditional Formatting. We're going to select the entire range. So let's get the range. This is going to be everything from d2 all the way to E7 and the format rules. So if it is equal to 0, then the formatting that we're gonna apply is we can make the text color white. And what this will do is this is going to hide it from the view. Now if we want to add to it, Let's say we've got another item that we want to add in. Once this total is not 0, it's going to then show it within the view. And also let's update this cost. This column should also be formatted as currency. And so now it will once again show and it will work like the other columns, where once we make updates, it'll recalculate the tax, the subtotal we get the subtotal tax and at the total that's being charged. This particular order, we can also take the tax rate and adjust it. And then once again, that tax new tax rate is gonna be once again reflected within the invoice. That's how you can set an absolute value for the cell by using the dollar sign. The dollar sign is going to always be an absolute reference across multiple cells. Without the dollar sign, it gets interpreted as a relative reference. And as we saw, if it's a relative reference, that number dot cell that's referencing will change as we drag down the formula to the following rows of content. Highlight this, and I'll format this to move it over to the left. And I'll move this one over to the right. So we cannot the tax rate kind of sitting there on its own so that we can easily see this value. 26. 3 Google Sheet Functions: Some of the commonly used functions within Google Sheets. Looking at some average, the count, the max, and the minimum. The sum will create a sum will also show you how you can add in multiple arguments into the sum. There's average, which will take a range and take the numeric values that are within that range and calculated an average. The count will also take the range and then count how many numbers, how many numeric values are within that range. Maximum will return back the maximum value, numeric value within the range. And men will return back the minimum, the smallest numeric value within the range. Let's look at functions in Google Sheets. So previously we saw that with a formula, we can go to the formula bar, we could hit the equals sign and then within the parentheses, we can make a formula. And sometimes these can take time and selecting the proper cells and the formula. So the easier way is to use the built-in functions that are already present within the Google Sheets. You can select and insert a function either by going to the Insert tab at the top and then selecting function. And then from that function, you've got some of the commonly used functions where we've got the sum, average count max, Min, and then there's all of the functions that get listed. And as you hover over the functions, it will give you a brief description of what the oxygen is that it's going to perform. Most of the functions are going to require some type of argument, and that's gonna be the what's contained within the parentheses. So let's go back to hit some, and it automatically creates the function using the function keyword sum. And it's expecting arguments. When we hover over that argument within the parentheses, it'll give us instructions that we can use the arrow keys to select the range that we want to use for the sum. This will create the sum range that we're calculating. We can also include additional arguments by comma separating and allow us to add additional arguments into the function. And then once we've completed the function that we're calculating, we hit the enter sign. Google Sheets will suggest an auto-fill using that same function. In order to populate the remaining rows of content. We can also see the formula. So this is the formula that's gonna be used. We're just going to take the values of existing row and then also add them to the values of the next row. So let's hit okay. And we can select on top of the cells in order to see the values that the function is using. Or you get have multiple arguments and all you have to do is separate it by a comma within the function. So let's update this and we'll select another function. And I will do average for this function. And now we need to select once again the range that we want to use to calculate the average. And it's going to auto-fill, accrete the average for the range. As I update these numbers attached to different functions, those folks are gonna update as well as I update the values within the cell. And those new values are now going to be reflected within the cells. So strength these down a bit. Then within column I, Let's do another function. And another way to select functions is to go to the three dots. And under the more you've got the function list. And once again from here you can select the functions and for account, Let's select those, that as the range within count. And what it's gonna do is it's gonna count how many values that we're adding in. In this case, we've got the four values and they're all actually going to be the same because it's just counting the number of values within the argument. For this as well. We can include, if you want to start at a and what count will do is it will count the numeric values, pin the columns a to D. These are gonna be string values. If we were to change these into a number, that would then get reflected within the count. And it would be able to count how many numeric values are within that selected range. Mac that will select a range and it will provide us the maximum, the largest number that's contained within the range. This is max numbers. This one is average. This is some, there's also minimum. So when we select within the range to get the minimum, this is going to be the smallest number that we've selected within the range. So these are just some of the commonly used functions and how you can apply them within your data. And as you can see, there's quite a lot that are available within the Google Spreadsheets. 27. 4 Sheets Functions Date Options: We're gonna be looking at working with dates. And as we see within the support document for the Google Functions, there's quite a lot that you can do with dates. So looking at how we can return the day of the month, how we can get the work days as well. So that's another option that we have. And we're gonna look at how we can calculate out also returning back the current date using either today or now. These are both going to return back the date today is going to return back the date value, whereas now is going to also include the time value. We can use that as we're calculating out within the function. So we can use those key, we can use those additional functions in order as arguments within the function in order to make the calculations of the function. So all of that is coming up in this lesson. There are a lot of different functions within the Google Sheets. So checkout how we can use the functions more effectively within our Google Sheets. So when you want to use a function, you just do the equal sign within the formula bar. And from there you can select the various functions. So it using under the More and then selecting the functions that are available. And if you want to find out more about the function, you can select it. Once you add the function, its name. Within the arguments area of the function, you can click the question mark or you can press F1 to turn on the formula. Turning on the formula bar will show you more information about what is expected within that function, the different arguments that you can add in. There's also an expand, where you can expand the details of it and it'll give you a sample. So we can have a sum of A22, 100 as the range, and then also add in 101. It gives you some more details about the boat returns a series of numbers or cells. The first value of the number is a range that we want to add together. And then you can click the Learn More. And that's going to open up within the sheets, another pop-up window where it's going to provide you more additional information about how to use this particular function with some sample code where we're comma separating out the values we can increase include numbers as well as ranges. And what will happen is all of these will get added together. The additional arguments are optional, so you don't have to add them in, in order to run the sum. You just do need to have the first value. And this is going to be either a number or a range that we're adding together. If you use a single number, then the sun will return only that number. There is a maximum of 30 arguments that are available within the sum. You can search additional function names as well under the help. Let's close that and we'll try out the sum, where we'll select a range for the sum and then comma separate out, adding in a value of 50 and then also adding it a value of 100. Once we've completed the calculation, it will populate those numbers in. So we're doing two plus five plus 50 plus 100 is returning back a result of a 157. Going over to the support at google.com, There's more information about the function list. So you can go to support.google.com and search for Google Sheets function list. And this will provide you a full list of the function, the type of function that it is, the name of the function, the syntax, and then also a brief description of how you can use that function and scrolling down, there's quite a lot of options here. Check out a few other ones that we have available to us. So I got to set thought and a format these as Dietz and then adding in a date value. Now this is gonna be a date object. And let's apply a function, a date function to this. And the date by default will convert a year, month date into a date. So it requires several different values, such as the year and then the month. And these can be numeric value, the month value, we're going to add that as J. And then the day will be coming from K12. Let's add those values in. So this is gonna be the month and then this is the day. So as we add values into these, it will adjust the month and the day to correspond with the values. If there are no values for the month or the day, it will take whatever the current day and month is, an add dot n. And as we saw that we are setting the year within here. If we want to extract out values from a date, we can also do that. And let's select down from the function list what we have for date, so we can get the number of days between the two dates. So that will be days and the end date. Then the second argument is gonna be the start date. So adding in the end date and then comma separated out, and then select the start date. This is going to calculate out the number of days that are between the two dates. This would be the end date, would be the start date, and we're calculating out the number of days in-between using the date function. So going back into the date function, we can also get the specific day of the month. That particular value false upon the argument is going to require a D. Let's select a date here and just needs the one argument. Then hit Enter. It will pull out the actual d that it falls upon. So we can see 45678910. So if these get adjusted and because these are the cells are formatted as dates. Once we make adjustments to them, they're also going to adjust as date values. Or you can use the calendar pop-up in order to update those date values. What now will do is it's going to return back the current date and time as a value. If we select now, it will just return back whatever the current date and time value is. And we can use now within the formula. So it'll pop up whatever the current date and time is. If we were to use that within the date function to calculate the days between the two dates. Let's select that. And we'll get the end date using the same end date comma separate out, and then use the function for now in order to get the current value. So this is gonna show us the number of days between right now and this end date. So it's gonna be using the calendar date. Let's update the date object, set the pattern. And now the dates are gonna be different because it's using whatever the current date is and these ones are the same, that's where we're getting the same number there. Let's look at some other data values that we have. We can also calculate the number of work days from the start date. Let's select the workday. Will select the start as our workday. If we do throw an error that it's not able to extract the value, we need to have two arguments, so it'll throw an error and it will reproduce the NA value. That means that you need to add, add two arguments. So it's expecting between two arguments and three arguments, but it only got the one argument. The second argument is where we're throwing the error that we don't have the second argument. So adding the second argument, and if we want more details about that particular function, we can always click and expand to see what the sampling is. So calculates a date after a number of working days from the specific start date and a suspecting a numeric value, not a date value. That's why we're not getting the result that we were expecting. We need to update this to a number using the number of days. So go back to the function and let's update the second argument. And we're going to use the numbers from Column F in order to indicate the number of days. So we've got our start date, which is on the fourth of me. And we're calculating five days after that, five work days after that, and that's returning back this date result here we're calculating 465 days after the start date. This is gonna be the result. And we had a third argument that we could add into this function, which will represent holidays. That holidays is a range or an array containing dates to consider as holidays. So that's another option to add in for the working dates, working days from that specific start date. And some of the functions that are available within the date. 28. 5 Sheets String Functions: We're gonna be looking at text functions that we have within Google Sheets. So different things that we can do with text, where we can pull out the right side of the texts, the left side of the text, texts from the middle. We can also split the text by a splitter and then return back those values, concatenate text together from various cells and formats in order to provide output and different types of formats. There's also the length where we can get the length. There's the replace value that we can use that to make this slightly larger so that we can see the contents. There's also a substitute converting a number into a Roman numeral, replacing string values by particular search where we're starting and where we're ending. Finding a string value, converting to lowercase, capitalizing the string, trimming it, converting it to uppercase nesting functions within other functions. That's all coming up in this lesson. Let's look at some string functions. We do have some columns with string values. And we're going to extract some of those values out of that looking at the ID. So let's just get the number value for the ID. And all of them start with the letter a. So they all have one value there that we want to take the string value and extract out the number. We can do this in a number of ways. And one of the functions that we can use is left. What left will do is it will get a substring from the beginning of the specified string, selecting that and the string that we're gonna use is gonna be from A2. And the sub string that we want to extract out is gonna be one. So let's see what we get returned back for the value. And that's going to get that returns back to a. But what we want to do is we actually want to do the other side where we want to do the substring. Where instead of going from the left, we want to start out on the right using the string value of a and extracting out just the one value from the right-hand side. That's going to return back the last character there. Let's actually track these where we've got the right, we've got the left string value. So copy that over and do the left string value. And we'll still gonna be using the column a. It's going to take the first character on the left. This is going to take the first character on the right. What we want to do is we want to Just return and remove back that first character on the right. So we actually want to get the middle part of the string. So mid is the function that we're after. So let's select Mid and we want to turn back a segment of the string and clicking the formula information. So we need a string starting out and extract length that we want to return back. This case they've got get this 54. So that's going to return back the segment. Selecting as our first string value is gonna be a2, starting at position one. So returning back up to two additional characters after that populate out and remove out a. Returning back to characters up to the two characters after a. You can also increase this. You can go as high as you want and it's going to take whatever value is. So if we did have a value of a and we had up to 20 characters after that. It would extract that value where the length is going to return back all of the available characters that are after the starting position, starting at position number two. Let's go ahead and split the values for firstName and lastName. So we want to get the first and last name out. I'm going to shrink this one down and make an adjustment on the ID value. For this, what we want to do is do a split. Split will do, well, it splits the texts by specific character delimiter. First we need to have the texts that we want to split. So it's going to be coming from cell B. Then the delimiter that we want to use. So the delimiter is what we're using or the character that we're using for the split. So the delimiter in this case it's just going to be a space. And let's close off that. Where we're splitting the firstname into two cells, we're getting the first and then the last name is split. If we make any updates to that content, It's going to also get reflected here and we're still going to be able to get the last name. So if we want to bring together two cells of content, we can do that as well with a concatenate. So let's apply the concatenate and it appends one string to another. So it requires the first string value, and then the second string that we're adding together. You can add in a blank space just by using the quotes and adding in the string value that you want between. Now that's taking the values from j and k and bringing them back together into one pleat value. And we can also update the space value as well. Or we can reverse order. So if we wanted to, instead of having the two, we can start with the K12 comma separate oats, and maybe we want to have a dash instead of the space, then this will be the value for J2. We can change the order, having the last name first and then the firstName after, and then having it with a dash. Or we can do a bracket, want that will bracket the FirstName. If we want to add in a space, you can do that within the space strength. Format it as needed in order to output that final string value. This is all originally coming from the original value that we have here. As you update those, that's going to update the corresponding string values that are associated with it. Let's look at some other string functions that are available when you go down to the text within the functions. We've looked at the left, the right. We can also get the length of a string. So you need to select the string that we want to add the length two and select the length from. This will return back the number of characters that are in the string. And you can always drag down if you miss the option to populate the next rows. You can update it that way. So this can adjust the length. So that way you can always get the length of the string. This is quite a lot of options there within the characters. You can also do a replace, selecting the text that we want to use. And in this case we have all here as test. So let's go ahead and select and then the position that we want to start. So it's going to be expecting a number starting at position and the length that we want to replace, it's gonna be up to the next five characters. We're going to replace it with a string that just says number. What this did is starting at position number four. It took the next five characters and replaced it with a value of num. So basically it took out the content starting at four space, one, space T, and removed at those five characters. Returning back the result. We can change the starting position and that will update the result and then drag it down to apply it to the additional rows of content. Going back into the text, we can format numbers into Roman numerals. So we need to select a number value. We've got that within column E. So now that will provide those values as Roman numerals. That's also available within text. We can use substitute, which replaces existing texts with a new text in the string. Let's select the values that we have within column n, and then select what we're searching for. And this is gonna be a string value that we're searching for. Search for the string text test. And that should be lowercase. And what do we want to replace it with? Replace it with text, new value, and the currents number is another optional field. By default, all the occurrences are gonna be replaced. However, if you don't want all the occurrences replaced, you can set a number value there. And once you've created the function, we can apply that to all of the rows where now we're selecting the content from n, we're replacing test with new value, the string new value. And if we had more than one test value, if we add test again, it will replace all the occurrences of test with new value. I'm going to shrink down these rows. You can select multiple roles and you can resize them. So that's just a quick way to shrink them down. Let's try another one where we're going to try the dollar. This dollar is going to format a number into a local specific currency. We're gonna need a parameter of a dollar, so we need a number. And then there are options to the dollar. You can open that up and always find out more about it into a currency so we can specify the number of decimal places that we want to format it to. Just keep it as the default and converts it into the local currency, which for me right now is within dollars. We can find text within the string. We can use the find. And what it's going to need is the string value and then the texts that we're searching for. So let's select the string that we want to use and then the texts that we're going to search for. So it's going to return back the result. We can set a starting position. So this again is optional. You don't need to set a starting position. For this one. What it's gonna do is we see that it's throwing an error. And that's because I need to have the find parameter first. Then the second parameter that it needs is going to be the text that we want to search for. Returning back. It's going to return back the position of where this particular string value was found. So it's going to return back a numeric value if you want to convert to lowercase. There's also a function for that where it will take specified string and convert it to lowercase, returning back the converted value as lowercase, you can also capitalize it. So in order to capitalize the content, you use a function called proper and capitalizes the word. We'll use the value that we have within S2 that we did lowercase. This will once again capitalize the values. If we had another word in there, it will provide the capitalized version of it if we have text with whitespace, and now that we've got more spaces, we see that the split is no longer working properly because it's not able to reference that array and expand it properly. It doesn't always have to make sure that it is formatted within the proper format. For the function, will try trim. And what trim will do is this will remove out any extra whitespace. So any space that we have on the left side, the right side, or within the words applying trim. B2 is going to trim out excess spacing within the string to uppercase. So that's going to take the whole string and convert it into upper function name for that is upper. And then we need to have the one parameter, which is the string that we want to use to convert it into Upper. Let's, instead of using B2, will use the U2. And that will have the result that's trimmed and it's also output. You can also use multiple functions to gather. In this case, we want to trim out the content from B2, but we also want to convert it to uppercase. So you could nest the functions within the other functions in order to produce this type of result where we're trimming at first and then converting it into uppercase. We'll do the inner function first. And then the outer function. In this case, it doesn't actually matter because we're gonna be using those functions. And the trim would actually trim out the string value even if it was uppercase. 29. 6 Sheets Filter Functions: This lesson we're gonna be looking at the filtering options. So what we can do with filters and how we can apply different conditions to those filters to return back those filtered results from the range. Also sorting how we can retry and back sort results from a specified range and how we want to sort it, selecting the column that we want to sort, and whether it's going to be ascending or descending, and then returning back that data from the range that was sorted. And also how we can select unique content from the range and return back only the unique results from that selected range. So that's all coming up in this lesson. Within functions, There's also filter options. Let's apply a filter option to one of these columns where we're selecting the filter. And for the filter, it's gonna require a range that we want to filter. Selecting the range that we want filter option is gonna be the condition that we want to apply for the filtering. This is going to return back a Boolean value, so either a true or false. So we need to have a condition and we're going to check to see if the condition is going to be for the range is going to be greater than a specific value. So apply that same range and then check to see if it's greater than three. So that's gonna be the condition that we're gonna be applying. What it does is it returns back a filtered list of the results from the range. And it only returns back the items that are greater than. And that's the condition that we're applying Sida in a sort function. The sort requires parameters that include the range that we want to sort. So let's sort the first two columns and then what column we want to sort by. So this is requiring the index value of the column that we want to sort. So the index values start at one. So if we want to sort by column number one where we've got the IDs, we can sort by that. Then there's an option to do ascending or descending. So let's set that to be true. And so that's going to provide the newly sorted values coming from the two columns where we're starting with a 11 for A2. So it's sorting it as a string value. So if any of these were B, it would automatically changed the order as these are, taking it as a string value and sorting it by the string value order. Thought the numeric order, but it's a string value order so that a 10 and then the A14 is larger than that, the a2 is larger than that. We can always select that cell and we can change it to a descending order as well. So that would be false. So that's going to reverse sort that order. You can always find out more about what you can do within sort by selecting the function and then pressing the up and down to minimize the details about that function. There's a few other options available within the sort function. Let's also look at the unique. What uniq will do is this will allow us an option to return back unique content. So it will discard any duplicates in the rows that are being returned. So select the content and the function. Select the unique function to find out more about it. So we do need to have a range of data that we want to select, that we want it to be unique. And let's select it from column. And make sure that this range is unique. Select the range and add the range in there, and it's going to return back the unique values. So if any of these come back as duplicates, it's going to remove those from the sorted values, so it's only going to return back the unique IDs. We can also update the range. We have an option to filter it by columns. So by default this is going to be false. You don't have to include the false If it's already included as a default. So now we're extracting out the unique ID. 30. 7 Sheets Info Functions: This lesson we're gonna be looking at how we can get information from the various cells so we can check to see if it's blank. We can also check to see if the cell is a date, if it's an email, if it has a formula, if it has logic, so Boolean value of true or false if it's a number, if it's text, we can check to see that type. Also get the address of the cell and various pieces of information such as the column, the contents, the role, the type, the width of those cells that we've selected, and we can return back the info of those items. Let's look at some info functions within Google Sheets so we can check to see if a particular cell is blank. Adding in the function. And the function that's going to check to see if it's blank is blank. And then we need to provide a referencing cell. Need a value that's gonna be used in order to reference selecting the particular range. Or we can select the particular cell itself. So let's use G2. If the cell is blank, That's going to return back the Boolean value of true. Otherwise it's going to return back a Boolean value of false. We'll update this and we're going to check to see if it is a date, if it's a valid date. And we want to use the G column stuff. And if it is a valid date, then it's going to return back true. Otherwise it's going to return back false. We can also check to see if it's a valid email. And we can use column D to check that if it's a valid email, that's gonna be checking is email. And using column E for the value, actually it should be d2. So return back true if it is an email, if it's not, it's going to return back false. We can check to see if a cell has a formula. Shrink these down a little bit. Let's check to see if a particular cell has a formula. So we'll look within each to see if the formula is there is formula. And we'll look at H for for the formula. When we go down. It's looking at age 12. And in each 12 there is no formula. So it's then returning back false. We can check to see if it's logical. If it's Boolean value true or false. So looking at the cell value, that's gonna be is logical. Need to select the starting value that we want to use. This case, it's true. Once we go down, G12 no longer has logic in it, and that's what we're going to return back false. We can also check to see if it's a number. Using is number. It's going to return back whether the value is a number or not. So let's try y4. We can actually do started at e2. So these are all going to be returned back as numbers. So they're all going to be coming back as true. We can also check to see if a value is text. Will check column a for text is text. The function. And we'll use a two as the value that we're gonna be checking. If a value is just strictly a number. Drag that down and we're going to get returned back. False. Can also apply conditions for these where making it a little bit easier to see if it's true or false. So checking to see the full range text exactly is true. And if it is, then we can set it to be green. And if the text is false, then let's set it to be a reddish color. Apply that condition, so that makes it a lot easier to read. The results could also shrink down the size of the texts because these are all just gonna be boolean values. Then selecting the cells, you can shrink the column width. On all of them by simply selecting them. We can also check to see the type of data. So it's going to return back the type of data within the cell, selecting the type, getting the type of a value. The value that we'll use is going to be from column a. And it will return back the corresponding types. If one of these is a boolean, that's gonna be type for value. Type one is gonna be a number, type two is going to be tested. Type is Boolean, type 16 is an error, type 64 is an array, and type 128 is gonna be any other type of cell, such as images. We can just get info in general on the contents by doing an info and typing cell. So it's going to get information about the cell. And let's use the column a. We're throwing an error there, so it's a wrong number of arguments to the cell. So we're expecting two arguments there. We need to specify the reference. We need to specify the type of information that we want to get, then specifying the cell. So here we've got the different types that we can use. An address returns the absolute reference as a plain text. I'll go through a few different types. These are expecting a string value such as address. Example. They do have address. That's gonna be the address of the cell. It's not going to change. These are the absolute addresses of the cell. So if you want to reference that cell and notice the dollar signs, so that means that that isn't going to change. So you want to access that cell, this is the address to that cell. And I'll do a few other ones where we're gonna be looking at instead of address, you can also do the column. So it's going to return back to the column for the corresponding cell. So those are all going to be column two. So it's certainly back the index value. You can return back the contents of a cell. Contents. Returning back the contents will just show you the contents that are contained within the cell. You can also return back the role of the cell. So instead, call them that would be row. So this one is in row four. You can select and return back the type to get the info of the type of the cell. So the type is going to be two for that cell. That's going to correspond with text type. And then you can also get the width of the cell as well. That's the current width of the cell if we change the width. So we are looking at cell number b. If we change the width, it returns back the current width of the cell. So as we've readjusted it, if we recalculate the information of that cell, it's going to return back the new recalculated information. In this case, now we've got a width of 13 where the original width was nine. So when we redo the calculation for the width That's going to return back that current width. This is how many characters are visible within the width at this current text size. So if you were to take the size of the text and if you were to make it smaller, that would also change the current width size that we have available to us within that particular cell. 31. 8 Google Sheets LookUp Functions: We're gonna be covering lookup options, such as looking up the column number of an address of a cell, looking up the number of columns within a range, that getting the formula text from a particular cell, getting the formula as text, looking at the H look-ups. So the horizontal lookup, looking and trying to V lookup, which is the vertical lookup, selecting an index and returning back particular row and column from that index selected range. Also look up and how that works. And then match how we can select a column or a row and match a value that corresponds to it and then return back either column or the row value. Looking at the lookup options within the Google Sheets, selecting one of the lookup options that we have is address. What address does is it gives us a cell reference as a string. So we need to select the row and the column. It's expected numeric values for those, and then the absolute relative mode. So that's optional as well as a notation is optional. So these are all optionals within the content. Selecting up the address where you are going to use the row first, so row seven, and then column index value of three. And that's gonna return back the cell C7. That's gonna be this one here. We're turning back the contents of that, and that's how you can get the address of that particular cell. To use that content. You can also get the column, the column and get the cell that we're referencing. Let's reference cell number B with row number of ten. That's going to return back the column number two. And that's the index value of that column. We can also get the columns from a specified range. Columns. We need to supply a range that we want to select. Let's select the range. And that's going to return back that particular range spans across six columns. There's formulate texts. What this does is it returns a formula as a string. So if we want to see the formula from a particular column, such as each two from a particular cell. It's going to return back the formula that's within the formula bar of that selected cell. There's also an HLookup, H lookup. What does, does is it's a horizontal lookup. And it will sort through for a particular search key. So this can be a string value and this is what we're searching by. Then the range that we want to search. Let's search for the string of test. And then the range that we want to search will select all of this content. As the range. The index is going to be the role of the value to be returned. Where the first row in the range is numbered one. Within the example. Once we've selected the range, the index is going to be the row value comma separated out. And we're going to use the contents from row number one. And then how we want to sort it. By default, it's going to be true. And this is the indicates whether the row to be searched, the first row of the specified range, and how it's sorted. We can just leave it as the default. So this is an optional argument. Set the index, and let's see what we get returned back here. So the result for the HLookup for looking for test within this range returns back the contents of b2. Let's change what we're searching for. So that's also returning back the contents from B2. Let's update the index value that we're searching. Now we're gonna be searching under the index value of three. And I'll just update column B to be either yes or no. And instead of looking for test. It will look for the value of yes. And the reason that it's returning, the reason that's returning back the know is that it's looking for yes. Once it finds it within the horizontal lookup. So it's looking under the horizontal rows, it matches, yes, and it's returning back the result from the second row. So that would be returning back the value of null. If we update this value. That's the value that we're going to see gets returned back within the horizontal lookup. Now more commonly we're gonna be using the vertical lookup. So let's update this and set this to the vertical lookup. We can use the same parameters where we're searching for the key which is gonna be yes, the range is going to be the same. Selecting range starting at B2 to G11. It'll say use the same range that we did for the horizontal lookup and returning back the contents of the column. And in this case we'll use column with the index value of two. That's returning back this wherever we've got the value of. Yes. If you want to return back the name, that's gonna be column with an index value of one. And if we want to turn back the first column, that's going to be using an index value of 0. The index values start. The next value is going to be represented by what we have for the range B2. If we were to change this to a2, that would then be returning back the first matching value from the column. From the first column is an index value of one. And that's where we're returning back the before, because now the range is specified as A22, G11. So that's going to be including this range. And returning back the result from column one as we've updated if want to get the result from column two now, because now the range is including a. If we wanted to include b, then we'd have to update it at this point, there's also an index lookup. The index lookup, what that will do is that's going to look up and return the contents of a cell specified by row and column. This is going to be the range that we're looking at. And then the row and the column are optional. Let's add in their range that we're selecting. And it's gonna return back that entire range. If we only want to return back a specified row and column that we can specify. If we want it to have just row three. Return back, it's going to only return back the results from that range from row three. And if we only want to return back a specified column that's within the range. So let's do 33. So that's going to return back that e-mail address for that specified row and column. That's how we can return back an index. And then we can specify what we want from that index and specify the range. Specifically, there's also a lookup. So lookup allows us to look up a value. Once again, we select the range. We need to specify the search key that we're looking for. And then the range that we want to look in. Let's look for the key of test. And then the range that we're gonna be looking for will be that entire range of contents. So we do get a value of yes. And because we have multiple matching results for test, Let's make this a little bit more unique where we're going to look for a three. And when we have one instance of A3 within the index, so see what we get returned back and now we get a value of no. It's returning back to the value from the last column of f and returning back at the result. So if we were to update that, then this would be the corresponding value. We can also look up and use match position of an item and arrange that matches the value. Again, let's look for the search key, and we'll use the string value of before. For the search key. Select the range that we want to search within. We can also specify the search type if we want. This is an optional value. The default is one that finds the largest value. We'll just leave this one out for now. So it's not able to match the range. And the matches actually should be looking within a single role or a single column. This case, we were looking across multiple columns. Let's update the range and select the range as column a. Now we're able to return back. The value that's being returned back is where we've got before. So that's returning back the role value for the match. Just as we saw with the columns. We can get the rows as well. So if we select the row value, we can also select the rows that are included within that selected range. Just as we did with the columns, we can return back the rows or the columns, or the exact role from a particular cell. When we're specifying the address of that cell. 32. 9 Sheets Math Functions: This lesson we are going to be looking at some of the math functions. We've already looked at some. But there's also is even, is odd. There's rounding, rounding up, rounding down that it can get random numbers. You can get random numbers between two different values. You can sum depending on a condition. And there is also pi. And we're gonna be looking at several other math options that are available within Google Sheets. So that's coming up in this lesson. Lot of different math type of functions and we already had looked at one of the most common ones, which is sum. So that summarizes two values. We can select those values and comma separate them out. And what this is going to do, this is going to total the values. In this case, this column is still sitting up as formatted as a date. So let's update that to be a number. So this way we're getting a number being returned back as the sum. There's also, we can check to see if a number is even or if it's odd. Checking to see if a number is even. And we'll use the result from column B. And this is going to return back a Boolean value. Just as we check even. We can also check odd and that will return back if the number is odd, we can round numbers. So if we have numbers that have decimal places, we can either round them down, around them up. Rounds a number according to the standard rules. And there's also round up and round down. So you need to specify the number that we wanted to use. This will return back the rounded value. This is just round. This is going to be up and this is going to be rounding it down. And we'll still use the value from E2. And instead of round, we'll do round up so that rounded numbers up. Let's also do the round down. So select that number from a and hit Enter. It's going to round the numbers down. You can also select and get random numbers. If we needed a random number, it's going to return back a random number from 0 to one. These are all going to be random numbers. Every time we drag it, it's going to generate a new set of numbers. As these are all randomly generated numbers that are coming in. We can do a random between the low and high with a low value and a high value. And that's random between whatever the minimum value is that we want to use. And then whatever the maximum value is. Now we're going to be producing random values between that set of data. There's also some way we can use some if it's a conditional sum across a range. So do a sum if the pattern that we want to apply. In this case, we can see if the sum is gonna be greater than 20. The other parameter is optional. So the sum if will fluctuate depending on if this conditional sum across the range is met, include Pi. If you want. There's a function to return back the value of pi. So that's always going to be the same. Returning back to the value of pi and all the typical math functions that you would expect. There's a whole list of them here. Under the math functions within the Google Sheets. You can try those out. And as you hover over them, it will give you more information about what's available for these functions. 33. 1 Investment Calculator: Setup the investment calculator app, opened up sheets login to your Google account, go-to sheets, create a brand new spreadsheet. I'm going to give it a title that's going to go across four fields, merging that cells together. So do a merge all, and then going into the quick menu will horizontally align those so we can center those. I'll make the font slightly bigger so that we can easier see the heading. So there are some values that we need to track into our investment calculator. Whatever the current value is of our investment. We also need to track back the rate of return that we want to have that we can get for our investment. The number of payments. Then what we want for the payment amount. So this is the amount that we're gonna be paying in each time. This is where we're going to calculate the future value of our investment after we've completed all of the payments. That fixed rate with the amount of payments that we're estimating. Let's start out with our current value that we'll subtract out 10 thousand from our current account to invest that amount into it. The rate of return that we're looking at or that we're hoping to get is 1 or 0.45% rate of return. The payment amount. So the number of payments that we want to make, let's do 24 payments into this. So they can be monthly payments over two years or whatever number of payments that you want to make into this within the given period of time. And this is going to be where we're going to have the resulting value that's calculate it back out. For the amount that we want to pay. We're gonna pay into it an amount of $500. So each payment period is going to result in $500 being subtracted. And that's going to result in a total payment as we're doing 24 payments of about $12 thousand. So let's calculate out what our future value will be once we've got the rate of return calculated. And for this, we can use a built-in function. And the function that we're gonna be using is going to be a financial function. It's a financial function called FV. So this is the future value of an annuity investment. You can always find out more about the functions in Google Sheets by pressing the question mark. And that will give you an example of what is being expected. So the rate is going to be the first parameter in the argument. So that's the interest rate than the number of payments to be made. So we've got that as 24, the amount of the payment to be paid. So we've got that at $500. If we do have a starting value, so that can be the current value. Then the last one is going to be optional, that's ending or beginning. Let's close that off and we're going to fill out first the rate that we have. You can select the cell with the rate. Makes sure that you're within the FV formula. Can close that off and select the rate first. And then next is the number of payments. We're doing 24 payments. I'm going to select that as the next argument, the payment amount. So that's what we've got in column D is gonna be the payment amount. Then the last value that we want is whatever the current value is. Let's calculate that out. Then as a result, our future value after we've made that 24 payments plus the current rate is going to be twenty three thousand seven, seventy nine, seventy six. We can have some other numbers. So you can just copy drag it down that will copy the formula over. So if we're making a payment of $200, then this is what the resulting end result is gonna be after 24 payments, we can adjust it to 12 payments of $500. So that's gonna be our resulting amount after the period. If our interest rate, if we can score a better interest rate, the rate return, if we can get a 1.85 rate of return, that will be our value. If we start out with a value of 100000 after the 24 month 24 period payment periods, we're gonna be at 13,775. You can adjust these numbers as needed in order to suit and to make the calculations for your investment over time with a given number of payments and periods and the rate of return. Adjusting the numbers as needed. 34. 2 Mortgage Calculator: In this lesson, I'm gonna be showing you how you can use the PMT calculator. So that's gonna be the PMT calculator, which is the periodic payment for an annuity investment. We're gonna be looking at how it affects the house price. This is the house price that you're buying it at. This is the downpayment percentage that you're making. So total mortgage amount is going to be 600 thousand. The interest rate that you're gonna be paying, the number of years that you're gonna be paying this over. And this is where the monthly payment is going to get calculated. And then just down below, I've calculated at out what your monthly which month of payment, the balance that you have at that current month, the amount of the payment that you're making, the interest that is being charged on the balance, also what the new balance currently is, and then the interest that you've paid cumulatively. So this is gonna be over a 120 months silver ten-year period. And this is gonna be the final result after ten years. And of course, you can drag it down longer depending on how many years you want to make the calculation. If you make some changes to this, let's say you end up with a 4.25% interest rate that's going to affect your monthly payment. Maybe you've get a little bit less of a down-payment. So you're only putting 15% down. Again, monthly payment all gets calculated. And then you can always see that where you are on each month as you make the payments towards the mortgage. So that's what we're gonna be covering in this lesson. We'll set the house price, the down payment. Then this is going to be the mortgage amount. The house price. If you're buying a house for 500 thousand, you want to make a down payment of 20% on the house. Setting your mortgage amount for that current mortgage amount by using the value from a eight and multiplying it by the value of B8, need to take a eight and subtract the result of a eight times B8, that will return back the mortgage amount that we're gonna be taking on the house. So if we were to adjust these numbers and we bought a house for 600 thousand, that would give us 480 thousand. I'll also update and format these cells into currency. And this is the total mortgage that we're gonna be taking out. If we have our mortgage amount and then our interest rate years of the mortgage. So let's set this to 20 years. The monthly payment that we're going to make. The PMT payment with annuity investment were first we're gonna be setting the interest rate, which is gonna be in D eight. And we wanted to divide that by 12 so that we're getting a monthly value for that number of years and multiply that by 12. So that will give us the number of months and the number of years is an EA eight. So multiply that by 12, subtract the value of c eight for the formula. That gives us a monthly payment of two thousand, seven hundred and eighty, three hundred and eighty one. And we can see once we make some adjustments, maybe we buy a cheaper house. So that brings down our monthly payment. Maybe we put a little bit more on the downpayment. Once again brings out our monthly payment. And maybe we get a slightly better interest rate. That again brings down the monthly payment and we extend it over more years. So once again, that brings down our monthly payment. This is a way that you can use in order to calculate out what the mortgage payments are gonna be. Let's set the full schedule starting on the first month. Your balance is going to be the total that you have from C of 300 thousand. You're going to be paying this monthly amount. And that's going to be coming from cell F eight equal that to F8. The amount of interest that gets charged on the balance amount is gonna be taking the balance amount, which is now B12, and multiplying it by what we have for the interest, which is gonna be sitting in column D using row number eight and taking that value and dividing it by 12, that's gonna be the number, the amount of interest that we've paid on this period. And this is gonna be the new balance, which is gonna be a value that's going to equal 12. Subtracting the value of the amount that we've paid, which is going to be a total from the amount that we paid from C 12, subtracting the interest value, which is D12. That's gonna be our ending new balance after we've made the payment. You can also calculate the interest paid, and this will just be the cumulative amount that we have from the interest value. And this will be the value from cell number d 12. So this is going to be equal to E 12, the amount that we're paying. For the next row, we need to make some adjustments to these amounts. Getting the balance from the old, previous New Balance. And that's where we're getting the balance, the amount that we're paying, it's going to stay the same. So it's gonna be a static amount coming from column number F, Ruth row eight value. That's the monthly payment value, which is actually coming from F8. That's not gonna be changing. So we're gonna be using the dollar signs in order order to indicate a static value and the interest paid will be cumulative. So we're going to add the role above will be the amount of D13, what we have in the value of f, C12, E12. Set that, then. Now it's we're on a third month and that value is correct, that value is correct. The number of interests has gone down slightly. So let's drag it down. So we're gonna go ahead and drag the formula down so that we can have the total calculations for a 120 months. And that's gonna be a period of ten years. And that's gonna be the result here. After ten years of making monthly payments of 1305 to the mortgage, we're gonna have paid 86 thousand to the mortgage. We're gonna have a balance left still honor mortgage of 230 thousand, and that's coming from the starting balance of 300 thousand. So let's make some adjustments and we're going to set this over a ten-year period. Now over ten years, we should have it fully paid off at the end of that ten year period. And this is gonna be the monthly payment to two thousand nine hundred thirty one hundred fifty seven. That's the number of amount of interest that we're paying on that particular month. And this is the cumulative total amount of interest that we've paid over that period. You can adjust these as needed. You can adjust some of the values. I'm just going to center it to make it look a little bit better. And depending on what the house prices. So if you bought a house for 800 thousand and maybe he got a slightly better interest rate. This would be what your monthly payment would be if you're paying it off in ten years in full. If you want to stretch that out to 30 years, you can still see what you're gonna be at, at the end of ten years. Now you're gonna be at this amount having paid this much in interest, and this is gonna be your monthly interest payments. You can change these numbers and that will affect the payments as well as the balances in the chart below for the details. 35. 3 Loan Calculator: Let's set up the loan calculator and then we'll use the function in order to make the calculations within sheets. So this is a loan payment calculator. We need a few values. The first value is going to be the principle than the periods, the number of periods that we're gonna be making the payments. So these can be months. Each month, what we're gonna be paying. And then the rate that we've got for the loan. We can also have a future value. Then lastly is gonna be the payment amount that we need to make for each one of the payment periods. We're gonna have a payment period. So let's do 60 payment periods. We'll start out with the principal loan amount of $50 thousand. So if you're buying a car, you want to pay it over 60 months. So let's put five years and let's see, you've got a rate of 2.25% for the loan. And at the end of the loan, you want to have it fully paid off. So set that amount to 0. Let's make the calculation of what each one of the payments is going to be. We're going to use the function that's going to be PMT. And this is a periodic payment for an annuity investment. And it's gonna require some arguments. The first argument that it's going to require, you can find out more about the arguments. The first one is going to be the rate than the number of periods, the present value. And then optional for future value and ending value, selecting the rate. And then comma, separate out the arguments with the periods. The next, let's get the principal amount. We can hit enter here. And this is going to calculate out the number of the amount that we have to do for the payments to adjust the interest rate for months. So that would be 12 months per year. So there's gonna be 12 months per year. That comes out to $881 per month over a period of 6060 months in order to make the full payment and to return back a value of 0, having it paid off in full. If we want to have a future value that's still sitting there, That's an amount owing. Let's say we want to have $10 thousand, so that's still owing. We can add in the future value. That would be coming from cell D3. That would reduce our payment amount to $724 per month and we'd retain a future value that we'd have to pay off of $10 thousand at the end of this. 36. 4 Basic Math and Text Functions: In this lesson, we are going to be covering some. So it's one of the commonly used functions within Google Sheets, as well as how we can use concatenate to pull back together to string values and have a spacer between there. We can also how we can separate out content from the left of a character, from the middle, and then also from the right-hand side, transform text to uppercase. And then also use the split with the delimiter and output the results from the string value into the spreadsheet. That's all coming up in this lesson. Let's create a full name. So we've got a series of first names, series of last names. We want to create a full name. So we want to bring it together, a string texts together, bringing them together using the concatenate function. So adding in concatenate, what cat carbonate will do is it'll append one string to another. So we select the first part of the string and the connector. So we want to have a blank space there. So it's using the double-quotes and the blank space and then the last name. We're going to be creating that. Let's set that up as the formula and using the auto, auto fill, it will do the same for the remaining cells where it will create the full name. Let's also create an account. If the users, if the particular users a savings account. And this is gonna be the number of the account. And I'll create a few other accounts and they might have a checking account. This is the number of the checking account. Another saving account. Let's just increment. Let's increment that by one, we're going to break apart these string values. So if we want to specify the type, the type is going to be contained within the first three characters on the left-hand side. So let's use the left function. And it's gonna be selecting the sub string. The substring that we're gonna be using is gonna be from D2. We want to take the first three characters from the left, press Enter, and then the autofill suggestion that will pull out the type. If maybe we have a branch or something. So these next three characters are going to be the branch value. So we can select that out as well from the string using the mid. So this is getting a segment of the string content. And we're gonna be using the value for D2 as well from coming from the account. Let's separate out. So separating out from the middle, starting at character number four, we want to return back the next three characters and press Enter. That's gonna be returning back that second part there from the account. So that's gonna be the remaining part on the right-hand side, just as we did with the format, set it to create the function. We're going to take a substring specified from the right-hand side. The string that we want to use is in D2. And we want to pull back the remaining characters. In most cases it's four characters. This should be selecting the four characters. And we'll do the autofill suggestion. We can also transform the names. So let's select the value that we wanted to use. And this is gonna be sitting within the C2 and converting it to upper from C2 content. That will convert the content within there to uppercase. We can also split those now as well to go to first and last name. So let's do that where we're going to use the split. And split allows us to strength to split a character by a delimiter. So we need to specify what we want to split. So we want to split the contents of H2 and how we want to split it. So we will split it by wherever there's a space, we're going to split that. It'll automatically create two columns or whatever number of items we have within the splitter. So in this case we're splitting it to the two columns there. We can drag it down and that will take care of the other remaining rows of content. There's also typical math functions. If we want to get the sum of some cells, we just need to select the range of cells that we want to get the sum of. And that will formulate the sum of those values. If we drag it and we want it to increment, we need to set up the pattern that we want to increment. Now, the pattern is going to increment by the same pattern. So here we're incrementing by one. So we've established a pattern. So the next one's gonna be 78910. Here we were incrementing by two. So that's the pattern that was established in the last column here we're just applying the same formula. So that's where we're getting the sum value being associated within there. These are some of the commonly used functions within Google Sheets. 37. 5 Logical Functions in Sheets: Hey, this lesson we are going to be looking at how we can apply logic. So using the if condition, checking to see if a value is greater than 1000. And if it is, then we're going to return back pro, otherwise beginner for the bonus, we're going to create our random value between 04 thousand, adding that to the initial points plus the bonus points. And then we're going to set either pro or beginner. And then using the or to check if either one of them are pro, if they are, then it's gonna come back true as this is a pro and also showing you how you can use and where it's going to be checking to see if either one of the values from C and E are gonna be pro. And if it is, then they both have to be true in order for it to come back true. So if one of them is not true, then it's going to come back as false. Let's set up some logical values. So if you've got some points here, and this is going to be the player information. And this is going to be the level that the player is odd. We've got adding and just some random values here. And then we can calculate the different points and the levels of the player is going to be currently at the player. These can all be different players, just going to randomly generate them here using the Player One, 23456789. Let's set up and select the level. The level we can do as a condition. So depending on what the value is of a, we can check to see if it's larger than a certain amount. And if it is, then we can set it up as a player. So using the if and it's gonna return back a value. So dependent, depending on if a2 is larger than one hundred, ten hundred. And if it is, then the result is going to be that it's pro. Otherwise, the player is gonna be beginner. And let's populate all of that information. We can also have a bonus value. This can be a random value. If you use rand, then this is going to select a value from 0 to one inclusive. If we use RANDBETWEEN, this is going to give us a random value between two values. So let's start at 0 and have a random value of 10000. These are just generating the random values. Let's do level plus bonus. We'll use the same values here. But we're gonna be using the value from d, two plus two. We have to put the parentheses around that. With the bonuses. Doesn't look like any of them change. Let's set this to random 10 thousand. And that puts them all pretty much within the pro level. Let's scale that back a little bit too random, 4 thousand with the bonus value where we're setting a random value that's gonna be updating. Let's check to see if both of them are the same. So do an OR, or a check to see if both have a value of Pro. So we're looking at the value of C2. We're checking to see if the value there is s2. And the other one that we want to compare is E two. And check to see if that has a value of pro. If they both have pro, then that's going to come back as true. If they have something different, then they're going to come back. If they're neither one of them is pro then it's going to come back as false. So this is or it can also do an and in there. They both have to have that same value instead of the or. Let's update this to and, and when you need to update this because it updated the values. So this has to be easy thing, the C2. And we can set this as a static value. So we can use the dollar signs in order to indicate that this is a static value that we don't want to change as we drag it. This would be E. Then dollar sign two to see if it's pro that comes back as thought. If we drag it down, then it's not going to actually update those values. But if we do want them to update the values, we can remove out the dollar signs from the row values. So we do want the rows to change and increment. If both of them are pros, then it's going to result in a true if we're using ors. So if any one of them is a pro, then that's going to come back as true and they both have to be true in order for a true result. Otherwise the result is gonna be false. Those are some of the logical functions that you can add into your spreadsheet. 38. Sheets sharing is caring: Coworkers asking you to get access to your spreadsheet. Well, this is really easy to do with Google Sheets. So you've got a few options. You can go under the File, under share and share with others. Or also one of these are ways is to select in the top right-hand corner and select the Share button. And this will give you a pop-up window already telling you who you've shared it with, what permissions they have. And this is also where you can add new people and groups to share the content. You can also set the link restrictions. Right now by default, it's restricted to only people that are added to the shared document that are gonna be able to see the link, you can adjust. So change this to anyone with the link so that can be restricted. And also anyone with the link then can see that the contents of the document. You can also set if they're gonna be a viewer, commenter or editor. So loose at different levels of permissions, viewers can view the content. Commenters can view and add comments to the content. Editors have full permissions so they can update, change the contents of your document. Stay away from this one if you're sharing it with just everyone with the link, make sure that you do have proper permissions as you are sharing it with the link. And one of the best ways to keep your document is to keep it restricted. And that way you can select who has access to it and individually select what type of access they have. It can only be one owner per document. You can also transfer the ownership if you are the owner to another person that the document is shared with. And you can also update their sharing permissions, either viewer, commenter. And then if you want to be really mean, you can also remove them. 39. Sheets offline no internet: Internet, no problem. You can go ahead and access your Google Docs offline. So this is an extension in a comb. So going over to the Chrome Web Store, search for Google Docs offline, you're gonna see there's a lot of users already that have downloaded Chrome extension and then just add it into your Chrome. Whenever you go to sheets, you can set and use the offline option that's under File, make available offline. Now my file is gonna be available stored locally on my computer within the browser. Can access this document now offline, make changes to it, update it and access it. There's also a setting within Drive. You can select the file within the drive and also click it and make available offline. So this is gonna be the same setting that we just saw within sheets. 40. Sheets Share it Now: What if your friend wants access to your spreadsheet, but you want to give them access to your sheep. You just want them to be able to view the data within the sheet. Well, there is an easy way when you go into your Google Sheet under File select Share, and then under the Share menu sub menu, select published to the web. When you publish it to the web, this is gonna give you a web URL. You can select either a webpage, comma separated, top separated PDF, Excel, or an Open Document Spreadsheet. And that will provide you a URL that you can share it with others. And they've even made it easy where you can share within the Gmail, also within their social accounts, either Facebook or Twitter. You can also select what you want to share. You can either share the entire document or you can select the separate sheets in order to share those. Once you have published it to the web, they're gonna be able to see your content, but of course they can interact with it. They can only see the content. And this content is gonna be updated. If you make any updates to your content, percent goes to the website, they're going to see that new updated content directly within the web browser. 41. Sheets How to Get Help: Hey, are you stuck with Google sheets? Do you need help? Well, one of the easiest ways to get help is to open up your sheets and in the top menu, select help from the help, you've got several options for help. You can use the pop-up window for help, which is the first option in the drop-down menu. And you can also search the menus using the option key. If you select that, this could open up a pop-up window where you can get additional help. So here we're selecting the can't open form and it's gonna give you more information about how to troubleshoot it, how you can open a form. The other way to get help is within the training. So that's the second option under Help where you can select training. And that's going to open up the pop-up window where you can learn more about Google Sheets and get more training and help and support from Google directly with your sheets content. 42. Sheets Versions2: Oh, oops, I've updated my document, but I need to go back. Something is very wrong. Let me show you how you can go back to other versions of your Google doc, which are automatically saved in the background. So under File select version, Version History. And here you can see the version history of the document. It automatically saves a version history as you're updating and working with the document. And also if you're collaborating, this is an excellent feature because you can see what others have done and the changes that have been made. If you do want to revert back, if you've accidentally removed a column or accidentally updated content. Now it's not there. You can always go back on the different versions. So going back and clicking the versions, you can see the different changes when they were made, highlights it within green. If I wanted to go back to this earlier version that I created earlier a few hours ago, I can select it in the right-hand menu under the version history. And from here I can restore it, this version. In addition, I can also give this version of the name. So this is a mean version. I can give it a name and this is going to commit it into the version history so that a later point I can also go back and revert to this version. In addition, I have an option to make a copy, so that will create a brand new spreadsheet with that data as the sheet data of that version. And if you do want to revert it, you can restore this version at anytime. And that will give you the new restored version of the document instantly. And from there, you can make updates as needed.