Replace 1,000 Formulas Instantly

Beginner

About this Tutorial

Learn a cool trick you can do with the ArrayFormula. If you don't know what the ArrayFormula does, I show you how to use it in the first few minutes of this video. Then show you how to use it better.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

ARRAYFORMULA() SORT()

Video Transcript

<div>0:00 Hey, so you're trying to sort of do a formula down a thousand rows. Like, for instance, I have a thousand names here, a thousand and one rows, including the header . 0:09 And I want to add a message. It's just going to be like, hi, and then their name here.

How are you now? 0:20 If I wanted to do that, I might do something like this equals Hi Get a space do the ampersand a2 ampersand and then How I'll put a comma, how are you? 0:45 And there, I can even auto fill that right away and I can see that there's a formula in every single one of these rows. 0:54 But I don't really want a formula here, I want the actual data . And when I, if I, move it or edit it, I want to be able to edit this at one time. 1:04 I don't have to copy and paste it all over again.

So what I can do is I'm going to delete all of those autofilled and go to it at the front. 1:13 I'm going to wrap this in array formula . Array formula , all caps. And we're going to, I'm going to put it in parentheses and I'm going to hit enter. 1:23 Still going to get this autofill , but I'm going to reject it. Now nothing happens. You see no change. The reason is, is we're still calling only A2. 1:33 With a ray formula we need to change this single cell reference to a range reference . Or an array.

And so all I have to do is add colon a here, hit enter, hit enter, and now we have all of this data here all around. 1:53 But there's a few problems with this, and I want to give you some tips on how to make this even better. 1:59 If we want to sort this. Something's going to happen where we only have this formula in one place. If we sort it, let's say z to a , you see it disappears. 2:11 Our array formula is down here. Well, that's no good. I want it to stay up here. So let's Command Z that. 2:22 And one workaround is we can do this sort .

We can sort the A colon B by the second column, actually A2 colon B. 2:34 And we can sort it by the second column. We can do it ascending false. And there we go. We have a. 2:41 In alphabetical order now. But we're doing it somewhere else. This is pretty problematic. And for some reason, I actually like doing this. 2:50 I like having data in one place and then morphing it in another place on another tab and stuff. But you might want to keep this array formula here, but not have it on the second row. 3:00 So we can do a little. Bit of a trick here.

We're going to do equal sign and we're going to add curly brackets here. 3:07 This curly brackets will be sort of an array we're going to create inside here. And the first item we're going to create is called message, which is just in quotes the header that we want. 3:21 I'm going to add a semicolon, and I'm going to hit Enter. It's going to have an error because we have nothing in this second spot after the semicolon. 3:30 But I'm going to take all of this formula . I'm going to command, actually, Command-C it.

So I'm just going to copy it. 3:39 I'm going to move it over here to the second. Argument . So we have curly brackets, message, semicolon, and then a formula , this array formula . 3:49 I'm going to hit enter, and you notice we have exactly the same thing in, or at least we think, we can see this, in the B column and the C column. 3:59 That's because this array formula is sort of, overflowing into the C2 and then it's actually giving us what we need. 4:08 So this is a lot more elegant because our formula is in the header .

And so this might be something you want to do in your own sheets just to give it a little bit more elegance, a little bit more pristine. 4:22 Look here, we have all of this data still here. We can edit, you know, a Mar Whitney, oh, maybe, which way, withy? 4:33 There we go, and it edits this text over here, right? The formula 's still working, great, and we have it in our header . 4:40 So let's go back. Let's see if this works. I'm going to sort this column and see we have our data . 4:50 Now the data in B column has disappeared, but our data in C column is totally fine.

We still have this message at the top. 4:59 We can sort it the other way, and there we go. We still have it. All of our data so we can get rid of this B column and now we have a sortable column of data here. 5:09 Pretty cool, pretty nifty. I thought that would be really fun to make your sheets better, make your better sheets better. 5:15 Make your sheets better. I hope this helps replace tons of formulas. Learn about the array formula and how to do this cool trick. 5:22 And the header here with the curly brackets, and I send my colon. And thanks so much for watching.</div>