Hey there stranger!

Sign up to get access.

How to Refer to Other Cells - A1 and R1C1 Explained

About this Tutorial

Get down to basics of spreadsheets.

Video Transcript

 Hello. Welcome to another basics video. This one is all about cell notation. It's how to refer to other cells, how, what's the address? How do we talk about other cells, what we talk about when we talk about other cells kind of thing. Um, I will admit one big thing at the beginning. We are gonna go through A one R one, C one.

I'm gonna share with you what the dollar sign does. I'm gonna share with you two really interesting formulas, one called address, one called indirect. But here's the thing. The big admission is that I use a one 100% of the time. I've never used R one C one notation, but it is extremely useful. In some cases, you might find a case for it, so I'm gonna roughly go over it after we go through a one.

So the first half of this video, It is gonna be a, a one in the second half. We're gonna roughly get through R one C one. There's a bunch of really interesting use cases I can imagine with R one C one. Um, and I'll probably get into that with other more detailed sheets. But right now I think a one, uh, type of notation will get us.

A hundred percent of the way there will only add to the confusion if we talk about R one C one right away. So I'm gonna hide these and we'll get started. So to begin, a one notation means we are literally the A means the column name or the column letter, and the one means the row. So we're saying A is the column, or K is the column and whatever row it is.

So every uh, cell has an address. It is K2 for this one. So this one equals k2. It is itself, if we ask for itself, it'll be a referral. It'll be circular dependency. We just got to itself. You can't refer to, it's a cell in that cell, but we can refer to the one above it. And we can say, you know, here's some info.

And then that info is now in both. And then as we go, if we copy and paste this, it looks like it's the same information. Right? But actuality, if you look here, let me do this. . Okay, so we have K four here and every one of these cells that I copied and pasted, the one changed. So one to two, to three to four, right?

So what is happening is this is, this is what's called relative, uh, K one. It's relative. If I wanted to say K one and copy and paste it. Everywhere I copy and paste, I really mean a one. I absolutely mean a one. Here's what we do. We use the dollar sign and like forces it to hold there. So we use the dollar sign to hold the row, hold the column, and now I really absolutely mean.

K one. So if we change this high, see all of them change and they're not referring to each other, they're all referring to K one. So they're absolutely held. So the, it goes like this, you can, um, have an absolute where you have absolute, absolute, where you have a dollar sign on the column, a dollar sign in front of the one, and then neither one will change.

You can try one or the other. So in this, The one will not change, but the A, if you copy and paste it across, columns will change to B, to C to D. If you hold the A and you don't hold the one, then it, if you go down the uh, columns, A will hold, but the rows, if you go down row 1, 2, 3, 4, will absolutely change.

And then the relative one, A one without any dollars will move all around. It's like, I don't know, it's like you're paying them to stay in place. This also works for columns. So if you wanna hold, um, a column. What I have really, really loved doing and, and this has helped me through a lot of, um, business models, is I will go, uh, some, I'll do something like some and arrange, and I'll be like, E eight to E eight, which seems like that's silly, right?

You have a range of one cell. But if I hold.  E eight at the beginning, and I have like 1, 2, 3, 4, 5, 6. Now I want to give a running total. Basically now all I have to do is take this, this F eight. , copy and paste it down. And now it's a running total. Each one, the, the first part of the, the, uh, equation or, or call in or range is being held.

The second part is moving. So this gives me a running total. It's great for playlists, it's great for running totals of. Of videos, it's good for songs, it's good for making all types of awesome, um, business models. It's, it's really, really useful. You'll see this again when I start doing, um, business models.

If you look at any other videos that I do, business models, I'll pro, this will probably pop, pop up. I'll create a range and then some that range across months. You know, if you wanna see what's your running, running total during a year, you'll see that there. You'll see this pop up time and time. . So R one C one is a very interesting, um, notation because it means while this, uh, a one is the a is the column and then the row R one is row one, column one.

It also acts very differently. Um, you cannot go to B six. Let's go a R one, C one. That's nothing, right? There's literally no formula there. It's not the same as saying A one, which is something which is here a one. Um, I've used the indirect. Uh, I'm gonna skip over to a over address for now and talk about indirect, cuz you have to use indirect to use R one C one notation.

You have to go equals indirect. And then in a quote, you say, R one C one N quote. And is it a one notation that is false? It is not a one notation. And so what is it doing? It's going R one C one, which is up here. . So let's see, that's now. Sorry, I moved my head so you can see it better. So now it's false. Now it should be true.

And so this, this indirect in quotations R one C one and false as the second argument is saying, go to row one, column one, which is A one, and get whatever's there, which is in a is a checkbox. It's just gonna be true or false. If it's R one, column two, let's see what that looks. It'll be a one. That's it.

The indirect function acts very differently when you use, uh, a one notation. So if I go equals indirect, uh, a one actually A one, and then I do true. What it's giving me is the actual. , it'll do, run it as a, if it's the information or run it as information. If it is a formula, it will actually give me that formula.

So watch if I change this to B one. So if it's actually without the quotes, if it's B one, what it'll do is it, if it's a, um, actual. Formula in there, it'll run it. It's, it's a really interesting, um, way to essentially write formulas in cells and then reference That's written one, and you can change it up.

It's, I'll go into this in a different, um, video. Very much. It's one of the most interesting things I've ever discovered in Google Sheets, where you can discover lots of ways to use, um, formulas by typing them in instead of putting them in.  in a equal sign, you can type them in here. So watch if I have indirect, let's say c1, right?

And that's nothing. It's literally gonna say there's nothing. But if I write, um, five, five, nothing. If I go a three A one, two B three, see it's actually running this formula in. , so this is really good. If you have like some interesting there, there's some interesting use cases. I can't even like go into them.

Um, they're good to like filter headers, filter sort things. Really, really good way to do some fun stuff. But when we use indirect for R one C one, that's actually the only way we can use. As far as I know, one of the only ways we can use, uh, R one C one notation. So we would go row three column three. And we say false, and now it's gonna give us whatever is here, right?

And if we say, Hey, let's give us row six, let's change this to six. It's giving us literally this text in here. Um, and the way that you go, this is an absolute reference. So if I copy and paste it, it is literally going to be that, that copied and pasted. If I put, um,  brackets around both the six and the three.

Even in this string watch, they will, they will actually change because what we're doing is saying this row six column three is actually from this point, not in the absolute row on column here, but relative. To where I am. So from D two, go down six. 1, 2, 3, 4, 5, 6, go over three. 1, 2, 3. That's what we're getting.

Sheet one A one right here is written here. So that's what we're getting. So with changes from absolute on this sheet to relative to where you are, um, and that's going here. We can see this. Let's do this. Let's hide this. And now you can see. . R one C one is bear is absolute, but with the dollar sign, a one notation is absolute.

It is opposite. R one C one with these brackets is relative, whereas with nothing here on the A one notation, it's relative. And the last thing I'll go over in this video is the address function. If you ever wanna know what is.  address of a fun, or rather if you want to re refer to the address, uh, the a cell with the address, you know, some like, you know, it's row one com one or something that you can use the address.

So you go address and it shows you, it says it returns a cell reference as a string. So this, this row is the first one, then column. Then we say, what kind of absolute, there's four levels right here, and I'll show you. It's four levels. This is, this is 1, 2, 3, 4 for R one C one, and the one is a dollar. Oh, absolute.

Absolute Is is one to four, which is totally relative. So here we have, I've given you the address of 1, 1, 1 in all four ways. So you can take a look at that. Probably you'll figure that out. Um,  by yourself. So this is, if it is false, is the third one is, is it a one notations? These are all one R one C one, and these are all a.

What is really good about using address is if you can formulaically or programmatically or automatically figure out sort of where things are and then we want to refer to them. Address is a really good way to do that because you're literally saying the address in a Formula One, instead of saying A one or using some kind of, um, you have to figure out R one C one, which is relative.

You can set this all as like a, um, In one notation, right? You know, okay. Absolute is four is one. So if I want to refer to something in the first column and this row, and I know these like programmatically, like maybe I want every 10th row or every 10th column you can do that with at the address, makes it really, really easy mathematically or programmatically or in this sort of formulaic way to get the address of the cell you want.

Get to, um, very, very almost cutesy, almost very, um, programmatic way to understand, um, cell notation. So that's why I put it at the end of this video. I think it's a incredibly, uh, not, not useless in any way, but you'll use it very, very, very. Few times in the world. And so I would say stick with the A one, um, notation.

It is the easiest when you wanna refer to a sheet, a different sheet. You just put the sheet name, um, exclamation point A one, s A one, and you've got it, you're done. Uh, that is the simplest way to think of, um, how to refer to other cells and it works. Real everywhere. You ever want to use a, um, reference to a cell, just use a one.

You're gonna use that a hundred percent of the time, but,

Courses

Better Than Happy | Redesign of The Feelings Wheel

How Starter Story Designs Data

Learn to Love Your Sheets

Best Header Font Ever

Sheet Review! 150 Active VCs by LemonIO

Anika Asks: How To Set Text Overflow All The Time

Introducing: Brutal Calendar

Add a Checkbox to Turn on Dark Mode

00:05:10

Create Drop Shadows! This makes your dashboards pretty.

00:12:04

Merge Cells for Dashboards

00:05:29

10 Things I Hate About Your Spreadsheets

Dark Mode / Better Font Color

00:09:35

Better Font Colors

00:02:45

Magical Things You Can do with Checkboxes in Google Sheets

00:12:31

How To Export Your Beautiful Sheets to PDF

00:04:06

Consider Labels as Opposed to Headers

00:04:02

Add Icons To Your Sheets With a Domain Name

00:04:21

How To Color Cell Blocks So Others Enter Data Easily

00:10:30

Great Sheets! Corona Hiring Sheet

00:10:43

Great Sheets! Community Information Board by Seedtable.com

00:07:35

Roast: Hotel PPC Channel Cost Calculator

00:11:25

Better Header Fonts - Best Fonts To Use In Google Sheets

Rishabh Asks: Conditional Format Whole Row Via Text inside a Cell

00:09:49

Basic Keyboard Shortcuts To Speed Up Your Productivity

00:13:44

Basics - 5 Ways to Change Row Height

00:05:08

Anders Asks: Can I Highlight Whole Row if Certain Columns have text?

00:09:31

How to Refer to Other Cells - A1 and R1C1 Explained

00:13:22

Change the Default Font

00:03:33

Biggest Flaw In Dashboards with Dark Colors

00:07:45

Basics - 4 Ways to Change Column Width

00:05:48

Basics - Structure of a Sheet: Index() Row() and Column()

00:06:49

Communicate Better with Gridlines, Border Styles, and Border Colors - Google Sheets

00:08:55

Use Cmd + Y To Do It Again, and Again, and Again

00:03:03

Create an Auto-Update Sales Chart: Trailing 12 Months

00:09:57

Google Sheet Basics - The Absolute Basics

00:09:48

Secure Your Sheets by BetterSheets.co

00:11:07

How To Create An AutoFill in Google Sheets

00:06:07

Build a Thermometer for Savings Goals

Make Your Lists Spicy Hot in Google Sheets

00:01:47

Restrict Access to a Cell if Another Cell is Blank

How to Use Smarket

00:03:28

Combine Data from a Tab and a Totally Different Sheet | ImportRange and Curly Brackets!

00:06:23

Job Application Tracker Template | From TheLandOfRandom - Sheet Improvement!

00:53:31