Hi, this is Wayne again with a topic “The Excel TOCOL and TOROW Functions”.
In this Excel video, I will show you how to use the relatively new Excel. Two call and two row functions. Let’S get started and the purpose of these functions is to take data that you have and to combine them into a column or a row, and so, as you can see, I’m in a spreadsheet that has nine different people’s top 10 lists for their favorite 80s New Wave bands and I would like to create kind of a master list of all of their bands put together. Let’S look at how to do that. So I’m going to browse over here to the right a little bit. You can see here’s my master list and I’ll just click on Cell J2 and I’ll type equals and I’ll use.
The two call function notice what the two call function does it Returns the array as one column, so next I’ll put in my left parenthesis – and I just need to select my array. So the array is the range of cells that I would like to combine into. One column so I’ll just click here and select all of the bands in each person’s list of top 1080s new wave bands, so I’ve got all of those selected, so you can see what it’s done here in the spreadsheet. It’S put in the range A2 through i1.
Now I should put in a right parenthesis in this case you don’t really have to and then I’ll tap enter and look what I have. I now have a master list of everyone’s top 10. The only problem is, as you can see, there are many repeats.
Several of these nine people, like Echo and the bunnyman several like New Order and those are repeated over and over now, there’s a way I can deal with that. If I don’t want there to be repeats, all I have to do is select the cell. That has my formula in it and rather than editing the formula here in the cell, with the cell selected, I like to go up here to the formula bar and that’s a great place to go to make edits to an existing formula.
So I’m just going to go here in front of the T in two call. So after the equal sign and I’ll just type in the word unique and then I’ll put in my left parenthesis and then at the very end of this entire formula. I’Ll put in one more right, parenthesis tap, enter on the keyboard and look now.
I have a list of all the bands, but it’s limiting the list to unique items. Only so there’s only one depes mode. We already knew that there’s only one New Order, Etc, whereas before I think I had 80 or 90 entries in column J now I have 42 total and I can undo that just so. You can see for sure you can see we’re back to 91 total entries, but by using the unique function it gets it down to 42 total okay.
So that’s how we can use to call what about to row. So in this case to row doesn’t make as much sense, but that’s okay, let’s just give it a try. So here on, Cell A45, I’ll type equals to row left parenthesis and then I’ll select all of the Bands again tap, enter and now they’re placed on the spreadsheet as rows. And it’s going to go way over here to the right. In my spreadsheet, like I said it doesn’t make as much sense in this case, but I wanted you to see that it does work.
So two call and two row are useful in that they can help us to combine lists into one giant list. There are other uses for them, though, for example, look at the different names I have here. I have Tom’s list, Brett’s list, Etc.
I have basically a row of names, but what, if I would like it to be a column of names, I could click type equals to call left parenthesis, click and drag to select the names now, of course, instead of clicking and dragging, I could simply type the Cell references A1 through i1, but anyway, I’ll put in my right parenthesis, tap enter so now I’ve taken what was a row of names and I’ve turned it into a column of names. Unfortunately, the word list is in there, but that’s okay! You get the point. Similarly, I could select a list, that’s in column, form and turn it into a row. So, for example, I’ll select this cell – let’s say cell A45 and then I’ll type equals to row, left parenthesis and then I’ll go up here and click and drag to select data. That’S in a column, tap enter and it’s taken that column data and turned it into a row of data. So, in my mind, those are at least two uses for two call and two row use number one is taking multiple columns or multiple rows and then combining them into one.
You use number two is for converting row: data into column, data column, data into row, data thanks for watching this video. I hope you found it to be useful if you did please like follow And subscribe, and when you do click the bell and you’ll be notified. When I post another video, if you’d like to support my channel the best way to do that is to become a channel member look for the join button below the video. You can also support me through my patreon account, or by buying Channel merch and you’ll, see information about those options in the description below the video you’re also welcome to click.
The thanks button below the video that’s another way to support the channel. Speaking of supporting the Channel, I want to say thank you to my super techie and ultr techie Channel supporters. Thank you. So much for all you do to support the channel.
I’M grateful to all of my channel supporters, not just the super and Ultra techies, but also the regular techies. Thank you all so much for the support you give so that I can continue to make these educational videos .