What's one thing you learned from a student you were teaching Google Sheets? Okay, I love this one because it was such a surprise to me, and now I love to share it. Here it is So if you have a spreadsheet of data , and let's say we wanna do first name, last name, and we wanna combine them, and you may or may not know this, but you can combine these by doing A2, ampersand, put a space in between quotes and ampersand, and this B2. Now, it's gonna try to give you, like, oh, just copy paste all of this down. Sure, sure, sure. But, like, an advanced user knows ARRAYFORMULA can be put around this, and the only thing we have to do is change these cells to array.

So AT- A2 colon A, B2 colon B, and that will get you everything. But here's what I was shown, and I love sharing because it's very unintuitive. So if you have a bunch of data in your sheet and you wanna continually sort your sheet, but you don't wanna have to rewrite these formulas and you want this formula to go down as long as you have data , if you sort or change the order of these rows, this ARRAYFORMULA might change location. And look, we get a reference error. So how do you not allow this to change its location, but you still get the output of this ARRAYFORMULA? Okay, I'm going to copy the ARRAYFORMULA and delete it from

here, so we have a blank column. And in the header , I'm gonna rewrite this header to equals, and I'm gonna put curly brackets around, so we have curly brackets. Then in quotes, full name, which is the header . Now I'm gonna put a semicolon, and I'm gonna paste that ARRAYFORMULA , the formula that I want. And essentially what this is doing is creating a vertical list and saying, At the top, put full name, then put this ARRAYFORMULA . If I hit Enter, I get the header and the ARRAYFORMULA output, but the entire source of that is in the header, so now I can freeze that first row.

I can move things around, move the rows around, and the formula stays the same. I think this is absolutely amazing. Very unintuitive way to use Google Sheets, very unintuitive way to use Arrayformula you're watching Better Sheets here on YouTube. Watch this video or this video. It's gonna make your Google Sheets better Go ahead, ask me anything down below in the comments . And I'm going to answer it in a future video become a member of Better Sheets today. . You get videos, you get sheets. So what do I have to do to get you into a brand new Google Sheet? Go buy some tools that I've built inside of Google Sheets doing things in Google Sheets you never thought possible. Go check it out