Hi everyone, Kevin here. Today we are going to learn how to use Google Sheets. Google Sheets is free spreadsheet software that you can use to get insights from your data. We’re going to start with the absolute basics. First, we’ll look at how you can even get Google Sheets, and I know, when you open up Google Sheets for the first time, it may very well feel like you’re in the cockpit of a jet plane. There are so many different menus, so many different buttons. What do they all do? Once we cover the basics, then we’ll advance to formulas and functions, inserting charts, using pivot tables, even collecting data from forms. By the end of this video, you’ll be a pro in using Google Sheets. Along with this video, I also highly, highly recommend using the official training provided by Google. They offer courses on getting started with Google Sheets, advanced topics, using functions, formulas, and charts. You’ll find links to these resources down below in the description. Here as an example, I’m currently in the functions, formulas, and charts quest, and it’s made-up of all of these different labs. When I click into the pivot table lab, here you can read all about pivot tables and how they work. But even better, you can follow along side-by-side where you actually learn by doing, and personally, I think that’s the best way to learn. Once you create your pivot table, you can check your progress to make sure that you learned everything correctly. At the very end, you can even earn a badge that shows the world your understanding of Google Sheets. Once again, you’ll find links to these resources down below in the description. What even is Google Sheets? Google Sheets is spreadsheet software that allows you to analyze and get insights from your data. Basically, it tells you the story behind your data. Here at the Kevin Cookie Company, we use Google Sheets to track all of our cookie sales data. With conditional formatting, I can very quickly see well which month was the best and which month was the worst. I could also insert a chart and that way I can visualize the data over time. I can use pivot tables to make sense of my data just by dragging and dropping my mouse, and best of all, I can even work together with others. We’re going to look at all of these topics and more. There are a few different ways that you can get started using Google Sheets. First head to the website Google.com and in the top right-hand corner click on the app launcher. If you don’t yet have a Google account, you’ll need to set one up, but it’s completely free to do that. Within the app launcher, here I see the icon for Google Sheets. Now you can also press and hold on this to move it to a new position to make it easier to get back to it in the future. When you click on this icon, this drops you on the start page of Google Sheets. Now alternatively you can also navigate directly to the website sheets.google.com and this will also drop you directly onto the start page. If you prefer to jump directly into a new spreadsheet, you can go to Google Drive. Also in the app launcher, click on Drive and in the top left-hand corner you can click on this new button, and here you have the option to create a new spreadsheet, and this will drop you in a blank new spreadsheet. You can also navigate to the URL sheets.new and this too will also drop you into a new blank spreadsheet. On the Google Sheets start page, in the top left-hand corner, we can kick off a blank new workbook, and in a moment, we’ll do that, but first, over on the right-hand side, you can also start from a template. When I click on this text, this expands the template gallery, and you have many different options here. It’s well worth a look to see if maybe one of these templates meets your needs. I’ll click on the back button in the top left-hand. To get back to your spreadsheet, you can use the recent list down below. You have different sorting options, and you could visualize these files in different ways. Up on top, you can also search for a file using this search field. Now let’s say that maybe you have a spreadsheet on your computer, maybe in Microsoft Excel format. You can click on this file picker icon and then you can upload a spreadsheet from your computer and then you can use Google Sheets to edit it. I’ll click on the X icon. Now let’s kick off a blank new workbook. I want to track the Kevin Cookie Company sales data, so let’s click on this. This drops us into a blank new workbook, and to return back to the start page, in the top left-hand corner, I can click on this sheets icon, and here that brings us back to the page that we were just on. But I do need to get some work done, so let’s once again click into a blank new workbook. Here in the workbook in the top left-hand corner, you see that the title was currently untitled spreadsheet. I want to give this spreadsheet a name, so here I’ll double click and I’ll delete that and then I’ll enter in the name cookie sales analysis and feel free to do the same if you would like to follow along. The spreadsheet now has a name and I see a few additional icons over on the right-hand side. When I click on the folder icon, here I can see where this file is saved and it’s currently in My Drive and here it’s under cookie sales analysis, but I can also create an additional folder if I want to save it in a different location. Over here I also have this star icon and when I click on this, this stars my file. So, what does that do? Well here, let’s click on this storage icon again and here I can go into My Drive. When I go to My Drive, here I see all of my files, but there’s a category on the left-hand side called starred, and when I click on that, here I can see that cookie sales analysis. This makes it easier to get back to the files that you care about. I’ll click on the cookie sales analysis and this brings me right back into the spreadsheet again. Also, over on the right-hand side, we see this cloud icon, and when I click on that, I can see that all changes have been saved to Drive. As we work on this spreadsheet, you don’t have to go to the file menu and save it. It’ll automatically save every single change that you’re making. Also, if you want to work on this spreadsheet while you’re offline, so let’s say you have no internet access, you can turn on offline access. You will need to install an extension to be able to do that. Let’s now shift our focus down below to all of these rectangles. These rectangles, these are all referred to as cells. On the sheet, you’ll also notice that we have all of these columns with letters, and we have all these rows with numbers. To refer to one of these cells, we use the intersection of the column and the row. Here I have my mouse in cell A1, so you start with the column followed by the row. Also, when I click into this cell, up here in the top left-hand corner, we see the reference to A1. Here, if I shift into B2, here we now see the reference to B2. When I hover over this, you see that this is referred to as the name box, and you also see the associated shortcut key, and all of the different buttons on the toolbar here have similar tooltips. Here when I hover over, here I can see that this is the bold icon, and I could also see the associated shortcut key. If you’re ever unsure what one of these buttons does on the toolbar, you can simply hover over and that’ll tell you what it is. I now want to start entering some data into this spreadsheet, but before I do that, I do want to adjust the zoom. Over here on the left-hand side, I can click on this drop down and I’ll adjust the zoom to let’s say 150%. That’ll make things a little bit easier to see. I’ll now go back to cell A1, and I want to type in a header for my table. Here I’ll type in sales. Then I can press the tab key or the right arrow key and that’ll move me over to cell B1, and here I’ll type in date. I can now press enter or the down arrow key and that’ll move me to cell B2, and here I want to enter all of the different months of the year to keep track of all of our sales data. So, of course, the first month of the year is January 2022 and then the second month is February 2022, and I don’t want to make you watch me fill out all the months of the year. That would take a long time. Instead, I’ve established a pattern here. Here I typed in one month of the year followed by another month, so hopefully Google Sheets is smart enough to follow this pattern. I’ll highlight both of these cells, and when I highlight them, we see this blue square in the bottom right-hand corner, and when I hover over it, my cursor changes to a plus icon. I can press on this and then drag down to row 13 and then I’ll release and here you see that Google Sheets was smart enough to follow this pattern all the way through the end of the year, and that brings me down to December 2022. That was a lot easier than typing it out. This is called smart fill. I can also use smart fill with numbers. In January, this is our worst month of the year. We only sold 1,000 cookies. People have New Year’s resolutions, and they just decide not to eat as many cookies. I’ll press the enter key, and then here for February, we doubled our sales to 2,000. I’ll enter 2,000, and here I’ve established the pattern. It increases by a 1,000 between months. Here I can click on this blue icon again and I could drag all the way down through December and here it follows that pattern to 12,000 sales. Now, of course, we don’t just have a straight line of sales. I want to randomize things a little bit just to make this analysis more interesting. Here I’ll highlight November through February data. Then I’ll right click and at the very bottom of this context menu, we have the option to view more cell actions, and here’s the option to randomize the range. I’ll click on that and here that randomizes the order of all of these numbers. This gives us some good data to work with. We have all of the sales data entered in now, but it is a little bit hard to read. I think I could format these numbers better. I’ll highlight all of these numbers and up on the top menus there’s the option for format. When I click on this, here I could select different number formats. Here I could format the text. I could set the alignment. I could set the wrapping, the rotation, so I have many different options here. Now I’ll exit out of this menu. Down here in the toolbar, I also have some of the most frequently used formatting options. Here I can click on this dropdown and here you’ll notice I get this same menu where I could format the numbers. I’ll format it as a number. Here with sales, it doesn’t make sense to include decimals because we’re not going to sell a fraction of a cookie, so I want to remove those decimals. Up here in the bar, I could also decrease the decimal places. I could click on that twice and that removes the decimals. Over here, I could re add them, but I don’t want them, so I’ll remove that. Looking down below, I should probably provide some context as to why cookie sales are so horrible in January and why they’re so good in December. I mean maybe we have someone new joining the company and they don’t know the cyclical nature of the business. Here in column C, I’ll type in note as one of the column headers and let me type an explanation for why January is so bad. Next, I’ll go down to December and type in a reason here. I’ve now typed in both reasons and I think that provides some good context, but one thing I don’t like is you’ll notice the contents of the cell bleed into the adjacent cells. Luckily, I can adjust the width of the column. Here I’ll click between columns C and D and here I can drag it out, so it fits all of the contents. Now instead of dragging that out, here I’ll press this undo button. Here you’ll see the shortcut key is CTRL + Z. I’ll undo that. Here I could also double click between column C and D and that’ll automatically adjust the width to fit the content that’s within the largest cell. Let’s say that maybe you have a lot of columns or a lot of rows where you want to fit to the content. Here in the top left-hand corner, I can click right here, and this highlights all of the columns and all of the rows. Here I can double click between two of the columns and that’ll adjust all of them to best fit, and I could do the same for the rows. Looking at this data, I don’t know if it makes sense to show sales and then the date. I think I’d rather show the date first. So, do I have to delete this column and then re-enter everything? No, of course not. It’s really easy to move data around. Here I’ll go up to column B and here I will click on it, and you notice that my cursor changes to a hand icon. Here I can press again, and this now grabs that column, and I can now move that to a new position. Here I’ll place that column ahead of sales and that switches the position. It’s really that easy. All of the data here is really solid, but I’m not quite satisfied with the look and feel. Let’s go back up to the format menu to see what we can do here? Here at the very bottom, there’s the option for alternating colors. Let’s select that. This adds some very nice formatting to this table. Here I see a treatment on the headers, and I also see banded rows. Over on the right-hand side, it opens up this pane where I can customize what this table looks like. Here I can choose different styles. Maybe let’s go with this yellow one. It’s amazing what yellow can do to brighten up the picture, and it really makes my boss have a more positive perception of all of this data. It really does wonders. Let’s now close out this pane. When I look at this table, it’s coming along nicely, but I think I can also apply some formatting to make the numbers stand out a little more. So maybe some formatting when a number is really good, and a number is really bad. I’ll highlight all of these numbers and let’s go back to the format menu. Right by alternating colors, there’s the option for conditional formatting. Let’s select that. Over on the right-hand side, here again, we see a pane that allows us to customize the conditional formatting. Conditional formatting is really neat. You can format the cells if and you have all these different conditions. You could say if it’s greater than something, you want to format it in a certain way, or if it’s less than something. You could even enter custom formulas in, so you have lots of different options here. Now I want a good sales number to be green and a bad sales number to be red, so over here there’s the option for a color scale. I’ll click on that and over here I can preview the different formats. I’ll click on this, and I think this option might work. Let’s select that. So here I see that December, we had really good sales and it’s in green, and here in January, it wasn’t so good, and it’s in red. This way we can visually just look at it and know which months were good and which months were bad. Here I have many other options as well. I’m all done with the conditional formatting now, so I’ll click on the X icon. Over here, I have my column C with a note, but come to think of it, most of the people who are going to review this data already know why January is bad and why December is good, so including this information isn’t really necessary. But at the same time, if someone new joins a company, maybe they don’t know these reasons. I don’t want to delete it. Here I can right click on column C and here I get this context menu. Within this menu, there’s the option to hide the column. So here I can hide the column when I show it to people who’ve been at the company for a while, and then maybe when someone new joins the company, here I could hover over between columns B and D. Here you see these arrow icons indicating that there’s a hidden column. Here I can click on this icon and that once again shows that column, but I want to hide it. I’ll right click and let’s hide the column. Thinking about this some more, we’re also supposed to get sales data for other countries, so I want to add one more column with the country. I want to add it before the date column. Here I’ll click on column A, and I could right click, and I can insert another column to the left. Now let’s say I wanted to insert 2 columns. Here I could highlight columns A and B, right click, and here I could insert 2 columns, so you could insert any number of columns. Simply highlight the number you want, and then you can insert those. I just want one column. I’ll click here and then insert one column to the left. For this column, here I’ll type in country and the country is going to be the United States. Here I’ll select this and then drag it all the way down and that’ll copy that value to the bottom. Here, let me undo this and show you one more way to fill this down. Instead of dragging and dropping it down, I can simply double click here and that’ll fill it all the way to the bottom of the data. So, another quick way to fill in some data. As I scroll down this sheet, you’ll notice that the header no longer appears. I know this is the number of sales, but it’d be nice if the header just stayed there just to remind me of that. Here I can go up to the view menu, and there’s the option to freeze. Here I can freeze the top row, the first two rows, or I can really just freeze any number of rows, and I could also do the same for the columns. I just want to freeze the top row. I’ll select one row and here that is now frozen that row. Here I can now scroll down and I continue to see the header. To undo that, I could go back to the view menu, here go to freeze, and then I can select no rows and that’ll remove the freezing, but I want it there, so I’ll leave that in place. The formatting all looks fantastic now, but I need to be able to answer some basic business questions about this data, like what were the total sales for the year, or how did we perform in the first quarter of the year, how many cookies did we sell? Luckily Google Sheets has lots of tools that can help me with this, and I don’t even have to enter a formula or function, I just have to know the question that I want to answer. I’ll put the active cell here in column C, then I’ll go up to the data menu, and right here there’s the option for column stats. When I click on that, that opens up a pane over on the right-hand side, and here I see all of these stats on column C. If I scroll down here, I can see that we had total sales of 78,000. That’s the sum of all of these numbers. I could see the average, the median, the min, the max. So, I get all of that information without having to do any work. Here, I’ll close that out. Let’s say I want to know what were the sales for the first quarter of the year. Here I’ll select Q1. I’ll select all of those cells, and in the bottom right-hand corner on the status bar, you’ll see this menu, and when I click on that, I can see that the sum was 16,000 for Q1. I could also see the average, the min, the max, so I get these really quick stats here without needing to calculate anything on my own. To the right of that, there’s a button called explore and when I hover over you also see the shortcut key and this is a really useful button. When I click on this, here I see those same stats, so the sum, the average, the min, the max, but I also get these charts that visually show me the data and it just automatically creates this for me. If I want to keep it here, I could simply drag and drop it over onto my spreadsheet. I’ll delete this for now. Now let’s say I want to know well what were the total sales for the year? Here I’ll click on column C and here in the explore view, here it updates, but I can ask a question like let’s say total sales and then hit enter and here it tells me that the total sales were 78,000. So, you really just have to know the right question to ask, and then you can get an answer back for your question without having to know formulas or functions or any of that. These tools work exceptionally well, but sometimes you just need to get your hands dirty, and you need to enter in a formula or a function. Let’s close out this pane and let’s see how we can enter in our own formulas and functions. First, I want to know what were the total sales in January and February, and here I’ll type in the question, and this is just a basic addition question. We had 1,000 sales here. I’ll press the control key and we can see that the total was 9,000, but how can we get that same answer using a formula? To enter in a formula, we start by entering in an equals sign. This lets Google Sheets know that we’re about to enter in a formula. Now in January, we had 1,000 sales. I can type in 1,000 and then I can enter in the plus sign and here I’ll type in 8,000, and I can see the answer right up above. It’s 9000. I’ll press the enter key and here we see 9,000. That works great. Now here I just reference the specific number, and the problem with this is, let’s say actually January was a little off. We actually sold 1,200 cookies instead of 1,000. I’ll press enter and here we see that the formula did not update, and the reason why is we hard coded in these numbers. It doesn’t reference these cells. Let’s delete this, and let’s try again. I’ll enter in the equal sign. This time, instead of typing in 1,200, let’s refer to this cell. This is C2. So here I could type in C2 and then I could type in plus, but instead of typing in the column and then the row, instead I could also just click on the cell. Here I’ll click on C3 and now I can press enter and now that shows 9,200. Now we did actually only have 1,000 sales in January, so I can now update this and when I hit enter, you’ll see that the formula automatically updates. That’s the benefit of referencing cells instead of hard coding in values. Along with addition, we can also do subtraction. Let’s say I want to know how much bigger was December compared to January. Here I’ll enter the equal sign and I’ll select 12,000 for December minus 1,000 in January, and then I’ll hit enter and here I could see that December had 11,000 more sales than January. We can also do multiplication. Let’s say I want to know what would happen if we doubled December sales. So, we had 12,000. Here I’ll enter equals 12,000. Here I’ll enter the asterisk symbol. This is how you multiply, and I’ll type in two and then hit enter and we can see that we would have 24,000 sales if we were to double it. Lastly, we can also do division. Let’s say I want to know what would happen if we only got half of December sales. Here I’ll enter equals, I’ll select December, and then for the division sign, we use the forward slash and then I’ll enter two, hit enter, and we would only have 6,000 sales for December. So far, we’ve just been using simple formulas, but you can also use something called a function, and with a function you pass in different arguments and you get a result back. Up on the toolbar over on the right-hand side, you have this icon for functions and when I click on this, we see all of these different function options. At the very top, we have some of the most common functions like getting a sum, an average, a count, max or min, and over here, we see all these different categories of functions. If I hover over the all category, here you can see all of the different functions available in Google Sheets, and there are many, many different options. Here at the very bottom of the list, you can also learn more, and this has detailed descriptions of all of the different functions that are available to you. Over on the left-hand side in my table, I would like to sum up all of the different sales and figure out what the total sales were, and to do this, we can use a function. I’ll place the active cell at the very bottom of this list of all of the sales, and then again in the top right-hand corner, let’s click on this function icon. Here in this list at the very top we have the sum function, and the sum function will sum up all of these different values. I’ll select this function. When I select that function, here I see some helper text appear that shows me how to use the function. So here it has the function name, which is sum, an open parentheses, and then I need to put in all of the different values that I want to sum up, and at the end, I close this off with the parentheses, and here we can see an example of how this works. I’ll close this out and let me select all of these different values. I want to sum all of these up. Here I can see the function down below. Just like with formulas, we start with an equal sign, then we type in the function name, here’s the open parentheses, and then this is the range C2 all the way down to C13, so there’s a colon C13 and then close parentheses. Here I can see that the sum is 78,000. I’ll press enter and there we just used our first function. Along with selecting a function just right up here using this icon, we can also just type the function directly into the cell. Here I’ll delete 78,000. Here we could type equals and then type in the function name sum and one thing you’ll see is Google Sheets is smart and it detects that if we’re typing in sum here, we probably want to sum up all the values above. So here I get this helpful suggestion that says to sum up those values. I can click on that and that also sums it up. So, another way to just type in a function on your own. Now that we have the total sales, I probably need to add some text that clarifies what this is. Over in cell B14, I’ll type in total sales colon and then hit enter. I’ll select this cell again and up on the top toolbar, I’ll right align this. Next, I’ll select these two cells and I’ll go up to the toolbar and here I’ll make these bold. I could also press CTRL + B. This looks pretty nice now. We had some pretty good sales for the year. I also want to add one additional column that tells me the percent that that month made up of the total sales. Up on top, I’ll add an additional column called percent of total sales, and here when I hit enter, it automatically applies the formatting to this additional column. For the percent of total sales, it’s the month divided by the total sales. Just like we’ve been doing, I’ll type in the equal sign and then select 1,000 and here it detects what the total sales is. It’s C14. I’ll select that and here it looks like it’s a little over 1%, but I want it to be formatted as a percent. I’ll make sure this cell is selected and up on the top toolbar, here I can select format as percent. I want this formula to apply to all of the other months, but of course, I don’t want to have to type this in for every single row, that would take a long time. Just like we did before, I can click on this blue square in the bottom right-hand corner of the cell. I can press on that and then drag it all the way down and then release. This now applies that formula to all of these rows, but one thing you’ll see is I get this divide by zero. I’ll click into the cell and then go up to the formula and let’s see if we could figure out what happened. When I dragged the formula down by one row, it automatically adjusted the reference to this next row. So that’s nice. Here’s looking at February, but with C15 or the total sales, it did the same exact thing. It also adjusted that reference by one row, and I don’t want it to do that. So instead let’s go back to the initial row where I entered this formula in. Over on the right-hand side, I can see this suggestion and it looks like it added a dollar sign in front of the column reference and also the row reference. So, what does that do? By adding a dollar sign in front of both the column and the row, that locks the reference to this specific cell. I’ll accept this suggestion and now the formula works as I expect it to. Here if we go up to the formula toolbar, here I can see there are now dollar signs in front. Now as a quick tip, you can press the F4 key, and you can toggle between different states. So here there’s only a dollar sign in front of the row. So, the row is locked, but the column can change. I can press F4 again. That places a dollar sign in front of the column but not the row. I’ll press it again and it removes all of the dollar signs, and here I could press it once again and that locks both the column and the row. Dollar signs work just fine if you want to lock a reference in a cell, but an even easier way to do it is to just give that cell a name. I’ll go down to cell C14 where I have the total sales and up in the top left-hand corner, we see the name box and right now it’s just referred to as C14. I can click into this box, and I can give it a name. I’ll type in total sales and then hit enter. Here I’ll go back up to this formula that I entered earlier, and here we see that references C14, but instead of referencing C14, I’ll delete this reference and instead I’ll type in total sales and then hit enter. It works just the same, but then you don’t have to worry about absolute or relative references. Instead, just name the cell that you’re going to use often and then you can use that name in a formula. It just makes things a little bit easier. To view all of your named cells, simply go up to the top menus and click on data. Right here there’s an option for named ranges. When I select that, that opens up the pane over on the right-hand side, and here I can see all of my named ranges. Here’s the one called total sales. I can click on this edit icon and here I could change the name and I could also change the reference. Now I want to leave this reference as is, so I’ll close out the pane. Next, we’re going to look at a function that’s a little bit more complicated than the functions that we’ve looked at so far, and this one is a function that you’ll likely use often. It’s called VLOOKUP or vertical look up. This allows you to look up a value in a table and then return a corresponding value. So, let’s say for example I want to know how many sales did we generate in, let’s say June 2022. So here I could look in this table and when I go down, here I see June 2022, and I see that the sales were 4,000, but I want a function to do that for me. I don’t want to have to manually look through this table to find out what the sales were. Over in cell F7, let’s make that the active cell and we can start typing in the function. I’ll enter the equal sign and let’s type in the name VLOOKUP. When I type that in, here I see the function. I’ll select that. Next, it gives me hints as to what I need to enter and first it asks me for the search key. This is the value that we’re searching for. If you’re ever unsure of what one of these items means within a function, you can click on this drop down arrow, and that expands all the details, and you could read about how it works. I’ll minimize this. So, I want to search for June 2022. I’ll select that. Next, I’ll enter a comma. The comma separates the different arguments within this function and next it wants to know the range. Well, I want to look up the month in this table right here, so I’ll select this area over here. The value that you’re looking up needs to be in the first column. Here I’ll enter a comma and next it asks me for the index. For this area that I selected, I now want to get the second column back, so here I’ll enter in a two and here I can close the parentheses. That’s all I need to enter in for this function. I’ll press enter and here it shows me that in June 2022, we had 4,000 sales. Now you might be wondering, well, what good is that? I could just look over and I can see how many sales we had. The beautiful thing here is I can change the value that we’re searching for. So, let’s say actually I want to know December 2022 and how many sales did we drive? I’ll hit enter and here VLOOKUP automatically adjusts and here returns 12,000 sales. This is one of the most powerful functions that you can use in Google Sheets. As I’ve been building out this sheet, here I have all of the sales data and over here I have some business questions, but I think it might work better if I put these business questions onto a separate sheet. Here I can select all of these different questions that I entered in, and I’ll press CTRL + X to cut. Down below, I can click on this plus icon, and this adds another sheet, and here I’ll press CTRL + V and that pastes all of these values onto the new sheet. One thing you might notice is all of these different formulas and functions that I entered in, they continue to work as expected. Here, when I click into cell A1 and we look at the formula bar, here you’ll see that the reference is now pointing to the previous sheet. So, you can also reference values on different sheets within the spreadsheet. The name of the worksheet down at the bottom is just sheet1 and then sheet2 and that’s not really that clear. I can double click on this and I can give this sheet a new name, so let’s call this one business questions and then I’ll hit enter. And sheet one, this has all of my sales data. I’ll double click on that and let me call this sales data and then hit enter. I’ll go back to business questions and here the formulas continue to work. Even though I renamed it, it automatically updates the reference to that new name. Down at the very bottom, I can re-order these sheets. Maybe I want business questions to come first. I can press and hold, and I can drag that to a new position. I can also click on this down arrow and here I have all these different options. I could delete a sheet, I could duplicate it, I could copy, rename, I could even change the color. For the business questions, let’s go with this blue color. That way it stands out a little bit more, and I also have all of these other options down here. To see all of my sheets, I could look across the bottom, but especially if you start getting a lot of sheets, you can also click on this all-sheets icon and here you can very quickly see all of your different sheets within this spreadsheet. Next, we’re going to look at how you can both sort and filter your data. For that, let’s click back into the sales data worksheet. Here in the table, I want to sort it so I can see which month had the most sales and which month had the least sales. Now I could go up to this column and when I hover over, I get this drop-down arrow. I can click on that and here’s the option to sort from A-Z or smallest to largest or largest to smallest. But if I sort it based on the column, it’ll also sort the total sales and I don’t want that. So instead, I can highlight this entire table, but I’ll leave out the total sales. Up on the top toolbar, over here there’s the option to create a filter. I’ll click on that. This has now added a new icon to the headers of my table. Here I see this icon. When I click on that, here I have various sorting options, and I also have various filtering options. Now I want to sort from largest to smallest, so I’ll select Z to A and click on OK and here I see that December again was the largest month and here January was the worst month. Here I can click here and let’s say I want to sort based on the months. Here I’ll select A to Z, and this restores the original order. Using that same icon I can also filter. Let’s say I just want to see the first three months of the year. I’ll click on this icon again, and here I have all of my different filter options. Here I can filter by color, I can also filter by conditions, and here I can specify what the condition is, but I just want a simple filter. I just want to look at the first three months. I’ll select clear so it removes all of the months and here I’ll just select the first three and then click on OK and now I only see the sales for January, February, and March. To come back to this filter in the future, I could go through all these same steps. I can add a filter, then I can filter it just in the first three months, but especially as your filters get a little more complicated, maybe you’re filtering on multiple columns, that takes a little bit of time. Instead, you can save your filter view. Up on the top toolbar, let’s go back over to the filter icon and then click on this drop-down arrow. Here I have the option to save as filter view. I’ll select that. Over here I can give this filter view a name. I’ll call this first three months and then hit enter. This filter view is now saved. I can go over and click on this X icon and here I’ll exit out of the filter view, and this brings me back to the original data. Now let’s say my manager comes up to me and says, hey Kevin, what were the first three months and what were the sales? Here I can go up to data, filter views, and here I can select first three months, and I have that view. Alternatively, I’ll exit out again. I can click on this drop-down arrow and here I can also select first three months and that gives me the filtered view. Let’s now exit out of the filtered view. Here in the top right-hand corner, I’ll click on the X icon. All of the data that we have here is currently just in a tabular format, but at least me personally, I think it’s a lot easier to understand what’s happening with the data when it’s in a visual format. So basically, a chart. Now I want to chart out the sales by date over the course of the entire year, just to see what’s been happening. Here I can highlight all these different values that I want to chart. Up on top, I can click on the insert menu and here’s the option to insert a chart. Alternatively, I can also go on the toolbar over on the right-hand side and here too I can also insert a chart. Let’s click on this. This now inserts a chart and over on the right-hand side, I have a pane that lets me edit this chart. Here I can press on the chart, and I can move it around the worksheet. I could also click in the corners if I want to make it smaller, or if I want to make it larger, I’ll make it a little bit smaller so we can see the entire thing. Now over on the right-hand side in the chart editor, here I can choose the type of chart. Currently it’s set to a column chart but here I can change it to let’s say a line chart. I can also go down and choose from all of these different types of charts. You have many, many different options. I’ll go back to the original column chart. I think that works well. Down below, I also have additional settings related to this chart. Up in the top right-hand corner, I can also customize what this chart looks like, and when I click on that, here I have many different options. I’ll click into chart style and over here I can change it to a 3D column chart. That looks pretty cool. I’ll collapse this category again. Here I can also click into chart and axis title and let me give it a better name than just sales vs. date. Here I’ll type in cookie sales by month and there it automatically updates the title. Down here I could also format what the title looks like. Maybe centering it would look a little better. I’ll select that. Here I’ll minimize this category. Next, there’s a category called series. I’ll click into that and when we look down a little bit, here I have the option to add a trendline. When I check this box, here I can see the trendline throughout the course of the year. It looks like as the year goes on; we happen to just sell more cookies. That’s a good sign for the business. One of the neat things about this chart is it’s always in Sync with the data. So here I’ll go up to my table and in January that was our worst month, but let’s say actually it was a little bit better. Let’s say we sold 2,000 cookies. I’ll update that in the table and then hit enter and you see on the chart it automatically updated to reflect that new number that’s in the table. So, these two are always in sync. Next, we’re going to look at how you can quickly analyze your data and get insights from your data without even needing to enter in a formula or a function. We’re going to use something called pivot tables, and you can analyze your data just by dragging and dropping your mouse. I’ll move the chart over to the right-hand side and then here we see our table. I want to include all of this data right here in the pivot table. I’ll select this. I’ll leave out the percent of total sales and the total sales down at the bottom. I just want this raw data right here. Up on top, I’ll select the insert menu, and here we see the option to insert a pivot table. Let’s select that. Here it asks me if we should set up a new sheet or use an existing sheet. I want to set up a new sheet. I’ll make sure that’s selected and then click on create. This now drops me into a new pivot table and down below I have a new worksheet called Pivot Table 1. Here I have my pivot table and over on the right-hand side I have the pivot table editor and this will help me pull together my pivot table. All the way over on the right-hand side, you see the country, the date, and the sales, and you might recognize these, these are referred to as fields and these are the columns that we had on the sales data sheet. When I click back into here, here you see country, date, and sales and those all correspond with what we see here. Let’s look at how you can get some very quick insights using pivot tables. Let’s say I want to know what were the total sales of the Kevin Cookie company? Here I have a category or a field called sales and I can press and hold and drag that over to values. Now you typically drag something into values if you want to calculate something. I’ll release and here I see the sum of all of the sales. Here I see that we had 79,000 total sales, but here I could also calculate other things. Instead of the sum, let’s click on this drop down and here I could also calculate the average. So, it looks like on average every month we sell about 6,583 cookies. Here I could click on this again and I have all these other calculations. I could do the max, the min the median. So I have all these different options here. Over on the right-hand side, I have these other fields and I can pull them into the pivot table as well. Here I’ll change this back to the sum and let’s pull the date into the rows. I’ll release that there and here I can see the sales by month. This is very similar to the view that we had on the sales data sheet, but let’s say actually I want to see all of the different months as columns. Here I’ll drag the date and here I’ll place it in columns, and I see the same data but just organized in a different way, and I can get this simply by dragging and dropping my mouse. Here I can move the date back up to rows and there you see that original view. Earlier to calculate the percent of the total sales, we needed to enter in a formula and then we had to set up the reference properly, but here it’s just so easy. I could take sales and let’s drag it down into values again. Now I have sales appear in my pivot table twice. Right here I could decide how I want to show it. Currently it’s just set to the number, but I could also set it to percent of column and there I could see the percent that that month contributed. I didn’t have to enter a single formula or function. All I had to do is select an option from this dropdown list. Pivot tables make analysis so easy. I can also filter this data, just like we did earlier. Here I could take date and I’ll drag it down to filters and here I’ll release. I can now click on this list, and I can decide what to include in this pivot table. Let’s say I just want to see the first three months. I’ll select January, February, and March and then click on OK, and here that’s now filtered my table. So here I was able to re-create most of the views that we had on the previous sheet simply by dragging and dropping my mouse. With all this data that we’ve been looking at so far, it’s just been based on this sales data, but maybe you want to collect data from third parties or from your customers. We can go up to the tools menu, and here’s the option to create a new form. Let’s click on that. This drops me into Google Forms, and the beautiful thing here is any of the responses that I receive in this form will automatically feed back into my spreadsheet. If you want to learn more about how to use Forms, you can check out the video or the training included down below in the description. Right up here, for the title of my form, I’ll call this favorite cookie, and for the first question, let’s call this what’s your favorite cookie? And I’ll leave it just as a short answer text. That looks good. Right up above I can now share this survey out with others. I could click on send and I could send it via e-mail, I could share a link, or I can even embed it in a web site. Let’s preview what this survey looks like just to see how the data flows back into my spreadsheet. I’ll click on preview and here I can now fill out this form. What’s your favorite cookie? Well, of course it’s chocolate chip. I’ll type that in and then click on submit. So, we now have our first response. Let’s go back to the spreadsheet. Here in the spreadsheet, there’s a new worksheet called Form Responses 1. I’ll click on that and here I can see the response from my survey. Here I see the time stamp and I also see what’s your favorite cookie, and here I see chocolate chip. So, Google Forms is an excellent way to collect feedback, and to bring that directly into your spreadsheet where you can then analyze all the responses. We’ve done a lot of fantastic analysis today, but next I want to share it out with the broader Kevin Cookie Company. In the top right-hand corner, we can click on this share button. I’ll click on that, and this opens up the sharing dialog. Within the sharing dialog, here at the very top, I can type in specific people who I want to share this with. I’ll type in Kevin Stratvert. He seems like a really good guy. Here I can notify him. I could also type in a message, and right here, I can set the permissions, so can he view it, can he comment, or can he edit? I’ll select editor and then click on send. Let’s now go back up to the sharing dialog to see what else we can do there. I’ll click on share, and here I can now see that I’ve shared this workbook with Kevin Stratvert and I can see that he’s currently set to an editor. Down below, I can also provide general access. Currently it’s restricted just to the two of us, but here I can click on this drop down and I could also set it so anyone with a link can access this spreadsheet, and over here I can set the permissions. Here viewer, commenter, or editor. I’ll leave it to viewer, and I can copy the link. I’ll provide this link down below in the description if you want to see this workbook and just take a look around. For now, I’ll click on done. Now that I’ve shared this spreadsheet, in the top right-hand corner, I can see all the other people who are currently in this sheet, and here I see that Kevin is currently here. His picture has purple around it and when I look down on the spreadsheet, here I see purple on the spreadsheet, so this lets me know where Kevin currently is in this workbook. When I hover over the cell, I could also see the name associated with this position. So here I can see this is currently where Kevin is. Up in the top right-hand corner, I could also chat with Kevin. I could click on this icon, and this opens up the chat interface. Here I could type in a message. Hi, how are you? Ah, very cool. It looks like he appreciates the hard work we’ve been doing today. You can also leave comments directly within the worksheet itself. Here where it says what’s your favorite cookie, let’s see if this is also Kevin’s favorite cookie. I can go up to the insert menu and right here there’s the option to insert a comment. I could also press CTRL + Alt + M. Let’s try the shortcut key. I’ll press CTRL + Alt + M and here I can type in a comment. One of the neat things about commenting is I can also @ mention people. I’ll type in the @ symbol and here it shows Kevin Stratvert is one of the options. The reason he shows up here is, well, this is one of the people who I shared this sheet with. I’ll select his name and let’s ask him if this is also his favorite cookie. Right down below, I can also assign it to Kevin. I’ll check this box and here I’ll click on assign. And look at that! He has already responded. In the top right-hand corner, I can also click on this icon, and this will show me all of the comment history, and here I see that same comment thread and Kevin’s now answered this question and I’m glad that he likes the same cookie that I like. Here I can click on this check mark to mark it as done and that will now hide the discussion. This makes collaboration with multiple people so easy. Especially when you start working with a number of different people, you might worry, well, what if someone makes changes and you didn’t like those changes and maybe you want to go back to a previous version? Right up here you can see when the last edit was made. When I click on this, this opens up version history and here I can jump back to any previous version of this spreadsheet so I could go through and see when all of the different changes were. In fact, I could even name some of these versions. I could click on the ellipsis or the three dots here, and I can give this version a name. For now, I’m OK with all of the changes that have been made, but this is a neat way to go back in the history, if you need to. In the top left-hand corner, I’ll click on the back icon. There are also other ways that you can share your file. When you click on the file menu, you could go down to share, and here you have the option to publish to the web. You could also e-mail the file, or you could even download a copy. Here I can download as a Microsoft Excel file. At the very bottom, and this is probably a more traditional way of sharing, you can also print out a copy. You could press CTRL + P and when you click on print, you have a whole bunch of different options to customize what your print out looks like. All right, well, that’s how you can get started using Google Sheets. Be sure to check out the free learning resources down below in the description. That way you could continue learning even more about Google Sheets. To watch more videos like this one, please consider subscribing, and I’ll see you in the next video.