This tutorial is available for all users. Start with a 7 Day Free Trial today and get access.
Hey there stranger!
Let's not be strangers any more. Start a FREE TRIAL today to view this tutorial, right now.
Basics - Structure of a Sheet: Index() Row() and Column()
About this Tutorial
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!