Your First Formula in Google Sheets

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.

Here’s what we are going to go into:

• Some errors you might encounter to get you over some challenging parts
• Show you a little bit of an expert trick that not many people know about the curly brackets
• Go into at least one very useful formula

First off, let me share with you how to unlock all formulas.

When you're in a Google sheet and you go to something like A1, just double click in there and hit the equal sign.

This unlocks over 500 formulas. 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.

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.

Unfortunately, Google Sheets is putting these formulas in alphabetical order, not in how useful they are.

One of the most useful formulas is the IF formula.

Then what we're going to do when we use the equal sign and use a formula is we have a parenthesis – an open parenthesis and a closed parenthesis.

In some other videos I talk about how to find out in about these formulas. You can go to the link below, which takes you to the website’s Formula section.

I have an entire directory of every single Google Sheet formula, and it has included video tutorials on anything that you're probably interested in. Something like the AND() function or formula.

All right. That's the equal sign. The equal sign will unlock everything for you, all of the formulas. But before we get into formulas, I want to share with you that you can do math. You can type this in A2: =2+2

This is very basic usage of Google Sheets, but you can do all kinds of math: addition, multiplication, division.

In addition to math of numbers, you can actually also do text. You can literally type in ="some text" and it’ll be in green.

It'll show you that it's in quotes. You have to exit the quote, meaning you have a first quote and a second quote.

You can actually combine these: ="some text"+"some other text"
If you use the plus sign some other text it, you're going to get a value error.

When you see the value error, you can just hover over the cell and it might tell you some hint as to what you can do.

It's saying that this plus sign is thinking it's addition.

If we change that plus sign to an ampersand, what that is now doing is combining that text.

If we need a space, we'll put a space. We can add a space here in quotes and an ampersand, and we have text. We have now written text.

This sounds like a really. long way to write some texts inside of a cell. What we will do later is we'll refer to other cells with text in them and we'll combine them. That'll be pretty cool.

I want to share with you some more errors you might run into.
Error: Name

If you have this, it means you've literally tried to write a formula name, but it's saying that the name is wrong.

For example: If I’ve typed in =ifsna(), I’ll get that #NAME? error. It's saying this formula is an unknown function

What I've used pretty much for 10 years, and I don't stop using it, is when I type in IF, I use, I use these help all the time.

There is no way on earth you're going to memorize 500 formulas nor use 500 formulas. But what this allows me to do is that I keep this help on and it allows me to see more information about the formula that shows me the name of it. I can select it instead of trying to type it out all the time.

Sometimes there's something like concatenate, which is just putting strings together, and I can never remember how to spell it.
So don't worry about actually remembering the names of all the formulas. You can just type in =IF or =conc and select it.

You don't have to type it out, and that's one way to get faster at sheets is if you're just selecting them.

The other error might be formula parse error.

What this is telling you is that you have written the syntax of the formula, not the information.

Most of the time, the information in it is correct. You got the name right. You got the information probably right, but it can't really do anything. 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.

If you don't have that and think, “I can't read it in here.”, maybe you have some other information. You can go to bettersheets.co/formulas, and each of these has the syntax already in it.

On the left side will be “How To Use in Sheets.” It'll tell you the logical expression that needs to be either true or false, a comma, and then value of true.

Now, if you might be in a different country, I have a list of locales. Some in the syntax of it might need a semicolon.

There are other types of formulas.

Something like Spark Line, where the data looks pretty simple: data and options. But what it needs to have is this syntax of the curly brackets, 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. This makes it faster. You don't need to be efficient. Don't memorize. Just know where the documentation and the help is if you wish to get it.

All right. Back to how to use formulas.

We're going to 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. I'm going to show you how to do some text, some cool stuff with text.

We can reference B1 and refer to “Math” in B2 with this: =B1&” “&B2

So now we've combined text inside of A6 and we've used the ampersand.

Now anytime we replace B1 with something else, like “Name of Equals”, that will change in A6.

We could refer to other cells. So we can have our cells reference other cells, and this powers up forecasting bookkeeping. It really is a powerful tool. This is literally the reason that spreadsheets online and on computers exist.

We can also, with a colon and curly brackets, get an entire series of cells. Now, this curly bracket is a bit of a trick. This is an expert trick that you're learning right now.

Putting brackets around the curly brackets specifically gets you an entire array. An array is called a series of cells in either a row, a row across, or a column.

You can get the entire column if you want to do B:B.

Now this curly brackets really is an expert trick, because it's the same as using another formula called array formula.

We don't have to type out the entire array formula. We can just use the curly bracket.

I want to show you one very useful formula here.

Let’s say we want all data in column B as headers in another sheet.

We're going to type in =transpose and the select a range.

We can click and drag the entire range. Then we can end with type on the keyboard and parenthesis. To end this, click enter.

This transpose now has turned our column into a row, and now we have headers displayed horizontally.

Let’s say you have this information somewhere in the sheet and you want to type it out on another page. You don't have to type it out. It's now there and the formula will automatically update if we change anything.

That's what what's really cool about spreadsheets and formulas and referencing other cells.

This is like really unlocking the magical powers of spreadsheets and I'm hoping that you will learn more and that you will stay with me over here on Better Sheets.

I hope you enjoyed that tutorial on your first formula!

Watch the video for this tutorial:

Discover more formulas for Google Sheets:

Get more from Better Sheets

I hope you enjoyed this tutorial! If you want to do more with your Google Sheets, I have other tutorials, like how to create a timer with Apps Script and learning to code with Google Sheets. Beginner? Intermediate? There’s a lot of tutorials for everybody! Check them out at Bettersheets.co.

Join other members for only $19 a month. You get instant access to everything: All the tutorials and templates. All the tools you’ll need. When you’re a member, you get  access to 200+ videos, mini—courses, and Twitter templates. For starters. Find out more here.

Don’t make any sheets. Make Better Sheets.