Hey there stranger!

Sign up to get access.

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

About this Tutorial

Let's figure out how a sheet is structured.

Featured Formulas

Video Transcript

 Hello. Welcome. This video is gonna seem really basic to a lot of people, but I hope for those beginners who are just getting into Google Sheets for the first time, this will be incredibly helpful. I'm gonna tell you what the structure of a sheet is, how to like, Unlock that and some really fun things I like to do with these three formulas, index, row and column.

And I'll get into them as quickly as I can. So a sheet, whenever you start a new sheet, it has 26 columns and 1000 rows. You can use row and column to find out which row or column the cell that you're in is on. This is incredibly useful. I've used it for like basic math. I've used it to reference other pages.

If you set up, say, a templated tab you're doing accounting and you know, like, okay, I'm gonna put the summary in the, in the, you know, top A one. You can always reference that.  by using a one, but you can also reference it by going to index and row one column. One index is a fun, fun formula that you can use to basically find any particular cell at any particular place that you know how many rows and how many cells it is.

Here, I'll, I'll, I'll go into this, actually, I'll show you here. So let's start, we're gonna get to this in a second. So row. So if I do equals row. This is gonna be two because it is on the second row here. Nine equals row is on the ninth row. You think like, okay, that's. That seems silly, right? Because you're on ninth row.

You can see two here. You can see two here. Well, sometimes I want to like do some really quick like cube to math, like I just want all the cubes or I wanna do like some really quick number problem, but I know I want to iterate through numbers. I use this in business models all the time. Show like movement.

Here we'll go equals row and we want to do like times, I don't know, 10. Just want these numbers. So I go through that copy paste here. Now I have the numbers 10 through one 10. I can do times 45. Put that all in there. Do it. Times 88. 858. Oops. Three times 800. Five. Now I have some pretty big damn numbers here, can for format them into different cells like this.

This is really fun to be able to do some really quick math.  and column is exactly the same way you can go equals column c o l. Actually you can do just c o l and it automatically goes into column and B is two. This is actually a really good way to find out what what number these, these columns are, because who knows you?

What is the actual number? M what? You have to remember that it is the 13th, 13th letter in the alphabet. So this is really good, especially if you have like hundred columns. This is really cool. So now we know we can get the row, we can get the column, we can add these together. We can find out this is column.

We can cat the row and the column.

We can concatenate the row and the column here. , and that's not 32, that's three and two. So let's do this. So let's do equals concatenate. Concat 10, eight. Let's do row comma row. Let's do put a space there. Column when.

There. And so now we have row and column in there. We now can look at this. I'll take this, scrap it around here. Here. Now we know every single position so we can say, okay, row one, column one, row four. Column four is here. If we want to get this row four column four, how would we do it? Right? We can do D four, right?

We, we would remember, okay, D four. But sometimes, like we don't exactly remember the letter. We might say like, we want the hundredth row or wanna range from here to here, and we just might not remember, or this kind of range or the, whatever we're looking at, whatever data we're looking at might move around.

How do we figure out what's in the, the, the top left corner or the, the. , the second column in the first row. Well, we use index for that. We can do index and we can say reference A one to E 11. Okay. That's the whole range, and we want one and one. And so what we're gonna do is we're gonna get from that the, the contents of the cell over at.

in this range. What's at row one? Column one? It says, it says row one, column one. We can change these numbers around 4, 4, 5, and get exactly row four, column five. Well, we can also move this range and we can say, okay, let's start at b2. B2 and go to E 11. Now what's gonna happen is if that column and row number is outside of the realm range, it'll tell.

So let's do two two. And it's going not from the top of the sheet, but actually the top corner of the range that we put in, which was b2. So it's going from b2. It's saying what's two across and two down? Well, it's row three, column three. This is really cool if you're moving stuff around, if you have different ranges and you wanna move it around, but you can also take these numbers out and reference other cells.

So I did that over here. So here we have index, C, d, E, and we're referencing, well, the row is gonna be whatever's in C 16, whatever's in C 17. And now we have our row and column outside and we can move it around and see this is changing here. So in essence, index will get you the.  contents of the cell. At any point that you know, the row and the column, Rowe will get you the actual row.

The cell that you're in is on column will get you the number of the column and it's incredibly useful because currently the, the columns are letters and who knows what number is AX or b q, we don't know, but you can know if you've grabbed the column. Use the column equation or formula inside of assault bite.


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


Create Drop Shadows! This makes your dashboards pretty.


Merge Cells for Dashboards


10 Things I Hate About Your Spreadsheets

Dark Mode / Better Font Color


Better Font Colors


Magical Things You Can do with Checkboxes in Google Sheets


How To Export Your Beautiful Sheets to PDF


Consider Labels as Opposed to Headers


Add Icons To Your Sheets With a Domain Name


How To Color Cell Blocks So Others Enter Data Easily


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!


Job Application Tracker Template | From TheLandOfRandom - Sheet Improvement!