Sorting is one of the many good things excel offers. And it does make our job a lot easier once the data is arranged in particular order. However there is one limitation to the usual use of sorting i.e. it sorts in vertical fashion that is columns are sorted top to bottom in the order you desire. But what if your data is presented horizontally and you want to sort it from left to right? Well we are not really out of luck even in this case.
Method 1: Transpose-Sort-Transpose
Not so practical but workable. Let’s say your data is presented like this:
Step 1: Select the data and Copy
Step 2: Now anywhere in the worksheet, I prefer a new worksheet, paste the data but instead of Ctrl+V press Alt+Ctrl+V. Yes this is not paste, it is paste special. A dialogue box will popup, In it just check the option Transpose and click ok. Now your data is presented in vertical form.
Step 3: Sort the data in the order you like
Step 4: Transpose the data back to its original horizontal state repeating step 2 above.
Watch the following animation to understand how these steps are carried out:
Although it does get the job done. But it is quite unprofessional and also lengthy process and above all error prone too. So why don’t we just fix our sort functionality to sort horizontally from left to right instead of top to bottom
Sort out the Sort facility – Easy method
Practical, workable and professional method is that you tame your sort function to your needs instead of hassling with transpose-sort-transpose
Step 1: Select your data
Step 2: Click Data tab in the ribbon above and i the Sort and Filter group click Sort button.
Step 3: From the dialogue box click options button and from second dialogue click Sort left to right orientation. Click ok. Doing this now Sort will now work on row basis horizontally from left to right as opposed to sorting on column basis from top to bottom.
Step 4: Select the row that you want to sort from the Row drop down list. Rest of the things are pretty understandable from this point forward
Caution: If your data has headings then exclude the headings from selection otherwise it will also be considered while sorting. Watch the animation above closely that headings at the left were excluded from selection before data is sorted
So there you have it. Sort option sorted for horizontal sorting in excel
Nice, I was not aware of this option! Thanks.
Re “Caution: If your data has headings then exclude the headings from selection otherwise it will also be considered while sorting.”; isn’t it easier to chek the field “My data has headers”? That’s what I do when sorting by column, and in that case, drop-down menus in “Sort by” field nicely show actual column names, instead of generic “column 3, column 4”, etc, or “row 3, row 4” like in this case.
I want to filter vertically.. not a drop down.. drop right data.