You have a column of data in Excel. You need to reverse the column, bottom to top.
How can you do this? Can you do it without a helper column?
Bill Jelen and Mike Girvin compare six ways
Table of Contents
(0:00) Description of problem
(0:49) Bill: SORTBY and SEQUENCE
(2:15) Mike: INDEX and Converging range
(3:23) Mike: INDEX and SEQUENCE
(5:22) Bill: Custom List
(6:43) Mike: LAMBDA
(8:49) Bill: Power Query
How can you do this? Can you do it without a helper column?
Bill Jelen and Mike Girvin compare six ways
Table of Contents
(0:00) Description of problem
(0:49) Bill: SORTBY and SEQUENCE
(2:15) Mike: INDEX and Converging range
(3:23) Mike: INDEX and SEQUENCE
(5:22) Bill: Custom List
(6:43) Mike: LAMBDA
(8:49) Bill: Power Query
Transcript of the video:
Hey, welcome back, it's time for another Dueling Excel podcast.
I'm Bill Jelen from MrExcel. I'll be joined by Mike Girvin from ExcelisFun.
This is our dual number 196: how to flip a column in Excel?
Alright Mike, I got a great one for you today. Over there in column A we have a list of items.
We don't know how long the list of items is going to be and we want to reverse those items. So whatever is at the bottom, we want to bring to the top and whatever at the top we want to send to the bottom.
Unfortunately, these are sorted by something that we no longer have in Excel.
And we're throwing a bonus point for the fewest number of helper columns.
So our goal is Spinosauras goes from the top to the bottom. Alright, so my method is to be equal SORTBY.
A new function in Microsoft 365. Here's the array that we want to sort.
How do we want to sort it?
We want to turn it upside down so I'll do that by creating a SEQUENCE in reverse. We have 11 rows.
I guess I could do COUNTA of that range. And then how many columns?
I'm going to leave that blank, which will give us one.
Starting - leave that blank, which will also give us one.
And then the step: negative one, which will generate a sequence in reverse.
And then Spinosaurus moves from the top and comes out of the bottom right.
There we are. Select the whole thing.
Control+C and then Alt+E S V to paste special values.
Alright, that's my method one. Mike, what do you have?
[ Mike Girvin ] Thanks MrExcel.
Wow, I've been flipping columns for decades because in academia in the syllabus you have to list grading possibilities from biggest to smallest in the syllabus.
But of course the VLOOKUP functions have always needed the table to be smallest to biggest. And guess what?
I would have never thought of using SORTBY and SEQUENCE.
If I F9 that is totally clever to take advantage of the default ascending in SORTBY.
Alright, since the vast majority of Excel users in the world do not have Microsoft 365, I'll use the INDEX function.
And in the array, these are the items to look up.
And we need to lock it, so I hit the F4 key, comma. And now in row number.
What do I need?
Well, there's eleven items, so as I copy the formula down I need eleven, ten, nine, eight and so on.
I use the ROWS function. Highlight the entire range.
And guess what?
I'm going to use a shrinking range by locking the second reference. So I will (F4) lock the row reference 14.
But that 4 as I copy down will go 5, 6, 7, and so on.
So the ROWS will give us 11, 10, 9, 8, and so on.
That's our formula. Close parentheses, Ctrl+Enter.
I do have to copy it down. It doesn't spill.
Go to the last cell and F2.
That is beautiful ROWS counting 14 to 14, which is one.
Now we could use the same approach if you wanted to spill it, but that means you'd have to have Microsoft 365.
So we take all of these. We do not have to lock.
Comma and I'm still going to take ROWS. So that gives me 11.
But from 11, I need to subtract 0, 1, 2, 3, and so on.
So I'm going to use ROW and this will give me 4 to 14.
Close parentheses.
And then, that's an array of numbers, but we'll subtract a single number. That's row four.
So four minus four is zero.
Five minus four will be one, and that's exactly the pattern we want. Close parentheses, Close.
And I'm not using any of the new functions, but if you have the new Microsoft 365 or Excel 2021’s amazing worksheet formula engine. When I hit enter I get a bunch of errors.
F2 - it's because I have to force that subtraction before that subtraction.
And when I hit Enter, Bam! There's a spilled version all right.
It's still like this one the best, but of course what's the problem with the old school method? You have to worry about locking.
You have to worry about copying.
And if you edit you have to come to the top cell and recopy.
None of those issues happen with dynamic spilled arrays, right? I'll throw it back to MrExcel.
[ Bill Jelen ] This is beautiful.
I expected your first one to be an INDEX, but I never saw the the contracting range coming for ROWS.
That's beautiful.
I always have an expanding range, I've never gone the other way. This is a great solution.
But this, alright… respect.
This is great and I see what you're doing there that it's just a single formula that's going to spill.
And that's awesome, but it's too complicated for me.
I'll have to go see if Tommy Tutone can record a song so I can remember this.
Alright, here's the one.
This is the one I think this is the one that is really cool. Although let's see.
We don't need Microsoft 365. But it has to be less than 255 items.
And there can't be any duplicates in the list. I'm going to choose the list.
File, Options, Advanced. Scroll all the way down to the bottom.
Edit Custom Lists and memorize that list as a custom list.
That's why that's be less than 254.
OK, so the list of course is already in the correct sequence.
Click OK, click OK, But then Data, Sort.
Over here in the order I can choose a custom list.
Which doesn't seem like it's going to help, because the list is still in the correct order. But this is so wild.
I've seen this before and think, when would I ever use this?
Clearly it was just waiting for this duel.
They now offer the list in the forward direction or the list in the backwards direction! How cool is that?
Click OK. Done, no need for any helper columns at all.
Let's see what you have, Mike.
[ Mike Girvin ] MrExcel - you definitely get the extra points because I don't have a method that's not a formula.
But what if I want a custom function that I can use anytime to flip any column?
Well that's where LAMBDA comes in. Now the only input we're going to need.
And we have to give this variable a name: column to flip.
And that name will show up in the screen tip when we use this later.
And now we create our formula. And I'm just going to steal your awesome SORTBY.
And the array?
Well, I'm a bad typer so I'm going to click and copy. Ctrl+V, comma.
By array?
SEQUENCE and then I want the number of rows, Ctrl+V. And then columns, we are going to skip.
Start, we are going to skip. Step minus 1.
Close parenthese on SEQUENCE. Close on SORTBY.
Close on LAMBDA.
And I want to test it before I put it up in define name So I'll Open parentheses, and highlight, Close parentheses and when I hit Enter - Bam, there's LAMBDA to flip this.
Now we copy everything except for the testing part.
Control+C, Enter. Control+F3 to open up Name Manager.
New.
I'm going to call this something really easy to understand like flip column.
And then down here, equals Control+V and that's it. Click OK, click Close.
Now I'm going to flip this awesome column of text equals flip and there it is.
And look at the screen tip.
It's even got column to flip and when I hit enter, Bam!
There's Lambda to flip but no extra point, because that's still a formula. Alright, I'll throw it back to MrExcel.
[ Bill ] Well, I’ll be. I was convinced you were doing Power Query.
I mean power query. How did you not do power query?
But you blew me away with this LAMBDA.
What a great formula and once it's in Defined Name that you can flip anything. I love that.
For me this gets the point, but let's take a look at Power Query.
Well, you know Mike is Thanksgiving Day, right?
But that's not going to stop your viewers or my viewers from yelling at us for not using power query.
So watch this.
I am going to create a named range here of FlipMe. And then Data, From Sheet.
There's my data.
I'm going to add a column and index column frrom zero.
And then the Home tab do Z to A to flip it and then remove. Right.
Now watch this. Close and Load, Close and Load To.
And in real life, I'm going to go all the way out to that column Z or somewhere where no one is going to see it. But for right now we'll put it right there.
Click OK. Alright, then this.
Equal. Double Click to copy that down.
Hide column C.
Alright, but column C still contains a real live query. So every time I do Refresh All, it flips.
And I have a formula up here to see if Spinosaurusus is the top item to do ascending or descending.
How about that? Ah, that was a lot of fun.
A Thanksgiving surprise for you.
If you like these videos, please down below, Like, Subscribe, Ring the bell for both the ExcelisFun channel and the MrExcel.com channel.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank you for stopping by.
We’ll see you next time for another Dueling Excel Podcast from MrExcel and ExcelisFun.
Let’s hear you, Nancy!
I'm Bill Jelen from MrExcel. I'll be joined by Mike Girvin from ExcelisFun.
This is our dual number 196: how to flip a column in Excel?
Alright Mike, I got a great one for you today. Over there in column A we have a list of items.
We don't know how long the list of items is going to be and we want to reverse those items. So whatever is at the bottom, we want to bring to the top and whatever at the top we want to send to the bottom.
Unfortunately, these are sorted by something that we no longer have in Excel.
And we're throwing a bonus point for the fewest number of helper columns.
So our goal is Spinosauras goes from the top to the bottom. Alright, so my method is to be equal SORTBY.
A new function in Microsoft 365. Here's the array that we want to sort.
How do we want to sort it?
We want to turn it upside down so I'll do that by creating a SEQUENCE in reverse. We have 11 rows.
I guess I could do COUNTA of that range. And then how many columns?
I'm going to leave that blank, which will give us one.
Starting - leave that blank, which will also give us one.
And then the step: negative one, which will generate a sequence in reverse.
And then Spinosaurus moves from the top and comes out of the bottom right.
There we are. Select the whole thing.
Control+C and then Alt+E S V to paste special values.
Alright, that's my method one. Mike, what do you have?
[ Mike Girvin ] Thanks MrExcel.
Wow, I've been flipping columns for decades because in academia in the syllabus you have to list grading possibilities from biggest to smallest in the syllabus.
But of course the VLOOKUP functions have always needed the table to be smallest to biggest. And guess what?
I would have never thought of using SORTBY and SEQUENCE.
If I F9 that is totally clever to take advantage of the default ascending in SORTBY.
Alright, since the vast majority of Excel users in the world do not have Microsoft 365, I'll use the INDEX function.
And in the array, these are the items to look up.
And we need to lock it, so I hit the F4 key, comma. And now in row number.
What do I need?
Well, there's eleven items, so as I copy the formula down I need eleven, ten, nine, eight and so on.
I use the ROWS function. Highlight the entire range.
And guess what?
I'm going to use a shrinking range by locking the second reference. So I will (F4) lock the row reference 14.
But that 4 as I copy down will go 5, 6, 7, and so on.
So the ROWS will give us 11, 10, 9, 8, and so on.
That's our formula. Close parentheses, Ctrl+Enter.
I do have to copy it down. It doesn't spill.
Go to the last cell and F2.
That is beautiful ROWS counting 14 to 14, which is one.
Now we could use the same approach if you wanted to spill it, but that means you'd have to have Microsoft 365.
So we take all of these. We do not have to lock.
Comma and I'm still going to take ROWS. So that gives me 11.
But from 11, I need to subtract 0, 1, 2, 3, and so on.
So I'm going to use ROW and this will give me 4 to 14.
Close parentheses.
And then, that's an array of numbers, but we'll subtract a single number. That's row four.
So four minus four is zero.
Five minus four will be one, and that's exactly the pattern we want. Close parentheses, Close.
And I'm not using any of the new functions, but if you have the new Microsoft 365 or Excel 2021’s amazing worksheet formula engine. When I hit enter I get a bunch of errors.
F2 - it's because I have to force that subtraction before that subtraction.
And when I hit Enter, Bam! There's a spilled version all right.
It's still like this one the best, but of course what's the problem with the old school method? You have to worry about locking.
You have to worry about copying.
And if you edit you have to come to the top cell and recopy.
None of those issues happen with dynamic spilled arrays, right? I'll throw it back to MrExcel.
[ Bill Jelen ] This is beautiful.
I expected your first one to be an INDEX, but I never saw the the contracting range coming for ROWS.
That's beautiful.
I always have an expanding range, I've never gone the other way. This is a great solution.
But this, alright… respect.
This is great and I see what you're doing there that it's just a single formula that's going to spill.
And that's awesome, but it's too complicated for me.
I'll have to go see if Tommy Tutone can record a song so I can remember this.
Alright, here's the one.
This is the one I think this is the one that is really cool. Although let's see.
We don't need Microsoft 365. But it has to be less than 255 items.
And there can't be any duplicates in the list. I'm going to choose the list.
File, Options, Advanced. Scroll all the way down to the bottom.
Edit Custom Lists and memorize that list as a custom list.
That's why that's be less than 254.
OK, so the list of course is already in the correct sequence.
Click OK, click OK, But then Data, Sort.
Over here in the order I can choose a custom list.
Which doesn't seem like it's going to help, because the list is still in the correct order. But this is so wild.
I've seen this before and think, when would I ever use this?
Clearly it was just waiting for this duel.
They now offer the list in the forward direction or the list in the backwards direction! How cool is that?
Click OK. Done, no need for any helper columns at all.
Let's see what you have, Mike.
[ Mike Girvin ] MrExcel - you definitely get the extra points because I don't have a method that's not a formula.
But what if I want a custom function that I can use anytime to flip any column?
Well that's where LAMBDA comes in. Now the only input we're going to need.
And we have to give this variable a name: column to flip.
And that name will show up in the screen tip when we use this later.
And now we create our formula. And I'm just going to steal your awesome SORTBY.
And the array?
Well, I'm a bad typer so I'm going to click and copy. Ctrl+V, comma.
By array?
SEQUENCE and then I want the number of rows, Ctrl+V. And then columns, we are going to skip.
Start, we are going to skip. Step minus 1.
Close parenthese on SEQUENCE. Close on SORTBY.
Close on LAMBDA.
And I want to test it before I put it up in define name So I'll Open parentheses, and highlight, Close parentheses and when I hit Enter - Bam, there's LAMBDA to flip this.
Now we copy everything except for the testing part.
Control+C, Enter. Control+F3 to open up Name Manager.
New.
I'm going to call this something really easy to understand like flip column.
And then down here, equals Control+V and that's it. Click OK, click Close.
Now I'm going to flip this awesome column of text equals flip and there it is.
And look at the screen tip.
It's even got column to flip and when I hit enter, Bam!
There's Lambda to flip but no extra point, because that's still a formula. Alright, I'll throw it back to MrExcel.
[ Bill ] Well, I’ll be. I was convinced you were doing Power Query.
I mean power query. How did you not do power query?
But you blew me away with this LAMBDA.
What a great formula and once it's in Defined Name that you can flip anything. I love that.
For me this gets the point, but let's take a look at Power Query.
Well, you know Mike is Thanksgiving Day, right?
But that's not going to stop your viewers or my viewers from yelling at us for not using power query.
So watch this.
I am going to create a named range here of FlipMe. And then Data, From Sheet.
There's my data.
I'm going to add a column and index column frrom zero.
And then the Home tab do Z to A to flip it and then remove. Right.
Now watch this. Close and Load, Close and Load To.
And in real life, I'm going to go all the way out to that column Z or somewhere where no one is going to see it. But for right now we'll put it right there.
Click OK. Alright, then this.
Equal. Double Click to copy that down.
Hide column C.
Alright, but column C still contains a real live query. So every time I do Refresh All, it flips.
And I have a formula up here to see if Spinosaurusus is the top item to do ascending or descending.
How about that? Ah, that was a lot of fun.
A Thanksgiving surprise for you.
If you like these videos, please down below, Like, Subscribe, Ring the bell for both the ExcelisFun channel and the MrExcel.com channel.
Feel free to post any questions or comments down in the comments below.
Well hey, I want to thank you for stopping by.
We’ll see you next time for another Dueling Excel Podcast from MrExcel and ExcelisFun.
Let’s hear you, Nancy!
