Wrangle Scattered Data with Flatten()

Beginner

About this Tutorial

Check out how to use flatten(). Discover the funky formula that makes data wrangling a breeze! Learn how to use Flatten in Google Sheets to organize scattered data and get insights that were previously hidden. Read on to see how this simple trick can save you hours of manual labor.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

FLATTEN()

Video Transcript

<div>0:00 Here's a funky little formula called flatten , and I'm going to show you how to use it, how I use it, how I love to use it. 0:07 But first I want to show you how it works. What is flatten ? You can flatten things in Google sheets and you think, oh my gosh, it's already flat. 0:16 It's a sheet. Right? Well, it can get flatter. Um, don't flatter me. I love this.

Uh, so if you have data like in <inaudible> and all the way down to like see to, right, and you have these like six little words and, but I really want them in like a colon. 0:36 What I can do is call flatten and it puts them into a column. It reads everything from left to right up and up to down. 0:44 So you can see a one is here in D uh, three, then it goes left to right. Hello. I M and then after it gets through the, uh, range , it goes back and to a, to getting flattened today. 1:02 So, hello, I'm getting collected today.

Uh, let me show you how, how that works or what to do to get there. 1:07 So you just, it was equal flatten , and then you put in the range that you want, and we can do a one colon C two, and we get exactly the same thing. 1:17 Right. We can also not even deal with the numbers and just do the whole column . So a colon C, and that gets everything in column eight to see, uh, notice, we'd go down to the bottom. 1:31 It didn't add any rows because everything's blinking. It understands what's blinked, but it also will show up blank.

So if I put a blank here, there is the blank there. 1:42 So it gets the data , but then once it ends, it ends. Uh, okay. So what is this? It's more like, uh, an unpivot almost, uh, some people are using it with, uh, uh, a few other formulas, like split transpose, um, and doing an unpivot . 2:02 But very, most of the time, I'm not trying to unpivot things I'm trying to pivot things more and more so flattened, where , where can we use it on that? 2:11 And I like to use it when I wrangled scattered data.

And here's, here's an example of what I mean by that. 2:17 So let's say we took a poll of everyone in the company. There's every employee, and we want to know what projects you've been working on just to get sort of qualitative data on this. 2:28 Right. And we said, Hey, just, we have a list of all the projects, go in, look at each project and put a yes or no. 2:34 Or maybe we didn't ask them what to put in there. Just say, tell us if you've been working on this or not. 2:39 You know, we took a very simple, we thought it was going to be simple.

He said, Hey, just go into this sheet, fill in if you're working on it or not, maybe we messed up and we didn't say, Hey, only say yes or no. 2:51 Maybe there are notes . There are extra little data pieces. People will leave comments , uh, all sorts of things gonna happen when you don't give exact instructions. 3:02 Right.

Or if you don't use data validation , uh, to tell someone they've entered something wrong, uh, this is what you get now, what happens is like everybody, some people capitalize, some people didn't capitalize. 3:15 Some people said sometimes, or forever or so Carl even here wrote in, oh no, nobody works on this. And in fact, yeah, nobody works on this when he could have just said no, but Carl had to put in a comment . 3:27 Right.

And look, it is so simple and easy and takes only a minute or two to go down a list like this, uh, 27, I think I've seen some more 30 projects, five people, not a big deal. 3:43 Right. Okay. You can probably change these by hand to like yes or no. Okay. Sometimes it means yes. Forever means. 3:52 Yes. You know, never will be no. Sure. You can do it by hand.

But what if some of this data is actually important? 4:00 What if we want to know, Hey, we didn't come up with these edge cases when we first asked this question, right? 4:06 We want to know the edge cases we, we wanted this data . So what I usually did in the past before flattened was I could do unique . 4:15 Unique is cool because let's do Bob's row B to B and it gets each unique point there. And then it also gets the name of the column. 4:28 So we can actually get that out, rid of that by just doing two.

Um, and now if we want to count, let's say count, if this is always fun, I use count F on column B and we use yes. 4:40 As the criterion and we get a nice count and double like on that. And we get that. We get all of the count in that row. 4:49 Right? Okay. Well, we have five, four other columns here. So if we do, yes, we can do this unique . We can do this again. 4:58 Right. We can Even get all the uniques of each row. There we go. We got all the uniques of each row. 5:09 So it's five unique . Now I need to get you to any of this.

So, you know, sometimes I might go like, um, I might actually get to the bottom here, Do something like, let's just delete everything on. 5:25 Um, Okay. And just deleted everything row 4 51 in down. And what we can do is then do unique . And this is going to take a while, right? 5:37 This is just a few steps too much. Um, but it will get us the answer that will be super fast with flattened. 5:46 So we can put all of these in a single column. Okay. So I put all five uniques in one column. 5:57 Now I need a unique of unique . So we'd go unique .

I've done this before with like thousands of rows of data . 6:04 And it's not that fun yet, or unique of uniques. Uh, H okay, now we have uniques of uniques. Now we know every unique thing in this entire thing, but like, that was not fun. 6:19 Right. We had to do with rows and columns and deletions, and this unique of uniques flattened is going to help us. 6:26 So-so so much in the next, uh, tap one second. All right.

In one formula , I'm just going to type in here equals flatten , and I'm going to do B two all the way to, we already know it F 50, And we get every single item in B2 to FFT. 6:52 And now you only one go, we can do unique . Uh, H H and there we go. We have the same answer, right? 7:01 In this unique of your needs, where we've had to copy and paste and edit, uh, these formulas pretty manually, right. 7:10 We still get the exact same, uh, answer, but we get it in so much faster.

You know, one interesting thing too, is we may want to use this data to figure out, is there a blank here or not? 7:25 Now, one thing to note is the blanks at the end of the rows, won't show up with them, think show you. 7:33 They do show up. There it is. There's a blank.

Um, and what is interesting is I've been able to find blanks pretty easily. 7:45 If I, if I hold down command and push down the down arrow, you can see what is it sort of hard to explain whatever is if you're in a piece of data , if you're in a cell with data , it'll go to the last cell with data . 8:00 If you're in a, uh, blink cell, it'll go to the last blink cell.

So this makes it super easy for me to check if there are blinks. 8:12 Um, if I just take my cursor and move through it with the command, I can really quickly see if there's any data or if there's any data in, um, blank cells or blank cells in where there should be data . 8:28 But now everything's in one, one column.

So now there's a blank right here, and I don't have to search around for five of them. 8:38 And again, this is going to be sound really stupid and silly if you're talking about 20 cells of data , but if you have 20,000 cells of data , if you even have a thousand cells of data or even, um, sort of not a wide or not a tall, uh, piece, a sense of data , you have maybe lots of rows in lots of cell, uh, columns. 9:04 Then this kind of flattened thing will help you immensely, right? It will bring you all of the data that sort of scattered, right. 9:12 And it will help you wrangle it.

So, uh, use flatten , let me know how you use it in the comments . 9:18 Uh, thanks for watching this video.</div>