MVP #13: How to sort data across a row, from left to right in Excel

, , 2 comments
Hi there!
This is a very special how-to, you don't get to see this just anywhere. In fact, lots of people aren't aware that this feature exists in Excel. Yet it can make your spreadsheet life a lot easier.

Just imagine you have a sales report to work on, having the sales persons in column A, the months in row 1 and the sales figure in the other cells. And then you discover that the months are not arranged in the proper order, February is not beside March and July is right next to January. How do you fix that? Your regular sort won't do it. You need something less used, a secret Excel tool called custom sort.

Here is a sample data to better illustrate.

The data above is an Excel Table. And needs to be converted first to regular cell entries. But lets play ignorant and see what will happen.


Select the months and the entries under them (omitting only the Sales person's column)


Rightclick and select Sort, Custom Sort...

A dialog box comes up.


Click on Options.


Notice that the Sort left to right option is greyed out. Well, that's the very option we need.
And the way to fix this problem is to convert the Table to regular cell range.

Here's how to do that. 
Rightclick on any cell in the table, select Table, Convert to Range


Confirm Yes. That will do.


Then repeat the previous steps.


Now you'll notice that the option to Sort left to right is selectable. 
So, select it. Click on OK.


Choose Row 1 ( the row that has the months you want sorted properly). Click on OK.


You'll get a prompt like that above. It happens whenever the selected cells are a mixture of texts and numbers.
Just go with the default and click OK.
And here is the final output.


Congratulations! You've just done an advanced sorting!

2 comments:

  1. Michael
    Many thanks for this...I've been searching for hours to find out how to do this!

    ReplyDelete

You can be sure of a response, a very relevant one too!

Click on Subscribe by Email just down below the comment box so you'll be notified of my response.

Thanks!