Once upon a time I had a master worksheet with data in predefined columns that was used for uploading data into a legacy system. Users would submit data in worksheets they developed, but oftentimes their columns didn’t always match that of the master worksheet.
So I used a little known option in the Sort feature of Excel to quickly rearrange their columns of data to match the master worksheet. This made it a snap to copy data from one worksheet to the other.
Default Sorting in Excel is top to bottom, but there’s another option to sort from left to right. Here’s a quick example on some bogus data so you can get a feel for how this feature works.
The column headers, from left to right are: Delta, Bravo, Alpha, Charlie. We want them to be in alphabetical order: Alpha, Bravo, Charlie, Delta.
Select a cell in the data range and choose Data > Sort. In the Sort dialog box, un-check the box for My data has headers.
Click the Options button, then in the Sort Options dialog box select Sort left to right, and click OK.
In the Sort dialog box, click the Sort by drop-down arrow and select Row 1. Make sure the Sort On selection is Values and Order selection is A to Z, then click OK.
The data is sorted left to right by using the first column.
Change the Example
Now let’s assume that we need the column order: Bravo, Delta, Charlie, Alpha. What to do? Well this is more like a real-life example that only takes a couple more steps to complete.
We need a blank row above the data, so right-click row 1 and select Insert from the pop-up menu. Enter 1 above Bravo, 2 above Delta, 3 above Charlie, and 4 above Alpha.
Now go through the same steps we did before, except the Row 1 sort Order will be Smallest to Largest.
Once sorted you can delete the row above the header and we’re done. Excel has done all the heavy lifting.
Now you can use this Sort feature to make your life easier.
Khushnood Viccaji says
I had even tried this feature long ago in Excel 97, but in that version, the big problem was that the re-sorted column data would not retain the original cell formatting.
That is, Excel would sort only the data VALUES, and not the CELL FORMATS along with the data.
This would create havoc when date column data would be moved to numeric format columns, (or vice-versa) and the display would show dates as numbers.
Seeing this post, I tried it out once again in Excel 2007.
And it appears that this problem has been fixed 🙂
However, I still have one more problem: the column widths don’t adjust accordingly !
Has that been fixed in Excel 2010 ?
In Excel 2010 formatting is retained when sorting left to right, but not the column widths. Additionally, a simple formula lost it’s reference when sorted one way, but was okay when I sorted in a different order. Strange. So I guess when sorting from left to right in Excel we have to autofit the columns and check all the formulas.
I think I love you. you saved me so much time and trouble. genius in its simplicity!!!!!