Transcript for:
Essential Google Sheets Course Overview

Learn the fundamentals of Google Sheets in this beginner's project-based course. Eamon Cottrell will teach you basic and advanced features of Google Sheets. You'll learn about navigation, functions, custom formulas, conditional formatting, data visualization, and a whole lot more. Welcome to this project-based Google Sheets course. If you're a beginner, this is perfect for you.

If you're an advanced user, you'll learn some new tips and tricks along the way. I'm going to teach you all about Google Sheets. We'll build a simple project together utilizing basic and advanced Google Sheets functions, formulas, and tools. By the end of the course, you will have learned navigation, collaboration, advanced functions, custom formulas, function combinations, data validation, drop-down lists, charts, data visualization, and a lot more. I hope you enjoy and you can find a lot more free resources at gotsheet.xyz.

Welcome to module one. This is where we're going to hop into a spreadsheet and actually go over, first of all, just some basic functionality. How do we move around? How do we do things? What's up with the taskbar?

What are functions? What are formulas? The difference between them? If you know all this stuff and this is old hat to you, you can skip on through it.

We're not going to spend a lot of time here, though, and we are going to use the spreadsheet that we start creating right now in the rest of the course. I'm going to be using a personal budget example. I've been using these for about as long as I've been using spreadsheets to track my own finances because I like to manually put stuff in here, share it with my wife.

We both have the ability to access it, and it does a lot of stuff automatically for us. So we're going to use that for all of our sections. So we're going to go over like basic functions and formulas.

Then we're going to get into conditional formatting. Then we're going to go to data validation and dropdowns. We're going to do advanced functions and automations, charts and data visualizations.

And finally, as I mentioned in the introduction, we're going to give a preview of using Apps Script, which is programming. I mean, it's legitimate programming, but it's very powerful. So it's going to be very interesting. We're going to build on everything that we do in each of these modules.

So I hope that you will enjoy. And here we go. Let's hop into a Google Sheet. If you type in sheets.new, it'll take you straight to a blank Google Sheet. Now, this happens for me because I'm already signed into the browser with my Google account.

If you're brand new to all that, let's just open up a incognito window. We can come over here to sheets.new and it'll take us to this sign in page. Now. If you don't already have a Google account, it's super easy to sign up for one.

Chances are you already have one of these, so just sign in. If you don't, though, just create one for personal use. It's all free. And then you'll have Gmail, then you'll have Sheets, and all of the other Google applications.

Now let's talk about what we see here. For the complete beginner, this is a spreadsheet. So a spreadsheet, I'm going to zoom in here.

And actually, I'm going to get rid of my face because it's going to... block some of what we want to see here. And if I ever leave my face there, please yell at me.

What we're going to see here is columns indicated up here by these letters. So columns are named by letters, A, B, C, D, E, F, G, all the way through the alphabet. And then if you were to add more, then it goes A, B, A, C, A, D. So you can add tons and tons of columns.

Google Sheets gives you A through Z out of the gate. And down here, we've got rows. These are just numbered one through however many rows.

And it goes down to nine hundred and ninety nine or actually goes to a thousand out of the gate. But as you can see here, you can add a thousand more if you want or a thousand more than that. So there's three thousand.

There are pretty generous limits. You can get some crazy big Google Sheets. And if you're in Microsoft Excel, as you may know, you can get even bigger over there.

Okay, what are all these grid lines? Well, in here, these are the cells. That's what we call this.

This is cell A1. So we reference the location by the letter of the column and the number of the row. So if I wanted to go to F13, that'd be down here.

And incidentally, as you can see, you can just click into any cell that you want. It's different from a Word document in that you can just click down in the middle here and just start. typing stuff in if you want.

You don't have to start at the beginning of a line and click enter or anything. Okay, so you can use your mouse to click around. You can also use your arrows. So you can just go like this.

And if you want to go really fast, you can hold the control button and press your arrows. This will take you to the next Cell where something is and if nothing is in any of these cells it'll take you to the end of these Columns it took me all the way to the end here So if I had something written here Then I can just warp straight to it pretty much by clicking control and the arrow Same thing going down up and down it takes you to the next active cell or the next cell with a value in it rather Final couple shortcuts just for navigation purposes. If you hold control home, it'll take you to the very top leftmost corner of the spreadsheet and control end down to the bottom right of the spreadsheet.

As you saw me do, you can add columns by just highlighting or if we want to just add one, you can just click here and then right click and you can say insert column to the left or the right. If you click and then hold shift. When then click again, we can select all of these columns and we can insert that many columns to the left or right.

Or we can delete those columns. And there's many other actions we can take, too. Same thing holds true for inserting or deleting rows. We can insert however many we have highlighted or delete that same number.

Now, down here at the bottom, let's go to the bottom first and then we'll come up to the top. At the bottom, this is our sheet name. So when you open up a Google Sheet, its naming convention is a little deceptive. What we have here is actually a workbook.

So this is the spreadsheet. Excel calls it a workbook, so I often will think of it that way. But this whole collection is a spreadsheet, and you can add sheets to the spreadsheet.

So this is sheet one, and let's just name this our budget. And we can click this little add button down here to add another sheet. And we're going to name this categories.

All I'm doing here is double clicking in here to rename these. And you can alternatively right click and you can rename right here. And we'll also change the colors.

Let's grab a green here and then let's change the color of this tab to a dark purple one. Good old dark purple one. As you can see there's some other things that we can get into we're not gonna talk about these It's a little bit more advanced, but we can protect or hide these as we see fit, okay Let's talk about the top bar up here.

So here we've got our menus and you're probably familiar with these It's kind of standard issue stuff. We've got file edit view insert format and these are Spreadsheet specific menus, but they function like any menu you've seen on any computer UI anywhere You lets you do certain things. Right below here we've got this toolbar and these are some of the more commonly used functions or tools rather for spreadsheets. So let's see if I've got something written here. Let's just write my name in this cell.

Click enter and there's my name. A little bonus tip here you can grab this and resize that cell or that column or you can double click and it'll auto resize it to however wide this is. But now that we've got something in this cell, we can come up here and do things like format it differently or change the font. I like this outfit font. We can change the boldness or the italicized nature of it.

We can resize that again if we do any of that. We can strike through it if we want and we can change the color. So all these are, you know, standard issue formatting deals. If we click this three dot. little line right here, this is just more options that are going to be unlocked here.

We've got alignment options, we've got borders we can put around things, we've got the background color that we can put in that cell, and then we've got vertical alignment. So if we had a row that was taller, we could center that name in the cell just like that. Click the font size up, resize this, and there you go. There's some basic stuff you can do, but... As you'll start to use spreadsheets, you'll find a lot of your tools that you use are just right here a click away.

Now, there's shortcuts available also that's outside the scope of this video, and that makes life a lot easier and faster. Let's just angle that up. That looks absolutely terrible, doesn't it? Undo.

Now, let's pop back up here, and the very first thing we should have done in the beginning was name our spreadsheet. Just like we named our sheets down here, we can actually name our spreadsheet up here. So we can just type in personal budget. Let's just put in an emoji.

I don't know, something obnoxious like a stack of money. There we go. Press enter. And now we've got the name of our spreadsheet, which consists of two sheets down here at the bottom. We've got our budget.

in our categories which we'll begin building here in the next section of the course. Let's talk about two more things in this module before we get into the actual nuts and bolts of beginning our personal budget. This table sidebar over here, depending upon when you watch this course, this may or may not still be popping up.

Tables were released in Google Sheets in the spring or summer, I guess, of 2024. And ever since then, it's got this that pops up that lets you just select these different types of tables because it wants us to know that, hey, we can put a table in automatically and we can format things as a legitimate table. We hadn't been able to do that in Google Sheets. This was a Microsoft feature only for a long time. So that's what this is. We're going to get rid of that.

And let's see if I can get rid of that table I accidentally inserted as well. We're not going to use that. It can be helpful. It can be a good starter template.

But we are, like we said at the beginning, learning the basics so that we know how to do these things on our own before we get into the automatically generated stuff. Number two, let's talk about sharing and collaboration. This honestly is one of the reasons that our company chose to use Google Sheets over Microsoft Excel for our very specific small business. purposes.

Up here at the top, we have this blue share button, okay? And right now it's locked with this little lock icon, private only to me, it says. So I can copy this link, and if I open this in an incognito window, it's going to prompt me to sign in, and only I would be able to sign in and view that.

If, however, we click the share button, well, check this out. Now I can share this with anybody I want to, okay? And once I share it with them, I can change. how they're, how they are allowed to access it.

So now I can say anyone with the link can view the spreadsheet or be a commenter so they can actually leave comments on it. Or heck, I just want to make them an editor of this sheet so they can actually come in and edit anything on the sheet unless I specify otherwise in the sheet itself and block off stuff that only I can edit because I'm still going to be the owner. So I've still got that master control. Okay, once we do this and we invite, and let's just invite one of my other email addresses, we can say, hey, buddy, here's a sheet from the video course. We send that.

Then in email, that person is going to get this right here. And it'll give us a warning that this is a dynamic email. It contains stuff that can be changed dynamically.

So it's going to say, hey, here's that. sheet and I'm going to open it up here and actually it stayed in my regular account, but I can go over to my FNA show account and you can see that I can edit as FNA show. Incidentally, that was the first podcast that I did back in 2006. It was called the FNA show.

In case you were wondering, I thought you might be. Okay. So that makes collaboration very easy and very, very useful in Google sheets.

Now stick around, we're going to get into building the personal budget in the next section right now. Welcome to module two. This is where the fun is going to start. We're going to start building this personal budget. Let's go ahead and just get started right away.

As I showed you before, do you remember how do we delete a row? Just right click it up here, select delete row, get rid of my name. We don't need that.

Now we got two sheets we're going to work with. Let's delete this out of the cell. We can just hit the delete button or the backspace button. We'll delete.

the contents of a cell. We've got this budget sheet and we got this category sheet. In the categories, let's start here and do a couple things.

Now we can just type in anything we want, right? Income accounts, expense accounts, and then payment methods is what we're going to have right here. I'm going to do a couple things real quick.

Control A, if you press that a couple times, it will select everything in our sheet. Let's do that double click right here so it resizes the columns. And then let's change the font to this outfit just so it looks not like the standard font. I'm going to create a bold font here by just control B is the shortcut for that. Or you could click it up here.

And now I want to fill in some income and expense accounts that we're then going to use a little bit further on in this course in our budget. Now I've got them in a spreadsheet already, so I'm going to copy them in. Another thing to note as we copy and paste, look at this.

I have pasted or I've copy and pasted and it's brought in some formatting. that maybe I don't want. So if I actually, instead of control V pasting normal, I do control shift V, it'll paste just the values. And you can see that it gives me an option to do that here also by clicking this little paste deal. And so now I've got those and I'm going to go ahead and copy and paste in these other items here with just those values as well.

Now let's use the new table features. If we select this range and right click, then we can convert this to a table. This is going to allow us to reference these things in our budget a lot easier than we would otherwise be able to. I'm going to double click up here, rename this into categories, spell it correctly, and we're good.

You know, we don't have to change the color, but show you how you can do that if you want to by clicking right up here. We'll change it to a red. Okay. Now that we've got our categories set up, let's go over here to our budget sheet and start doing the good work here. I'm going to select all this and make it the font we like.

And I've already got some headers that I've brought in over here. Let's lock this header row in place, too. If you go to the very top left corner, your mouse cursor will turn into a little hand if you hover over this gray bar.

Drag that down and look. We locked that first row into place so we can always see our header row. Now. How do we get some fake data? I'm not going to give you my own personal budget data, so let's go generate some here.

And you know, you can use Gemini to do this, but I found that it's not as good as I want it to be. And this site right here called Mockaroo just gives us a ton more options. So I've selected a date, a transaction from these random departments, and then an amount, and I'm going to generate data from that.

It downloads a file. You can see it pops up here. We just save that to our desktop. And then to import data from our desktop to Google Sheets, we can select File, Import.

It's going to give us some options to look in our drive, but what we want is to upload, and there it is right there. Once we're there, we can pick where we want this data. So I'm going to say append to the current sheet and detect automatically for the separator type.

And usually it's pretty smart. It'll do what we need it to. All right, so we got dates, transactions, and amounts. I'm going to delete this row because it brought in another header. And now we've got some fake data to work with.

The dates are mixed up though. So how can we actually sort this out? If we click this little triangle right here in the column A and scroll down here to sort sheet A to Z, it will actually sort all of those amounts.

by the date. So now we've got everything from January through March 2024. And those are all in order. So now we're good to go in filling out the rest of the sheet. Before we start doing some more advanced stuff and finishing out the rest of the build here, let's just talk about formulas and functions. So a function is a built-in function in Google Sheets.

It looks like this. Sum, this written word, is a function that It takes values and then adds them together. So if I put sum 5 and 5 with a comma in between, it's going to give me 10. If I put sum of these two cells by clicking and dragging down, it's going to add up these cells together.

If I type in something like 5 plus 5 immediately in a cell, it's going to also give me 10. It'll do basic arithmetic and everything on its own, but the functions that are built in are where the real power is. And something Google Sheets does a really good job of that is actually something Excel could take some notes on is this right here, this pop-up menu. If you're not seeing the pop-up menu and you're just seeing this little band, click this expand details right here and you get the full menu. Now, this is especially helpful if you're using something like.

xlookup where you have no idea what this does because you've never used it but it'll tell you each of the values that you have to have and then in these bracketed values are optional values and then it'll break down what all of them do down below furthermore once you start typing these in let's do our sum example again if you click that it will then highlight which step in the function that you're on So we're on this one because it's highlighted in the dark green right there. Commas go in between the values. And because it's written like this, value two, dot, This can actually accept as many as you put in here. So I can just put all these with commas in between and it'll keep adding those up. We need to close the parentheses.

The parentheses is what's around all of the arguments in each of the functions. And then it gives us the answer right here. And if I were to change one of these values, that amount changes too. So that's the powerful thing about using functions is it will manipulate or it will give us answers.

on our data if we change or manipulate our data. Okay, here's another one. Average, find the numerical average value in a data set ignoring text.

So that's pretty cool. If we drag this down, this is the notation used for a range instead of just a cell. We've got this little colon in between two values, C2 through C19.

Remember, Reference cells by the column first and the row second. So C2 is right here, all the way down through C19. And again, they do a great job of color coding where that box of range or where that range box is. And then we find that the average amount in there is 1,107.91. If I change a value, then that changes as well.

All right, there's all kinds of functions. In fact, there's over 400. functions. Google Sheets functions.

Let me just pull this up for you. Here's the function list. Look at this.

It's long. There's a ton of them in here. And you can search for your function here.

If you're looking for like finding the max of something, there's several different max functions there. You can also narrow by category. So if you wanted to look up the lookup functions, here's all of the functions that have to do with looking up things in your sheet. There's a lot of them.

All right, but we digress. Back here in our sheet, let's use another basic one called count. This will return the number of numeric values in a data set versus count A, which is the number of values. So if I do this and I go 2 through 11, that'll be 10 values. But then if I say count A and do all of these, that'll give me 30 values.

And I should have done the same exact thing. If I do A2 over there through C11, that'll give me 20 because it's only counting the numeric values in column A and the numeric values in column C. Okay, there's a basic primer on some of these basic functions.

There's way more than we could go over, but you'll find that pretty much anything you want to do with numbers, you can... figure out a function. There's either a function already built for it or you can figure out a combination of functions to make it happen.

Hey, let's do something very practical. Let's do some formulas right here. Let's give ourselves a starting balance of $20,000.

I'm just going to put it up here in H1 and we're going to reference it down here in our balance column. So just like in our functions, if we hit the equals sign, then we can start. typing in things. So I'm going to reference H1 and I can either say H1 and type it in right there, or I can click H1. Either way, we'll select H1 and I'm going to say H1 minus this amount.

So there's my balance for after this $1,700 book purchase. From then on though, all we need to do is take the value right above us. and subtract the value of this line. Now instead of typing that in over and over and over and over, we can drag this down. See this little blue dot in the bottom right corner of the cell?

If we click and drag that, looky there, that's going to drag the same formula down, but it's going to change the cell references. So instead of looking at F2 all the time, right here it's going to look at, or right here it's going to look at F3. then F4, then F5, and similarly it's going to look at the next value in column C. So we can drag this down all the way to the bottom, or we can scroll down, hold shift, and click down at the bottom. So we've got this whole range, and control D is a shortcut that will do that same thing, drag it all the way down.

So if we scroll down here, you can see we're negative 78,000 in the hole. We really need to add some paychecks. And it's done the same thing all the way down to this last row.

Okay, before we get into formatting and more advanced setup options. We're going to go over the if function. Now this might intimidate some of you because it's a little bit more complicated than just adding numbers together, but you're going to use this. I promise you, you will use if and derivatives therein and conditional statements.

What we're going to do in our example is in this column G, I've just labeled it big spender. That's not going to be practical for our spreadsheet in the real world, but it's going to illustrate how to do these if statements. We're going to click.

equals and then type in if we'll select this and it's going to say we need a logical expression and it gives us an example which is helpful down here usually they're helpful if something then we type a value that's true and if not then we type a value that's false so logical expression value if true value if false let me show you an example so let's just say if C2 is greater than $1,000. Then we'll type in big spender in quotes and comma, not much. Okay, so that's saying if the value in C2 is greater than $1,000, we're going to type big spender.

And if it's not greater than C2, we're going to type not much. Now, check this out. Sometimes we'll get this autofill suggestion.

and it'll say, hey, if you want to just auto fill this down, click control enter. And I'm going to say yes, because that's exactly what I want to do. So now for every amount over here in column C, we are typing in big spender or not much. Now, this is a silly example, but it illustrates the power of using an if statement. And we'll be using these more as we build more complicated things to do automation for us.

All right, let's look at some of the other if statements that are available to us in Google Sheets. If we type in equals if, we can see in our little drop down menu all the other functions that are built in that combine with them if. So we have conditions and then something will happen based on this without us having to manually piece together some of these more common functions.

Now if you're not seeing this menu, if nothing's popping up to help you, remember you can just click on formula suggestions in that little blue question mark. So let's use count if right here, a condition count across a range. Another way to select our range, we could click and drag as we've shown, but we could also just type it in. And furthermore, we can select that whole column C if I say C2 where I start, colon C, and then I don't put an end value or an end row.

It's just going to select the entire row. Now, if we hit enter here, we got an error. Why? Well, we didn't put the condition, right? So we need another.

argument here. We need the criteria by which we're going to count. Let's just put 500 and we have to do this in between these quote marks. That's just the syntax that Google Sheets expects for this function.

And here we can see we've got 76 transaction amounts that are greater than $500. Okay, well, a better way to do that. What if we wanted this to be more user-friendly?

Amount to search point. So let's say we just have a cell up here that's saying, hey, change this value right here so that it is whatever we want to count by. Well, now we can go in here instead of hard coding this in, we can type in J1, which is the cell where this value lives.

Right. Only we got a problem. It's saying zero. Well, we followed the directions like we were supposed to.

What's going on? If we're referencing a cell. inside of these criterion, then we can't actually leave that cell reference inside the quote marks. So the syntax gets a little bit more complicated.

We leave the greater than sign because we want it to see that we're using that, but then we have to hit the ampersand and then type in J1. And you can always tell if you're actually referencing a cell when it turns a different color. So we've got this outlined in purple.

the letter and number of the cell reference actually turn purple inside of our little formula bar and we hit enter and we still get that 76 search count. This is more useful because then a user can just come over here and select a different amount, type it in, and it immediately recalculates instead of having to come in here and manually type it out. So there it is.

Now let's get geared up to do some conditional formatting in the next section, followed by some data validation. And all of this together, we're going to have at the end a fully functional, actually working personal budget. Welcome to module three. We're going to start talking about formatting.

And we've done a little bit of that and we'll clean things up a little bit more now. But specifically, I want to talk about conditional formatting. So we're going to have cells be formatted in different ways based on different criteria.

We talked about the if statement and different variations of that in the last section. And this is kind of a play off of that. We're going to have a criteria, a condition for a range of cells, and then we're going to do something to those cells automatically.

So for instance, the easiest version we're going to do first, if a cell is over a certain value, it's going to turn a certain color. If it's under a certain value, another color. And there's color ranges and scales we can work into there too. For you perfectionists out there, and I myself am one, please don't be worried too much about the fact that our sheet looks very basic and very incomplete.

By the end of this course, it will be complete. We're going to start filling in these two columns and then making some better and more advanced calculations on our balance based on all the things that we've learned up to this point in the next two sections. So it's coming. Bear with us. Let's talk about formatting.

All right, I'm going to clean things up just a hair here at the start. And we could do a lot of things manually if we wanted to, or we could just come up here and say, hey, format, convert to a table. And Google Sheets does a great job of guessing the data range and just converting this whole thing to a little bit nicer looking table. Now, we can see that it added these columns over here that we'd don't really want to be part of our budget table.

These were just for our examples. So after we've converted it to a table, we can select this little downward pointing arrow thingy and we can say, hey, adjust table range. And instead of going through column J, let's just cut it off at column F. That way we've got our data for our actual budget here and it cuts off after this balance column. I'm going to show you one other formatting trick here.

If you highlight a column and then you see how our cursor turns into a hand. We can click and drag this column over and now we've got a little bit of space just like that. Just a little separation of space.

I think that looks better. Easier to wrap my head around. Let's go up here to view, show grid lines and select that off. So now we lose those grid lines.

That's a personal taste thing. Some people may want to leave those in. We got our table. Let's highlight everything and make sure everything's still in outfit font because that table actually changes the font.

And again, if you wanted to change the colors of the table itself, we could do that over here. Let's do this nice, I would say, dark plum purple. Now that we've got our table nice and formatted, let's look at the actual conditional formatting. So if you go up here to the format menu and select conditional formatting, It's going to open this sidebar, and let me just get my head out of the way so you can see everything over here.

This is the conditional formatting rules sidebar. And as you can see, it applies to whatever range we specify. And since our cursor was in this A2 cell, it's wanting to apply it just to that range.

Well, let's actually go A2 all the way down through the whole column. And underneath here, we have format rules. We've got general rules about the text right here, whether it's empty or not. The dates right here, if a date is an exact date or before or after some date. If it's a value that's greater than or equal to or less than or between certain numbers.

And then down here at the bottom, we've got a custom formula. So we can actually write custom formulas down here as well. That's outside the scope of what we're doing.

Let's just come up here to the date section. And let's say date is after. And we can put today, tomorrow, yesterday, or an exact date. Well, let's just say, I don't know, 2-1-2024.

So let's scroll down and look. It is highlighting now all of the dates after 2-1-2024, just like we wanted it to. And the formatting style that it's doing that in is right down here. So anything I click, you can watch it over there on the left, adds bold, italics, underlines, strikethrough. We'll change the text color.

to red the background will do this crazy bright green we can do whatever we want to the format here and it applies only to those ranges where this format rule is true so it's like an if statement just built into conditional formatting rules Okay, let's look at another range. Let's do this in the amount range, C2 through C. And now we're looking at the columns with the amounts on them.

Let's go up here and say greater than 500. And now it's applying that same format to anything greater than 500. Let's make it a little bit more easy on the eyes, change that and this and there we go. If we wanted something that was less than, I don't know, $100, then we could change that to highlight in red. And there we go.

And so it highlights those cells. Well, what if we wanted to do several things at once? That's where color scale comes into play. So now we have not a single color, but clicking here, we have a color scale.

And it's got a default value. You can change it like red to green. You can use a custom color scale, whatever you want to do there. We're just going to leave it on this default one. And we're going to say the min point.

So the minimum value is going to be this red. The percentile, the midpoint is going to be 50 percent. And the max point is going to be in this color green. So it's going to go from red to white to green.

And as you can see, as we get closer to the middle of the values, I think. I think these go up to like 2000 is like the top end that I put initially when I made these values. So they get darker green up closer to 2000 and then they get darker red down closer to zero.

And if we change the percentile to like 75, then it's going to take that 75 point to where it goes. Everything from zero to 75 percent of 2000 is going to be this reddish to light red to white. and only the last 25 percentile are going to be green so you're going to see as we scroll up there's more red more pink less green and the inverse is true if we put 25 so it's going to start turning green way down there towards the bottom now most commonly you'll probably just leave this at 50 you can also change it to a specific number or you can leave none as a midpoint that's formatting conditional formatting In this next section, we're going to start getting into some really fun stuff called data validation.

We're going to create drop-down lists. We're going to attach them to other ranges that we made. This is where our categories are going to start finally coming into play.

We made this back in the beginning, and we didn't really do anything with it yet. Well, now we're going to throw it in here using data validation. Stick around for that.

It's up next in Module 4. This video is brought to you by me. Head on over to gotsheet.xyz. This is my free weekly newsletter where I teach you how to get good at spreadsheets. I walk through different project-based lessons. Most of them are bite-sized, so you can walk through an easy example with a video and an article every week.

Hope you'll check it out. GotSheet.xyz Welcome to Module 4, Data Validation. Now, this is one of my favorite things to do inside of a spreadsheet. This is where you've got a little thing that you click in the cell, like a...

a pill-shaped thing or maybe just a down arrow. And when you click it, a drop-down list appears, and then you can select one of those values. Well, we're going to show you how to put that in our spreadsheet, and this is going to make this a lot more functional.

We're going to practically use this for the categories and payment methods. We've got these over here. It's one of the first things we created, but we haven't done anything with them yet. Well, we're about to.

We're going to pull them over here into our budget table. There are a few different ways to create data validation drop-down lists. The first and the classic way is in the data menu, select data validation, and this is going to pop up some data validation rules here.

When we click add rule, it's going to add a rule, and actually I'm going to bring it over here and add a rule to M2 first before I show you the table options. If you're just in a regular range outside of one of our created tables, Then it's going to say, hey, apply to range. And we can say we can modify this. It's just showing the one where we were highlighting. We're going to say M2 to M5.

We have criteria from a bunch of different choices. And we're going to say drop down. And then we're going to, you know, if we want to select colors for these options, we got option one and two. We've got some advanced options we may look at later.

But for now, let's click done. Now, this will allow us to select option one. or option two in any one of these cells. Furthermore, option three, if I do that, then it's gonna say, hey. You can't do that.

We have data validation here. You have to select option one or two. And let me just show you how you can change that. Let's do look at advanced options. So if we open advanced options, we can say show help text, select an option, please.

We can say if the data is invalid, reject it, which is what we just did. We rejected that value. And then we can say display type, and it defaults to chips.

ever since Google added chips, but you can easily change that to just an arrow where the whole cell is shaded or plain text where you can even tell that it is a dropped out list. I'll leave it with chip. I'll click done and let's try typing option three again.

And now it's actually got a custom message where it says select an option, please. That's where we hit that custom message, select an option, please, for the help text. Okay, that's fine and dandy.

That is in a regular cell. Well, what's the difference about tables? Well, in tables, it's going to apply all the way down automatically. There's also a couple other ways you can actually access it. You can go to the drop-down menu here if you right-click a cell in here, or we can go up to the top of the table, and this is how we change the column type.

And drop-down is actually a column type for our table. as well as smart chips, but that's a different topic altogether that looks similar. Let's click drop down, and it's going to take us over here to that data validation rule.

Let me remove my face so you can see everything. I probably blocked stuff a minute ago. We can apply to column.

However, you see how this is grayed out up here? That's because we're in a table. It's simply automatically applying this to the entire category column, and it's stopping at the bottom of our table.

So down here, it doesn't do anything on row 102. The criteria, we still have option one and option two. That's fine. And we can do advanced options.

All that remains the same. But as you can see here, inside of this table, we have less criteria options. We're basically just able to define these values or get them from a range.

And the range is what we want. So we want to get the criteria here from a range. from our categories range, right? So that needs to come from over here.

And we need both the income accounts and expense accounts. So I'm going to click this and I'm going to select all of these and then click OK. And now look at that.

Here in our data validation rules, we have all of these different categories that are available to us for our budget sheet. Now we can go in and change the little color-coded things. I'm not going to do that right now.

We can change the advanced options. I'm not going to do that right now either. As you can see inside of a table, we also don't have that custom help text. We just have those few regular options. So we could select paycheck, auto, etc.

And now we can categorize our transactions. Test. If we test something here in a table, it's automatically going to give us a warning. So it's going to allow the value to remain here, but it's going to say, hey, something's wrong.

It's going to flag it. Let's change that to kids. And that's how we enter data validation inside of our table. OK, let's do the same thing with our payment methods column. Let's select drop down after right clicking one of the cells here.

It's going to pop up this same validation rule. We want to drop down from a range. We want to select that range from our categories table. And let me show you another way to do this.

Because this categories table is a table, we can actually enter categories or equal categories bracket payment methods, which is the name of the column C that we want. And that's going to pull in all of our options and payment methods. And it will add another card. It will add cards not in real time in this menu, but if I go back here to the budget where I've put the drop down list, it will add anything that I add to that payment methods table column over here into our payment methods column of the budget that we're using. The reason you might want to use this table reference instead of just selecting a set list of cells is if we ever for some reason had even more payment methods down here all the way to yet another card.

and extended that table down past the original range, well, that's no problem because we're dynamically still selecting that inside of our dropdown validation rules. So there it is, yet another card still in there. Okay, now in the next video, we're gonna start piecing these things together using conditional statements to make the budget actually do what a budget is supposed to do.

So if I got a paycheck, I don't need to subtract that to my balance. I need to add it to it and so forth. Welcome back. This is module 5 and we're going to get a little advanced. We're going to start combining functions and formulas together and we're going to do three things.

First of all, we're going to flesh out the rest of our budget here. We're going to add a column type so we can designate whether the transaction is an expense or an income automatically. And we're going to do that by using the categories we already set up and combining the match function, the if error function, and the if function.

So we're going to do several things all at once there to do that automatically. Then we're going to set up some filtering. I'm going to show you how to create and use the filter function as well as use what's called a slicer.

This will enable us in this transaction sheet to filter out transactions based on categories or payment amounts. And then finally, we're going to create a new sheet that has a search bar on it. So we'll be able to type in a search term.

And then it'll search through all of our transactions and only return those that match the search term. This is great. And I use this in the real world with my budget because I've got a whole year's worth of transactions in one sheet.

And sometimes I just want to filter things out using that search term. OK, let's get into it. OK, I've filled in all the blanks here that we need.

Just randomized a bunch of categories on the column D as well as the payment methods on column E. The only thing that I left blank on payment methods is when I've got a paycheck over here. That's just an auto draft and I'm just leaving that blank.

So it doesn't really matter. The only thing we're going to be tracking payment methods for are going to be for our expenses. Now, let's throw in a column to the left right here.

We're going to say that this is the type. Now, what we want to do is we want to look at the category and we want to determine if it's paycheck. then we want this to be expense. Okay, so we could do a couple different things. Here would be kind of a hard-coded, more simple version.

We could say if category equals paycheck, then we're going to type in income. And if it doesn't, then we'll type in expense. If we only have one category that's going to be paycheck, then this will be fine. And we can just drag this down. And now anywhere that we've got a paycheck, Here's one down here.

It's going to successfully say that, yes, this is income. And right here is another paycheck, income, income, income. Okay.

So that's one way to do it. What if we have multiple income accounts? And we do, we've got other income.

And so let's just add other income right here. We'll make this like a, I don't know, just give ourselves a bunch of money, right? $4,500. How about that? And we'll change this to other income.

Oops, other income. But it still says expense. That's because we hard-coded this. Okay, well, the way we can get around this, we got to use some other formulas too. So let me go over here to categories, and I'm going to walk through on this sheet how each of these is going to work before we combine them together in one fill swoop.

So let me show you the match function first. Match is going to take a search key and let's say it's in this cell right here. In fact, let me just go ahead and say other income. So we've got something listed there and we're going to say match this in a range. And our range is going to be this category's income accounts.

And it's going to say that it matches it. Well, we also need to specify a search type. This, if you remember, is an optional parameter.

Because it's in these brackets, we don't have to have that there. In fact, let's leave it out. And it gives us the answer two.

Okay, is that meaningful at all? Well, not really, not yet. What we want is to make sure that we find an exact match. If the income accounts are not in order, we want to just make sure that it gives us an exact match.

By default, it's going to find the largest value less than or equal to the search key. We don't want that. We only want exact matches to our search key. So zero finds the exact match when a range is unsorted. You'll find that there are often these optional parameters in some of our more complicated functions.

And for example, here, it's not going to change anything. But let me just show you if we have something else in here like... the word pay, that's still matching it. It's not giving us any different answer because it's finding what it refers to as the largest value less than or equal to the search key. So if we throw a zero in here, then boom, did not find value pay in match evaluation because it ain't there, right?

Okay. But if we write paycheck, then it is there. It's in position one is what the one is indicating. How do we get it so that...

It doesn't give us this nasty error because we don't want that. So what we can do here is we can copy all that, cut it out of the way, and we can use this isError function. And this says checks whether a value is an error. So if I put this in and I wrap it like that, it says true.

So that is an error. Paycheck is not an error. Other income is not an error.

Okay, how is this going to be useful for us? Well, if we see how the logic here works, then we can make an if statement regarding this whole logic. So if I cut all of that out and I say, if, if this value is true, so if there's an error, then we're going to return a value.

So if there's an error, remember, it's not finding this in an income account. So if that's an error. it's not finding an income, then it's an expense account, right?

And if it does find it, it's an income account. So this is how we can check and return automatically whether it's an income or an expense account. Okay, let's put this over into our budget.

So here, instead of all this junk, we are simply going to match E2 And that's not the correct thing that we're going to match. We're going to match D2 to this category's income accounts using an exact match. And if there's an error, we return expense. If not, we return income. Let's click that.

Let's press control enter to autofill all the way down. And then let's come over here and check out all of our accounts where we've got income. So here, right here is a other income and it's showing income.

Here's our paycheck showing income. I think I put like two a month in here. Here's a paycheck right here showing income. And we could just search for all the paychecks.

So they highlight real easily for us. There's one on three, one, there's one on three 15. Let's throw in other income again, or let's just say bonus over here. And we'll do, Oh, let's give ourselves. What?

Ninety eight hundred dollars. How about that? And we have to categorize it because this is what it's checking against. And boom, type income.

OK, let's talk about filtering. We can do these filters in a few different ways in Google Sheets. We actually have filters built right here into the table that we built.

We can filter by color, by condition, by values. We're not going to use that built in menu because what I want to show you are two other things, a filter function. over here in a new sheet in just a moment and then a slicer which is like a drop down menu for filters so first of all let's use the filter function i'm going to create a new sheet i'm going to put on my font that i like i'm going to turn off the grid lines i'm going to zoom in i'm going to rename this filter and then look here i'm going to move this you can move your sheets on the bottom over here to the end and then change the color to i don't know orange how about that Now type in equals filter and we've got the filter function.

All it takes is a range and then a condition and optional other conditions. So you can put multiple conditions in here. So we're going to use a range as our table itself.

And you know what we didn't do earlier? We didn't even name this thing. So let's name our table budget by double clicking in the top left where the title is.

Come back over here to our filter tab. Type it in. And now if we start typing budget, we've got all the categories or all of the columns rather for our table named budget. And we're just going to select the whole thing as our range.

And we want the condition to be, let's type in budget again. And let's filter by category. And we want that category to equal A1 because we're going to type our term right up here in A1. And let's type in auto and show you that, hey, check that out.

It actually filters everything except for the auto transactions out. Now let's make this a little bit more user friendly. I'm going to come up here and we're going to add.

row above and we're going to paste in these column headers make those bold let's just move this over here filter by and Then we've got that selected. Okay. So now anytime I change this It'll give me all the transactions in that category if you remember we can drag this line down so we can scroll down here and then actually keep our Top two rows or three rows.

You can drag this down however far you need. We're going to keep our top two rows so we can see everything. What do we got?

Paychecks. We can look at all those. Other income. We can look at all those. Well, why don't we just make this a dropdown menu though?

Let me show you another way to do that. If you click the at sign, at, dropdown, this is a shortcut for some of the built-in smart components. Another way to get into our data validation rules.

I'll zoom out just a hair here. And now what we want, if we just go over here to drop down from a range, we have a categories table with a, what do we have? Well, I guess we want to use both of these. So instead of defining it ourselves by the table references, we do need to select this range like we did previously. Press OK.

Click done. And now back here on our filter, let's even actually do this. Watch this. We can combine those, merge and center those cells.

And now we've got all of our options for categories just built into a drop-down list. So we don't have to type anything in there. That is filtering using the filter function.

Up next, we're going to look at filtering using a slicer. Welcome back. We just got done with filter using a filter statement. And now let's go back to our budget sheet. and add a slicer.

So I'm going to click anywhere inside of our transactions here in our budget table. I'm going to go up here to data and then down here we've got the add slicer button. So it gives us this little pill shaped guy here and it opens up a slicer drop down over here where we can select what we want to filter by.

So I'm going to select category and If you saw, if you notice this, it's got some weird stuff over here. It's actually pulling in data all the way through column L. So we don't want that. We just want this through column G. And so now I've got these regular columns here to filter by.

I'm going to do the same thing we did in the previous video and filter by categories. And we'll leave this checked. We're not creating pivot tables today.

But if we were creating pivot tables out of our data and we wanted the slicer to also control those dynamically, We leave this checked or uncheck it if we just want to filter our data without affecting those tables. Okay, our slicer can also be customized. So we have fonts that we can choose from here.

Let's put on the font we've been using. Let's change up our background. Let's match it up to that plum color we were using.

There it is right down there. And the text color, we'll keep that as white. Let's make it bold. And now we've got our pill shaped.

slicer right here. You can also drag this over here. Let's just make these a little bit taller, this first row. That way it'll stay up here in the top if I scroll down. And that's all there is to this.

Now we can filter out or on any of these values. So I'm going to clear all these and just select auto business and clothing. Scroll down here, select OK.

And there we go. Now our table has filtered everything except for auto, business, and clothing expenses. This is a nice user-friendly way of being able to manipulate data very easily and intuitively using the slicer.

Okay, next up, we're going to do something a little more complicated that's also very user-friendly, though, by creating a search bar. Welcome back. We've done some filtering. Now let's do some searching.

I'm going to create another new sheet down here. Let's name this search. And in the same way we did last time, let's just put this as a red color to color code everything.

Let's turn off the grid lines and search term. It's going to go here. What we want is for this search term to return all of the values in our budget where we find that in the transaction column.

All right. So let's pop in our. headers right here. And now what I want to happen is anytime I type in something up here, like kids or paycheck, or even just part of the word, I want it to return all of the transactions where we find that search term. And this is how the search term works.

So over here, let's say we've got a couple words like this. And if we start typing in search, the search function searches for text, this text right here, in a string. So this string right here.

And it returns the position that it finds it. So pay starts at the first letter in that string. If I type in check, it starts at the fourth letter.

And if I... type this to J2, then it's not actually found in kids, but like DS is. So that's how search is going to work.

How can we use that for our full return here? We want to wrap that, or we want to put that inside of a filter. So we want to search down a column, a transaction column for this search term, and then filter everything else out because of it. So let's do this. We're going to search for this term and we're going to search in our budget sheet in the transaction column.

And we're going to get an error if we leave it like that, because what we want to do is actually filter that by this condition. So the way filter works is you've got to match up the range sizes. So the budget is X number of rows tall and the search result function.

This budget transaction that it's searching through has to be that same size. And you'll see here, now it's returning all of those where paycheck or where pay is found. Anytime I have kid in here, it's going to return these, these kids transactions. I don't know what else I actually put. Okay, so there's beauty.

Let's type in A. So anything with the letter A in it. And it's case insensitive also.

So let's just type in, see how industrial is capitalized. If I type in IND, it's going to return it even though it's lowercase. I could type in two uppercase and a lowercase. It would give me the same thing.

Paycheck with some weird letters. Case insensitive. So this is super handy.

This has been one of my most popular things to show people how to do and create is creating this search bar. And I've gone into some more detail on a couple other ways that you can make this in Google Sheets. This is not the only way.

But for our purposes here, it's the quickest and most concise way to search this one column. Okay, get ready because we're about to dive into charts and data visualization. Welcome back to Module 6, Charts and Data Visualization. That's right, we're going to make some charts today and hold the front door.

If you're intimidated, don't be. If you've ever pulled up the menu inside of Excel or Sheets, you might be a little bit overwhelmed by all the options for charts. But really, 90% of what you're going to be doing in most use cases, line charts, bar charts, maybe even pie charts.

A lot of people swear against those, but we're actually going to do that first and foremost. Why? Well, the whole reason behind data visualization is to make things easy, to make things simple, to know what's going on inside of a spreadsheet. It's kind of like we did a couple modules ago in the conditional formatting, where we add some color-coded... cells.

In our case, we did like green to red so that we could quickly at a glance see what's going on. Well, we're doing the same thing with charts. First up, we're going to create a simple pie chart to show incomes versus expenses. Very practical, very easy to grasp visually. But when we're in a huge spreadsheet with a bunch of transactions, we can't really look and see what's going on without adding a simple chart.

So let's dive into it. Before the chart, one quick bit of cleanup work. We didn't add a conditional statement up here in our balance column.

The whole purpose of this budget is to keep running balance. Well, one of the purposes. So when we added this type column, we've got this correctly identifying whether or not we have an expense or an income, but we're still just subtracting everything from up here. Also, we've got maybe a bigger problem. We, because of these crazy amounts in column C, because I just made up a bunch of random numbers, We're in the hole like $96,000 by the end of these two months.

So why don't we just give ourself a starting balance with an extra zero? So that looks a little bit nicer. How about that? Okay, I feel better.

What about you? Now, let's take care of the more glaring issue. And let's type in if this equals income. So if it's an income statement, then what do we want to have happen?

Well, Here's the value if true. We want to take our initial starting value and we want to add whatever amount of this current transaction is. And we actually can't drag that down or anything because we're using this initial value for that first line.

But now for everything else. We're using the value right above as the starting balance. And we're going to go if same thing. If this equals income, well, then we're going to take our previous balance and we're going to add to it whatever this line is.

And if it's not income, then it must be expense. So we're going to take this and we're going to subtract that line. OK, and I'm going to just highlight all these.

And my shortcut for this is control D. That drags the formula all the way down. You can double check this at the end on my very last row.

And it is successfully using this if statement to check whether this is an income or an expense. Let's go up here to like this bonus amount we have written in. And it is indeed adding the $9,800 to this $133,000.

Wow. What if we had that in our bank account? Okay. So a little bit of cleanup work.

Let's get into the charts now. Okay. Let's make a chart.

We are inside of our table here and if we select the insert menu we get a zillion different things we can insert including look another place where they've got drop down menus selected. So they love their their drop down list in Google Sheets. What we want is up here the chart and it's going to open this chart editor menu.

Let me get my face out of the way because we got another sidebar to look at here and we have several different things to select here in the setup part of it. And then we have other options here in the customization where we can select things like the format of our chart. OK, let's do setup first. And the first thing we want to do is change this.

Again, there's a ton of options in here, but you're really not going to use many of them. Column, bar, line, pie. You know, we're going to do pie today. Those are the most popular that you can use for most things, because what we're worried about is very simply and concisely displaying something useful.

And this right here. is definitely not useful. What is this kaleidoscope?

Oh my goodness. So here's what's going on. Moving down the sidebar, it's trying to guess the data range we want to use, and it's kind of right, but ultimately wrong.

It's selecting columns A, C, and G. Let's just bring in all the columns, so we can type G101 right here, and now we'll have all of our columns available in our full table right here from date to balance, and Right row. What happens if your sidebar goes away? Well, two things. You can click this little guy and select edit chart, or you can just double click in the top of the chart and it'll open the editor.

Okay, so now we've got for label options, all of the different headers from those top, that top row in our table, where we can select one of these to look at. So if we select category, it'll bring up the categories. If we select amount.

It'll bring up all the amounts. All of these are useless for us right now. What we want is type because that's where we hit income or expense in column F, right?

So we just want to compare how much income we have versus expense. And here, what's going on? It's still taking every single one individually.

So we click this aggregate button and now we have everything aggregated. So we've got expenses in the blue or excuse me, income in the blue and expenses in the red, which. Uh, incidentally, this is not good. Hopefully it's the other way around more income than expense, but we're using crazy dummy data.

So bear with us. Uh, immediately now we are brought over to the customize tab to customize our pie slices. We'll go ahead and do that since we're here. Let's just change the color of this. I don't know.

We'll do this kind of muted red distance from the center. If we want it to pop out, we can pop it out like that. I don't think that looks good at all. So I'm going to use zero. And then we're going to change income in the same way.

There's not tons of customization that we can do, as you can see here. Actually, let's put a green. We'll use green for income.

And same thing, we could pop that out if we want. I don't know, let's leave it like that. Looks like Pac-Man. That's pretty cool. Let's go through all the options here in the customize menu.

The first one is chart style. So we could create a dark background if we wanted. And let's just do that. Got a dark background. We can change the fonts, which come on, Google.

Why are these the only fonts that I can use in a chart? This is silly, but this is what we're stuck with. For whatever reason, we don't have access to all of them.

We've just got these basic ones. I don't understand it. Chart border color.

Let's just match that to our background. If you click maximize, it'll do exactly what it says and just make it giant. 3D makes it 3D. I don't like either of those.

So I'm going to leave it just like it is. The next area is pie chart itself. We can make it a donut hole if we want. I think that looks kind of silly.

Border color, another spot for that. slice label we can label this and we can put the label right there in the slice or we could put the value in the slice which I like to do. Curiously the format is not great here so let's see if I can put a dollar sign.

I don't know why it's got a zillion zeros over here that's that's weird. Formatting leaves something to be desired here and now that we've got the value which we could have done percentage and value or just percent. Now we can change the text on that. Let's just put that to white also. Title and chart subtitle.

If we wanted to change this type of transaction, we could type something there for the title. Change that to white. Subtitle, income versus expenses.

Unnecessary for our purposes here, but just showing you all the options and what it does. And then down here, we've got the legend itself. So we could change the position of the legend if we wanted to put it over here on the right. Let's change the color to white so we can see those a little bit better.

Put it on the top if we want. Doesn't look great. Let's leave it on the bottom.

How about that? And those are the customization options. This is how we customize a chart inside of Google Sheets.

Now, in the next section, we're going to create a new sheet down on the bottom for our visualization. So we're going to throw this over there. And then we're going to create a couple more charts to show you a couple other styles and things that might be useful for our budget.

Okay, let's do some more charting. So we got this guy here. It's in the way.

Let's copy it. Click in here. We can select copy. Let's create a new sheet. We'll rename it charts.

We'll grab it and slide it down here to the end. We'll even change the color just so we continue to do the same type of style things down there. And then we paste in our chart here.

and we can edit it as we see fit over here on its own data visualization page. I'm going to select show grid lines off so those are going and now let me show you another thing you can do. You can actually move this to its own sheet and it becomes a sheet like a full chart sheet.

This might be useful for you in some circumstances. You can still edit it here pops up the same side menu but you got no graph, no grid line, no nothing except for the chart itself. You can also download it as an image or a PDF, or you can even publish it to the web and have a link for it, changing some things here to where you can make it interactive, even publish it, or you can embed it on your Web page. We don't want any of that, though. And in fact, let's come over here.

We'll select copy. I'll leave that there just so it's still there for you. Paste it here. So now here's going to be where our data visualization lives. OK, we are inserting a new chart, though.

We want to make a bar chart, excuse me, a column chart this time. And we want this to be vertical and we want to show the amounts of money that we've spent in the different categories. So it doesn't know what on earth we're doing because we're on our own page here.

We're not in data or anything. So we click this select data range. It's got some suggestions or we can go over to our other page and select what we want.

But this is the one we wanted, the whole budget deal right there. All right, now we've got different and more options than the pie chart. We've got x-axis and then we've got series.

Let us remove all series just to start from scratch. It throws a bunch of stuff in there that's usually wrong when you start. And for our x-axis, we want to be looking at the categories. We want to aggregate those. And now this is close to what we want to see.

However, we have weird amounts, right? 0, 5, 10, 15, 25. Down here, we've got add series. So the series we want, this is the amount. Okay, so this looks more like it. Now we're in the dollar amounts.

Now it's taking those amounts, summing them up. You could change this to average, to minimum, to the count, however many transactions there were. And it looks like that this is what it was doing out of the gate.

It was simply counting how many paychecks. How many auto transactions? How many eating out transactions?

So forth and so on. So we want the series to be not a count, rather a sum. And we want the amounts to be visible on here too. So let's do some customization.

I'm going to go up here. Let's just go top to bottom again, shall we? Let's type in categories because that's what this is.

Let's change this to white. I do want to go ahead and change the background. I like this dark theme we're going with.

Change the border to match. We'll go with Y just so we keep some consistency here. All right. And we got some more formatting work to do.

Chart title, subtitle. We don't really need access titles, but if we wanted to, we could do it like this. And here's how they would show up.

I put that on the wrong thing. That should be amounts. But again, this is not really necessary. And in fact, I'm going to delete it now.

Going down to the series. So we got amount, fill color. I do want to change this.

Let's just make it orange. Line color, line type. We can change all sorts of things here.

And if we put that we can on yellow or something, we can see what that is. And that's actually this outline, the line around. And I'm going to change it to the same color.

Line type. I'm not going to mess with any of that. Axis, left axis, aggregate type, some format data point.

We don't need anything else there except. Here's what we need to do to turn on data labels. So data labels are on. I'm going to make this like a career. Turn that white.

The font size I'm going to take way down. We click over here. It's the vertical axis.

Text color needs to be white. And then the horizontal text color needs to be white as well. Now we can read everything, right? Check that out. There's our categories.

I'm going to shrink that down, make it the same height as our other chart right there. Voila. Okay, so now we've got two pretty useful at a glance data visualizations for our budget.

We can tell exactly how much income versus expenses and see that, wow, we need to do something different here because we're spending a ton of money. And then we can see where we're spending that at a glance. And actually, I can just move this over this way. So that becomes a little bit easier to read everything. And we can see that, okay, miscellaneous.

We need to do something about these miscellaneous expenses as well as entertainment. We are really entertaining ourself to death here. But again, these are fake numbers.

I just put them in here for fun very quickly. Now, the final chart we're going to do below these is going to be a line chart that shows our spending over time. All right, one more chart, shall we? Let's do a balance over time charts where we can see over time where our balance is going. Let's select insert chart.

This is going to be a smooth line chart. And in case the data range is not here under suggested, let's just say we come over here and then we select budget A1 through G. There we go. G 101. It's another way to select all the proper data. And I'm going to pop this underneath our other charts and then let's get to work.

We've got the data range correct. We got three series on here, which we don't want. Let's remove all series.

Let's change our X axis to the date because this is going to track over time along the X axis. And then let's add the balance as our series. And here we go.

In its simplest form, this is the balance over time of our bank account. So you can see very little bitty bits that goes up. But on the whole, it's going way down because I think we spent, what, $89,000 to our $26,000 in income.

So we went down over time. That's not the trend line we'd like to see. But let's make this chart look a little bit better so that it blends in with the rest of what we're doing like it should. If we want to maximize this one, I think I will maximize this one.

That way it just takes up the whole space. Let's give it a title. Balance over.

time. And we'll put that in wide font like we've been using. And now we just need to clean up the colors so we can read things. Series.

Let's make this line color red because it's declining rapidly. And oh, my head's in the way. Sorry about that. And we can actually change the line type if we want to do this like a dash line or weird dashes like that. I don't know.

It lets you do this dotted line thing. Change the thickness. Sure. It'll look better as a solid line if we do that.

It's way too thick. Okay. You could go into the weeds here with all this. We won't go further than we need to. Point size.

Do we need points? No, because that's going to have a zillion points on there. Let's change the point size to none on there.

Axis is on the left. Format data point. Now, if we put data labels on this time, it's not going to work because there's so many data labels.

It's giving the balance at every single day. The trend line, we could just put that in if we wanted and change the type, you know. Any way you do this one, it's going to be bad because it's going way down.

We don't want that, though. We want it just like it is. We've just got the horizontal and vertical axes.

We're going to change those so that we can read them by putting the text color on white for both of them. And I think that that's about it. Here's grid lines. We haven't done these before.

If you wanted to add minor grid lines and change the color of those for some reason, you can do that here. Could make them kind of faintly colored like this. Major ticks, that's along the bottom.

I'll make it so you can see it in this yellow down here. You can barely see that. We don't want those. And then minor ticks, same thing on the bottom. We don't need any of that.

In fact, we don't need really anything. Let's turn all those off. That looks better.

And the final thing that I'll do is I'll select a cell back here and I'll just control A to select all. Then I'm going to go up here and make that our same. color so that it blends nicely in. Okay, so we got our little dark mode sheet of charts here.

One more thing, I'm going to select all these cells and just delete those rows. That way we can't scroll down further than we need to. And over here, we can't scroll right further than we need to. So it's all contained just right here in this viewable area. Stick around, there is module seven coming up where we're going to do a little sneak.

peek at some more advanced things using Google Apps Script. Now, this is for the geeks out there for sure, but also it's extremely powerful if you learn just a little bit. This is where we can use programming to actually code in things that we couldn't do natively in our spreadsheet.

So we can do things like add events to our Google Calendar. We can email people. We can trigger things to happen based on certain conditions in our spreadsheet.

It takes automation to another level. It's very fun to use. I enjoy teaching the basics of it as well. That course will be coming up in another course offering, but I'm going to give you a sneak peek of it in module seven next. Again, check that out.

I hope you will. If not, thanks so much for sticking around through this course. Hope you've learned some useful things.

If you wouldn't mind to leave a testimonial, I would be eternally grateful for that. It helps me get the word out, grow my channels. Thanks a bunch. Have a great one. and talk to you in the next one.

Welcome to module seven. This is the bonus module. This is where we preview Google Apps Script, which is a programming language built inside of Google Sheets.

It's a lot of fun to use, I gotta tell you. You can do a whole lot of stuff that's just not possible otherwise inside of our spreadsheet. Microsoft Excel's version is called VBA.

It's a visual basic for applications. Both of these allow our spreadsheets to be extra powerful, more productive. automate a ton of neat stuff. If you hear nothing else here, or if you sign off right now, go over and make sure you're signed up for my absolutely free newsletter called Got Sheet.

Links are all over the place in the description below and stuff. It's gotsheet.xyz, completely free. There you'll be able to get my weekly tips and tricks.

I'll shoot out all the videos that I do. I have free videos available also on a weekly basis on YouTube. And I've even got some Apps Script stuff up there right now that you can go check out. I've done tons of projects with Apps Script.

You'll also be the first to know when the next course is released, if it has not been released yet, because I'll send that out to my newsletter first. So you'll have first dibs on the Apps Script course. Okay, enough of the prelude.

Let's get into a spreadsheet again, back into our budget sheet, and let's show you what we're doing. Let's talk about what Apps Script is. Up here in the extensions menu, we've got several things that we've never even looked at, including some custom add-ons that I have put in this spreadsheet. But what you will see for sure are add-ons, macros, and Apps Script. Click it and let's go in.

Let's go into the matrix here. So this opens up an untitled project and we can name this whatever we want. Sample code.

And then it's got a function, my function with parentheses. curly braces and lines and what the heck. Okay, this is a code editor. Don't be intimidated.

Over here, we've got files and we can add files. We've got libraries that we can add and we've got services that we can add. Over here, we've got an editor, which is where we currently are, project history, which will show us over in the sidebar what we've actually done and we can revert back to previous versions if we screw something up.

We've got triggers. So these are things that we can add that based on events, we can trigger certain events to happen or certain code to run. Super useful.

And then we've got project settings. So here's my settings. Let's go back to the editor.

What is this? Well, this is how we write code and take advantage of a lot of built-in functionality way above and beyond the built-in functions to Google Sheets. We're going to do code here in just a second.

For right now, let's add a new file. Let's make it a script file. Let's call it macros.gs. I knew not to do that and I did it anyway. We don't have to add the.gs.

It does that for us. So here is macros.gs, and I'm going to say function. This just defines a function.

Let's just say amen. Inside parentheses, we're going to put input. This is a parameter that it is going to accept in our spreadsheet. We're going to do curly braces, and now we traditionally put the next stuff on a new line.

Anything that we want to have happen is going to occur inside those curly braces. So this just... holds all the code, all the instructions, if you will, for our custom function. And what we're going to do, we're not going to do anything fancy.

We're just going to return input plus is awesome. And I think you might see what this is going to do. We'll even put an emoji there.

You can do control S to save this. And that little orange dot next to the macros GS goes away. See if it's orange, that means there's unsaved work here.

And now we've created a custom function. You remember functions are what we type into our Google Sheets, right? So we write sum, that's a function. If we've used that a bunch in this course. Well, now we have access to the Amen function.

And we don't have helper text to go along with it. I'll show you how to get that in just a second. But let's just put E1 over here.

And hey, it's awesome. Well, what if I type in something in E1? Hey, Amen is awesome.

So if we drag this down and put other awesome things in here, what else is awesome? Oh, East of Eden. That was a great book. What else is awesome?

Oh, this is a good one. Hampshire. It's hard to spell that.

Hotel New Hampshire. That was a great book. Give me something else that's awesome. Something from recent years. All right, that was an awesome movie.

We just saw that the other day. And look, we've got custom functions that actually print out something based on a cell in here. Here's a little bonus tip for you.

There's another way to do that. In the data window, we can come down here to named functions. And we can actually add a new function right here.

And we'll call this Amen2. Adds is awesome to any text. Argument placeholders.

So this is going to be the placeholders. And we're just going to put cell formula definition. Right.

So this is going to. So this is going to be cell plus is awesome. All right. So now if we define it like this. Oh, my head's in the way, isn't it?

Now we click next and we can add additional details. Argument description. The cell where the text you want to be augmented. lives.

That's a terrible description, but okay. Argument example, cell, a cell of text. So this is the helper text that will now create this.

All right, so now let's do in B2, let's say amen two, and now it's a named function inside of our spreadsheet, and we've got the helper text and everything. Cell of text. Okay, so I'm going to select amen over there, and then comma. Oh, that was it. So boom.

Oh, check this out. So let's edit this. So it is seeing this plus sign and saying we can't do that in our code. We can do that. But in our formula here, we have to use the ampersand sign.

Now we're going to update that. And there we go. Check that out. I'm glad I ran into that error.

So right here, this is code and this lives in our macros GS sample code. And so this code syntax and code editors, we can append things or concatenate things with the plus operator. However, in Google Sheets, the spreadsheet where this named function lives, you actually can't do that in the same way.

So we had to use the old ampersand trick here. Now we'll get into a little bit more complicated code coming up next. Okay, now into the really cool stuff.

We're going to create our own custom menu up here. and then it's going to run our own custom code in our spreadsheet. Sound pretty cool? Yeah, it's because it is.

So first thing we're going to do is we're going to use this special function called on open. So that literally means when we open our spreadsheet, we're going to do whatever we put in between these curly braces. And the first thing we're going to put is we're going to grab the UI.

So these are all built in methods. You know how we have the built in functions to our Google Sheets, like sum, if, count if, all that. Well, inside Google Apps Script, And I'll leave links to the documentation. It is massive. There are built-in methods that do things.

So inside spreadsheet app.getUI, we actually have this create menu method. So we're going to define our menu by saying we want the menu up there to be Amen. And we're just going to go with the narcissistic theme like we've been doing. Then we're going to add an item to it called dark mode. And the item dark mode is going to run this code.

this function right here. So we're defining two things, the text in the menu, and then the function that it runs. And then we're going to actually add that to the UI.

So we have to have this part in order to put it up in the user interface. You'll notice that I'm using dot notation. So if I wanted, I could have all of this just on one continuous line up here.

But it's kind of common convention to break it up on new lines when you do new methods, just for cleanliness. ease of readability if you're coming back in here later and looking at the code or sharing it with someone to figure out what the heck did this person do when they built this. So it just looks better and it's easier to read and edit if we leave it on separate lines.

Now we need to deal with this dark mode function. We're calling this function when we click it it needs to do something. So what it's going to do we define in another function and we title that dark mode.

We're going to do the same type of notation. We're going to say spreadsheet app dot get active. So that's grabbing the active spreadsheet that we have open.

We're going to get the range and we can do this in a few different ways. We can define the range based on this right here, just A1 through Z1000. This was an easy way to do it. We can put a named range in here by using another similar method.

But for this purpose, we're just all you need to know is we're getting a range. So A1 through Z1000. And then. Dot set background. This does what you would think it would.

It's going to set the background to this color code. That's a dark color code. And we're going to set the font color to this color code, which is white.

Okay. If I click save, then we're going to go back over here to our personal budget and we don't see anything, do we? Well, that's because it's on open the very first thing, this function on open.

So let's refresh this so that it reopens. And only then will it trigger that and check it out. There's our custom menu and there's our custom function.

Now, when we run this the first time, this should not alarm you. Authorization required. There's a script attached.

It's just warning us that, hey, you're running code. Are you sure you know what you're doing? And yes, we know what we're doing. So we click OK.

And it's going to want to authorize this based on one of your Google accounts. It's going to try to scare us again. The app is requesting access to sensitive info.

Advanced, you have to click right over here and say advance. It's going to give you the big option back to safety every time. Continue only if you understand the risks and trust the developer.

So it's going to show you the developer behind the code. This is us. We trust us. Go to code.

And it's going to say unsafe. All that is totally normal. When we create these custom app scripts. It's always going to do this because it's using the built-in methods behind the scenes that could be used maliciously if you were in here doing things that you don't need to be doing.

This again tells us, hey, this sample code will see, edit, create, delete all your Google Sheets spreadsheets. Now, are we deleting all of our spreadsheets by running this? No, it's just telling us that by accessing these things and allowing the permission of the code to access it, then...

These things would be possible. There are code methods that delete things. We're not doing any of that today. All we're doing is some good formatting. So we're going to allow this.

Now, after all that click through, if we come back up here, we have to run it again. It's going to say running script and then finished and check it out. We have just done A1 through Z1000 in a dark background with a light white text.

Pretty cool. I've got another one of these that I have several different color schemes like papyrus and synth wave and everything and created different combinations to just one click change the color format for all of the cells. Hope this has been a good little sneak peek at what you can do with Apps Script. This, trust me, only scratches the surface.

Check out my YouTube channel. I have made some pretty complicated projects in there. using calendar events, using emails and automation.

If you want another further taste of this, make sure that you are signed up for my free email newsletter at gotsheet.xyz. It's dot sheet dot xyz. I couldn't get the dot com, but go over there, grab that. I do free info, free newsletters and free videos all the time on a weekly basis there. And you'll be the first to know about the Apps Script course when it's released.

Anyway, I hope you have a great one. Thanks again, and we'll see you on the next one.