Google Sheets Introduction: Data Entry, Functions & Formatting for Beginners

The exercise files for today's course are located in the video description below. Don't forget to like and

subscribe. Hello everyone and welcome into Google Sheets introduction. My name is Joe and

I am so glad you're here and excited to have you join us for our Google Sheets adventure. In this course, we will help

you learn the essentials of Google Sheets. Whether you're a complete beginner or just looking to sharpen your

skills, this course will guide you through the basics of Google Sheets from data entry and formatting to simple

calculations and functions. By the end, you'll feel confident using Google Sheets for various tasks.

Looking to support our channel and get a great deal? Become a member today to unlock adree videos. That's right, your

favorite courses without a single ad. Interested in a specific video? Purchase one of our ad free courses individually.

Looking for even more? Gain access to exams, certificates, and exclusive content at learnit anytime.com.

More information can be found in the video description below. To get to Google Sheets, the first thing

we'll do is open up a web browser. I'm going to be using Google Chrome today, but you're more than welcome to use

whichever browser that you're comfortable with. So, I'm going to open up my Google Chrome here.

And in the top right hand corner, you'll see your Google apps. Now, if you aren't signed I if you're not signed into this

yet, you might have to uh you might just have to go in and then sign in using your Google account, your Gmail here,

and then whatever password you set up. But once you're signed in, you can just click on those apps, and this is where

you'll see all the different Google apps you have available to you. Now, the one that we're going to be focusing on for

this course is Google Sheets. So, we'll give that a click. And then once we open up our Google

Sheets there, it's going to bring us to the landing page. Now, the landing page is great

because this is where we can either create a blank worksheet. We can see some of the templates that Google has

available to us. We can access what we've been working on the previous 30 days. And not only that, we can also

search for any of our files up top. So, this is a nice simple way to access all of your different Google Sheets or being

able to access those templates or being able to access this blank spreadsheet, which that's what we're going to be

doing today. So, let's all go over to blank spreadsheet. We're going to click on that and we're going to build a

financial ledger. Or maybe maybe not. We'll we'll figure out what we'll do. Maybe we'll do something with uh we'll

switch it up. We'll do something with students or something. We'll have fun with it though. So, here I'm going to

just click into blank spreadsheet. And right away, we are now in our main interface. So, you'll see up we have a

way to name this sheet. And I'm going to do that right away. And once you name it, it'll stay that name unless you want

to rename it again. You're more than welcome to. And I'm just going to call this um

let's do Yeah, let's do this one. Student scoring information.

Student scoring information. I'm going to press enter and it saves right away into your Google Drive. So that's pretty

cool. Right away we notice that it has a automatic saving. Now you can go look it interface. You'll

see right over to the right it start to ask you hey do you want to build some pre-built tables? Here's a couple other

features that you have access to. So this is pretty cool. Once again bringing in those templates and look at that. If

you are new or maybe you don't know where to start, you can quickly go in and take a look at all the different

things it has available to you. But we're going to have fun. We're going to create our own from scratch. So, we'll

click the X on that. We're going to just start from blank here. Another thing that's really important with this is up

top, see your different menus. We have the file menu. Then when you click on it, you'll see the menu then drop down

and you can look at all the things you can do in the file menu. edit, view, insert, format, data, folds, extensions,

and even help. If you do need to search the menus, you're more than welcome to do that. And also, if you like this

training, of course, you can utilize it. But if you do want more resources to training, Google does have their own

here under training. So, that's pretty cool. And also, they have their function list and keyboard shortcut if you want

to learn those. but we'll go through a couple of those throughout the training. Now, besides STEM, we also have what's

known as our quick access tools. And these quick access tools are some of the most frequently used tools by Google

Sheet. You can once again search the menu. That's the same as going to help clicking search the menu. So, all these

tools here are actually up top here as well, but these are the most accessible ones. Undo, redo, print, format,

painter, right or paint format, copies of format. You have to do you can zoom in and out of the worksheet. You have

currency format, percentage format, decimal increasing and decreasing and also more formats as well where you can

change what the number looks like. Always remember format means the appearance. You then have your tools for

the text itself. What kind of font do you want to use? increase or decrease the font size through formatting like

bold, italicize, strike through, and also changing the text color. You can also change the background of the cells.

So, if you want a cell to be a second color, you're more than welcome to. You can add borders to those cells and then

also merge the cells as well. You can align your text if you want it center or to the right or to the left. You can do

vertical alignment, top, middle, bottom, and also text wrapping if you want to do overflow or wrapping or clip and then

rotation of your text. So, there's a ton of different options here. Couple of the other ones down here are inserting

links, comments, you can insert charts for visualizing your data for non filters or take a look at some of the

filter views that you have. We'll talk about this in another session. And then also functions all the different

functions and then all of them are categorized. You can either go to arrays database

data engineering. So they have a ton of things here that you can look at. So

when we're talking about using the interface here, one of the most important things is knowing where you

are in the worksheet. So for instance, what Google Sheets does well is it utilizes what's known as columns

and row. So that's how we reference. So these are the columns going across here and these columns are indicated by

alphabet, right? So characters A, B, C, D. We then have of course down going down here the rows and these rows are

indicated by numeric value. So they're just numbers 1 2 3 4 and when you select into a cell like right here I'm selected

on B5 you're always going to name the column first column B and then what row you're

selected on five. And you'll even see over here in our name box, it tells you you've selected

B5. Another thing that's really important when we're talking about

cell reference is what's inside of a cell, right? So, right back to the main box where we just saw it tells you what

cell you're in. You'll see also we have our formula bar. And this formula bar is super important because it tells us the

truest value of a cell. So you might be thinking, well, what does that mean? Well, let's say I did something like

that. I click into a cell and I do this. And then I click into cell and I do this. Well, looking at these, they look

identical, right? They don't look like they're different at all, but they're completely different. One of these

values is a one, and one of these values is some sort of calculation. But which one's which? Well, that's

where the formula bar comes in. If I click into this cell, it tells me that this is actually a calculation.

It's 1 0, which equals one. and it tells me that this cell I just typed in the number one. So even though they look the

same, they're completely different. That's why the formula bar is super important, not only for writing

formulas, but for knowing what really is in these cells. So I'm just going to clear that out. But

try this out for your own real quick. I want you to sign into Google Sheet. I want you to open up a blank worksheet,

take a look around, get familiar with some of the tools that they have to offer, and then when we come back, we're

going to start to enter in some data into this worksheet. Now that we know a little bit more about the interface,

we're going to start entering data. And today, we're going to do our student scoring information. Now, in order to do

that, we can't just start entering data in. Like, I can't just start entering a bunch of numbers because you might be

thinking, well, what are those numbers? It's always a good idea to label your columns and those are known as headers.

So, we're going to create a title first. Let's click into A1 and we're just going to call this student

scoring. Then in A3, we're going to start to put our headers in to really describe what

kind of data is going into our columns. Starting off with our students ID.

I'm then going to press the tab key to go over to the next cell. Now, you can either press the tab key next to the Q

on your keyboard to go to the right, or you can use shift tab to go to the left. You can press enter

to confirm and then go down if you press enter twice, or you can hold your shift key and press enter twice to go back up.

You can also just simply use arrow keys or click into each cell. So, totally up to you. There's no right or wrong on

those. Uh, but yeah, let's continue to fill in what kind of information is going in here. So, we'll have the first

name, and then we'll have the last name. Let's also do the email,

the birthday. We'll take birthday. Uh, let's do also class attendance. Now I want to do the

class attendance for each quarter. So we'll have attendance quarter 1. Then we could just say QR1.

But before we go and have to rewrite that a ton of times, I actually want to talk about this real quick. You'll see

that whenever we click into each cell, we actually get this little ball icon, right? And that little dot or

ball is actually autofill. What it does is it follows a pattern. Now I'm going to show you how it can fill in series.

And then later on when we get into calculations, functions, and formulas, we'll talk about how it works with

those. But for now, let's say I want to just repeat the number one. I can actually do that by clicking and

dragging. You'll see it just repeats the number one as many times as I want. I can also choose to do sequential

order. For instance, I'll put a one and then a two. And now I can say, okay, I'm going to either click and drag or after

you select those two, you can do a double click. And you'll see it's in sequential order because we're telling

the system, hey, listen, we're going from one to two, which is increments of one. I can do two to four and do

increments of two. Select them both. Double click on the ball. There we go. But what's awesome is not only that, we

can also do things like dates. I can put in a date. Let's do this date. And then I can double click and it'll

put it in sequential order. Pretty cool. I can also do January double click or even non-abbreviated

January double click Monday

day of the week or even non-abbreviated Monday. Or another thing I can do is maybe I

want to do week one and it will automatically put it in sequential order.

And not only can we do this going down vertically, but we can do this horizontally. For example, with our

attendance for each quarter, I can just click and drag. That'll be quarter two. That'd be three. That'd be four.

And look at that. How awesome is that? We don't have to sit there and write it four times. We just write it once and

then it does it for us. Now, I'm going to take all of this. I want to delete this out. So, you could just select it

all and press the delete key. Not the backspace. You could do backspace if you want, but I always like to use the

delete key. So, I'll show that again. You can either select it all and use delete key or backspace, but I always

use the delete key. Next up, we're going to put into column J3 holds for a total attendance. So,

we'll just say total attendance. And you might notice as of right now that we're cutting some stuff

off, right? We're cutting off the one, the two, the three, and now the four. We're going to talk about how to fix

that in a second, but let's put in the rest of our headers here. We're also going to keep track of scoring, right?

So, I want to see what they scored on test one, test two,

and then their final test. We'll then in column N3 put in their average score

and then in column 03 we'll put in whether they've passed. So we'll say pass slash fail.

Now once again this is pushing really far. We can't see everything and we have to also fix the attendance. You can do

this by going up to the top between the columns where you want to change the width. For instance, F and G. And I can

click and drag to make this bigger. Click and drag. Or click and drag to make it smaller. But if you want it to

be an exact fit, you could just give a quick double click and it will auto size it for you. One, two. Look how nice that

looks. One, two. Now, if you want to fix this all at once, let's say they're all squished in

a little bit and you want everything to be autosized really nicely, you can actually select right here underneath

the name box, this little rectangle. And when you click on that, it selects every single column and row. Once you do that,

you can go between any column and double click and it will actually auto adjust the entire

spreadsheet. So that's a little trick that I like to use. So try this out on your own. And then once we come back,

we're going to start entering in some data. Now that we have our headers and

everything is auto, we will now start to put in our information. So as we enter in the data here, I'll just enter in

some fake data for 10 students. So I'm going to do that autofill technique I showed you before where you put in a one

and then a two. And then we can just click and drag from our autofill

and we'll fill in 10th student. After this, we'll then put in some names. So, let's just do some fake names

here. John, Jane Smith. And then you could take your time with

this. Pause the video. I'll go through a little quicker, but you're more than welcome to pause at any moment. We'll

add in just a couple of fake names here. And you're going to love what we learn in a second. This is going to be a lot

of fun when we create the emails. So, just a couple more here. Max Walker and Olivia.

All right. So, now let's say that we want to put in some emails. I'm going to make those emails a little bigger by

just clicking and dragging the column D width because I know I want those to be the full email. Now, for the email,

we're just going to do a first name, last name at.com. Very simple and just fake. So, you might be thinking, well,

wow, that's going to take us a minute to do, right? we have to put in John Dell as or you might be thinking well Joe

I've heard there's ways to do strings of text like concatenation and different advanced functions you can use and all

of that would be correct but there is an awesome new feature known as smart fill that is accessible to us within Google

Sheets and what smart fill does it's kind of like autofill where it follows a pattern but it's following a pattern

with our names here our strings of text So watch this. Let's click into D4 and type it John.

Oo email.com and then we'll press enter. We'll then

do the next one. And then press enter. Now when we do the last one, you're going to see something

here. It says, "Hey, Smart Fill, we have a formula suggestion. We think we can fill these

cells using a formula to help you out so that you don't have to continue to do all that work." And I'm going to say,

you know what? Thank you so much, Google Sheets. And we'll click the little check mark here. Or you can also use control

plus shift + Y to do the rest of the smart fill. Now, I'm just going to do a little check mark. And how awesome is

that? You might have heard me just say, hey, you know, we might have wanted to do concatenation, right? Well, it is

actually using a concat function on top. So, using the concatenate function, it's saying, let's take the first name with a

period and the last name and then add an email.com. So, it's doing it for us. And we didn't even need to know how to do

that function. Now, later on, if you decide that you don't want these to be functions, you're more than welcome to

copy these. And you can copy something by right-clicking. And cut means to actually take it and move it. Copy means

to take it, make a copy, and then move it. And paste means well to paste it in there. But there are a couple of really

cool options here under paste special where we can copy or cut the function result and paste it as a value instead

so it's not an actual function. That way it's just the email. So I'm going to take all this.

I'm going to rightclick. I'm going to cut it. And then I'm going to rightclick paste special. And I'm going to say

values only. That way, it's actually a value instead of a function. Pretty cool. Now, we can fill in the rest of

the data. All right, but before we fill in the rest of the data, try that out. Fill in some of those uh data sets there

and also use smart fill. So, now that we know a little bit more about smart fill, we're going to fill in the rest of the

data here. And once again, you can pause this video at any time you want to. You're going to enter in some fake taps

here. We'll do uh just a random one. I'm just using some data that we looked up from artificial

intelligence, which is something that is amazing. It allows for us instructors and

trainers to actually come up with fake data a lot quicker. So, we don't have to go and do name generation or anything

like that. We could just say, "Hey, give us 10 names and 10 different birth dates." And then it'll just come up with

a bunch of them. So that's how I've been using it. But of course, people use AI all types of ways.

But once again, I'll be just filling these out. And we'll fill in all this data. We're going to just use a random

number. You don't have to do that part with me. When we do fill this out, you can actually

fill out the numbers. I'll do the same thing we sort of did with the email where I use a function to come up with

random numbers, but then I will utilize the cut and then paste special just to add them in as values so they're not

constantly changing. You'll see what I mean in a moment. But just finish entering this data here. We have a

couple more and I think that's it. Awesome. Okay, so we

got some birthdays in there. So now I'm going to once again just use a function. You do not have to do this with me. I'm

going to use something known as rand between where we just pick a random number between two and five. So it's a

random integer. It's known as the rand between function. It'll ask for a low part of the number two, a high part

which is five, and when I press enter, it's just a random number between two and five. and I can autofill and we'll

learn about that later on. And I'm just doing that for quarter 1, 2, 3, and four. The only problem is if something

changes in your data, like if I write below here, these change again. So, we want to be sure that those aren't

constantly changing. So, I'm just going to take this and I'm just going to rightclick,

cut it, right click, and paste as the values all. So once again, you just copy the same numbers that you see here, but

don't use random clean like I did. That was just to get the numbers. Take a moment to fill in and enter in the rest

of the data. And then when we come back, we're actually going to talk about how we can find the total attendance and

what the difference between calculations, functions, and formulas are. Right? So we're going to have a lot

of fun with that. Before we go and try to find the total attendance, what we're going to do is talk about the difference

between calculations, formulas, and functions. So, this part's really for you to just sit back, relax for a

moment. I'm going to actually add a worksheet. So, you'll see we do have a sheet one, and you're always more than

welcome to click on that little drop down. If you want to rename the sheet, if you want to change the color of the

sheet or project it or duplicate it, you but I'm just going to click the little plus icon, add another sheet here just

so I can showcase the difference between calculations, formulas,

and functions. And once again, you don't have to do this with me. We will have some fun with

formulas, functions, and calculations in a little bit. So, let's say I have a little bit of data here, right? Maybe I

have some numbers 1 2 3 4 and five. And what I want to do is I actually want to calculate

the total of those numbers. Matt, that's great. And Google Sheets has many ways to do this. But which way is the most

efficient way? Well, that's where it comes down to calculations, formulas, and functions. I'm going to showcase the

difference between these and ones that are really great to use and ones that you'll probably never use.

Anytime we do use calculations, formulas, or functions, we have to start off with a universal sign of

mathematics, which is our equal sign. that prompts the system to know that we're trying to do some sort of

calculation formula or function. Now that we have the equal sign, I'm going to do a calculation. A calculation, it

sounds like calculator, right? And it works very similar to a calculator. After I put my equals, I just type in

the numbers like a calculator. So, I want to add 1 + 2 + 3 + 4 + 5. And when I press enter,

I get 15. That worked really great, but that was a little slow, right? Welcome to August. And another thing is what

happens if our data changes, right? Data is always changing. If I go in here and I do something like that, well, look at

that. The data an update. So that's known as being dynamic or not dynamic. And in this

case, calculations are not dynamic. And I'll change that back to a five. So calculations are slow and they're not

dynamic. And you might be thinking, well, when would we use them? And I'll just say that you'll probably never use

calculations in Google Sheets unless you just want to do a quick calculation of numbers that's temporary and then delete

it. If it has nothing to do with your data, you'd probably use it. But if you're working with data, calculations

are not the way to go. That's where formulas step in, right? That's the next step up. See, formulas are a little

different because they're not calculating just the number values, right? The numbers. They're calculating

whatever's in the cell. So, we're going to start off and do equals. And instead of typing 1 + 2 + 3 + 4 plus 5, I'm

going to do whatever's in B3 plus whatever's in B4 plus B5 beat 6 plus B7. You'll even see it does a little color

code for you. I love that. And when I press enter, there we go. We get the answer. Now, I

will admit that was still slow, but is it dynamic? Well, take a look. I'm going to change up five to a 15. And

there it is. How cool is that, right? It is dynamic. Awesome. So, let's take a look at formulas next

and talk about why we would use them, right? So, formulas, we would use them even though they're

slow, they are dynamic as we just saw. We would use them most of the time for multiplying. I use formulas a lot for

multiplying. and also dividing because we don't really have a function to divide. Well,

I mean there's functions out there for multiplying and dividing but formulas are going to work way more efficient. So

I always say formulas are really great again if you're doing any multiplying or dividing of cells.

And now we have our last thing which is functions. Functions are actually formulas. their formulas that Google

pre-created already for us to use. Now, there's a huge list of functions. You can just go to your quick access toolbar

and you'll see the little functions icon. And when you click on that icon,

it'll tell you all your aggregate functions like sum, average, kill, mac, n. And then it also has all of your

functions all categorized. And you can even learn more functions that are available.

And they're always adding new ones like this one right here. This is awesome. They now are using regex.

And reg is a unique code that you can use to extract things, match them, replace them. It's pretty cool. But

they're constantly adding all different types of things. Lookups, right? The new X look up. Awesome. But

we're just going to keep it simple for today. We are using the sum function, right? The top function allows for us to

add things together. You'll always see your top aggregate right up top here. So, we're going to click in once again.

Any calculation formula or function starts off with an equal sign. We're going to choose what function we want.

I'm using the sum function. We're going to press the tab key right after you write your function. Right

next to that Q on your keyboard, if you press that tab key, it opens up the parenthesis. And then you can select

a whole range of cells, right? When you select one cell, it's just a cell and you select the range of cells like that.

So, you'll hear that analogy often when I press enter, I get the answer. Now, it will do some uh autofill here. It looks

like I don't know what it's trying to autofill. So, I'll say no on that one. But I wasn't teaching this. This is how

fast this would be. Ready? Equals sound select enter. So functions are very fast. Not only that, are they dynamic?

Well, we'll take a look. They are they update. So when changes in our data, they actually update. So it is dynamic.

So really, if we're looking at this calculations are something that we'll never really

use when we're dealing with our data. formulas we'll use when we're dealing with multiplying or dividing data and

functions we'll use for everything else for more complex situations. So really we're only going to ever use formulas

and functions. But now that we know the difference between those, we're actually going to go back over. I'll just delete

the sheet here. Delete it. Click okay. And now we're going to talk about using

formulas or functions. Oh, zoom back out to get the total attendance. And you're all probably thinking, well, what would

we use? What would be the most efficient? Well, I just want to add up the cells right here. So, I'm going to

use a sum function because that was the easiest one, right? It's the fastest. So, in J4, let's do equal sum.

And you'll even notice that Joe, we see what you want to do. You want to calculate all the attendance. So, it

kind of guides you, too. I love that about Google Sheets. It always has a guide. And that's actually the correct

formula right right there. So, I'm going to press the tab key. And if you just want to do it on your own, you're more

than welcome to. You can still click and drag just to practice. But when we press enter,

look what it does. It then says, "Well, since we did the first one with you, do you want us to do the rest of them?"

You're going to say, "Of course." I want you to save me time. I'm going to click the box. And you'll see now it's doing

right F4, F5 through I5. Summing F6 through I6, 7 through I7. So, it constantly using

what's known as relative reference, right? it's relative to the one before it, following the pattern all

the way down. So, take a moment and try that out on your own and then I'll have you do this again, but with a different

function. Right now, we just use the sum function. We're going to talk about the average function next.

We've learned about the sum function, which we saw was really amazing to help us find the total attendant and also use

autofill fill in the rest of the relative data. Well, the next thing we're going to do here is put in the

test result. So, the grade and then we'll see the average score. And we're going to use the average function to do

this. Now, to make things quick again, what I'll do is I will go through I'm going to use the rand between and then

you can pause the video and start copying the same scores that I wrote down. That way, you are able to have

access to the same exact average once you try the general. All right. So, let's try this together. I'm going to

fill these in and then you can pause the video and fill them in as well. All right. So, now that we have our

scores in, and once again, you can pause the video and take a look at these and put in the same ones that's made. We're

going to find the average. Now, to use the average function, what we'll do is we'll say equals.

Once we put in our equal sign, we'll then start typing out the function we want, which is the average function.

You'll see the minute we start typing in average. It's the first one that pops up here. Once we click on that, it'll fill

in the rest and even open the parentheses for us. And then we can choose the average of test one, two, and

three. Now, once we press enter, you'll see right away it says, hey, we suggest autofill. And we'll see. This is super

important. It'll allow for us to go through and say, "Yeah, you know what? This is what I want. I want you to fill

in the rest because I want the average." And I'll click the little check mark. And there we go. Now, looking at this,

you'll see some of them come with a long, long list of decimal. And if you want this to be a little more shorter,

you're always welcome to format. Now, you can format numbers as percentages, as currencies, monetized amounts, or you

can even format it so it doesn't show so many decimal places. So, I'm going to select everything I want to affect, and

I'm going to go over to my toolbar towards the beginning, and that is where you'll see all the different pools for

formatting, currencies, percentages, or increasing or decreasing your decimal places.

So, I'm going to click on decrease decimal places because I do want to decrease them and I want them to be

whole numbers. I think that looks great. So, now that we have our averages, the last thing

we're going to do is talk about being able to now take their average score and see if they pass or fail. But we don't

want to do this manually, right? We want to use a function. And this function is going to be a really fun one. It's

called the if function. It's a boolean factor, which means we're going to ask the question, are they passing, right?

And if they are passing, then put pass. If that's true, if it's not true, then we'll put fail. So, we're going to

automate this. It's going to be a lot of fun. But try out the average, and then we'll get into the if and then we'll

talk about how we can start to format the entire worksheet to look really cool.

The next function we're going to learn about is the if function. Now the if function allows for us to ask a

question. If the answer is true, it does something. If the answer is false, it does something different. So it's a

boolean factor. So what we're going to do first is set up what the criteria is. Let's all click into N1. In N1, we're

just going to say that the passing score has to be greater than or equal to. So that's all we're writing in N1 patching

score is greater than or equal to. And then in 01 we'll say that if it's greater than or equal to 65, we'll just

put a 65 in there. So if it's greater than or equal to 65, if that's true, they pass. If that's not true, then they

fit. What we'll do is click now and do our pass fail. And we're going to start off

with our equal sign. type in the if function and then press the tap key to open up the parenthesis.

And it's going to ask us for three different things. The logical expression, which is the question we're

asking, the value if it was true, and the value if it's false. So the first thing the logical

expression is is this number here the average tour in N4 greater than or equal to

whatever is in 01 right 65. So that's 88 greater than or equal to 65. Now we know that's true. It is greater than.

So when we put the comma it's now asking us what do you want to do if it's true? Well, if it's true, we want to write the

word pass. Now, it's important to note that in Google Sheets, it doesn't recognize just the word pass. We have to

actually put it in what's known as a string of text. So, we're going to do double quote, write the word, and then

end quote. And now, if AD is greater than or equal to 65, it'll write the word past. But we

also have to figure out by putting our comma, what would happen if it was not true? Well, if it was not true, it would

be they fail. So, we would write the word fail. And then we can close that parenthesis.

So, it's asking the question, is whatever's in N4, which is 88, greater than or equal to whatever is in 01,

which is 65. If that's true, write the word pass. If it's not true and it's false, write fit. When we press enter,

it's pass, which is perfect. Now, of course, we're going to want to use autofill, right? We would don't want

to have to write that nine more time. So, we can autofill, but you're going to notice something in a moment. If we go

to autofill, right? Now, this pass. That makes sense. But why did the person fail? They got a 72. 72 is

greater than 65. Or 90 failed. I mean, this one, yeah, we get it. This one shouldn't be. This one, yes, this one

shouldn't be. that's what it shouldn't be and so on and so forth. So there's something wrong here. And if you've

noticed when we were doing total attendance, right, it did the sum of F4 through I4 and did F5 through I5. It

followed that relative pattern, right? Relative cell reference. F6 to I6, F7 and I7.

Same thing here when we did the averages, it's an average of K4 to M4. K5 to M5, K6 to M6. So there's a pattern

going on. But when we autofilled here, look what happens. N401.

Oh, N52. There's nothing there. And tick 03. So N is perfect. We want

that to move. N4 N5 and six N7 because we want to take all of it then compare it to this one cell. So we actually have

to use not relative cell reference but we have to use what's known as absolute cell reference where we lock the 01 so

it doesn't move when we use autofill. So we're going to do this again but this time we're going to use absolute cell

reference in our function. So let's do equals if tab open the parenthesis. Same expression. We're saying take

whatever's in four and if it's greater than or equal to 01.

But this time we're going to put a dollar sign known as anchors in front of the O and in front of the one. Now, I

will say there is a awesome keyboard shortcut for this so you don't have to constantly put in dollar signs like this

one at a time. You can use the fun and explore key. So, if you look up above your keyboard, you'll see F1 through

F12. And you're going to want to hit the F4. Now, if the F4 doesn't work when you first hit it, you might have to hold

down the Fn key, which is usually in the bottom lefthand corner of your keyboard, and that turns on the function key so

they work. So for me, I'll press F4 and boom, put in the dollar sign. Saves you a lot of time. Now that we have the

dollar signs in locking the 01, we can finish the rest of our expression. So we'll say pounds.

And then if it's false, fail. So it's the same exact thing as before, except this time we're locking the 01.

And when I press enter, it works beautifully. And now it says, "Well, Joe, we see that you locked the 01. We

can now do autofill." And I'll say yes. So now if we look N4 01, N5 01, it got locked in and thick 01 and

so on and so forth. So sometimes we do need to use absolute cell reference instead of utilizing relative cell

reference. So try this out. Hopefully this works perfectly for you. We see we have three fails because they're less

than 65 and then the rest are passing. So try this out on your own and then when we come back, we're going to

actually format this worksheet, talk about some of the other cool features that Google Sheets has to offer. What

we're going to be learning about next is how we can format. Now we have here a pretty just simplistic

look of our data. But what happens if I want to add some color to this? Well, what we can do is we can just go in and

we can format our headers. We can just go to the top and use some of these formatting tools. Maybe I want to bold

the headers. Maybe I want to underline the headers. And if you don't see underline here, you can always go to the

format tab. text and you'll see all the different options. And then we can choose underline. Maybe I want to make

these headers a different color. Maybe I want to put the fill color on. And let's fill it with a light blue. So, right

there, we're already adding some color to this. I'll just readjust all of my widths there.

And it looks great. But not only can we do this, I'm going to actually use Z to undo everything I just did. and I wanted

to talk to you about different themes. Now, when you go into the format tab, you'll see that there's a overarching

theme here and you can choose whatever theme you like. Now, if you start choosing these themes, they use

different colors and different font styles. So, whichever one makes sense to you, you can do. But what I really like

is this option here. It adds color to the entire table. If you go up to the format tab, you can then go over to the

alternating colors. And what that does is it looks at your data and it creates a header color and then alternating

colors afterward, which I think is awesome. So, I'm going to go in to format, alternating colors, and then

choose whatever color I like. You can choose whichever one makes sense for you. Uh, and then once you choose

one of these default styles, you can then go in and change each one. So, I might go with this blue.

Actually like this one a little better. And maybe I'll change this to even different color like that. Maybe a gray.

There it is. And now we have a nice style. And then once the style is done, we can click done. And look at that. Now

we have readability, alternating rows, and our headers look nice. And then of course we can add in more to this. We

want to bold or even underline those. We can. And then we'll make those a

little bigger. And everything looks perfect. The last thing I may want to do here just to make

it look really nice is just center everything. I like that center look. Look at that. That just looks perfect.

So now we have a formatted sheet. You're more than welcome to change this. Maybe I want to make this a little bigger or

maybe I want this, you know, student information going across. We can call it student

information or student yearly information, whatever you want to put in here. And then we can

take this and we can actually go across and we can make this a certain size. So maybe I want it to go all the way to

here and merge and center or maybe I want this passing score to go somewhere else. You can always move these. And

what's great about this is even if you click and drag and move this, let's say down here, it'll keep the reference

point. So now I'll say that it's looking at N8 and B15 instead of where it was in the 01. So it does update dynamically.

Now we can select all of these and we're going to use our merge and center. So we'll merge all and then of

course we'll center this. I'll make this bold. I'll make it bigger.

And maybe I'll add in some sort of color to this. Maybe a nice little gray here. Or we can even if we wanted to copy this

look using this format painter and we can apply it to this look. And we can even

choose what color we want. So totally up to you on how you want this. I might even add that color down here too, just

because I want to keep everything consistent. I think that looks great. And now we have our score here. We'll

make that a little bigger. Maybe put it to here. Awesome. And I think that's looks awesome. You

can format this once again any way you want. All right. So, take a moment, try that out on your own. Format whatever

you like, however you want. Choose your favorite colors. And then once we're done, I'm going to actually showcase a

couple other cool features like checkboxes and other things that Google has to offer.

What we're going to be exploring next is checkboxes. I really like them because they're actually boolean factors. If you

check mark it, it's true and if it's not checked, then it's false. And because of this, we can do many different things

like conditional formatting, if functions, count if functions, and so on and so forth. So, let's create a new

worksheet here. We're going to go to the bottom and click the little books icon. And you may have also noticed that I

renamed my student info 2024. So, it might have said sheet one for all of you. But if you click the drop down,

remember we can always rename the sheet or even duplicate it for the next year. Once I click the plus symbol, however,

it creates a blank worksheet. And in this worksheet, I'm actually going to zoom in here. I'm going to go to maybe

125. That looks good. We're going to do a project alpha. Underneath, we'll just have a couple of

quick little things here. Uh let's do task name and the status. I'll then underline those. And next,

we'll do task one. And I'll just keep this very generic. We'll go through like task one through task something like

that. Perfect. And then I'll highlight everything next to it and go to the insert tab. And inside of that insert

tab, we'll click on checkbox. Now once we click on checkbox, it'll insert a checkbox in there for all of

them. And once again, if you click into it, you'll see false. And if you check mark it, it's true. So if it's check

mark, true. If it's not false. And because of this, we could actually do conditional formatting and even count.

So that's what we're going to do. But before that, we could also change the format of these checkboxes by going to

the text and then changing the text color. I'm going to use blue. It's my favorite. So

you can have company logos and names. Next up, I'm going to actually create I'm going to just say total and I'm just

going to create a simple function here where we count if it's true because remember if you check mark and it's

true. If you don't it false and I'll do equals count if press tapped in the parenthesis. select my range which is

from B4 to B13 and for my create three on it's going to be true because if it's true that means it's check mark.

Now once I press enter it'll say zero and then once we start checking it'll do a count. You can even get a little more

complex with this by adding in strings of text. You can do a percent sign which concatenates or combines things

together. And I'm just going to put a little slash. And you have to put the slash in quotes.

Well, then do another amp percent. And I'm going to do a count A. Count A will tell everything

that you select. So I'm going to select B4 to B13 again and then close that parenthesis. So all

we're doing is saying take the count of how many are checked, put a slash and take count of all of them total and that

will give me four out of 10, five out of 10, 6 out of 10 for now that we've done that another thing

we can do is we can actually use conditional formatting. Now we're going to learn about conditional formatting in

the next video. So we'll get a little deeper into those skills. But for this video, I'll just do it

quickly and I'll explain. First thing we need to do is select the data we want to affect. Then we go to the format tab.

And inside of the format tab, that's where you'll find conditional formatting.

We'll give that a click. Next up, it'll say, okay, we're going to apply the format to the range, but how

do you want to do that? What are the rules? Well, the rule is if it's checkarked, so I want it to look here.

And if that's true, which conditional formats are automatically by default true, then it'll do whatever you want.

So, I'm going to use actually a custom formula. And I'm going to say starting with B4, right? So, I'm going to do

equals B4. And I'll put a dollar sign in front of B because I want it to stay

in B. So, we're absoluting the column, but we're not actually putting an absolute on the four. That way, it goes

to look in B4, then it goes to B5, B6, B7, and so on and so forth. I don't need it to look into C, D, or E, but I do

need it to stay in the B column. So, that's what I'll do. And now I can just choose what I want. So, I'm going to

choose the bold it, strike it through, and I'll make this look like that. That looks perfect.

And you can also maybe I'll make this green. Awesome. So now whenever you check mark

something, it'll cross it off, make it green and bold. And boom, then we can have everything

done. So as you can see, check boxes are just for the look of checking something off, but they can be really functional

with other things like conditional formatting. We're also utilizing things like count it or count all or count a.

Right? So try this out on your own and when we come back we'll get into our closing and then we'll talk about what's

up next for the next video. All right everyone, I want to say thank you so much for being here. I hope you enjoyed

this course. What we're going to be doing is our next course, our next video and it'll be Google Sheets Intermediate.

There we'll talk about how we can create a table, do some things like sorting and filtering those tables, being able to

visualize our data using charts and spark lines. And then also we'll be getting into things like formatting,

conditional formats, and data validation, and also being able to create comments for collaboration. So,

we have a ton of fun stuff we're going to be doing in the next video. So, feel free to check that out and I hope you

all enjoyed this one. Stay safe, stay healthy, and I'll see you all next time. Hello everyone and welcome to Google

Sheets Intermediate. My name is Joe and I'll be facilitating today. In this course, we'll transform you from a basic

spreadsheet user to a data manipulation pro. You'll learn powerful techniques like pivot tables and data

visualizations that will supercharge your productivity. Whether you're in finance, marketing, or even project

management, these skills will help you. So, let's open up the example file and get started. One of the first things

we're going to learn about are being able to take raw data and creating tables. Tables are great for

readability. They're great for grouping data and also for filtering data. We can use tables with

freezing rows. And when we use them together, we're able to stop from dragging down and saying, well, what is

this data? I don't know if extension or what is this data hiring date and we can freeze our third row. So using those two

together in connection allows for us to really make a very userfriendly experience. instead of having just raw

data, using tables will allow for so much more benefit. So for putting this into a table, all you need to do is

click into your data tab and then we're going to go over to the format and we're going to use convert to table. Now be

careful because a lot of times people will go over and say, "Well, wouldn't we just go to the insert tab and choose

tables?" Well, that'll actually insert a blank table or even a predefined one by Google. So, if you choose tables, it'll

pop up over here and it'll say, "Oh, do you want to insert a blank table, event task, project task?" So, these are all

precreated already. And there's really great ones here. So, if you are planning on using one of these pre-made

templates, you're more than welcome to. But since we already have data, it's important that we just click into the

data and go to the format tab instead and then choose convert to table. You can also use this

keyboard shortcut. Once I click on that, you'll see it turns my entire data set

into a table. Now, at this point, we can do so many different things. First off, we can click on the little drop down

next to table one and we'll get all of the options. For instance, renaming the table. Maybe I want to name this table

employee information. So I can say employee info.

You can also change the way the table looks. comes sort of with this like green heading, but I can click the drop

them and I can actually adjust either the table range, turn off the alternating colors or go to table header

color and choose one of these pre-created ones. If you want to get more custom, you can click the little

plus icon and I'll just customize this. I like the color blue, so I'll click and drag.

Let's go with that's a nice color and I'll click okay. So now I have a nice blue.

If I want to change the color of the alternating colors here, we can always just go over to format and inside of

that format tab there, you'll see alternating colors. And that's where we can change the way this table looks. So

I'll give that a click. Format, alternating colors. And now I can change my header again if

I want to. They have quite a few. That's really bright. Let's go with blue. Like that. That looks good. And

then for my alternating colors, I'll do white. And let's go with

white. You'll see now we can see a little bit of a marker here. And I think that looks great. Now, of course, you

can always change these any anytime you want. For instance, maybe I do want to make this more of a lighter blue. I do

something like that. That looks good. And maybe for this one, I want to go with a gray.

Maybe a lighter gray. And we have just I'll even go one lighter. Oh, perfect. We've just created

our own style table. So, this looks great. I'm very happy with it. And once again, alternating rows allow for

readability. So I can disconnect between each row of data. Now, if you really don't like that, like

I said, you can click the drop down near the table menu. You can always turn off alternating colors, which will just make

it that raw data look again. But I actually like those, so I will turn those back on. Another thing you can do

with this table is if you want to you can go to the drop down and you can actually revert it back to unformatted

data. So that raw data I was talking. So if you don't like the table, you can always change it back. But before we

revert it back or we delete this table, it's important to note that table does have many other features. You'll see

right next to the table name that we have views. And what these views allow us to do is either create filters or

create groups. So let's say I wanted to group this data. Maybe I want to group it by department. I have so many

different departments. Or maybe I want to group it by location. I want all of my building one people together.

Building two, building three. So let's do location. And look at that. These are all the people that are from building

one, everyone from building two, and building three. Now, this is just a temporary view. You can always save the

view by going to the top right hand corner where it says save view. Or if you don't want the view on anymore, you

can go and click the X. That will exit the view. Or you can always go back to the views menu where you can choose to

exit view here. And it'll even ask you, do you want to save your view? You could say no.

Now you can always turn those back on. Now besides that, we can also do filtering, right? So if I decide I want

to filter this stuff, you'll see a little drop down next to each column here. These drop downs allow for us to

manipulate the data quicker. If you click the drop down, you can either edit the column type right away. Maybe I

don't want it to be uh just none. Maybe I want to specify that this is a text, right? And it makes it a text type

column. I can say well this higher date is specifically a date. And you can even add the time if you want or maybe I want

to say that these pay rates right or a certain type of number of currency. I can choose currency. Now you'll see the

little dollar bill there. So you can change the data types which can be very beneficial later on or especially when

you're transferring this data into something else. But if you did want to click the drop

down, you also have sorting ability. We'll talk about sorting a little later. You can filter or you can even group.

You can insert column to the left or the right or even delete the table column. I'm going to say that I want to filter

this column. And then it'll get my filtering options here. And once again, we'll talk about filtering and how to

utilize this later on in the course. So tables can be very beneficial. The last thing I want to showcase within

this table though is how we can use this in connection with freezing rows. Because as I said before, even though we

have this table, once we start going down, we're running into that issue again where we could say, you know what,

what are these numbers? I forget what these numbers are. Oh, moments. What are these dates? Are they

birthdays? Oh, they're higher dates. So, when you have large amounts of data, it can be really hard to determine what is

that data without scrolling back to the headers. A quick fix for this is going to the view tab. And in the view tab,

we're going to use what's known as freeze, where we can freeze columns and rows. I'm going to go up and I'm going

to say, you know what, I want to freeze one row. Right? That doesn't really do much for us. So then I'm like, okay, I

want to freeze two rows. Well, we're almost there, but still doesn't do much for us. What we

want to do is go to view, freeze, three rows up to row three. That will freeze right below the header. And now look,

you can click anywhere in your data. What is this number? Oh, the phone extension, email, department. And I'm

just using my mouse wheel to scroll. So now I'll never get lost or not know what the data is that I'm looking at. But

this is the hire date. These are the pay rates, locations. Now we have a fully functioning table where we can analyze

the data. So take a moment, try this out on your own. And when we come back, we'll talk about some of those features

we saw like sorting and filtering data. When we're dealing with data analysis, a lot of times what we want to do is look

at certain pieces of data and look at it in a certain order. Now, we saw with the table, we were able to click into this

little drop down and we have the ability to sort or even filter the column. So, that's a great thing within tables is

it's already built in where you can click and you can sort something or you can click on the filter and turn on the

filters. And before we get into that though, I want to switch up our data. Let's all switch over to our next

worksheet here, which is sorting and filtering. Now, inside of this data set, you'll see

where we can select the data and we could start to sort or filter it. It's really important though before we start

sorting or filtering data that we have some sort of numeric index. That way, whenever we sort the data, we can always

go back to the original order that the data was sorted in. If we didn't have the numeric system, well, then we'd

never know the original order. So, it's always nice to have that index. If I want to sort the data, what we

could do is we can go up to the top and we can click over to the data tab. Inside of the data tab is where you'll

see we can either sort the entire sheet or we can sort the range. Now, it's really important to note here as well

that if we do sort this, if we just click into the data and we say, okay, I want to sort this entire sheet, it's not

going to work really nicely because it's going to be sorting the entire column B from A to Z or Z to A. If we click on

that, it'll also include right our headings. So this doesn't work nicely. It actually puts this in and yeah, it's

a huge mess. So I'm going to actually use control Z to undo. And let's try that again but with department. Maybe I

want to sort the departments. And I'll go to data sort. And you'll see column F, E to Z or

Z to A. And once again, it does the same thing. Now, even though it does sort it at the bottom here, you'll notice that

our title goes to the bottom. It once again doesn't work as nicely as we want. So, you might be thinking, well, what

happens if we select a range of cells? You're going to just use control shift down arrow, select all of the data in

column F. If I go to data, you now see that not only sort sheet is available, but also sort range. And this is going

to do the same exact thing. If I go to sort range A to Z, it'll do it. It'll work nicely.

But once again, I have to select the data first in order for it to work like this. So sometimes you have to select an

entire range of cells. And if I go like this, I can do C to A. And you might think, wow, this is working really

nicely. But take a look at the rest of the data. Nothing's really changing. So it's not

working right. It's just literally changing the cells that we have selected. That's not what we want. We

want everything to then change, right? Everything to be sorted. So, what do we do here? Well, the first thing we're

going to do is turn on the filtering off. This will allow for us to indicate each of our columns. So, I'm going to

click into one of my headers here, go to data, and choose to create a filter. Once we do that, all of our columns now

have little filter options. And inside of this filter options, you'll see one of the first things we can do, it's sort

A to Z or Z to A. So I can say I want to sort this data like this. And now it's working beautifully. As you can see

here, we're sorting the information. So even though they have its own sort feature for the tree or for a certain

range, sometimes it's easier for us to turn on the filters and use the sorting through there.

Now if I ever want to sort this into a original order, I can just go to the numbering system after sort A to Z and

it brings it back in sequential order. So try this out on your own. Go in sort, you know, sort for instance, maybe you

want to sort by division. I can sort D to A. This time I want to do and it starts with Vermont all the

way down to Connecticut. And then I can do that again, right? Sort this back by the numbering system.

Maybe I want to sort by first name day to day. Starting with Alice all the way down to William. So once again, this is

going to be an easier way to sort than having to go through and use the data sorting options here, which are sorting

by an entire sheet or by entire range. Because if I use sort sheet column A, a A to Z, it'll do that thing again where

it also messes up the headings. And just be careful with that when you're using sorting. Always make sure to turn on

your filters. That's one of the easiest ways. And then you can just use the sort feature there. And you can even sort by

color. There's an added feature. So take a moment, try this out on your own. And then when we come back, we're going to

talk about how we can sort, but not only sort, also filter our data. Let's say that we only want to sort the data. We

don't just want to filter it right now. So you might be thinking, well, when would we use the sorting options? Well,

you would use them when you don't have the filter on. So, I'm going to choose remove filter and let's say I want to

sort this entire data set. I'm going to select it all by using control A and then go to data. You'll see once again

we can sort the G, but we're going to actually sort this entire range by using advanced range sorting options.

Once we click on that, it pop up this dialog box. It'll ask us if our data has headers, which it does. And now we can

look at those headers. And let's say I want to sort this by last name, A to Z. I'll then click sort. And it worked

beautifully. So if you don't want to turn on the filters just to do sorting and you want to use those options in the

data, you still can. You just later on if you need to change this, go back to advanced range sorting options, say it

as headers, and then sort this A to Z by number sort and it brings it back to the

original. So that's our sorting portion. But let's say we do want to use filters. See, sorting is good for grouping

things, right? And putting them in a certain order. But when we're talking about looking at specific data with a

specific criteria, filtering is going to be what we want because let's face it, we have a lot of data. We don't always

want to look at the entire picture. We just want to look at a certain piece of the data.

So the first thing I'll do is once again click anywhere in my data, go to data tab, and we're going to choose create a

filter. Once I click on that, it'll then turn on my filters. There they are. And at this point, I can go in and I can

click on my little filter icon next to any of the headers and choose what I want to see. For instance, division. I'm

going to click on that little drop down there and I'm going to say that I want to only see maybe Connecticut. So you

can either clear all the check marks, select them all, or you can go in individually and

turn them on or off by just clicking on them. A quick way would be to clear and then just choose Connecticut.

You can even search if you want to just look for Connecticut. You can start typing in Connecticut and just click on

that and then click okay. And now I have only Connecticut showing. I'll know that there's an actual filter

on it too because you'll see the icon changes to a little little icon. And if you ever want to clear that filter, just

click on it again. And then you can choose to select all and click okay. And now our data is back. You'll know that

the data is not being filtered because it goes back to the original icon. Now, what's really great about filtering

is you don't have to just filter one thing at a time. You can have more criteria. For instance, maybe I want to

look at my New Hampshire development department. So, how many people do I have in New Hampshire development? So,

I'll click I'm going to clear it all and just click on New Hampshire. And then I'll go to department and clear

and only choose develop. And then click okay. And there we go. I have three people working in New

Hampshire that are in development department. After that, I can always clear this out.

I'll just click on these. Then you can say clear. Or another thing you can do is you'll see on your quick access

toolbar. We can remove all the filters at once by clicking the little filter icon here. That way you don't have to

click into especially if you have multiple criterias. You don't have to click into each one, right? You can just

click here and it brings back all the data and then you can turn them back on. Another thing that's really cool with

filtering is every type of data has a filtering. For instance, I can go in and if I click into like division, I can

choose to filter by color, filter by a condition or I can also filter

by values, right? And I can choose what those values are here like we've been doing. But the filter by condition is

really cool because we can say, well, I only want to see things that are empty or not empty or text that contains a

certain thing or text that doesn't contain or maybe text that starts with one thing or ends with another or I need

exactly this text. You can also look at dates. The date is a certain date. equal to today or before today or after today

or even comparison operators like greater than, greater than or equal to, less than.

And then some data validation or even custom formulas to filter. So there's quite a few things we can do here by

filtering by condition. So try this out on your own. Filter a couple different things, do some sorting as well. And

then when we come back, we're going to change the pace and we're going to talk about not only analyzing data, but

actually visualizing it with charts. We've talked about how we can gather our data, how we can sort it, and how we can

filter it. Now, we're going to talk about how we can visualize it. So, let's all go over to the chart worksheet.

We're going to give that a click. And we have some simplistic data here. We have 2023 sales report where we have

our salesperson, the four quarters that they worked and how much each one of them made for each quarter. The first

thing we want to do to visualize the data is select what we want to visualize. So I'm going to select from

A3 saleserson all the way through until E8. We don't need to include the header here. We'll then go over to the insert

tab. And inside of that insert tab, we'll go over to the chart and give that a click. Then once you go to insert

chart, it'll open up this little chart editor pane and also give you your chart where you can click and drag it. You can

resize it using our little sizing bullets. And there we go. Now, the first thing we

want to do when we're in the chart editor is set up our chart. After we set up the charts the way we want it to be,

we can then choose to customize it any way that we like. So, I'm going to go in and I'm going to say I want this chart

to be a certain type of chart. I mean, I do like the column chart, but let's look at our options. First off, it has some

recommendations and suggestions for us. We can either do a type of chart like this column chart, a stacked column, a

bar, or even a stacked bar chart. So looking at our data, Google says that these are the columns or stacked columns

or bars or stacked bars that we can use. Down though we can choose whatever we want whether we want a certain line

chart, a smooth line or even a combo chart. We can do area charts. We can do column or bar charts, pie charts,

scatter charts, or even maps or even other types like waterfalls or over here we have a radar chart or

even fun stuff like organizational charts or a table chart. So, we have so many

different ways to visualize our data, but I think the chart looks good. So, I'm going to choose that one.

After that, we can choose what kind of stacking we do. Do we want standard stacking, which turns into a stacked

column chart, or 100%. But I'm going to say that I like none. I like seeing the individual bars.

We can also change the data range. So, if we might have missed something or if we add another sales reps on here, we

can then select the data. Now, right away, it tells us that the x-axis is our salesperson.

And we can click on that and we can actually choose specific salespeople, right? And we can look at what numbers

they have for each quarter. So that's quarter 1, quarter two, quarters three, and quarter four. Or we can just look at

all the sales reps in all the quarters. After that, we can also see the series. So I can click on Owen to see Owen, or I

can switch this. Maybe I want Owen here twice. Maybe I want Sophia first. So whatever order you want, you're more

than welcome to choose. We can also choose aggregation. So right now we have our aggregate as sums. It's

summing up their total sales and then it's putting it on the system. But maybe we want to see the averages instead. So

we can click the drop down and choose the average or maybe we want to see the count or the max or the median.

But I think sum is perfect and we don't have to use these. Another thing that we can do is switch

how we're looking at this. Right now we're looking at it where we're seeing each sales rep compared against them

each other in quarter one. We can also look at the sales rep compared against themselves by just switching the rows or

the columns. So now I can see Owen did the best quarter 1, Sophia did the best, quarter three, and so on and so forth.

Always remember you can switch these. So if they end up being out of order, just reorganize them nice and and that

makes it look nice and pretty. But I like the way it was where we were seeing who did the best overall.

And then always remember that we only have Owen, Sophia, Lucas, and Isabella. If I want to add in Noah, right, cuz now

it switched. I can add Noah, and now it shows all my sales reps. So if you're ever missing a sales rep, it might be

because you switched it and there was only four quarters and when you switched back it got rid of no style sometimes

happens. Just keep yourselves aware of that. So you can always fix that. You also have other things like use column A

as headers or use three row, right? Row row row three has labels there and you can use all that. Now once we're set up,

we're then going to want to customize. So, we can go next to the setup button, go to customize, and we'll get all the

different ways to customize this. And I like the way they set this up because each one is in its own sort of category

like style. What do we want the background or the border of the chart to be? Maybe I want the background to be a

nice light gray. Oh, that looks good. And I want the border to be black.

I can also maximize this. zoom in to a 3D or I can do compare mode which doesn't really do much because we're not

comparing each separate data sets. Once I'm done with chart style, I'll close that and go to the next one and

access titles. So for the chart title, I don't really like the way it says oh and Sophia Lucas, right? So we're going to

get rid of that and we're going to say the chart title is going to be 2023 sales leadport.

You can also choose if you want it bold, underline. You can choose the color of the text. You can choose. Let's go with

Yeah, black looks good. And let's make it in the middle. Oh, that looks awesome.

Not only can you just choose here, by the way, you can also go in and anything you choose on it, it'll open up the

menu. So, for instance, if I click on the chart title again, it opens it up. Now for the series, you can either

change all of them at once, apply to all series, and change all the colors the same,

right? Or you can also go in and you can choose each one and what you want the colors to

be. So, what do I want the fill color to be? Well, maybe I want this one to be red, right, for Owen. Maybe Sophia, I

want Sophia to be green. Lucas, I want Lucas to be blue. And then Isabella. I like that. And then

Noah. Let's go with purple. Let's see. You can change them all. I think that looks awesome.

And you can also change the opacity if you want. Maybe 10%. Make them lighter. 50%. You can have a lot of fun with

these. Maybe I'll change them all to fit. I think those look really good. But there's no right or wrong here. This

is really just the design part of things and just have fun. And there it is. That looks like a fun

little dart there. And now that we have this all the series, we can also add in error bars if we wanted to with

percentages as data labels or even inlines. We can choose what that trend line is.

And I think we're pretty good on those. We don't need any of that. And now that I'm done with the series, I

could jump to the legend. How do I want the legend to look? Where do I want it? Maybe I don't want it up top there.

Maybe I want it in the bottom. Or maybe I want it in the left side of the chart. Or or maybe inside, right?

Or maybe to the right. The bottom looks pretty good. Let's check that one. Yeah, that looks

awesome. Next up, we have horizontal axis. Do we want to make any changes to the labels?

The font size. I think everything looks good. Maybe bold them. I like the bold. Maybe also

the legend. We want to bold. You can do that, too. And then for the vertical, we'll bold

that. And then for the grid lines, do you want to change the way the grid lines look?

Right now, I just see the grid lines as major grid lines. We can change the spacing, by the way. We could do stabs

or counts, make them a little bigger. But for the grid lines, I think we'll go with black. That looks awesome. And then

not only that, do I want minor grid lines or do I want only major grid lines? Or maybe I don't want grid lines

at all. or do I want major tick, minor ticks? So, totally up to you. And I I think

this is coming really nice together. I'm just going to leave it like this. I think this will be perfect. So, once you

have it fully done, you can close out the chart editor. And once again, you can move this

around, make it bigger if you want to. And now we have an awesome looking chart. So, what I want you all to do is

try this out on your own. Just go in, create the chart, choose whatever kind of chart you want. You don't have to do

the same one we did here, but play around with it. Add your favorite colors or maybe company logos or themes. And

then when we come back, we're going to talk about another type of visualization.

We saw how we could take the 2023 sales report and visualize it in a beautiful chart and we were able to format that

chart. But there's other types of visuals, too. And the next one we're going to talk about are spark lines. So

let's switch over to the spark lines worksheet. And once we're there, you'll see we have

sales for 2023. But we have some different sales here for different items like t-shirts, hoodies, hats, mugs, and

gift cards. What we want to do is we want to track the trend of each of these items. And in order to do that, we're

actually going to use spark lines. Now, spark lines are great for tracking trends of data and they come in all

different types. You can use line spark lines, bars, columns, or even when loss if there's negative value. So, click

into F4. And the first thing we're going to do is click the word trends. And you'll notice that it doesn't really

look like the other headings. So, a quick fix to this is to one could then use that format painter. picture that

looks like a little paint roller. And when you click on that paint format, it'll copy the look of this. And then I

can apply this one. That looks perfect. Now it looks like it's part of our data. Another thing I may do here real quick

is just zoom in just so I can see my data a little closer. So I'm using the 150 zoom.

Now I'm going to click in the F5 and we're going to start off by creating the spark line. And the spark line is

actually a function feature. So we're going to start off with equals. Type in the function spark line and it tell you

that it'll create a minute chart with a single cell. Once we click on that, the first thing it asks us to do is grab the

data. So we're just tracking the trend for t-shirts from quarter 1 to quarter 4 sale. So I'll select B5 through E5.

We can then stop right here and it'll just be the default spark line, which is just a simple black spark line.

If you want to add more deep to this, you can. You have different options here. Things like changing the chart

type instead of it being the default line, we can make it into a bar or more. Or maybe we want to do maximum value or

maybe we want to change the color of the line width. So, we're going to go through a couple of these. Let's put a

comma. And in order to view these options, we're actually going to use curly brackets. So we're going to do an

open curly bracket and we're going to put in the chart type in quotation marks. So I'm going to say chart

type and that could be all lowercase. And then what we'll do is we'll put a comma and choose what kind of chart type

we want. So for instance, if I just do line, well, we're going to get the same exact thing that we're getting. Now,

make sure though you do put the ending curly bracket. So, charts type in line because curly brackets and you press

enter. There's the line. If I want to change this to let's say a bar, I'll just type in bar instead of line. And I

can quickly change it to a bar. Or maybe I want to do a column chart. And now I have a column chart. Now, I

don't like the color of the column chart here. So once again we can add more option and if I wanted to add more

options let's say I have the chart type as column after column I'm going to put a semicolon and now I can start adding

more options like the color for instance. So I'll write the word color in quotes I'll put a comma and now I can

choose the color that I want. Maybe I want the color blue. So I'll put the color blue in quote and press enter.

And now we have a blue column chart. If we wanted to maybe choose instead of a blue column chart, maybe we want blue

win loss chart. I mean this won't make much sense because we have no negative value. But if we put in one loss,

if there was any loss, let's say this became negative 600 instead, it will look like that.

And then 600 goes right back. But I'm thinking, let's just stick with the line. I like the line chart. So,

I've won last line. And there we go. Another thing I don't like is the blue color for the line. I'm

going to just use black. So, going to go in and write or you know what? Let's do let's do red. Red. Let's see what that

looks like. Oh, I like that. Actually, that looks good. But I do want the line to be just a little thicker. So I'm

going to use what's known as line width option and we'll change that to either three or two depending on what we want.

So same thing after we choose chart type line semicolon to split it color red we're going to use a semicolon to split

it and we're going to write line width. I'll then put in a comma and let's say we put in a five. That's going to be

really huge. That's what it looks like. So, I'm going to change that line with two. Let's see what three looks like.

Not horrible, but I might want to go just one smaller. Let's go with two. That's perfect for me. I like that.

Looks nice. Now, one of the greatest things about these spark lines is that because they are functions, we could

just simply autofill. So, if we go to the bottom right hand corner, you can just autofill down. And now we have the

rest of our spark lines. You can add in if you want to. I like to do this for this type of table. I'm going to add in

borders. So you'll see borders up top by your quick access tool. I'll add those in. And I'll just do all

borders. That way it sort of separates it a little more. And then another thing we can do here is if you want you can

make row five 6 7 8 and 9 just a little bigger. So I'm just going to increase

the width a little and I'm happy with the result. So I can see that t-shirts are, you know, they're

doing okay. They were amazing in quarter 3, but they started trending down. Uh hoodies are doing amazing. It looks like

we've been trending upwards since quarter 2. quarter

two all the way down to quarter four for hatch. Not too good. And then I can see mugs. We were really doing great in

quarter two, but then we took a nose dive in quarter three sales. And I'm assuming that's because in quarter two

maybe there was, you know, some sort of holiday or discount on the mugs. So this is just a great visualization of how

we're trending in our sales for our different items. So take a moment, try this out on your own, and when we come

back, we're going to talk about analyzing data with pivot tables. Let's all take a look at pivot tables. So

we're going to move over to the pivot table worksheet. Now, what I love about pivot tables is that it allows us to

really analyze our data quickly and efficiently. For instance, we have the sales data for 2022 and 2023. And we

have quite a lot of data here. I mean, the data is not too rough to look at cuz it's pretty simplistic. We have the

month, the year, what we sold, who sold it, what region they sold in, how much we made, and how many of them we sold.

So looking at this data, it's nice, but at the same time, if we keep scrolling, you'll see we have hundreds of rows of

data here. So what I want to do is I want to take this data and transform it into a pivot table. Pivot tables allow

for us to look at the data and analyze it quickly because it's like a table, but it allows for us to interchange the

rows and columns. That way, we can switch out the information, answer a question, and then pivot to the next

question, answer that question, and pivot to the next one, which is exactly why it's called a pivot table. So, we're

going to select all our data here. I'll use control A. We'll go over to the insert tab and inside of that insert

menu here you'll see pivot table. So we'll give that a click. Insert pivot table and it's going to say okay we see

here that you want to create a pivot table using A4 to G168. Do you want to put this in a new sheet or an existing

one? Now I will say I like to put these in new sheets. The reason being is because if you put it in an existing one

it can mess up the data that's already there. So, I'm going to choose new sheet. Click create.

And now we have well a pivot table, right? There's not much to go on. You'll see here that we have our columns, our

rows, and our values. So, those are the three main pizzas. We can also filter on pivot tables, but we'll talk about that

in a second. So these three main pieces are important because this is where we can list our data out whether we want it

in the columns or the rows. And then the values I would say about 95% of the time you're going to only use

numbers for values. Once in a while it might be beneficial to use something that's not a number, but always remember

numbers can be aggregated, right? You can add them together. You can average numbers. You can find the max number.

the minimum number, but it's also beneficial for us to count things like maybe we want to count how many times a

salesperson made a sale. That would be really interesting. So, we don't always have to use numbers. We can also put in

let's say the saleserson's name and it would just do a count because of course I can't have like hey what's Joe plus

Ben, right? It would just be Joe and Ben make two people. I can count those. So let's say for instance, and by the way,

you'll see suggestions here, which I always think is great of Google to do. So Google did give you some suggestions

of what you want. You don't have to use those, by the way. Uh you do have a clear all button to clear out your pivot

table at any time. And then over here, you have all the different fields, which is all of your data just plugged up into

these fields. So for instance, all my months are here, all the years are here, all the sales reps are here, all the

type of items are here, and so on and so forth. So now to build this out, let's say that

I just want to take a look at sales reps. Who's my sales reps? Well, I can click and drag sales rep, right? Or

saleserson into the rows, and it'll just list them going down the rows. Andy, Julia, Paul. Okay. I can also sort this

or order it in any way that I like. Now, if I don't want salesperson anymore, I could click the X and they're gone.

Let's say I take salesperson and put them into the columns instead. Well, if I put them into the columns, right, make

sure you kind of click right underneath there, you'll see now they're just going across the columns. Andy, Julia, Paul.

Okay, that's great, too. So, I can either list them going down the row or across the columns. It doesn't change

the data, just changes where they're located. So if I put them in the rows, it goes down the rows. If I put them in

the columns, goes across the columns there. Now, if I put salesperson, which once again is a person, right? We can't

add people or average people or min or max people. So what would happen if I put a person into a value? Well, the

only thing it could do if it's not a number is count it. So, if I do that, it automatically does a count and it's

counting everything. You'll see 164. So, the reason it's doing that 164, you might be wondering, you know, Joe, we

only have three sales reps. Why is there 164? Well, if we go back to pivot table, you'll see here that we have Julia,

Paul, and Andy, but they made 164 sales. They didn't just make one sale each. So this is already answering a question,

right? How many sales did we make? 164. So sometimes that is beneficial to take something that's not a number and put it

into values. But like I said, 95% of the time you're only putting numbers here. So let's ask a couple questions about

our data. For instance, how much should we make total in sales? So I look for sales. Yeah, there's all my sales. And

since sales is a number, I'm going to put it in the values. Now, you don't always have to click and drag. I like to

click and drag, but you're more than welcome to just click the add button and then choose here. So, whatever you know