In yesterdays podcast, I showed how to rearrange one column. But what if you need to rearrange all of the columns? Episode 544 shows how to use a little known sorting trick to sort the columns back to the original sequence.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast, I'm Bill Jelen.
In yesterday's netcast we talked about if we had to rearrange one column to another place it was a simple cut and then insert cut cells that reminded me there's this really cool trick in case your columns are completely in the wrong sequence, we have a data set here and I want to basically rearrange every single column and so I'm going to insert a new row just above the data set, and I'm going to add sequence number so I'm going to let's say customer numbers first street, is second, city's third, state and zip are fourth and fifth, and then probably invoice number, invoice date and amount.
Now here's what I want to do I'm going to go to the data menu and choose sort data sort and in the options tab I'm going to say instead of sorting top to bottom which is the usual way, I want to sort left to right, now once I do this then instead of headings I basically just can sort by Row 1, Row 2, Row 3, well of course I want to sort by Row 1 and ascending sequence and click OK, and is now rearranged all the columns it doesn't keep the column widths with the original columns so of course you want to go through and select all of your data and then use format column autofit selection in order to make everything in line.
Now in Excel 2007 of course they change the sorting routine a little bit same thing you'll insert a new row with the different numbers go to the data tab of the ribbon, click the big sort button, and then there's an options button at the top or we can say that we want to sort left to right, and then of course you'll be able to say that it's going to be by Row 1 smallest to largest, click OK and again it'll take care of it in Excel 2007.
So most of the time when we're sorting we're sorting the Rose I'm in this case though it's very unusual we can sort the columns basically just add a temporary row up there with the correct column sequence, and you can sort the data into the right sequence.
Hey thanks for stopping by, we'll see you on Monday for another netcast from MrExcel.
In yesterday's netcast we talked about if we had to rearrange one column to another place it was a simple cut and then insert cut cells that reminded me there's this really cool trick in case your columns are completely in the wrong sequence, we have a data set here and I want to basically rearrange every single column and so I'm going to insert a new row just above the data set, and I'm going to add sequence number so I'm going to let's say customer numbers first street, is second, city's third, state and zip are fourth and fifth, and then probably invoice number, invoice date and amount.
Now here's what I want to do I'm going to go to the data menu and choose sort data sort and in the options tab I'm going to say instead of sorting top to bottom which is the usual way, I want to sort left to right, now once I do this then instead of headings I basically just can sort by Row 1, Row 2, Row 3, well of course I want to sort by Row 1 and ascending sequence and click OK, and is now rearranged all the columns it doesn't keep the column widths with the original columns so of course you want to go through and select all of your data and then use format column autofit selection in order to make everything in line.
Now in Excel 2007 of course they change the sorting routine a little bit same thing you'll insert a new row with the different numbers go to the data tab of the ribbon, click the big sort button, and then there's an options button at the top or we can say that we want to sort left to right, and then of course you'll be able to say that it's going to be by Row 1 smallest to largest, click OK and again it'll take care of it in Excel 2007.
So most of the time when we're sorting we're sorting the Rose I'm in this case though it's very unusual we can sort the columns basically just add a temporary row up there with the correct column sequence, and you can sort the data into the right sequence.
Hey thanks for stopping by, we'll see you on Monday for another netcast from MrExcel.
