Create a Trello Board with Transpose Formula in Google Sheets

Most Popular Intermediate

About this Tutorial

Use Transpose() to create a web-based, Kanban style, list-making application inside your Google Sheet! Learn how to display tasks and to-do’s in vertical categories.

Featured Formulas

TRANSPOSE()

Video Transcript

<div> Hi thanks for watching this video. Today we're gonna talk about the trans post formula . And one really cool thing I think it can do, which is create a Trello board. As as you may know, a tr avoid allows you to see vertically in items, right lists of items. And what happens is we may have a list like this maybe we have all these chores, we give them some kind of status. We can even create a dropdown menu for this. And. Change these individually, but we really don't know what's totally, of all of this.

We can't really tell which ones are done, which ones we're doing, which ones will, we will do. And also something like whoever's responsibility something is, we don't really have a good indication of. Joe doesn't really know which items are as his tasks unless he goes down this entire list. And also a cello board. Funny enough. Helps us go through statuses, but it also just lets us count very quickly and visually, right?

You can't visually count this row, or sorry, this column very quickly. You have to maybe do a calculation , like a count if, and then you say in this row if it's Joe and you get a count, and then maybe even you say, Joe, Gary. You might make a little summary here, Wilma, and you can, instead of saying, Joe here, press space bar and just select this. You can just get a nice quick count. Oh, and. You might have forgotten. This is a pretty just use these dollar signs to hold and just copy and paste and see. We get all these nice. For two. Yeah. Just making sure to, to 18, right?

6 74. Great. That is a perfectly fine solution to figuring out how many each person has, but then, which you have to look at which of these. And actually, funny enough, you can do this even in a formula here. It's a join and a filter , right? You say join with the eliminator of a you can do this funky little thing here which is a command enter. Join filter . We want all of this filtered by conditional . Is this right? Oh, I always do this wrong. This C2 two C 18, right? And this gets a little confusing, right? And we can say, okay, instead of c do a.

And put 'em all in one group here. And this is pretty awful. Especially if we're gonna get a lot right, you, this is okay with six or seven, but if we have, 10, 20, 30, this is gonna be a really bad solution to this. So we don't want to do that. What we can use the transpose formula for. If you are unfamiliar so far with what transpose is, what it's gonna do is it's gonna flip what are the rows with, what are the columns?

Typically like you'll see a use case like not a use case because I don't really like this, but you say, okay, this group . Actually everything here transpose it and you see the row of task status or owner moved to the this column and now everything's here, and you're like, okay, great. That's useless. It's not useless if we think about it in this way. If we add unique and we only want to take this column and turn it into a row. So what we can do is equal transpose and then unique . So all we wanna do is take the unique ones of these, which is three, which we know of them, right?

And I'll show you why we want to do this instead of just listing them out. We do, oh. We did that again transposed. And now we have Joe, Gary, Wilma. So let's say we have another person that moves into this apartment, a fourth person who lives on the couch. And we had hard coded this in, right? We just wrote these in a column and we now have take Gary out and we put in Let's say Fred had Fred in there.

He's living on the couch for a few weeks, so he's gonna take some of the chores and see automatically this this is Fred is added here, right? So we don't have to go and add him here which is really nice. So we don't have to change this. That's why we use this formula instead of hard coding it. And what's interesting about this transposing of a single. Column and using Unique is that we can create these other tabs.

If you add information on this sheet, it gets a little hard to use, right? So what we can do is take these two columns, break them out into their own tabs, and then rearrange this data in a way to make it like easier to read in a particular way. Like you just wanna know what's been done. What am I gonna do? And maybe in another, you just want to know which people have to do each thing, right? So let's change back Gary here and do status. And we're gonna do it in the header here. We're gonna go, he, in the header , we're gonna do the same thing. Transpose unique .

And then what I'm gonna do differently, Is, I'm gonna do this, but I'm gonna put a two here next to the first B, and then nothing after the second B so that it gets everything. So if we add anything to this, it'll automatically be added. So here's our three categories and what I need to do now is a filter . So I go filter , and the range is a column A here. The condition is that column B. Here equals and go back to chores. And I'm just gonna select a one. Okay. And now I have a list of everything that, let me fold this. You should make it a little bit bigger, right?

And I'm gonna make these columns a little bit wider so you can see what's in them. And I'm gonna take this down here. Make it header . So see, this is, Chores in the column A, filter it by what's in column B for just a one. And now I have this nice R row or yeah, column of everything that I'm doing that's being doing. We can also put a dollar sign in front of these so they don't move and we only want to allow a one to move. To the right. So that'll be B2 once it moves to the right. And we can copy and paste just that formula over. And now we see, oh, we have so much to do.

We have only very few things done, and we're doing a few things. So this is a really easy way to do it. Now you probably know what I'm gonna do next, but I'm just gonna do it again. Transpose. Unique , unique , and then go over here. I want C and I want just C2 down and I want everything there. And now I have the headers of everyone. So again, I'm gonna move this down to make a nice header . I'm gonna do a filter equals, and I'm gonna back. I just want the chores column. I do want the C column equals. And who am I equaling A one again.

And now, right now I know what I'm gonna do, so I just put in the dollar signs. I'm gonna scroll. Over or copy and past he's over, right? And now we see very viscerally, very visually, oh, Gary has a lot to do, and Joe has something to do, and Wilma has very little to do. Now maybe it's because Gary doesn't pay as much rent, or there might be some other issues going on here. Or he lost a bet. But generally speaking, we can clearly see what the differences are.

And then also if we wanna say, okay, I'm gonna trade you I'm not gonna do preparing breakfast, Wilma is, we can quickly go back to this tab and see, things are lining up pretty evenly now. So this gives you a really nice view of obviously a Trello board or some, any kind of categorization that you want to do with these this type of information that maybe it has status here or the responsible party.

And sometimes these lists go on and on, and a summary tab, which just numbers isn't gonna do it. And so this kind of thing makes it really using this transpose formula on these columns and unique , adding unique in there makes this really easy. One thing I will point out that if you add statuses, so if I say, oh we're doing the we're gonna do the laundry, but like it's only on Wednesdays, right? And we change that status and you start adding more, maybe do making coffee need. Tuesday, and maybe you start splitting up by different days in this status column, right?

Maybe Joe's gonna do it on Wednesday, and now you add more. What's gonna happen on here? See these, this automatically adds these statuses over here. But we are ready for this. All we have to do is take this formula and copy paste it over here and now we're done. That's just a little bit of upkeep you might have to do to keep up with anything you're changing in these columns. That's just a note . Thanks for watching and keep on making better sheets. Bye.</div>