79% OFF online library of tutorials for mastering Google Sheets
This tutorial is not available for free users. Please purchase a subscription to view this tutorial.
Hey there stranger!
Please sign-up or sign-in to watch this tutorial.
How to Refer to Other Cells - A1 and R1C1 Explained
About this Tutorial
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,
Better Than Happy | Redesign of The Feelings Wheel
Best Header Font Ever
Learn to Love Your Sheets
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
Create Drop Shadows! This makes your dashboards pretty.
Merge Cells for Dashboards
Dark Mode / Better Font Color
Better Font Colors
Magical Things You Can do with Checkboxes - Google Sheets
Export Your Beautiful Sheets to PDF
Consider Labels as Opposed to Headers
Add Icons To Your Sheets With a Domain Name
Coloring Cell Blocks for Others to Enter Data
Great Sheets! Corona Hiring Sheet
Great Sheets! Community Information Board by Seedtable.com
Roast: Hotel PPC Channel Cost Calculator
Better Header Fonts - Best Fonts To Use In Google Sheets
Rishabh Asks: Conditional Format Whole Row Via Text inside a Cell
Basic Keyboard Shortcuts To Speed Up Your Productivity
Basics - 5 Ways to Change Row Height
Anders Asks: Can I Highlight Whole Row if Certain Columns have text?
How to Refer to Other Cells - A1 and R1C1 Explained
Change the Default Font
Biggest Flaw In Dashboards with Dark Colors
Basics - 4 Ways to Change Column Width
Basics - Structure of a Sheet: Index() Row() and Column()
Communicate Better with Gridlines, Border Styles, and Border Colors - Google Sheets
Use Cmd + Y To Do It Again, and Again, and Again
Create an Auto-Update Sales Chart: Trailing 12 Months
Google Sheet Basics - The Absolute Basics
Secure Your Sheets by BetterSheets.co
How To Create An AutoFill in Google Sheets
Build a Thermometer for Savings Goals
Make Your Lists Spicy Hot in Google Sheets
Restrict Access to a Cell if Another Cell is Blank
How to Use Smarket
Combine Data from a Tab and a Totally Different Sheet | ImportRange and Curly Brackets!