Your First Formula

Beginner

About this Tutorial

Learn how to use Formulas in Google Sheets. Use the Equal sign "=" to unlock a magical portal of over 500 functions and formulas. Learn one cool trick that even experts might not know about formulas.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

CONCATENATE() TRANSPOSE()

Video Transcript

<div>00:01 All right, welcome. It is your first formula . I'm so excited that you've chosen better sheets to learn more about formulas that you're getting ready, getting geared up to go deep, deep into Google Sheets. 00:13 We are gonna go into some stuff here.

We're gonna get into some errors you might encounter to get you over some challenging parts you might encounter. 00:22 We're going to even show you a little bit of an expert trick that not many people know actually over at the colon, at the not the colon, oh my god, at the curly brackets. 00:31 And then we're gonna go into at least one very useful formula .

But first off, let me share with you how to unlock all formulas. 00:44 So when you're in a Google sheet and you go to something like a one and you just double click in there and hit the equal sign, just hitting the equal sign unlocks over 500 formulas. 00:55 It might not look like it because it's a little tiny cursor that's blinking and it has a little underlying flat thing there. 01:03 What this is showing you is that you can start typing anything.

You can actually start typing letters like A or B, or C or D, and it will give you a list. 01:12 And unfortunately, Google Sheets is putting these formulas in alphabetical order, not in how useful they are. So one of the most useful formulas is the if formula , if that's all we need. 01:29 And then what we're gonna do when we use the equal sign and use a formula is we have a parenthesis and we're gonna have an open parenthesis and a closed parenthesis. 01:37 In some other videos I talk about how to find out and about these formulas.

You can go to better sheets.co, better sheets.co/formulas. 01:49 And here I have an entire directory of every single Google Sheet formula , and it has included video tutorials on e on anyone that you're probably interested in. 01:58 Something like the and function or formula . You have a video right here. Alright, that's the equal sign. The equal sign will unlock everything for you, all of the formulas. 02:10 But before we get into formulas, I wanna share with you that you can do math, you can type in here equals two, and then the plus sign two. 02:19 And here you can do math.

This is very basic about basic usage of Google Sheets. But you can do all kinds of math. 02:28 You can do addition multiplying with the asterisks. You can do division two divided by two. And in addition to math of numbers, you can actually also do text. 02:41 So you can literally do in quotes some text and it'll be green. It'll show you that it's in quotes. You have to exit the quote. 02:50 Meaning, meaning you have a first quote and a second quote. And you can actually combine these. If you use the plus sign some other text It, you're gonna get a value error . 03:01 Okay?

So when you see the value error , you can just highlight hover over the cell and it might tell you some hint as to what you can do. 03:11 So it tells you right here, function add parameter one, which don't worry about that expects number values . I think it's saying that this plus sign is trying to give you like, oh, it thinking it's a addition. 03:24 If we change that plus sign to an ampersand little curly ampersand, what that is now doing is combining that text. 03:33 And so we see here we have actually let's include this there we go. Some we're combining this.

If we need a space, we'll put a space. 03:42 We can add a space here in quotes and an and, and we have text. We have now written text. Now this sounds like a really long way to write some texts inside of a of a cell, right? 03:57 But what we can do, and we will do later, is we'll refer to other cells with text in them and we'll combine them. 04:03 That'll be pretty cool. But first I want to share with you some more errors you might come in that you might run into. 04:10 The first one is name.

If you have this, you might have something like this where you've literally tried to write a formula name, but it's saying that the name is wrong. 04:20 You've gotten the name wrong. So if we go, if we might get name as well, because it's saying this formula , it's, it's an unknown function if sn right what I've used very, pretty much for 10 years and I don't continue and, and, and I don't stop using it, is when I type in, if I use, I use these help all the time. 04:45 There is no way on earth you're gonna memorize 500 formulas nor use 500 formulas.

You'll probably use like 20 to 50 of them total. 04:55 But what this allows me to do, I keep this help on and it allows me to see more information about the formula that shows me the, the name of it. 05:04 I can select it instead of trying to type it out all the time. Sometimes there's something like concatenate , which is just combining joining together, putting a pending strings together. 05:16 And I can never remember how to spell it. I have to literally sound it out.

So don't worry about actually remembering the names of all the formulas you can just type in equals if or can cat con cat Nate and select it. 05:33 You don't have to type it out. So that's one way to get faster at sheets is if you're just selecting them though, honestly, this is really one of the fastest way to use Google Sheets. 05:43 And you're gonna get around ever seeing that name error because you'll always get it right.

The other error might be formula , parse error . 05:50 This happens if when in the middle of writing a formula equals, let's say if We'll have, I don't know, something like this. 06:00 Let's see if that's also name unknown range show. We'll do a one and we have semi, maybe we'll do something like this, right? 06:11 We get an error formula , parse error .

What this is telling you is that you have written the syntax of the formula , not the infor. 06:18 Most of the time the information in it is correct, like you've selected, you've referred to the correct cell, you have a correct string , but the actual syntax , the way that you've constructed the inside of that formula , so you got the name right you got the information probably right, but it can't really do anything. 06:34 The formula can't really function because the syntax is wrong.

Again, one of the easiest things to do is to keep this help documentation available to you inside of Google Sheets. 06:47 If you don't have that and you're like, I can't read it in here, maybe you have some other information you can go to better sheets.co/formulas. 06:55 And each of these has the syntax already in it. So we can go to like if, let's go find if, there we go. 07:05 Right here on the left side will be the syntax , how to use it in sheets.

It'll tell you the logical expression that needs to be either true or false, a comma, and then value of true. 07:15 Now, if you might be in a different country so I have like a list of locales, some in the syntax of it, it might need a semicolon. 07:27 And then there's others. There's other types of formulas, something like sparkline , which the data , it looks pretty simple, data and options, but what it needs to have is this syntax of the curly brackets here. 07:45 And that's very particular spark line. How do I remember this? I don't necessarily actually remember it.

I keep this help documentation available to me all the time. 07:55 This makes it faster. You don't need to be efficient If you memorize it all, don't memorize. In fact, don't memorize it. 08:01 Just know where the documentation and the help is if you wish to get it. All right, back to how to use formulas. 08:08 We're gonna use the equal sign again, and any time we use the equal sign, we can use text. We can do math, but we can also refer to other cells . 08:16 So just like I said, we're gonna, I'm gonna show you how to do some text.

Cool stuff with text is we can reference this B column or B one. 08:26 We can then and put a space and, and refer to this. So now we've combined text inside of a six and we've used the ampersand. 08:37 And now anytime we replace B one with something else, like name of equals that will change in a six. This reference is like probably I would say the reason Google sheets exist. 08:53 It's, it's the reason spreadsheets exist,  On computers when you're doing calculations by hand and, and like 200 years ago, right? 09:01 We could not do this, we could refer to other cells .

We could maybe make a little notation of this is from here, but this is hard coded in here. 09:10 This is when we reference a B one. It will give you whatever is in B one. It will give you a number . 09:17 It will give you if this could also refer to another cell like this. So we can have our cells reference other cells . 09:26 And this powers up forecasting bookkeeping. It really is a powerful tool. This is literally the reason that spreadsheets online and on computers exist. 09:40 We can also with a colon and curly brackets, get an entire series of cells . So we can do equals.

Now this curly bracket is a bit of a trick. 09:51 This is an expert trick that you're learning right now as a beginner. Put put brackets around the curly brackets specifically gets you an entire array, which is what's called a series of cells in either a row, a row across or a column. 10:07 And you can get the entire column .

If you wanna do B to B, something like this, put it actually up in a one. 10:13 If you get a reference error here, it might say array result was not expanded because it would overwrite data in a two. 10:19 This is literally telling you there is something in a two. We can't delete it and it won't delete it unless you do, if you delete it. 10:27 Now this works, so that's another error you might run into. So the curly bracket's pretty cool, right?

You don't have to get the entire column though. 10:35 Maybe we only want B one to B nine and we're going to use what's called a one notation , meaning the column first, then the number of the row, so the column, then the number row colon, the last cell that we want. 10:52 And it doesn't look like it. There's any change, but now we have it. And now this curl bracket's really is an expert trick because it's the same as using another. 11:03 Another form, a formula called array formula . There we go.

That's the same thing, but we don't have to type out the entire array formula . 11:12 We can just use the curly bracket. See there exactly the same. Let me show you one more cool trick, right? 11:22 I hope you can stick around for a one more minute. And I wanna show you one very useful formula here. 11:31 And why I like to use this is say we want all of these in a, this column to actually be across a row, maybe as headers on another sheet. 11:42 So we're gonna type in equals transpose.

And now we can put an array or a range , similar things we're going to click once we have this, I don't know if You can, can really see it, but there's like a little under underlying little gray underline. 11:57 Once we have that, we can use our mouse and we can select a range . We can click and drag the entire range . 12:04 Then we can end with type on the keyboard and parenthesis. To end this, click enter.

This transposed now has turned our column into a row, and now we have headers all here. 12:18 So say you have this information somewhere here and you wanna type it out on another page. You don't have to type it out. 12:24 It's now here. And this formula will automatically update if we change anything. So if we add some exclamation points here, they are added there exactly where they need to be. 12:38 So we don't have to edit that again.

That's what what's really cool about spreadsheets and formulas and referencing other cells . 12:45 This is like really unlocking the magical powers of spreadsheets. And I'm hoping that you will learn more will stay with me over here on Better sheets, better sheets, dot co check out more. 12:58 I'm, I'm hoping to take you from a beginner to an intermediate.

Or if you're an intermediate and you've been using an intermediate user of Google Sheets and you've been using Google Sheets for a few years, I hope you learn something really cool. 13:09 Maybe that curly bracket's trick or the transposed formula . Comment down below. Let me know if you learned anything here and keep watching. 13:17 Bye.</div>