Learned This Google Sheets Trick from a Student
In this video, discover a unique Google Sheets trick for combining first and last names using ARRAYFORMULA in a way that keeps your headers intact and allows for easy manipulation of your data. This unintuitive method will enhance your spreadsheet skills and streamline your workflow.
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. , 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, 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 the rows around, and the formula stays the same. I think this is absolutely amazing. Very unintuitive way to use Google Sheets,