Sorting Columns Left to Right in Excel

by Gregory on February 16, 2011

Sort Left to Right Theme PicOnce 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. Sort Left to Right Data BeforeHere’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.

Sort dialog box default settings

Click the Options button, then in the Sort Options dialog box select Sort left to right, and click OK.

Sort options dialog box

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.

Sort dialog box Sort by Row 1

The data is sorted left to right by using the first column.

Sort Left to Right Data After

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.

Sort Left to Right with row above

Now go through the same steps we did before, except the Row 1 sort Order will be Smallest to Largest.

Sort dialog box Sort Order Smallest to Largest

Once sorted you can delete the row above the header and we’re done. Excel has done all the heavy lifting.

Sort example 2 after

Now you can use this Sort feature to make your life easier.

Related Posts Plugin for WordPress, Blogger...
Khushnood Viccaji February 16, 2011 at 10:39 pm

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 ?

Khushnood

Gregory February 16, 2011 at 11:41 pm

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.

talismaaniac January 3, 2013 at 10:15 pm

Nice!! Awesome..

Sarah April 14, 2013 at 6:13 pm

I think I love you. you saved me so much time and trouble. genius in its simplicity!!!!!

Laurence May 9, 2013 at 4:29 am

THANKS!! Awesome…

Comments on this entry are closed.

Previous post:

Next post: